Hi all
I'm running Oracle 12
We are having a problem trying to schedule a job to run asynchronously using PL/SQL. The job is not running whether we just RUN_JOB or just CREATE_JOB in the future. This happens even if I run it manually in SQL Developer so regardless of permissions. The Job gets created but it doesn’t run.
BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => '"MYSCHEMA"."MYPROCEDURE_JOB"',
job_type => 'STORED_PROCEDURE',
job_action => 'MYSCHEMA.MYPROCEDURE',
number_of_arguments => 2,
start_date => NULL,
repeat_interval => NULL,
end_date => NULL,
enabled => FALSE,
auto_drop => FALSE,
comments => 'runs MYSCHEMA.MYPROCEDURE(ID1, ID2)’);
DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE(
job_name => '"MYSCHEMA"."MYPROCEDURE_JOB"',
argument_position => 1,
argument_value => '3700');
DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE(
job_name => '"MYSCHEMA"."MYPROCEDURE_JOB"',
argument_position => 2,
argument_value => '1704');
DBMS_SCHEDULER.SET_ATTRIBUTE(
name => '"MYSCHEMA"."MYPROCEDURE_JOB"',
attribute => 'restartable', value => TRUE);
DBMS_SCHEDULER.SET_ATTRIBUTE(
name => '"MYSCHEMA"."MYPROCEDURE_JOB"',
attribute => 'store_output', value => TRUE);
DBMS_SCHEDULER.SET_ATTRIBUTE(
name => '"MYSCHEMA"."MYPROCEDURE_JOB"',
attribute => 'logging_level', value => DBMS_SCHEDULER.LOGGING_RUNS);
DBMS_SCHEDULER.enable(
name => '"MYSCHEMA"."MYPROCEDURE_JOB"');
END;
create or replace PROCEDURE run_adhoc_job_asynch(ID1 IN NUMBER, ID2 in number) IS
BEGIN
DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE(
job_name => '"MYSCHEMA"."MYPROCEDURE_JOB"',
argument_position => 1,
argument_value => ID1);
DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE(
job_name => '"MYSCHEMA"."MYPROCEDURE_JOB"',
argument_position => 2,
argument_value => ID2);
DBMS_SCHEDULER.RUN_JOB(
JOB_NAME => '"MYSCHEMA"."MYPROCEDURE_JOB"',
USE_CURRENT_SESSION => FALSE);
END;
I don’t see the job being run in ALL_SCHEDULER_JOB_RUN_DETAILS unless I change USE_CURRENT_SESSION to TRUE and run it synchronously. In that case I do see the job run AND it runs successfully so there’s probably no code errors.
Another approach I took was to create a job on the fly in PL/SQL with auto_drop=TRUE and start_date=sysdate or systimestamp or even sysdate+30/86400 (meaning 30 seconds) but the job did not run immediately or after 30 seconds.
Anyone have any insight what’s causing the job not to run?