Oracle - Query inside query

Question

I want to create users in oracle, using my existing table call EMPLOYEE, I want to generate a query to create users with user name. User name is in EMPLOYEE table.

I want to create a query like below

CREATE USER (SELECT first_name FROM employee where employee_id = '1') 
IDENTIFIED BY password .....so on

Mind expanding this with more details? Do you have a User table your are trying to populate from the Employee table? What SQL have you tried? — bowlturner
– bowlturner, Commented Dec 19, 2014 at 16:02

psaraj12 · Accepted Answer · 2014-12-20 03:03:17Z

4

DBMS_UTILITY.EXEC_DDL_STATEMENT can be used to run DDL statements in PLSQL so you can try the below

BEGIN
FOR c IN (select 'CREATE USER  ' || first_name || ' ' || 'IDENTIFIED BY ' ||
                 password_column ||' ' as rec
            from employee where employee_id=1) LOOP
  begin
    dbms_utility.exec_ddl_statement(c.rec);
  exception
    when others then
      dbms_output.enable;
dbms_output.put_line  ('failed for '||c.rec);
dbms_output.put_line(sqlerrm);
    end;
  END LOOP;
  END;

edited Dec 20, 2014 at 3:03

answered Dec 19, 2014 at 16:08

psaraj12

5,0783 gold badges25 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

psaraj12 Over a year ago

this will create user based on first_name from Employee Table

Sebas Over a year ago

@ArunaDissanayake, hi. Your problem is that you can't use subqueries in CREATE USER statements. The alternative is the one proposed by psaraj12 that consists in dynamically creating and executing as many CREATE USER statement as you have users in your table. However for the sake of safety I would rather export them to the console than execute them on the fly, at least until you know the code works as you intend.

Aramillo Over a year ago

Good answer, but i recommend you do not use when other then null, because you are hiding possible errors that can occurs

Collectives™ on Stack Overflow

Oracle - Query inside query

1 Answer 1

3 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Related