CODEWITHSUNDEEP
sqloracle-databaseif-statementprocedure
ChantelleL
ChantelleL

Reputation: 47

Oracle Procedure with an IF-THEN-ELSE produces error

I have created this procedure in Oracle, to assign a role to a user based on the grade stored in the grade column of the marketing table. However, when I run it I get errors.

Initial Problem

CREATE OR REPLACE PROCEDURE proc_assign_role IS
    vn_grade NUMBER(5);

CURSOR cur_user_grade IS
    SELECT grade, username
    FROM marketing
    WHERE grade BETWEEN 1 AND 3;
BEGIN
    FOR rec_cur_user_grade IN cur_user_grade
    vn_grade:=
    IF grade= 1
        THEN 
            GRANT ROLE admin_staff;
        ELSIF grade= 2 THEN
            GRANT ROLE marketing_staff;
        ELSIF grade= 3 THEN
            GRANT ROLE event_staff;
    END IF;
    DBMS_OUTPUT.PUT_LINE(username||'YOU ARE A GRADE '||vn_grade|| 'USER');
END proc_assign_role;
/

This is the error I get:

ERROR at line 11: PLS-00103: Encountered the symbol "VN_GRADE" when expecting one of the following:

   . ( * @ % & - + / at loop mod remainder range rem ..
    || multiset
1. CREATE OR REPLACE PROCEDURE proc_assign_role IS
2.  vn_grade NUMBER(5);

Upvotes: 0

Views: 642

Answers (3)

Allan
Allan

Reputation: 17429

grant is DDL and therefore cannot be used in PL/SQL directly. In order to accomplish this, the DDL needs to be executed dynamically, using execute immediately. Additionally, grant always requires you to specify the recipient of the role. The result would be something like this:

execute immediate 'GRANT ROLE admin_staff to ' || rec_cur_user_grade.username;

An ORA-00990: missing or invalid privilege error is fairly self-descriptive: the owner of the procedure does not have the necessary privileges to take the actions being attempted by the procedure.

The most likely culprit here is roles: permissions granted by a role cannot be used in a procedure. The first step you should take is to make sure that the owner of the procedure has been explicitly granted permission to administer the roles involved.

Upvotes: 0

Daren Beattie
Daren Beattie

Reputation: 76

I'm seeing a few things that would keep this from working:

  • After your FOR statement, there's no LOOP statement (which is what the error is complaining about). There's also no END LOOP after your DBMS_OUTPUT.
  • vn_grade is followed by the := assignment operator, but nothing is being assigned to it.
  • The GRANT statements are written as bare DDL, which isn't allowed in PL/SQL. They need to be wrapped in EXECUTE IMMEDIATE.
  • grade and username need to be qualified by the cursor variable (e.g., rec_cur_user_grade.grade and rec_cur_user_grade.username).

Try something like this (which runs as an anonymous block, rather than a procedure, and uses an implicit cursor):

BEGIN
    FOR rec_cur_user_grade IN (
        SELECT grade, username
        FROM marketing
        WHERE grade BETWEEN 1 AND 3
    )
    LOOP
        CASE rec_cur_user_grade.grade
            WHEN 1 THEN
                EXECUTE IMMEDIATE 'GRANT ROLE admin_staff TO ' || rec_cur_user_grade.username;
            WHEN 2 THEN
                EXECUTE IMMEDIATE 'GRANT ROLE marketing_staff TO ' || rec_cur_user_grade.username;
            WHEN 3 THEN
                EXECUTE IMMEDIATE 'GRANT ROLE event_staff TO ' || rec_cur_user_grade.username;
        END CASE;
        DMBS_OUTPUT.PUT_LINE(rec_cur_user_grade.username || ' YOU ARE A GRADE ' || rec_cur_user_grade.grade || ' USER');
    END LOOP;
END;
/

Upvotes: 1

mason
mason

Reputation: 32694

vn_grade:=

You need to assign a value to that line, or get rid of it. You can't assign an IF statement to a number variable. Probably get rid of it, then change your IF statement to look at the grade from the cursor. You also need to end your loop.

Additionally, you can't do a grant directly within a PL/SQL code block. You have to use the execute immediate statement for that. And you have to tell it who you're granting the role to.

FOR rec_cur_user_grade IN cur_user_grade LOOP
    IF rec_cur_user_grade.grade= 1 THEN 
        execute immediate 'GRANT ROLE admin_staff to ' || rec_cur_user_grade.username;
    ELSIF rec_cur_user_grade.grade= 2 THEN
        execute immediate 'GRANT ROLE marketing_staff to ' || rec_cur_user_grade.username;
    ELSIF rec_cur_user_grade.grade= 3 THEN
        execute immediate 'GRANT ROLE event_staff to ' || rec_cur_user_grade.username;
    END IF;
DBMS_OUTPUT.PUT_LINE(username||'YOU ARE A GRADE '||rec_cur_user_grade.grade|| 'USER');
END LOOP;

Upvotes: 2

Related Questions