CODEWITHSUNDEEP
sqloracle-database
arun
arun

Reputation: 5

Oracle - Query inside query

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

Upvotes: 0

Views: 98

Answers (1)

psaraj12
psaraj12

Reputation: 5072

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;

Upvotes: 4

Related Questions