Oracle + iSQL*Plus – PLS-00103: Encountered the symbol “GRANT”

55 pts.
Tags:
iSQL*Plus
Oracle
Oracle error messages
PLS-00103
Hi, i am trying to create some new csdba users and assign some roles to them however i keep getting the following error message - 7/22 PLS-00103: Encountered the symbol "GRANT" when expecting one of the following: * & | - + / at mod remainder rem => .. <an expo nent (**)> || multiset this is the code which i have entered and really can't solve this error message ************************* set serveroutput on; create or replace procedure proc_add_new_csdba AUTHID CURRENT_USER as begin character_value := to_char (VAR2); dbms_output.enable(20000); FOR VAR2 in 1..10 LOOP dbms_output.put_line ('grant create user csdba'||to_char (VAR2)|' identified by csdba'||to_char (VAR2)|'); --execute immediate 'grant create user csdba'||to_char (VAR2)' identified by csdba'||to_char (VAR2)|'); execute immediate 'grant create session to csdba'||to_char (VAR2)|'); execute immediate 'grant cs_reps_role to csdba'||to_char (VAR2)|'); execute immediate 'grant cs_admin_role to csdba'||to_char (VAR2)|'); execute immediate 'grant cs_sales_role to csdba'||to_char (VAR2)|'); execute immediate 'grant cs_clerk_role to csdba'||to_char (VAR2)|'); execute immediate 'grant cs_manager_role to csdba'||to_char (VAR2)|'); execute immediate 'grant cs_programmer_role to csdba'||to_char (VAR2)|'); execute immediate 'grant cs_dba_role to csdcsdba'||to_char (VAR2)|'); END LOOP; end proc_add_new_csdba; / show errors; --execute proc_add_new_csdba; Any help would be greatly appreciated Thanks

Answer Wiki

Thanks. We'll let you know when a new response is added.

hi again

i have now ammeded the code slightly as realised there was an error however i still end up with a similar message

7/22 PLS-00103: Encountered the symbol “CREATE” when expecting one of the following: * & | – + / at mod remainder rem => .. <an exp onent (**)> || multiset

this is my code now

set serveroutput on;
create or replace procedure proc_add_new_csdba AUTHID CURRENT_USER as
begin
character_value := to_char (VAR2);
dbms_output.enable(20000);
FOR VAR2 in 1..10 LOOP
<b>dbms_output.put_line (‘create user csdba’||to_char (VAR2)|’ identified by csdba’||to_char (VAR2)|’);</b>execute immediate ‘grant create session to csdba’||to_char (VAR2)|’);
execute immediate ‘grant cs_reps_role to csdba’||to_char (VAR2)|’);
execute immediate ‘grant cs_admin_role to csdba’||to_char (VAR2)|’);
execute immediate ‘grant cs_sales_role to csdba’||to_char (VAR2)|’);
execute immediate ‘grant cs_clerk_role to csdba’||to_char (VAR2)|’);
execute immediate ‘grant cs_manager_role to csdba’||to_char (VAR2)|’);
execute immediate ‘grant cs_programmer_role to csdba’||to_char (VAR2)|’);
execute immediate ‘grant cs_dba_role to csdcsdba’||to_char (VAR2)|’);
END LOOP;
end proc_add_new_csdba;
/
show errors;
–execute proc_add_new_csdba;

Any help would be greatly appreciated

Thanks

————-

This code has many errors.

What is the purpose of the procedure ? is it to create users and grant privileges, or is it to generate the script to do it ?

If the former, try this code:

set serveroutput on;
procedure proc_add_new_csdba AUTHID CURRENT_USER as
begin
dbms_output.enable(20000);
FOR VAR2 in 1..10 LOOP
–dbms_output.put_line (‘create user csdba’||to_char (VAR2)||’ identified by csdba’||to_char (VAR2));
execute immediate ‘create user csdba’||to_char (VAR2)||’ identified by csdba’||to_char (VAR2);
execute immediate ‘grant create session to csdba’||to_char (VAR2);
execute immediate ‘grant cs_reps_role to csdba’||to_char (VAR2);
execute immediate ‘grant cs_admin_role to csdba’||to_char (VAR2);
execute immediate ‘grant cs_sales_role to csdba’||to_char (VAR2);
execute immediate ‘grant cs_clerk_role to csdba’||to_char (VAR2);
execute immediate ‘grant cs_manager_role to csdba’||to_char (VAR2);
execute immediate ‘grant cs_programmer_role to csdba’||to_char (VAR2);
execute immediate ‘grant cs_dba_role to csdcsdba’||to_char (VAR2);
END LOOP;
end proc_add_new_csdba;

Discuss This Question: 5  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Cazza88
    hi, the purpose of this procedure is to create users and grant roles. the procedure is now not getting any errors, however it is not actually creating any users when i run it. Thanks
    55 pointsBadges:
    report
  • carlosdl
    It should. Keep in mind that we are just creating the procedure with the above code, not running it. are you running it this way ?
    EXECUTE proc_add_new_csdba;
    If so, are you getting errors ?
    69,510 pointsBadges:
    report
  • Cazza88
    Theprocedure creates but when i run it i get these errors Procedure created. BEGIN proc_add_new_csdba; END; * ERROR at line 1: ORA-01917: user or role 'CSDCSDBA1' does not exist ORA-06512: at "INS3011_103.PROC_ADD_NEW_CSDBA", line 14 ORA-06512: at line 1
    55 pointsBadges:
    report
  • carlosdl
    Yes, it was a typo. Replace the line before END LOOP (i.e. execute immediate 'grant cs_dba_role to csdcsdba'||to_char (VAR2);) with this: execute immediate 'grant cs_dba_role to csdba'||to_char (VAR2);
    69,510 pointsBadges:
    report
  • Cazza88
    Hey, Thanks for all your help i can't believe i missed it!!! Thanks Again :-)
    55 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following