oracle - How to write pl/sql code with return type boolean for user authorization?

Question

I am trying to implement User Authentication with pl/sql function.

DECLARE
roleId RAW(16);
userRole User_Role%ROWTYPE;
BEGIN
    select role_id
    into roleId
    from role
    where role_name='guest';
    BEGIN
        select 1
        into userRole
        from   user_role
        where  user_id = :APP_USER 
        and role_id = role_id;
        return true;
        exception when no_data_found
        then return false;
    END;
END;

With the above code I want to get 'role_id' of guest role and then try to find a record with that role id and app user in a 'user_role' junction table. But when I run this code the follow error occurs:

How can I solve this?

标签： oracleplsqloracle-apex

Answer 1

You have defined a variable userrole using the %rowtype syntax. This means the variable is aligned with the projection of the USER_ROLE table. However, you are selecting a literal 1 instead of columns, so the projection of the query does not match the target variable.

Either change the target variable to be a simple number type, or change the query to select * from user_role.

Incidentally, your WHERE clause is wrong: instead of role_id = role_id you should have coded role_id = roleid.

I think roleid is a poor name for a local variable, because it is so similar to the column name that it's easy to make mistakes: the convention of using a prefix like l_ highlights local variables (or their absence).