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.

Answer 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.