ORA-01722 invalid number despite not casting anything to number

95 views Asked by At

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.

0

There are 0 answers