PL/SQL FUNCTION WITH VARCHAR PARAMETER

Question

These are the errors that I got. https://i.sstatic.net/hBB2e.png

The question asked for people who is born after 30th June 1990 to take injection. It asks the user to enter his/her id. I got error saying that 'literal does not match format string'. I don't know how to return varchar in plsql function. Here is my code:

 create or replace function p_immune (ptdob in date)
 return varchar2 
 is sta_imm varchar2(30);
 
  BEGIN 
    if ptdob > '30th June 1990 ' then sta_imm := 'REQUIRED'; 
    else sta_imm := 'NOT REQUIRED'; 
    end if;
   return(sta_imm);
  END p_immune; /

 Accept pt_id prompt 'Enter the patient ID: '
 DECLARE
 v_dob patient.ptdob%type; v_ptdob patient.ptdob%type;

 BEGIN
     select ptdob  
     into v_dob
     from patient
     where pt_id = &pt_id;

   dbms_output.put_line('Enter the patient ID: '||&pt_id);
   dbms_output.put_line('The status of X-immunization :'||p_immune(v_ptdob));
 END; /

Littlefoot · Accepted Answer · 2022-01-03 08:29:12Z

2

Use date literal:

if ptdob > date '1990-06-30' then sta_imm := 'REQUIRED';

or TO_DATE with appropriate format mask (and language):

if ptdob > to_date('30th June 1990', 'ddth Month yyyy', 'nls_date_language = english') then sta_imm := 'REQUIRED';

Also, make sure you pass DATE datatype value to the function. How? As described above. Don't pass strings.

answered Jan 3, 2022 at 8:29

Littlefoot

144k15 gold badges41 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PL/SQL FUNCTION WITH VARCHAR PARAMETER

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related