How to create an async Oracle job to run in multiple instances

Question

I created the following program and the subsequent Oracle JOB:

BEGIN DBMS_SCHEDULER.create_program (program_name => 'myProg', program_action => 'myProc', program_type => 'STORED_PROCEDURE', number_of_arguments => 3, enabled => FALSE); DBMS_SCHEDULER.DEFINE_PROGRAM_ARGUMENT (program_name => 'myProg', argument_position => 1, argument_type => 'NUMBER'); DBMS_SCHEDULER.DEFINE_PROGRAM_ARGUMENT (program_name => 'myProg', argument_position => 2, argument_type => 'NUMBER'); DBMS_SCHEDULER.DEFINE_PROGRAM_ARGUMENT (program_name => 'myProg', argument_position => 3, argument_type => 'NUMBER', DEFAULT_VALUE => NULL); DBMS_SCHEDULER.create_job ('myJob', program_name => 'myProg', enabled => FALSE, comments => 'Send data'); DBMS_SCHEDULER.SET_ATTRIBUTE ('myJob', 'PARALLEL_INSTANCES', TRUE); DBMS_SCHEDULER.SET_ATTRIBUTE ('myJob', 'logging_level', DBMS_SCHEDULER.LOGGING_FULL); END; / 

Now I have a user who can run / execute tasks that call the following procedure:

  PROCEDURE runJOB(param1 IN PLS_INTEGER, param2 IN PLS_INTEGER DEFAULT NULL, param3 IN PLS_INTEGER DEFAULT NULL) IS PRAGMA AUTONOMOUS_TRANSACTION; BEGIN DBMS_SCHEDULER.enable ('myProg'); DBMS_SCHEDULER.set_job_argument_value ('myJob', 1, TO_CHAR (param1)); DBMS_SCHEDULER.set_job_argument_value ('myJob', 2, TO_CHAR (param2)); DBMS_SCHEDULER.set_job_argument_value ('myJob', 3, TO_CHAR (param3)); --DBMS_SCHEDULER.enable ('myJob'); DBMS_SCHEDULER.RUN_JOB (JOB_NAME => 'myJob', USE_CURRENT_SESSION => FALSE); --DBMS_SCHEDULER.disable ('myJob'); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.put_line (DBMS_UTILITY.FORMAT_ERROR_BACKTRACE); END runJOB; 

What are my problems?

• I need to run the task in asynchronous mode. This is why I have enable or run_job using the USE_CURRENT_SESSION FALSE parameter. I think it works.
• I need to do multiple instances of the same work, starting with different users at the same time. For example, user A calls runJOB . The task can be completed in 20 seconds. In this 20 seconds, user B can call the same procedure in another session. This is why I tried to use the PARALLEL_INSTANCES attribute, but I only get one execution. I think that Oracle sees that work therefore cancel the second attempt of start.

When resuming, I need a job that must run in asynchronous mode and with multiple instances at the same time.

After "double" execution of the task for two instances, I get only one entry in the user_SCHEDULER_JOB_RUN_DETAILS table, but 2 I allow the task for two different users (SGSS and EX01882_BD)

 52367532 26/12/2016 12:08:44,584878 +00:00 SGSS myJob DEFAULT_JOB_CLASS RUN SUCCEEDED (HugeClob) 52364238 26/12/2016 12:08:36,529539 +00:00 SGSS myJob DEFAULT_JOB_CLASS ENABLE EX01882_BD (HUGECLOB) 52367534 26/12/2016 12:08:34,302807 +00:00 SGSS myJob DEFAULT_JOB_CLASS ENABLE SGSS (HUGECLOB) 

Any help?

Note: I cannot use different names in this solution ( How to start two or more instances of an oracle task at the same time? ), Because the task has already been created, and the users who call this task do not have permission to create.

Answer 1

