create user

[chikkodi]

Hi

I want to create a user in a trigger. I use the following code:
CREATE OR REPLACE TRIGGER add_user
BEFORE INSERT ON ADMININFO
FOR EACH ROW
DECLARE
v_roleName VARCHAR2 (100);
v_cursor number;
v_cmd varchar2(100);
v_result integer;
BEGIN
--execute immediate 'select username from admininfo';
--execute immediate 'create user ' || :new.username || ' identified by ' || :new.passwd;
--execute immediate 'grant dba to ' || :new.username;

v_cursor := dbms_sql.open_cursor;
v_cmd := 'create user ' || :new.username || ' identified by ' || :new.passwd;
dbms_sql.parse(v_cursor, v_cmd, DBMS_SQL.NATIVE);
v_result := dbms_sql.execute(v_cursor);
dbms_sql.close_cursor(v_cursor);
END;
/

As can be seen, I have tried both options of execute immediate as well as dbms_sql. For execute immediate, I get the error message
'Invalid option' and for dbms_sql I get the error message 'cannot commit inside trigger' These are runtime errors, the trigger compiles
fine

I dont know how else to do this.

Please help
Neelima

[rotem_fo]

Hi
you can use:
CREATE OR REPLACE TRIGGER ADD_USER BEFORE INSERT ON ADMININFO FOR EACH ROW
BEGIN
dbms_utility.exec_ddl_statement('create user ' :new.username || ' identified by ' || :new.passwd);
dbms_utility.exec_ddl_statement('grant dba to ' || :new.username);
END ADD_USER;