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