How to create multiple users using a procedure with a loop

Question

How to create multiple users using a procedure with a loop

shawndidy

Say Hey Kid

Points: 687
More actions
April 22, 2012 at 2:57 pm

#256771

Hi,
Please I need to create multiple users using a procedure with a loop.
When I execute the procedure, I keep getting an error.
Please see below
CREATE OR REPLACE PROCEDURE create_user (
username varchar2, password varchar2, staff_id number)
AUTHID current_user
IS
BEGIN
for rec in 1..3 loop
DBMS_OUTPUT.PUT_LINE('USERNAME'|| 'username created' );
EXECUTE IMMEDIATE 'create user '||username||' identified by'||password|| ' DEFAULT TABLESPACE USERS';
EXECUTE IMMEDIATE 'GRANT' || staff_id|| 'TO' || username ||'';
I need to create multiple users using a procedure with a loop.
When I execute the procedure, I keep getting an error.
Please see below
DBMS_OUTPUT.PUT_LINE('role'|| 'role granted to username' );
END Loop;
END create_user;
/
BEGIN create_user; END;
*
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'CREATE_USER'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
Thank you.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

SQLServerCentral.com is a MS SQL Server site. Your code is Oracle and you will probable get much better help from a site that is Oracle centric.

Lowell SSC Guru Points: 323518 More actions · Answer 2

additionally, when you append strings together to create commands, you need to include spaces between the words.

'GRANT' || staff_id|| 'TO' || username ||'';

will make a string like "GRANTstaff_idTOLowell"

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

vinu512 SSCoach Points: 15729 More actions · Answer 3

By executing the procedure do you mean executing the above code you showed us(creating the procedure) or do you mean running the procedure?

PLS-00306: wrong number or types of arguments in call to 'CREATE_USER'

From the above error, I think, it is not the procedure that is wrong, its is the way you are calling it that is wrong.

Anyways its Oracle and would be answered much better on an Oracle Forum.

Vinu Vijayan

For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden[/url] 😉

shawndidy Say Hey Kid Points: 687 More actions · Answer 4

Hi Guys,

Thank you for your contribution guys. My sister's doing oracle pl/sql so i thought SQL Servercentral Gurus could be of help. I've advised her to post it in an oracle centric forum.

Appreciate your help! 🙂