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