dbms_scheduler fails to create job when created within a stored procedure

Question

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;

How do you know it isn't throwing an exception when you're catching anything it does throw with `when others`? Are you seeing the `dbms_output` result, which can only be 1, or does that not appear - indicating you went into the exception handler? Anyway, is your permission to create a job granted through a role or directly to your user? — Alex Poole, Sep 22 '16 at 18:04
Oops, I just wasn't outputting the exception. I see that it is insufficient privilege issue. It was granted through roles. Its working fine after granting appropriate permissions to user. Thanks Alex Poole. — DG3, Sep 22 '16 at 18:20
It's almost always better to just let the exception propagate, rather than use `when others` and trying to display the exception yourself; for a start the caller might not have `dbms_output` enabled; and the PL/SQL block this is called from can't tall if it actually succeeded. — Alex Poole, Sep 22 '16 at 18:26
Why do you use create_job within a procedure instead of creating a permanent job and use run_job? — 0xdb, Sep 23 '16 at 11:38
I want to get unique job names as there will be multiple instances that might be running. It would be easy for identiying the job that may fail. — DG3, Sep 23 '16 at 15:33

0xdb · Answer 1 · 2016-09-26T18:34:00.430

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;

dbms_scheduler fails to create job when created within a stored procedure

1 Answers1