Can a JOB be created dynamically inside a trigger?

Question

The execution of this trigger fails (it compiles but once I do the specified insert -> error)

create or replace
TRIGGER AFT_INSERT_TMP_TBL
AFTER INSERT ON TMP_TBL
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW
DECLARE 

    V_SQL VARCHAR2(1000);
    A_NAME VARCHAR2(100);

BEGIN
    A_NAME:='ANY_NAME';

    V_SQL:='BEGIN 
              DBMS_SCHEDULER.CREATE_JOB (
                  job_name => '''||A_NAME||''',
                  job_type => ''PLSQL_BLOCK'',
                  job_action => ''BEGIN DBMS_OUTPUT.PUT_LINE('||A_NAME||'); END;'',
                  start_date => TIMESTAMP''2011-12-4 10:30:00'',
                  repeat_interval => ''FREQ=MINUTELY;INTERVAL=2'',
                  auto_drop => FALSE,
                  comments => '''||A_NAME||''');
            END;';
     DBMS_OUTPUT.PUT_LINE('SCHEDULER :'||V_SQL);
     EXECUTE IMMEDIATE V_SQL;

END AFT_INSERT_TMP_TBL;
-----------------------

Printed SCHEDULER creation code is totally valid.

I am getting a ORA-04092 'cannot in a trigger... A trigger attempted to commit or rollback. Rewrite the trigger so it doesn't commit or rollback'.

Is this a 'commit'? So a JOB cannot be created inside of a trigger?

I know I've used triggers with inserts into different tables, and that is also a "commit" and Oracle didn't complaint.

Answer 1

Calling DBMS_SCHEDULER.CREATE_JOB implicitly commits so you cannot create a DBMS_SCHEDULER job in a trigger. This is one of the situations that still call for using the old DBMS_JOB package since DBMS_JOB.SUBMIT does not implicitly commit.

This trigger should create the job you want using the DBMS_JOB package rather than DBMS_SCHEDULER.

create or replace
TRIGGER AFT_INSERT_TMP_TBL
AFTER INSERT ON TMP_TBL
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW
DECLARE 

    V_SQL VARCHAR2(1000);
    A_NAME VARCHAR2(100);
    l_jobno NUMBER;
BEGIN
    A_NAME:='ANY_NAME';

    dbms_job.submit( l_jobno,
                     'BEGIN dbms_output.put_line( ''' || a_name || ''' ); END;',
                     sysdate + interval '2' minute,
                     'sysdate + interval ''2'' minute' );
     DBMS_OUTPUT.PUT_LINE('Job Number:'||l_jobno);

END AFT_INSERT_TMP_TBL;

Answer 2

You can also consider autonomous transaction https://community.oracle.com/thread/2399412?start=0&tstart=0

Answer 3

Yes this is a commit. Oracle is updating a system table, job$ I think, when you create a scheduler or a job etc which means an automatic commit.

Why don't you just insert the information you need into another (driver) table and have a cron job or a dbms_job to create your scheduler jobs?