OraFAQ Forum: SQL & PL/SQL » Create User using stored procedure

Home » SQL & PL/SQL » SQL & PL/SQL » Create User using stored procedure (oracle, 11.2.0.1.0, windows 7)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Create User using stored procedure [message #550330]

Sun, 08 April 2012 14:19

aubyn19ja
Messages: 42
Registered: January 2012

Member

I'm trying to create a store procedure that will accept a username from a flat file but i don't know how to do read file into store procedure. Can someone help please?

Below is a sample store procedure by itself i created to add user which created okay but when i execute I got the error displayed below. Can I also get some help in fixing this too please?

create or replace procedure addUsers(userNam in varchar2)
is
begin

EXECUTE IMMEDIATE 'CREATE USER'||userNam||'IDENTIFIED BY "pass1234" DEFAULT TABLESPACE USERS'||'QUOTA "1M" ON USERS'||
'PASSWORD EXPIRE';

end addUsers;
/

SQL> create or replace procedure addUsers(userNam in varchar2)
2 is
3 begin
4
5 EXECUTE IMMEDIATE 'CREATE USER'||userNam||'IDENTIFIED BY "pass1234" DEFAULT
TABLESPACE USERS'||'QUOTA "1M" ON USERS'||
6 'PASSWORD EXPIRE';
7
8 end addUsers;
9 /

Procedure created.

SQL> execute addUsers test;
BEGIN addUsers test; END;

*
ERROR at line 1:
ORA-06550: line 1, column 16:
PLS-00103: Encountered the symbol "TEST" when expecting one of the following:
:= . ( @ % ;
The symbol ":=" was substituted for "TEST" to continue.

Report message to a moderator

Re: Create User using stored procedure [message #550331 is a reply to message #550330]

Sun, 08 April 2012 14:41

Its_me_ved
Messages: 979
Registered: October 2009
Location: India

Senior Member

Can use UTL_FILE/External table

execute addUsers test;

Hint:
You are passing empid

exec getempdetails(empid);

Regards
Ved

[Updated on: Sun, 08 April 2012 14:45]

Report message to a moderator

Re: Create User using stored procedure [message #550332 is a reply to message #550330]

Sun, 08 April 2012 14:45

Michel Cadot
Messages: 68774
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

This is not the way to pass parameters to a procedure.
In addition some spaces are missing.

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version, with 4 decimals.

Regards
Michel

Report message to a moderator

Re: Create User using stored procedure [message #550334 is a reply to message #550332]

Sun, 08 April 2012 14:55

aubyn19ja
Messages: 42
Registered: January 2012

Member

Thanks for all the guidlelines Michel and Ved...I'm a beginner at Oracle.

CREATE OR REPLACE PROCEDURE addUsers(userNam in varchar2)
IS
BEGIN
  EXECUTE IMMEDIATE 'CREATE USER'||userNam||'IDENTIFIED BY "pass1234" DEFAULT TABLESPACE USERS'||'QUOTA "1M" ON USERS'||
 'PASSWORD EXPIRE';
END addUsers;
/

Is this how it is to be done? What do you mean by some spaces are missing Michel?

[mod-edit: ending code tag fixed by bb]

[Updated on: Mon, 09 April 2012 01:02] by Moderator

Report message to a moderator

Re: Create User using stored procedure [message #550335 is a reply to message #550334]

Sun, 08 April 2012 14:56

aubyn19ja
Messages: 42
Registered: January 2012

Member

Ved can you give an example how to use UTL_FILE with a procedure please? I have no clue what to do.

Report message to a moderator

Re: Create User using stored procedure [message #550340 is a reply to message #550334]

Sun, 08 April 2012 16:09

Barbara Boehmer
Messages: 9106
Registered: November 2002
Location: California, USA

Senior Member

aubyn19ja wrote on Sun, 08 April 2012 12:55

...What do you mean by some spaces are missing..?

A standard method for debugging dynamic SQL is to store the string to be executed dynamically to a variable, then output it using dbms_output to see what it looks like. You can also take that output and copy and paste it and try to run it from SQL*Plus to see what error it raises. Please see the example below that modifies your code to do that, making it easy to see where the missing spaces are.

SCOTT@orcl_11gR2> CREATE OR REPLACE PROCEDURE addUsers(userNam in varchar2)
  2  IS
  3    v_sql  VARCHAR2 (32767);
  4  BEGIN
  5    v_sql :=
  6  	 'CREATE USER'||userNam||'IDENTIFIED BY "pass1234" DEFAULT TABLESPACE USERS'
  7  	 ||'QUOTA "1M" ON USERS'||'PASSWORD EXPIRE';
  8    DBMS_OUTPUT.PUT_LINE (v_sql);
  9    EXECUTE IMMEDIATE v_sql;
 10  END addUsers;
 11  /

Procedure created.

SCOTT@orcl_11gR2> SHOW ERRORS
No errors.
SCOTT@orcl_11gR2> EXEC addusers ('test')
CREATE USERtestIDENTIFIED BY "pass1234" DEFAULT TABLESPACE USERSQUOTA "1M" ON
USERSPASSWORD EXPIRE
BEGIN addusers ('test'); END;

*
ERROR at line 1:
ORA-00901: invalid CREATE command
ORA-06512: at "SCOTT.ADDUSERS", line 9
ORA-06512: at line 1


SCOTT@orcl_11gR2>

Do you see that there are spaces missing in the following?

CREATE USERtestIDENTIFIED BY "pass1234" DEFAULT TABLESPACE USERSQUOTA "1M" ON
USERSPASSWORD EXPIRE

[Updated on: Sun, 08 April 2012 16:40]

Report message to a moderator

Re: Create User using stored procedure [message #550341 is a reply to message #550340]

Sun, 08 April 2012 16:12

Barbara Boehmer
Messages: 9106
Registered: November 2002
Location: California, USA

Senior Member

I might be more inclined to use an external table to reference the text file, especially if there are multiple user names in it. That way you could loop through them and create a user for each one.

Report message to a moderator

Re: Create User using stored procedure [message #550342 is a reply to message #550341]

Sun, 08 April 2012 16:19

aubyn19ja
Messages: 42
Registered: January 2012

Member

Thanks a million Barbara. I understand the spaces now. Smile

Smile

. But for the file, are you saying put the data in the file into a table and then grab the data from the table?

Report message to a moderator

Re: Create User using stored procedure [message #550344 is a reply to message #550342]

Sun, 08 April 2012 16:39

Barbara Boehmer
Messages: 9106
Registered: November 2002
Location: California, USA

Senior Member

Here is an example of how I might do it.

If you have your user names to be created, one per line, in a text file, like this:
-- contents of c:\my_oracle_files\users.txt:

user1
user2

I would create an Oracle directory object that points to the directory path that the file above is in:

SCOTT@orcl_11gR2> CREATE OR REPLACE DIRECTORY my_cir AS 'c:\my_oracle_files'
  2  /

Directory created.

Then create an external table that uses the directory object above as the default directory and the file name that contains your users as the location.

SCOTT@orcl_11gR2> CREATE TABLE users_ext_tab
  2    (userNam  VARCHAR2 (30))
  3  ORGANIZATION external
  4    (TYPE oracle_loader
  5  	DEFAULT DIRECTORY my_dir
  6  	ACCESS PARAMETERS
  7  	  (RECORDS DELIMITED BY NEWLINE
  8  	   LOGFILE 'test.log'
  9  	   FIELDS TERMINATED BY WHITESPACE LDRTRIM
 10  	   REJECT ROWS WITH ALL NULL FIELDS
 11  	     (userNam))
 12  	location ('users.txt'))
 13  /

Table created.

The result is that when you select from that external table, it reads the data from the file.

SCOTT@orcl_11gR2> select * from users_ext_tab
  2  /

USERNAM
------------------------------
user1
user2

2 rows selected.

Then create the procedure that loops through the table above.

SCOTT@orcl_11gR2> CREATE OR REPLACE PROCEDURE addUsers
  2  IS
  3    v_sql  VARCHAR2 (32767);
  4  BEGIN
  5    FOR r IN (SELECT userNam FROM users_ext_tab) LOOP
  6  	 v_sql := 'CREATE USER "' || r.userNam || '" IDENTIFIED BY "pass1234"';
  7  	 DBMS_OUTPUT.PUT_LINE (v_sql);
  8  	 EXECUTE IMMEDIATE v_sql;
  9    END LOOP;
 10  END addUsers;
 11  /

Procedure created.

SCOTT@orcl_11gR2> SHOW ERRORS
No errors.

Execute the procedure.

SCOTT@orcl_11gR2> EXEC addusers
CREATE USER "user1" IDENTIFIED BY "pass1234"
CREATE USER "user2" IDENTIFIED BY "pass1234"

PL/SQL procedure successfully completed.

Confirm that the users were created.

SCOTT@orcl_11gR2> SELECT username
  2  FROM   all_users
  3  WHERE  username IN
  4  	    (SELECT userNam
  5  	     FROM   users_ext_tab)
  6  /

USERNAME
------------------------------
user1
user2

2 rows selected.

Report message to a moderator

Re: Create User using stored procedure [message #550345 is a reply to message #550344]

Sun, 08 April 2012 16:48

aubyn19ja
Messages: 42
Registered: January 2012

Member

I really appreciate your help Barbara. Thanks a million. I am going to try it using a csv file. Can I add you to my buddy list?

Report message to a moderator

Re: Create User using stored procedure [message #550346 is a reply to message #550345]

Sun, 08 April 2012 17:00

Barbara Boehmer
Messages: 9106
Registered: November 2002
Location: California, USA

Senior Member

aubyn19ja wrote on Sun, 08 April 2012 14:48

I am going to try it using a csv file.

If you mean that your data will be like:

user1,user2

then you will need to modify the external table creation:

CREATE TABLE users_ext_tab
(userNam VARCHAR2 (30))
ORGANIZATION external
(TYPE oracle_loader
DEFAULT DIRECTORY my_dir
ACCESS PARAMETERS
(RECORDS DELIMITED BY ','
LOGFILE 'test.log'
FIELDS TERMINATED BY ',' LDRTRIM
REJECT ROWS WITH ALL NULL FIELDS
(userNam))
location ('users.txt'))
/

aubyn19ja wrote on Sun, 08 April 2012 14:48

Can I add you to my buddy list?

Sure.

Report message to a moderator

Re: Create User using stored procedure [message #550347 is a reply to message #550346]

Sun, 08 April 2012 17:02

aubyn19ja
Messages: 42
Registered: January 2012

Member

Thanks so much.

Report message to a moderator

Re: Create User using stored procedure [message #550348 is a reply to message #550347]

Sun, 08 April 2012 18:07

aubyn19ja
Messages: 42
Registered: January 2012

Member

It stated one user in the csv file and everything executed without errors but when I tried to log in as the user I got an error, invalid username/password; logon denied.

SQL> CREATE OR REPLACE DIRECTORY my_file AS 'c:\my_oracle_files';

Directory created.

SQL> CREATE TABLE users_ext_tab
2 (
3 userNam VARCHAR2 (30))
4 ORGANIZATION external
5 (
6 TYPE oracle_loader
7 DEFAULT DIRECTORY my_file
8 ACCESS PARAMETERS
9 (
10 RECORDS DELIMITED BY ','
11 LOGFILE 'test.log'
12 FIELDS TERMINATED BY ',' LDRTRIM
13 REJECT ROWS WITH ALL NULL FIELDS
14 (userNam)
15 )
16 location ('users.csv'))
17 /

Table created.

SQL> CREATE OR REPLACE PROCEDURE addUsers
2 IS
3 v_sql VARCHAR2 (32767);
4 BEGIN
5 FOR r IN (SELECT userNam FROM users_ext_tab) LOOP
6 v_sql := 'CREATE USER "' || r.userNam || '" IDENTIFIED BY "pass
1234"';
7 DBMS_OUTPUT.PUT_LINE (v_sql);
8 EXECUTE IMMEDIATE v_sql;
9 END LOOP;
10 END addUsers;
11 /

Procedure created.

SQL> EXEC addUsers;
CREATE USER "Marky" IDENTIFIED BY "pass1234"

PL/SQL procedure successfully completed.

SQL> SELECT username
2 FROM all_users
3 WHERE username IN
4 (SELECT userNam
5 FROM users_ext_tab)
6 /

USERNAME
------------------------------
Marky

SQL> select * from all_users where username ='Marky';

USERNAME USER_ID CREATED
------------------------------ ---------- ---------
Marky 95 08-APR-12

ERROR:
SQL> connect Marky/pass1234;
ERROR:
ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.
SQL>

Report message to a moderator

Re: Create User using stored procedure [message #550349 is a reply to message #550348]

Sun, 08 April 2012 18:12

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

double quote marks are rarely needed & should be avoided.

Since the user was created using double quote marks & lower case characters,
you must always do the same forever more!

SQL> CONNECT "Marky"/pass1234

By the way no trailing semicolon (";") should follow username/password

Report message to a moderator

Re: Create User using stored procedure [message #550352 is a reply to message #550349]

Sun, 08 April 2012 18:19

aubyn19ja
Messages: 42
Registered: January 2012

Member

Thanks a lot BlackSwan. I tried it and it worked. But how do I get rid of the double quotes so I can just create a user and type connect User1/pass1234;

SQL> connect "Mary"/pass1234
ERROR:
ORA-01045: user Marky lacks CREATE SESSION privilege; logon denied

Warning: You are no longer connected to ORACLE.
SQL>

Report message to a moderator

Re: Create User using stored procedure [message #550353 is a reply to message #550352]

Sun, 08 April 2012 18:21

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

16:18:45 SQL> show user
USER is "SYS"
16:20:36 SQL> create user marky identified by pass1234;

User created.

16:21:02 SQL> grant create session to marky;

Grant succeeded.

16:21:15 SQL> connect marky/pass1234
Connected.
16:21:24 SQL>

Report message to a moderator

Re: Create User using stored procedure [message #550355 is a reply to message #550353]

Sun, 08 April 2012 18:26

aubyn19ja
Messages: 42
Registered: January 2012

Member

Thank BlackSwan, I can do it like that but I was using a stored procedure that Barbara helped me with that will read username from a csv file then add the data to a table and then use the table to populate the users from the stored procedure.

Report message to a moderator

Re: Create User using stored procedure [message #550357 is a reply to message #550355]

Sun, 08 April 2012 18:29

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

the SQL to CREATE USER should NOT contain double quote marks!

Report message to a moderator

Re: Create User using stored procedure [message #550360 is a reply to message #550355]

Sun, 08 April 2012 18:38

aubyn19ja
Messages: 42
Registered: January 2012

Member

I found where the double quotes were. Thanks so much BlackSwan, Barbara, Michel and Ved.

Report message to a moderator

Re: Create User using stored procedure [message #550363 is a reply to message #550360]

Sun, 08 April 2012 18:51

Barbara Boehmer
Messages: 9106
Registered: November 2002
Location: California, USA

Senior Member

You should change the LDRTRIM in the external table to LRTRIM to prevent trailing spaces on the username, in case they have them in your data file.

You should remove the double quotations if you do not want the username and password to be case sensitive.

You can add the grant to create session to the procedure.

Please see the revised demonstration below.

-- users.txt:

user1,user2,Marky

SCOTT@orcl_11gR2> CREATE OR REPLACE DIRECTORY my_cir AS 'c:\my_oracle_files'
  2  /

Directory created.

SCOTT@orcl_11gR2> CREATE TABLE users_ext_tab
  2    (userNam  VARCHAR2 (30))
  3  ORGANIZATION external
  4    (TYPE oracle_loader
  5  	DEFAULT DIRECTORY my_dir
  6  	ACCESS PARAMETERS
  7  	  (RECORDS DELIMITED BY ','
  8  	   LOGFILE 'test.log'
  9  	   FIELDS TERMINATED BY ',' LRTRIM
 10  	   REJECT ROWS WITH ALL NULL FIELDS
 11  	     (userNam))
 12  	location ('users.txt'))
 13  /

Table created.

SCOTT@orcl_11gR2> CREATE OR REPLACE PROCEDURE addUsers
  2  IS
  3    v_sql  VARCHAR2 (32767);
  4  BEGIN
  5    FOR r IN (SELECT userNam FROM users_ext_tab) LOOP
  6  	 v_sql :=
  7  	   'CREATE USER ' || r.userNam || ' IDENTIFIED BY pass1234
  8  	    DEFAULT TABLESPACE USERS QUOTA 1M ON USERS PASSWORD EXPIRE';
  9  	 DBMS_OUTPUT.PUT_LINE (v_sql);
 10  	 EXECUTE IMMEDIATE v_sql;
 11  	 v_sql :=
 12  	   'GRANT CREATE SESSION TO ' || r.userNam;
 13  	 DBMS_OUTPUT.PUT_LINE (v_sql);
 14  	 EXECUTE IMMEDIATE v_sql;
 15    END LOOP;
 16  END addUsers;
 17  /

Procedure created.

SCOTT@orcl_11gR2> SHOW ERRORS
No errors.
SCOTT@orcl_11gR2> EXEC addusers
CREATE USER user1 IDENTIFIED BY pass1234
       DEFAULT TABLESPACE USERS QUOTA
1M ON USERS PASSWORD EXPIRE
GRANT CREATE SESSION TO user1
CREATE USER user2 IDENTIFIED BY pass1234
       DEFAULT TABLESPACE USERS QUOTA
1M ON USERS PASSWORD EXPIRE
GRANT CREATE SESSION TO user2
CREATE USER Marky IDENTIFIED BY pass1234
       DEFAULT TABLESPACE USERS QUOTA
1M ON USERS PASSWORD EXPIRE
GRANT CREATE SESSION TO Marky

PL/SQL procedure successfully completed.

SCOTT@orcl_11gR2> connect Marky/pass1234
ERROR:
ORA-28001: the password has expired


Changing password for Marky
Password changed
Connected.
MARKY@orcl_11gR2>

Report message to a moderator

Re: Create User using stored procedure [message #550379 is a reply to message #550363]

Mon, 09 April 2012 01:09

Michel Cadot
Messages: 68774
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

I'd change "r.userNam" to "dbms_assert.simple_sql_name(r.userNam)" (or the like) to prevent from sql injection.

Regards
Michel

Report message to a moderator

Re: Create User using stored procedure [message #550405 is a reply to message #550379]

Mon, 09 April 2012 05:34

aubyn19ja
Messages: 42
Registered: January 2012

Member

Thanks Michel

Report message to a moderator

Previous Topic:	Regexp_like
Next Topic:	Combining 2 select statements in a view

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Jan 18 19:11:19 CST 2026