0

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.

Abraham Murciano Benzadon
  • 1,424
  • 13
  • 27
  • 1
    u.username = id where id gets passed in as users.id%type is a possible comparison between string and number. Also you are in PL/SQL so you don't actually need a SELECT statement, you could instead do this all via your IF checks and avoid the SELECT statement all together. – Aaron Oct 14 '20 at 05:23
  • @Aaron thanks for your help, all id fields are number(8), no strings... Also can you elaborate on how I'd use an if for that? A link will suffice, thanks. – Abraham Murciano Benzadon Oct 14 '20 at 05:26
  • 1
    "all id fields are number(8)" exactly so when you do this: where (u.username = id). you are comparing a string to a number... also I am hesitant to give you the answer as this is a school question - hrm... ok, so basically what you are trying to do in your where statement checks you can use to replace your valid IF statement checks. i.e. SELECT 1 INTO valid FROM table WHERE check = check; can be written as an IF instead: IF check=check THEN blah; ELSE blah; end if; – Aaron Oct 14 '20 at 05:28
  • @Aaron OMG, I'm so stupid... I meant to compare the IDs, idk why I wrote username XD I spent ages trying to figure it out... Thanks so much – Abraham Murciano Benzadon Oct 14 '20 at 05:30
  • 1
    no problem. Glad to help. btw, I was wrong about being able to do away with the select completely - you can clean up the select and make it simpler - You need to either do the check in the select like you are or return the password into a variable and do the checks in the IF instead. – Aaron Oct 14 '20 at 05:34

0 Answers0