Oracle PL/SQL Functions: "Invalid Datatype"

Question

The following function checks to see if a zip code is present in a table called ZIPCODE:

create or replace function zip_does_not_exist(i_zip in zipcode.zip%type)
return boolean
as
v_zip zipcode.zip%type;
begin
select zip into v_zip
from zipcode
where zip = i_zip;
if v_zip is not null then
return false;
else
return true;
end if;
end;
/

The schema for the ZIPCODE table is as follows:

To test this function, I issued the following statement which should return false as the zip code passed as an argument is present in the ZIPCODE table:

select zip_does_not_exist('00914') from dual;

However, I receive this message when trying to run this code:

Error report:
SQL Error: ORA-00902: invalid datatype
00902. 00000 -  "invalid datatype"
*Cause:    
*Action:

This should not be happening, as '00914' is the proper datatype needed as an argument (varchar2(5)). Why am I receiving this message?

To explain Barbaros's answer a little further: PL/SQL and (Oracle's dialect of) SQL are closely integrated, but they are not the same. Perhaps the most significant difference is that SQL does not have the boolean data type, while PL/SQL does. You may ask "what good is it in PL/SQL if I can't use it." The answer is "you can use it, but only in other PL/SQL code - not in SQL". If you need to use a value like that in SQL code (and often you do), make the return data type number and the return values 0 and 1 (for false and true), or char(1) with values 'F', 'T' or maybe 'N', 'Y'. — user5683823
– user5683823, Commented Feb 20, 2018 at 20:02
@mathguy thank you for nice explanation colleague(mathematician). — Barbaros Özhan
– Barbaros Özhan, Commented Feb 20, 2018 at 20:37

Barbaros Özhan · Accepted Answer · 2020-06-22 07:54:49Z

6

Try to create the stored procedure as :

create or replace function zip_does_not_exist(i_zip in zipcode.zip%type)
  return pls_integer as
  v_zip zipcode.zip%type;
begin
  select zip into v_zip from zipcode where zip = i_zip;
  if v_zip is not null then
    return 0;
  else
    return 1;
  end if;
end;
/

Then you'll be successful to call :

select zip_does_not_exist('00914') from dual;

Since, a boolean can not be set as returning variable for a sql statement.

edited Jun 22, 2020 at 7:54

answered Feb 20, 2018 at 19:09

Barbaros Özhan

65.9k11 gold badges36 silver badges64 bronze badges

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

Collectives™ on Stack Overflow

Oracle PL/SQL Functions: "Invalid Datatype"

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