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

Question

I have created the next program and 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 jobs that calls the next 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 here?

• I need to run the job in asynchronous mode. That's why I have a enable or a run_job using the USE_CURRENT_SESSION parameter FALSE. I think this works.
• I need to execute multiple instances of the same job, starting by different users, at the same time. For example, user A calls the runJOB procedure. The job can run in 20 seconds. In this 20 seconds, user B can call the same procedure, in a different session. That's why I've tried to use the PARALLEL_INSTANCES attribute, but I get only one execution. I think Oracle sees that the job is running, so discard the second attempt to run.

In resuming, I need a job that must be executed in async mode and with multiple instances at the same time.

After a "double" execution of the job for two instances I only get one record in user_SCHEDULER_JOB_RUN_DETAILS table, but 2 enable job's 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 have job's different names as in this solution (How run two or more instances of an oracle job in the same time?), because the job is already created and the users who call this job don't have permissions to create.

Answer 1

Solution: Event-Based Jobs

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 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, check documentation:

This specifies whether or not the job run should occur in the same session that the procedure was invoked from.

When use_current_session is set to TRUE:

• The job runs as the user who called RUN_JOB, or in the case of a local external job with a credential, the user named in the
  credential.

• You can test a job and see any possible errors on the command line.

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

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

When use_current_session is set to FALSE:

• The job runs as the user who is the job owner.

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

• run_count, last_start_date, last_run_duration, and failure_count are updated.

• RUN_JOB fails if a regularly scheduled job is running.