-
Hi all !
I want to create a DDL trigger to automatically execute a GRANT SELECT to a role when a table is created.
I use the following procedures and DDL trigger :
create or replace trigger tri_grantauto after create on test.schema
begin
if ora_dict_obj_type in ('TABLE','VIEW') then
p_execjob(ora_dict_obj_name, 'TESTROLE');
end if;
end;
/
create or replace procedure p_execjob (p_objname varchar2,p_grantee varchar2)
is
v_job number;
v_text varchar2(100);
begin
v_text := 'p_grantauto('||chr(39)||p_objname||chr(39)||',';
v_text := v_text ||chr(39)||p_grantee||chr(39)||');';
dbms_job.submit(v_job, v_text, sysdate+(60/86400));
dbms_job.run(v_job);
end;
/
create or replace procedure p_grantauto (p_objname varchar2,p_grantee varchar2)
is
begin
execute immediate 'grant select on '||p_objname||' to '||p_grantee;
end;
/
I've tried to create a table and received the ORA-04092 error message.
I've modified the last procedure to add the PRAGMA AUTONOMOUS_TRANSACTION :
create or replace procedure p_grantauto (p_objname varchar2,p_grantee varchar2)
is
PRAGMA AUTONOMOUS_TRANSACTION;
begin
execute immediate 'grant select on '||p_objname||' to '||p_grantee;
end;
/
And it doen't make any difference ...
Always :
ORA-00604: error occurred at recursive SQL level 1
ORA-04092: cannot COMMIT in a trigger
ORA-06512: at "SYS.DBMS_IJOB", line 405
ORA-06512: at "SYS.DBMS_JOB", line 267
ORA-06512: at "TEST.P_EXECJOB", line 9
ORA-06512: at line 3
Please help ...
-
From MetaLink:
All Data Definition Language (DDL) statements execute an implicit commit.
Even if you create a separate procedure, to execute the DDL statement, the
execution of the separate procedure within the trigger is still in the same
transaction, forcing a commit of any statement executed in the trigger and
hence causing the error ORA-04092.
Use autonomous transactions to create the table:
1) Create a procedure called my_procedure that will execute as an autonomous
transaction:
SQL> create or replace procedure my_procedure
2 is
3 pragma AUTONOMOUS_TRANSACTION;
4 BEGIN
5 execute immediate 'CREATE TABLE my_table (id NUMBER)';
6 END;
7 /
Procedure created.
2) Create the trigger, that will call the autonomous procedure to create the
table:
SQL> create or replace trigger my_trig after delete on emp
2 BEGIN
3 my_procedure;
4 END;
5 /
Trigger created.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|