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
dbms_output.put_line ('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

-------------

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;

Please complete the following information:

REGISTER / LOG-IN TO DISCUSS

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

Submit your e-mail address below to continue

E-mail: 

E-mail: 

Handle:   

Password: 

Forgot Password?

Create Handle: 

Your handle identifies you in ITKnowledgeExchange. Max 30 char.

Create Password: 

Confirm Password: 

Yes, I agree to the IT Knowledge Exchange  Terms and Conditions.

hidden modal window

EXECUTE proc_add_new_csdba;