Parameters on Execute Immediate Sentence (Inside a procedure)

Question

I'm triyin to create a ORACLE USER from my User table inside a procedure. The problem is that I don't know how to call a specific column. I've tried with Camp.user.username and that stuff.

create or replace 
PROCEDURE PR_USERPASS AS
 BEGIN
  UPDATE CAMP.USERS
  SET USERNAME = (DBMS_RANDOM.string('x',15)), PASS = DBMS_RANDOM.string('x',12);
  EXECUTE IMMEDIATE 'CREATE USER ' || USERNAME || ' IDENTIFIED BY ' || PASSWORD;
  EXECUTE IMMEDIATE 'Grant connect to ' || USERNAME;
 END PR_USERPASS;

Is there anyway to call that references in the same procedure? Thank you in advance.

Ed Gibbs · Accepted Answer · 2013-05-24T15:18:26.093

1

Use a cursor to loop through the Camp.Users table and access its columns. Your code would go something like this (untested):

create or replace 
PROCEDURE PR_USERPASS AS
BEGIN
  UPDATE CAMP.USERS
  SET USERNAME = (DBMS_RANDOM.string('u',15)), PASS = DBMS_RANDOM.string('x',12);
  FOR userRow IN (SELECT Username, Pass FROM Camp.Users) LOOP
     EXECUTE IMMEDIATE 'CREATE USER ' || userRow.Username || ' IDENTIFIED BY ' || userRow.Pass;
     EXECUTE IMMEDIATE 'GRANT CONNECT TO ' || userRow.Username;
  END LOOP;
END PR_USERPASS;

Addendum: The original answer generated USERNAME as DBMS_Random.String('x', 15), which allows digits and numbers for the username and password. This caused trouble when the username began with a digit. The answer was changed to use DBMS_Random.String('u', 15) to generate only Oracle-acceptable username values. The password seemed to be OK with the leading digit.

If usernames beginning with a digit are wanted, just surround the username with double quotes:

     EXECUTE IMMEDIATE 'CREATE USER "' || userRow.Username || '" IDENTIFIED BY ' || userRow.Pass;
     EXECUTE IMMEDIATE 'GRANT CONNECT TO "' || userRow.Username || '"';

That said, I'm not sure if having non-standard usernames is such a good idea.

Documentation for DBMS_Random.String can be found here.

edited May 24 '13 at 15:18

answered May 24 '13 at 14:39

Ed Gibbs

25,924
4
46
69

I've tried with EXECUTE IMMEDIATE 'CREATE USER (:a) IDENTIFIED BY (:b)' USING userRow.USERNAME, userRow.PASS; And still not working. – Sergiodiaz53 May 24 '13 at 14:59
Was it the `ORA-01935` error? In that case it's probably because `DBMS_Random.String('x', length)` returns a value with letters and numbers, and if the first character is a number then it's not a valid name for a username or password. Do you want to have usernames or passwords that begin with a number? Either way is fine; just let me know and I'll post an update. – Ed Gibbs May 24 '13 at 15:00
You are right! I changed the 'x' for 'u' in the DBMS_RANDOM and now it's working. Anyway, do you know how to do it with password that begins with a number? Thanks for all! – Sergiodiaz53 May 24 '13 at 15:06
1

I just tried that here and Oracle let me create a user with password `123456`, so I was wrong when I said a password with a digit at the beginning was invalid. You should be able to get the password using `DBMS_Random.String('x', 12)`. That said, if you run into any trouble, just surround the password with double quotes: `... IDENTIFIED BY "' || userRow.Pass || '"';`. With the double quotes you can get away with *any* value. – Ed Gibbs May 24 '13 at 15:11
Also note that the double quotes, in addition to letting you use non-standard characters (including punctuation and spaces), will also make the value case-sensitive. A lot of folks (including me) run into trouble with that :) – Ed Gibbs May 24 '13 at 15:12

Parameters on Execute Immediate Sentence (Inside a procedure)

1 Answers1