I am figuring out PL/SQL for the first time for a school project, and I have to write a package with a boolean function which takes a user id and a password, and checks if that user has that password. Here is the relevant part of the package body.
create package body user_management as
-- more functions
function check_password(
id in users.id%type,
password in varchar
) return boolean is
valid number;
begin
select ( -- Error occurs here
case when exists (
select * from users u
where (u.username = id)
and (hash_password(password) = u.password)
) then 1 else 0 end
) into valid from dual;
if valid = 1 then
return true;
else
return false;
end if;
end;
-- more procedures
end user_management;
However, I get the error 'invalid number' at the begining of the select statement whenever I try to call the function. I have googled this error, and all results say that this error happens when failing to convert a string to a number. But I don't think I am ever doing that here. The same error occurred when my select query selected a count(*) instead of using cases as I am doing now.
Why am I seeing this error, and how can I fix it? Any help would be greatly appreciated.