Solution: Event Based Work

 GRANT AQ_ADMINISTRATOR_ROLE TO USERA; GRANT EXECUTE ON DBMS_SCHEDULER TO USERA; GRANT EXECUTE ON sys.DBMS_SCHEDULER TO USERA; GRANT EXECUTE ON sys.DBMS_ISCHED TO USERA; GRANT CREATE JOB TO USERA; GRANT CREATE EXTERNAL JOB TO USERA; CREATE OR REPLACE TYPE t_event_queue_payload AS OBJECT ( event_name VARCHAR2(30) ); BEGIN -- Create a queue table to hold the event queue. DBMS_AQADM.create_queue_table ( queue_table => 'event_queue_tab', queue_payload_type => 't_event_queue_payload', multiple_consumers => TRUE, comment => 'Comments'); --Create the event queue. DBMS_AQADM.create_queue (queue_name => 'event_queue', queue_table => 'event_queue_tab'); -- Start the event queue. DBMS_AQADM.start_queue (queue_name => 'event_queue'); END; / BEGIN DBMS_SCHEDULER.create_program ( program_name => 'myProg', program_action => 'USERA.PACKAGE.myProc', program_type => 'STORED_PROCEDURE', number_of_arguments => 3, enabled => FALSE); DBMS_SCHEDULER.DEFINE_PROGRAM_ARGUMENT (program_name => 'myProg', argument_position => 1, argument_type => 'NUMBER'); DBMS_SCHEDULER.DEFINE_PROGRAM_ARGUMENT (program_name => 'myProg', argument_position => 2, argument_type => 'NUMBER'); DBMS_SCHEDULER.DEFINE_PROGRAM_ARGUMENT (program_name => 'myProg', argument_position => 3, argument_type => 'NUMBER', DEFAULT_VALUE => NULL); DBMS_SCHEDULER.enable ('myProg'); DBMS_SCHEDULER.create_job ( 'myJob', program_name => 'myProg', comments => 'StartJob', auto_drop => FALSE, start_date => SYSTIMESTAMP, event_condition => 'tab.user_data.event_name = ''give_me_a_prod''', queue_spec => 'event_queue', enabled => FALSE); COMMIT; END; / PROCEDURE enqueue(param1 IN PLS_INTEGER, param2 IN PLS_INTEGER DEFAULT NULL, param3 IN PLS_INTEGER DEFAULT NULL) IS PRAGMA AUTONOMOUS_TRANSACTION; l_enqueue_options DBMS_AQ.ENQUEUE_OPTIONS_T; l_message_properties DBMS_AQ.MESSAGE_PROPERTIES_T; l_message_handle RAW (16); l_queue_msg t_event_queue_payload; BEGIN l_queue_msg := t_event_queue_payload ('give_me_a_prod'); -- Define arguments DBMS_SCHEDULER.set_job_argument_value ('myJob', 1, TO_CHAR (param1)); DBMS_SCHEDULER.set_job_argument_value ('myJob', 2, TO_CHAR (param2)); DBMS_SCHEDULER.set_job_argument_value ('myJob', 3, TO_CHAR (param3)); DBMS_AQ.enqueue (queue_name => 'USERA.event_queue', enqueue_options => l_enqueue_options, message_properties => l_message_properties, payload => l_queue_msg, msgid => l_message_handle); COMMIT; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.put_line ( SQLERRM || ' - ' || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE); END enqueue; 

In the package:

 PROCEDURE myProc(param1 IN PLS_INTEGER, param2 IN PLS_INTEGER DEFAULT NULL, param3 IN PLS_INTEGER DEFAULT NULL) IS PRAGMA AUTONOMOUS_TRANSACTION; BEGIN ... PKG_SAP_FSCD_INTERRECIBOS.callSomething.... ... END myProc; 

Answer 2

DBMS_SCHEDULER.RUN_JOB (JOB_NAME => 'myJob', USE_CURRENT_SESSION => FALSE );

now, the documentation :

Indicates whether the job should run in the same session from which the procedure was called.

If use_current_session is set to TRUE:

• The task is executed as a user who calls RUN_JOB, or in the case of a local external task with credentials, the user specified in the credentials file.

• You can check the task and view possible errors on the command line.

• run_count, last_start_date, last_run_duration and fail_count are not updated.

• RUN_JOB can be run in parallel with a scheduled regular job.

If use_current_session is set to FALSE:

• Work is performed as the user who owns the job.

• You need to check the job log to find error information.

• run_count, last_start_date, last_run_duration and fail_count are updated.

• RUN_JOB fails if a scheduled task is running.