-
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
-
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;