CODEWITHSUNDEEP
oracle-databaseplsqloracle11gdbms-scheduler
DG3
DG3

Reputation: 5298

dbms_scheduler fails to create job when created within a stored procedure

I am trying to create a dbms_scheduler job within a stored procedure after executing some logic. My procedure code is as below. The issue is that the scheduler code is not running and the procedure is not throwing any exception at that time of creating the job. The job creates fine when run independently as a anonymous block. Is this the right way of accomplishing what I am trying to do?

create or replace procedure PROC_INS_TEST AS
     v_success varchar2(255) := '1';
     v_job_name varchar2(255);
    BEGIN

      SELECT 'TEST' || dbms_scheduler.generate_job_name into v_job_name
      FROM DUAL;  

      INSERT INTO T_TEMP_STAGING--log table
      (job_id, process_name) VALUES (1, 'TEST');
         Commit;
          v_success := '1';

       BEGIN
          sys.dbms_scheduler.create_job (
            job_name        => v_job_name,
            job_type        => 'PLSQL_BLOCK',
            job_action      => 'BEGIN PROC_TEST_SCHEDULER; END;',
            --start_date      => sysdate,
            --repeat_interval => 'freq=hourly; byminute=0; bysecond=0;',
            enabled         => TRUE);

        END;
        dbms_output.put_line(v_success);


    EXCEPTION
        WHEN OTHERS THEN        
          v_success = '0';
          ROLLBACK;
          RETURN;
    END PROC_INS_TEST;

Upvotes: 1

Views: 1112

Answers (1)

0xdb
0xdb

Reputation: 3697

It seems you forgot to include separate exception block to catch the exceptions from create_job:

    begin
        sys.dbms_scheduler.create_job (
        job_name        => v_job_name,
        job_type        => 'PLSQL_BLOCK',
        job_action      => 'BEGIN PROC_TEST_SCHEDULER; END;',
        enabled         => TRUE);
    exception when other than
        dbms_output.put_line('Job ' || job_name || ' cannot be created! ' || SQLERRM);
        raise;
    end;

Upvotes: 2

Related Questions