CODEWITHSUNDEEP
oracle-databaseplsql
Sherine Amoit
Sherine Amoit

Reputation: 1

ORA-00902: invalid datatype 00902. 00000 - "invalid datatype" *Cause: *Action: Error at Line: 1 Column: 8

I have an SQL function to check age from a table. when i run the function i keep on getting the invalid datatype error. The datatype are the same and the error is at the column where CHECK_30 starts. my function also complied and ran smoothly.ORA-00902: invalid datatype 00902. 00000 - "invalid datatype" *Cause:
*Action: Error at Line: 1 Column: 8

CREATE OR REPLACE FUNCTION CHECK_30(LASTNAME staff03.lname%type)
RETURN BOOLEAN
IS
dofb staff03.dob%type;

cursor c1 is
     SELECT dob
     FROM staff03
     WHERE lname = LASTNAME; 
     
BEGIN
   open c1;
   fetch c1 into dofb;
   close c1;

IF dofb < '1992-11-15' THEN
 DBMS_OUTPUT.PUT_LINE('Older than 30');
  RETURN TRUE;
  ELSE
  DBMS_OUTPUT.PUT_LINE('Young in');
    RETURN FALSE; 
 END IF;
END;

SELECT CHECK_30('NELSON') from dual;

Upvotes: 0

Views: 442

Answers (1)

William Robertson
William Robertson

Reputation: 15991

Oracle SQL currently has no Boolean datatype (as of version 21c), so you cannot call a function that returns a Boolean value from a SQL query. You can only use it within PL/SQL code.

So you can't do this, because SQL can't handle the result:

select check_30('NELSON') from dual;

but you can do this within a PL/SQL procedure/funtion/package/type:

if check_30('NELSON') then
   dbms_output.put_line('Check passed');
end if;

Upvotes: 1

Related Questions