0

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);
Improve this question
3
  • vn_grade:= there is an expression missing there and it's not terminated with a ; Commented Feb 19, 2015 at 14:42
  • I put the initial problem back in the question. The goal of this site is not just to solve the OP's issue, but to provide guidance to people that have similar problems in the future. In order for the evolving answers to make sense, the evolution of the question needs to be apparent as well. Commented Feb 19, 2015 at 15:39
  • Your latest error ORA-00990: missing or invalid privilege just means you need to grant the user you're running the procedure as the necessary privileges to perform the grant. Commented Feb 19, 2015 at 15:50

3 Answers 3

Reset to default
2

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;
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

In doing that, I now get the error ERROR at line 19: PLS-00103: Encountered the symbol "PROC_ASSIGN_ROLE" when expecting one of the following: loop
thanks, i understand that, but still getting error ERROR at line 15: PLS-00103: Encountered the symbol "GRANT" when expecting one of the following: begin case declare exit for goto if loop mod null pragma raise return select update while with
1

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;
/
Improve this answer

1 Comment

Just so you know, you can select an entire section of code, then use the code format button. Then your code is grouped into one section, instead of having to make each line a code block (this adds 4 spaces to the front of each selected line to mark it as code). This also has the benefit of doing code color highlighting.
0

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.

Improve this answer

8 Comments

Thanks, this fixes the other error, but now gives: ERROR at line 18: PLS-00201: identifier 'REC_CUR_USER_GRADE.GRADE' must be declared
If i take out the DBMS_Output line, it says procedure created, so i assume i need to declare the rec_cur_user_grade.grade somewhere?
Please edit your question to add the latest version of your code (in addition to the initial version). My guess is that the DBMS_OUTPUT line is outside of the loop, so the rowtype variable created by the loop is out-of-scope.
@ChantelleL You did declare it. In your for loop. You created REC_CUR_USER_GRADE which is going to hold a single row from your cursor, and the grade column is in that cursor.
@ChantelleL That belongs in the question. Please edit your question to include that information. Always remember on Stack Overflow. Code and error messages should be placed in your question, because comments are considered ephemeral.
|

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.