Oracle PL/SQL Functions: “Invalid Datatype”

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?

Answer

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.