Oracle dbms_scheduler

Starting with 10g offers a brand new job-scheduling facility, known as The Scheduler, controlled via the new package dbms_scheduler. This package replaces the dbms_job (but that one is still available). The new scheduler offers more functionality over the dbms_job package.

CREATE TABLE KTEST

(

   X   NUMBER,

   Y   NUMBER

);

CREATE SEQUENCE ktest_s

   START WITH 1;

CREATE OR REPLACE PROCEDURE KPRO (PX IN NUMBER, PY NUMBER)

AS

BEGIN

   INSERT INTO KTEST

        VALUES (PX, PY);

   DBMS_LOCK.SLEEP (60);

   COMMIT;

END KPRO;

BEGIN

   DBMS_SCHEDULER.create_program (

      program_name          => 'TEST_STORED_PROCEDURE_PROG',

      program_type          => 'STORED_PROCEDURE',

      program_action        => 'KPRO',

      number_of_arguments   => 2,

      enabled               => FALSE,

      comments              => 'SOA Invoke Program');

   DBMS_SCHEDULER.DEFINE_PROGRAM_ARGUMENT (

      program_name        => 'TEST_STORED_PROCEDURE_PROG',

      argument_position   => 1,

      argument_type       => 'NUMBER',

      DEFAULT_VALUE       => '1');

   DBMS_SCHEDULER.DEFINE_PROGRAM_ARGUMENT (

      program_name        => 'TEST_STORED_PROCEDURE_PROG',

      argument_position   => 2,

      argument_type       => 'NUMBER',

      DEFAULT_VALUE       => '2');

   DBMS_SCHEDULER.enable ('TEST_STORED_PROCEDURE_PROG');

END;

CREATE OR REPLACE PROCEDURE SOA_INVOKE (px IN VARCHAR2, py IN VARCHAR2)

AS

   ljob_id   NUMBER;

   ljob      VARCHAR2 (100);

BEGIN

   ljob_id := ktest_s.NEXTVAL;

   ljob := 'RequestID_' || ljob_id;

   DBMS_SCHEDULER.create_job (ljob,

                              program_name   => 'TEST_STORED_PROCEDURE_PROG');

   DBMS_SCHEDULER.set_job_argument_value (ljob, 1, px);

   DBMS_SCHEDULER.set_job_argument_value (ljob, 2, px);

   DBMS_SCHEDULER.enable (ljob);

   DBMS_OUTPUT.put_line ('Job ID : ' || ljob);

END;

EXECUTE SOA_INVOKE(1,2);

SELECT *

  FROM ALL_SCHEDULER_RUNNING_JOBS

WHERE job_name = :1

References  http://www.pafumi.net/Scheduler_DBMS_SCHEDULER.html http://dba.stackexchange.com/questions/42119/passing-parameters-to-a-procedure-executed-by-dbms-scheduler

Wait for concurrent request using fnd_concurrent.wait_for_request

Most of the times while submitting concurrent request one has to wait for its completion to perform sequence of steps. This can be achieved through fnd_concurrent.wait_for_request in oracle apps. It returns the status of the previously submitted concurrent program upon interval set.

This is a conjunction to the SUBMIT_REQUEST. Follow ARTICLE on submitting concurrent program and use below call upon completed. There are some parameter that has to be declared.

      lb_complete      BOOLEAN;
      lc_phase           VARCHAR2 (100);
      lc_status           VARCHAR2 (100);
      lc_dev_phase   VARCHAR2 (100);
      lc_dev_status   VARCHAR2 (100);
      lc_message      VARCHAR2 (100);

ln_request_id is return variable for concurrent_request_id from above article. If ln_request_id > 0 (means request submitted successfully) then wait_for_request.

   Arguments (input)
     request_id    - Request ID to wait on
     interval         - time b/w checks. Number of seconds to sleep (default 60 seconds)
     max_wait      - Max amount of time to wait (in seconds) for request's completion
  Arguments (output)
                 User version of      phase and status
                 Developer version of phase and status
                 Completion text if any
     phase            - Request phase ( from meaning in fnd_lookups )
     status            - Request status( for display purposes          )
     dev_phase    - Request phase as a constant string so that it can be used for comparisons
     dev_status    - Request status as a constatnt string
     message       - Completion message if request has completed


      IF ln_request_id > 0
      THEN
         lb_complete :=
            fnd_concurrent.wait_for_request (request_id      => ln_request_id
                                                             ,interval            => 2
                                                             ,max_wait        => 60
                                                             -- out arguments
                                                             ,phase              => lc_phase
                                                             ,status              => lc_status
                                                             ,dev_phase      => lc_dev_phase
                                                             ,dev_status      => lc_dev_status
                                                             ,message         => lc_message
                                            );
         COMMIT;

         IF UPPER (lc_dev_phase) IN ('COMPLETE')
         THEN
            dbms_output.put_line('Concurrent request completed successfully');
         END IF;
      END IF;