I am reposting my question from stackoverflow.
I am trying to create an event based job using oracle dbms_scheduler as described below. Is there a way we can pass the job information that raised the event into the job that is executed based on that event.
Subscribing to the queue
BEGIN sys.DBMS_SCHEDULER.add_event_queue_subscriber ('my_queue_agent'); END;
Create program that will be called by the first job
begin
sys.dbms_scheduler.create_program(program_name => 'PROGRAM_TEST',
program_type => 'STORED_PROCEDURE',
program_action => 'PROC_TEST_SCHEDULER',
number_of_arguments => 2,
enabled => false,
comments => '');
sys.dbms_scheduler.define_program_argument(program_name => 'PROGRAM_TEST',
argument_position => 1,
argument_name => 'P_JOB_NAME',
argument_type => 'VARCHAR2',
default_value => '');
sys.dbms_scheduler.define_metadata_argument(program_name => 'PROGRAM_TEST',
metadata_attribute => 'LOG_ID',
argument_position => 2,
argument_name => 'LOG_ID');
sys.dbms_scheduler.enable(name => 'PROGRAM_TEST');
end;
Create job that utilizes the program.
begin
sys.dbms_scheduler.create_job(job_name => 'TEST_PROGRAM_JOB',
program_name => 'PROGRAM_TEST',
start_date => to_date(null),
repeat_interval => '',
end_date => to_date(null),
job_class => 'DEFAULT_JOB_CLASS',
enabled => false,
auto_drop => false,
comments => '');
sys.dbms_scheduler.set_job_argument_value(job_name => 'TEST_PROGRAM_JOB',
argument_name => 'P_JOB_NAME',
argument_value => 'TEST_PROGRAM_JOB');
sys.dbms_scheduler.set_attribute(name => 'TEST_PROGRAM_JOB', attribute => 'raise_events', value => sys.dbms_scheduler.job_started + sys.dbms_scheduler.job_succeeded + sys.dbms_scheduler.job_failed);
end;
Create second job that would be triggered based on events of first job.
BEGIN
sys.DBMS_SCHEDULER.create_job (
job_name => 'UPDATE_STATUS_JOB',
job_type => 'PLSQL_BLOCK',
job_action => 'insert into t_log values (''UPDATE'' || tab.user_data.log_id,sysdate);',
event_condition => '(tab.user_data.event_type = ''JOB_SUCCEEDED'' OR
tab.user_data.event_type = ''JOB_FAILED'' or
tab.user_data.event_type = ''JOB_STARTED'' or
tab.user_data.event_type = ''JOB_COMPLETED'') AND tab.user_data.object_name = ''TEST_PROGRAM_JOB''',
queue_spec => 'sys.scheduler$_event_queue,my_queue_agent',
enabled => TRUE);
END;
Is there a way I can pass the current log_id of TEST_PROGRAM_JOB into UPDATE_STATUS_JOB? I want to log the status of the TEST_PROGRAM_JOB. Right now, I tried using tab.user_data.log_id but with no success.