PL/SQL API of Wrap Utility - DBMS_DDL package

PL/SQL API of Wrap Utility - DBMS_DDL package

Sometimes it is necessary to hide the source code because of some security concerns or copyright procedures. (but note that there are some tools to un-wrap a wrapped code, like de-compiling) I prefer wrapping pl/sql source codes. Because, it is safer, nobody cannot change and compile it. Testers also cannot see the code, so they can concentrate the business rules.

To wrap(encrypt, compress) a pl/sql source code, there is a utility executable -named wrap- that is shiped with Oracle software. (for more information please read this…. )

Now, this wrap utility has a PL/SQL API via DBMS_DDL package. It is possible to get a wrapped code or directly compile it in the database easily. Please see demonstration below:

p.s. : i use chr(56) instead of “;” my IDE has some problems with “;” J

A sample function that returns 1:

CREATE OR REPLACE FUNCTION GET_1 RETURN NUMBER IS

BEGIN

  RETURN 1;

END;

/

Test it:

SELECT get_1 FROM dual;

Check dba_source:

SELECT LINE, TEXT FROM DBA_SOURCE WHERE NAME = 'GET_1' ORDER BY LINE;

      LINE TEXT

---------- ----------------------------------

         1 FUNCTION GET_1 RETURN NUMBER IS

         2 BEGIN

         3   RETURN 1;

         4 END;

Get wrapped source

select SYS.DBMS_DDL.wrap( '

create or replace function get_1 return number

is

begin

  return 1' || chr(59)||'

end' || chr(59)||'

' ) wrapped_source from DUAL;

WRAPPED_SOURCE

--------------------------------------------------------------------------------

create or replace function get_1 wrapped

a000000

27

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

8

38 69

xzk0ly8PFjTNqoXGivso2L4+wvYwg8eZgcfLCNL+XlquYvR4w+fAsr2ym16lmYEywLIGm3SL

wMAy/tKGCamhBKPHvpK+VMd/pqa+9ct/

Compile wrapped source in the database:

BEGIN

  SYS.DBMS_DDL.create_wrapped( '

create or replace function get_1 return number

is

begin

  return 1' || chr(59)||'

end' || chr(59)||'

' );

END;

/

Check dba_source again:

SELECT LINE, TEXT FROM DBA_SOURCE WHERE NAME = 'GET_1' ORDER BY LINE;

      LINE TEXT

---------- --------------------------------------------------------------------------------

         1 function get_1 wrapped

           a000000

           27

           abcd

           abcd

           abcd

           abcd

           abcd

           abcd

           abcd

           abcd

           abcd

           abcd

           abcd

           abcd

           abcd

           abcd

           abcd

           8

           38 69

           xzk0ly8PFjTNqoXGivso2L4+wvYwg8eZgcfLCNL+XlquYvR4w+fAsr2ym16lmYEywLIGm3SL

           wMAy/tKGCamhBKPHvpK+VMd/pqa+9ct/

Parallel Processing with Oracle Scheduler Chains

Parallel Processing with Oracle Scheduler Chains

As i mentioned before, Oracle Scheduler one of the my favourite utilities of Oracle Database. The capabilities of Scheduler is limited by your imagination J

The issue that i want to mention now is, how to make a process to be run in parallel by the help of Chains. Oracle Scheduler Chain is a set of steps,rules and programs that allows you to design the program blocks. For instance, you have 4 programs(jobs or processes) the first one is should run firstly, second and third ones should run after the first one succeeded, the last one is should run after second and third one is completed(failed or succeeded) This complex logic can be implemented with Chains easily. It is really easy, i am not joking. Chains have several APIs and you can use these API functions as your design needs.

It is time to see Chains in the action…

Business Scenario : We are in the telco sector and we are implementing something that should be run before montly billing process. Our telco operator has over 30M subs. So, we need to implement the something enough fast…

Implementaion : We have one process that should be run first in order to find the subscirbers who we are interested in. After the subs have been found, we can run some business rules over them.

Design : We will create a table(CUTOFF_INTERIM_TABLE) in order to hold interested-in-subscribers. A simple routine(FillInterimTable procedure) will fill this table. Then we will use a routine(ProcessInterimTable procedure) to get the subscribers and do something. First procedure(FillInterimTable) should take an input parameter(pin_TotalProcessCount) and use a simple MODULO operation(MOD(i, pin_TotalProcessCount) + 1) to seperate the subscirbers into chunks. Second procedure(ProcessInterimTable) can be run (pin_TotalProcessCount) times simultaneously, and every process will take only their chunks(…WHERE PROCESS_NUMBER = pin_ProcessNumber…). The important thing is that, second procedures should run if and only if the first procedure succeeded. Otherwise there is no need to run second processes because of there is no subscribers to be processed. We will use 5 parallel processes.

Tables to be created:

CREATE TABLE CUTOFF_INTERIM_TABLE

(

  CUTOFF           DATE,

  CO_ID            NUMBER,

  STATUS           VARCHAR2(1),

  CREATE_DATE      DATE,

  REMARK           VARCHAR2(1024),

  PROCESS_NUMBER   NUMBER

);

CREATE TABLE CUTOFF_PROCESS_EXECUTION_LOG

(

  CUTOFF           DATE,

  PROCESS_NUMBER   NUMBER,

  LOG_MESSAGE      VARCHAR2(1024),

  LOG_TIMESTAMP    TIMESTAMP

);

Procedures to be created:

CREATE OR REPLACE PROCEDURE CreateProcessExecutionLog

(

  pid_Cutoff        IN CUTOFF_PROCESS_EXECUTION_LOG.CUTOFF%TYPE,

  pin_ProcessNumber IN CUTOFF_PROCESS_EXECUTION_LOG.PROCESS_NUMBER%TYPE,

  pis_LogMessage    IN CUTOFF_PROCESS_EXECUTION_LOG.LOG_MESSAGE%TYPE

) IS

  PRAGMA AUTONOMOUS_TRANSACTION;

BEGIN

  INSERT INTO CUTOFF_PROCESS_EXECUTION_LOG

    (CUTOFF, PROCESS_NUMBER, LOG_MESSAGE, LOG_TIMESTAMP)

  VALUES

    (pid_Cutoff, pin_ProcessNumber, pis_LogMessage, SYSTIMESTAMP);

  COMMIT;

END CreateProcessExecutionLog;

/

CREATE OR REPLACE PROCEDURE FillInterimTable

(

  pid_Cutoff            IN CUTOFF_INTERIM_TABLE.CUTOFF%TYPE,

  pin_TotalProcessCount IN NUMBER

) IS

  vn_ProcessIndependent CUTOFF_INTERIM_TABLE.PROCESS_NUMBER%TYPE;

  vs_LogMessage         CUTOFF_PROCESS_EXECUTION_LOG.LOG_MESSAGE%TYPE;

BEGIN

  vn_ProcessIndependent := 0;

  vs_LogMessage         := 'FillInterimTable started...';

  CreateProcessExecutionLog(pid_Cutoff, vn_ProcessIndependent, vs_LogMessage);

  --some business checks etc

  ---

  ----

  EXECUTE IMMEDIATE 'TRUNCATE TABLE CUTOFF_INTERIM_TABLE';

  FOR i IN 1 .. 100000 LOOP

    INSERT INTO CUTOFF_INTERIM_TABLE

      (CUTOFF, CO_ID, STATUS, CREATE_DATE, REMARK,  PROCESS_NUMBER )

    VALUES

      (pid_Cutoff, i, 'N', SYSDATE, 'Successfully created.', MOD(i, pin_TotalProcessCount) + 1);

  END LOOP;

  vs_LogMessage         := 'FillInterimTable finished...';

  CreateProcessExecutionLog(pid_Cutoff, vn_ProcessIndependent, vs_LogMessage);

 

  COMMIT;

END FillInterimTable;

/

CREATE OR REPLACE PROCEDURE ProcessInterimTable

(

  pid_Cutoff            IN CUTOFF_INTERIM_TABLE.CUTOFF%TYPE,

  pin_ProcessNumber     IN CUTOFF_INTERIM_TABLE.PROCESS_NUMBER%TYPE,

  pin_TotalProcessCount IN NUMBER

) IS

  vs_LogMessage         CUTOFF_PROCESS_EXECUTION_LOG.LOG_MESSAGE%TYPE;

BEGIN

  vs_LogMessage         := 'ProcessInterimTable started for process ' || pin_ProcessNumber;

  CreateProcessExecutionLog(pid_Cutoff, pin_ProcessNumber, vs_LogMessage);

  IF pin_TotalProcessCount = 1 AND pin_ProcessNumber = 1 THEN

    -- there is only one process, no need parallel processing

    FillInterimTable(pid_Cutoff, pin_TotalProcessCount);

  END IF;

  FOR rec_InterimTable IN (

    SELECT T.CO_ID, T.REMARK, T.ROWID AS RD

      FROM CUTOFF_INTERIM_TABLE T

     WHERE STATUS = 'N'

       AND PROCESS_NUMBER = pin_ProcessNumber

    ) LOOP

    --some business processes

    ---

    ----

    UPDATE CUTOFF_INTERIM_TABLE

       SET STATUS = 'S',

           REMARK = 'Successfully processed..'

     WHERE ROWID = rec_InterimTable.RD;

    COMMIT;

  END LOOP;

  vs_LogMessage         := 'ProcessInterimTable finished for process ' || pin_ProcessNumber;

  CreateProcessExecutionLog(pid_Cutoff, pin_ProcessNumber, vs_LogMessage);

END ProcessInterimTable;

/

First process Scheduler configuration(5 processes):

DECLARE

  vn_TotalProcessCount   PLS_INTEGER;

  vd_CutOff              DATE;

BEGIN

  vn_TotalProcessCount := 5;

  vd_CutOff            := TO_DATE('20.09.2011', 'DD.MM.YYYY');

  DBMS_SCHEDULER.create_program(program_name        => 'CUTOFF__FILL_PRG',

                                program_type        => 'STORED_PROCEDURE',

                                program_action      => 'FillInterimTable',

                                number_of_arguments => 2,

                                enabled             => FALSE,

                                comments            => 'Program for FillInterimTable(5 parallel processes)');

  DBMS_SCHEDULER.define_program_argument(program_name      => 'CUTOFF__FILL_PRG',

                                         argument_position => 1,

                                         argument_type     => 'DATE',

                                         default_value     => vd_CutOff);

  DBMS_SCHEDULER.define_program_argument(program_name      => 'CUTOFF__FILL_PRG',

                                         argument_position => 2,

                                         argument_type     => 'NUMBER',

                                         default_value     => vn_TotalProcessCount);

  DBMS_SCHEDULER.create_job(job_name     => 'CUTOFF__FILL_JOB',

                            program_name => 'CUTOFF__FILL_PRG',

                            start_date   => NULL,

                            enabled      => FALSE,

                            auto_drop    => FALSE,

                            comments     => 'Job for FillInterimTable(5 parallel processes)');

END;

/

BEGIN

  DBMS_SCHEDULER.enable(name => 'CUTOFF__FILL_PRG' );

  DBMS_SCHEDULER.enable(name => 'CUTOFF__FILL_JOB' );

END;

/

Second process Scheduler configuration(5 processes):

DECLARE

  vn_TotalProcessCount   PLS_INTEGER;

  vd_CutOff              DATE;

BEGIN

  vn_TotalProcessCount := 5;

  vd_CutOff            := TO_DATE('20.09.2011', 'DD.MM.YYYY');

 

  FOR vn_ProcessNumber IN 1..vn_TotalProcessCount LOOP

    DBMS_SCHEDULER.create_program(program_name        => 'CUTOFF__EXEC' || vn_ProcessNumber || '_PRG',

                                  program_type        => 'STORED_PROCEDURE',

                                  program_action      => 'ProcessInterimTable',

                                  number_of_arguments => 3,

                                  enabled             => FALSE,

                                  comments            => 'Program ProcessInterimTable ' || vn_ProcessNumber || ' of 5 process');

      DBMS_SCHEDULER.define_program_argument(program_name      => 'CUTOFF__EXEC' || vn_ProcessNumber || '_PRG',

                                             argument_position => 1,

                                             argument_type     => 'DATE',

                                             default_value     => vd_CutOff);

      DBMS_SCHEDULER.define_program_argument(program_name      => 'CUTOFF__EXEC' || vn_ProcessNumber || '_PRG',

                                             argument_position => 2,

                                             argument_type     => 'NUMBER',

                                             default_value     => vn_ProcessNumber);

      DBMS_SCHEDULER.define_program_argument(program_name      => 'CUTOFF__EXEC' || vn_ProcessNumber || '_PRG',

                                             argument_position => 3,

                                             argument_type     => 'NUMBER',

                                             default_value     => vn_TotalProcessCount);

      DBMS_SCHEDULER.create_job(job_name     => 'CUTOFF__EXEC' || vn_ProcessNumber || '_JOB',

                                program_name => 'CUTOFF__EXEC' || vn_ProcessNumber || '_PRG',

                                start_date   => NULL,

                                enabled      => FALSE,

                                auto_drop    => FALSE,

                                comments     => 'Job for ProcessInterimTable ' || vn_ProcessNumber || ' of 5 process');

  END LOOP;

 

END;

/

DECLARE

  vn_TotalProcessCount   PLS_INTEGER;

BEGIN

  vn_TotalProcessCount := 5;

  FOR vn_ProcessNumber IN 1..vn_TotalProcessCount LOOP

    DBMS_SCHEDULER.enable(name => 'CUTOFF__EXEC' || vn_ProcessNumber || '_JOB');

    DBMS_SCHEDULER.enable(name => 'CUTOFF__EXEC' || vn_ProcessNumber || '_PRG');

  END LOOP;

END;

/

Scheduler Chain configuration:

BEGIN

  DBMS_SCHEDULER.create_chain(chain_name          => 'CUTOFF__CHAIN',

                              rule_set_name       => NULL,

                              evaluation_interval => NULL,

                              comments            => 'Chain for CUTOFF( 5 processes)');

                                  

  DBMS_SCHEDULER.define_chain_step(chain_name   => 'CUTOFF__CHAIN',

                                   step_name    => 'FILL_INTERIM_TABLE_STEP',

                                   program_name => 'CUTOFF__FILL_PRG');

  DBMS_SCHEDULER.define_chain_step(chain_name   => 'CUTOFF__CHAIN',

                                   step_name    => 'EXECUTE_1OF5_STEP',

                                   program_name => 'CUTOFF__EXEC1_PRG');

  DBMS_SCHEDULER.define_chain_step(chain_name   => 'CUTOFF__CHAIN',

                                   step_name    => 'EXECUTE_2OF5_STEP',

                                   program_name => 'CUTOFF__EXEC2_PRG');

  DBMS_SCHEDULER.define_chain_step(chain_name   => 'CUTOFF__CHAIN',

                                   step_name    => 'EXECUTE_3OF5_STEP',

                                   program_name => 'CUTOFF__EXEC3_PRG');

  DBMS_SCHEDULER.define_chain_step(chain_name   => 'CUTOFF__CHAIN',

                                   step_name    => 'EXECUTE_4OF5_STEP',

                                   program_name => 'CUTOFF__EXEC4_PRG');

  DBMS_SCHEDULER.define_chain_step(chain_name   => 'CUTOFF__CHAIN',

                                   step_name    => 'EXECUTE_5OF5_STEP',

                                   program_name => 'CUTOFF__EXEC5_PRG');

END;

/

BEGIN

                                  

  DBMS_SCHEDULER.define_chain_rule(chain_name => 'CUTOFF__CHAIN',

                                   condition  => 'TRUE',

                                   action     => 'START FILL_INTERIM_TABLE_STEP',

                                   rule_name  => 'FILL_INTERIM_TABLE_RULE',

                                   comments   => 'Start CUTOFF - Fill Interim Table.');

  DBMS_SCHEDULER.define_chain_rule(chain_name => 'CUTOFF__CHAIN',

                                   condition  => 'FILL_INTERIM_TABLE_STEP SUCCEEDED',

                                   action     => 'START EXECUTE_1OF5_STEP',

                                   rule_name  => 'EXECUTE_1OF5_RULE',

                                   comments   => 'Execute CUTOFF - 1 of 5 process.');

  DBMS_SCHEDULER.define_chain_rule(chain_name => 'CUTOFF__CHAIN',

                                   condition  => 'FILL_INTERIM_TABLE_STEP SUCCEEDED',

                                   action     => 'START EXECUTE_2OF5_STEP',

                                   rule_name  => 'EXECUTE_2OF5_RULE',

                                   comments   => 'Execute CUTOFF - 2 of 5 process.');

  DBMS_SCHEDULER.define_chain_rule(chain_name => 'CUTOFF__CHAIN',

                                   condition  => 'FILL_INTERIM_TABLE_STEP SUCCEEDED',

                                   action     => 'START EXECUTE_3OF5_STEP',

                                   rule_name  => 'EXECUTE_3OF5_RULE',

                                   comments   => 'Execute CUTOFF - 3 of 5 process.');

  DBMS_SCHEDULER.define_chain_rule(chain_name => 'CUTOFF__CHAIN',

                                   condition  => 'FILL_INTERIM_TABLE_STEP SUCCEEDED',

                                   action     => 'START EXECUTE_4OF5_STEP',

                                   rule_name  => 'EXECUTE_4OF5_RULE',

                                   comments   => 'Execute CUTOFF - 4 of 5 process.');

  DBMS_SCHEDULER.define_chain_rule(chain_name => 'CUTOFF__CHAIN',

                                   condition  => 'FILL_INTERIM_TABLE_STEP SUCCEEDED',

                                   action     => 'START EXECUTE_5OF5_STEP',

                                   rule_name  => 'EXECUTE_5OF5_RULE',

                                   comments   => 'Execute CUTOFF - 5 of 5 process.');                             

                                  

  DBMS_SCHEDULER.define_chain_rule(chain_name => 'CUTOFF__CHAIN',

                                   condition  => '(FILL_INTERIM_TABLE_STEP FAILED) OR (EXECUTE_1OF5_STEP COMPLETED AND EXECUTE_2OF5_STEP COMPLETED AND EXECUTE_3OF5_STEP COMPLETED AND EXECUTE_4OF5_STEP COMPLETED AND EXECUTE_5OF5_STEP COMPLETED)',

                                   action     => 'END',

                                   rule_name  => 'EXECUTE_END_RULE',

                                   comments   => 'Execute CUTOFF - finished.');

END;

/

BEGIN

  DBMS_SCHEDULER.ENABLE('CUTOFF__CHAIN');

END;

/

Job configuraion that will be used to invoke Chain:

BEGIN

  DBMS_SCHEDULER.create_job(job_name     => 'CUTOFF__CHAIN_JOB',

                            job_type     => 'CHAIN',

                            job_action   => 'CUTOFF__CHAIN',

                            start_date   => NULL,

                            enabled      => FALSE,

                            auto_drop    => FALSE,

                            comments     => 'Job for CUTOFF__CHAIN ');

END;

/

Every thing is OK.  We can test our implementation.

TRUNCATE TABLE CUTOFF_INTERIM_TABLE;

DELETE FROM CUTOFF_PROCESS_EXECUTION_LOG;

COMMIT;

BEGIN

 DBMS_SCHEDULER.run_job(job_name => 'CUTOFF__CHAIN_JOB',use_current_session => FALSE);

END;

/

Let’s check running chain:

SQL> SELECT step_name, state FROM USER_SCHEDULER_RUNNING_CHAINS WHERE CHAIN_NAME = 'CUTOFF__CHAIN' order by start_date;

STEP_NAME                      STATE

------------------------------ ---------------

FILL_INTERIM_TABLE_STEP        RUNNING

EXECUTE_2OF5_STEP              NOT_STARTED

EXECUTE_3OF5_STEP              NOT_STARTED

EXECUTE_4OF5_STEP              NOT_STARTED

EXECUTE_5OF5_STEP              NOT_STARTED

EXECUTE_1OF5_STEP              NOT_STARTED

6 rows selected

SQL> SELECT step_name, state FROM USER_SCHEDULER_RUNNING_CHAINS WHERE CHAIN_NAME = 'CUTOFF__CHAIN' order by start_date;

STEP_NAME                      STATE

------------------------------ ---------------

FILL_INTERIM_TABLE_STEP        SUCCEEDED

EXECUTE_2OF5_STEP              RUNNING

EXECUTE_3OF5_STEP              RUNNING

EXECUTE_4OF5_STEP              NOT_STARTED

EXECUTE_5OF5_STEP              NOT_STARTED

EXECUTE_1OF5_STEP              NOT_STARTED

6 rows selected

SQL> SELECT step_name, state FROM USER_SCHEDULER_RUNNING_CHAINS WHERE CHAIN_NAME = 'CUTOFF__CHAIN' order by start_date;

STEP_NAME                      STATE

------------------------------ ---------------

FILL_INTERIM_TABLE_STEP        SUCCEEDED

EXECUTE_2OF5_STEP              RUNNING

EXECUTE_3OF5_STEP              RUNNING

EXECUTE_1OF5_STEP              RUNNING

EXECUTE_4OF5_STEP              RUNNING

EXECUTE_5OF5_STEP              RUNNING

6 rows selected

SQL> SELECT step_name, state FROM USER_SCHEDULER_RUNNING_CHAINS WHERE CHAIN_NAME = 'CUTOFF__CHAIN' order by start_date;

STEP_NAME                      STATE

------------------------------ ---------------

FILL_INTERIM_TABLE_STEP        SUCCEEDED

EXECUTE_2OF5_STEP              RUNNING

EXECUTE_3OF5_STEP              RUNNING

EXECUTE_1OF5_STEP              RUNNING

EXECUTE_4OF5_STEP              RUNNING

EXECUTE_5OF5_STEP              RUNNING

6 rows selected

SQL> SELECT step_name, state FROM USER_SCHEDULER_RUNNING_CHAINS WHERE CHAIN_NAME = 'CUTOFF__CHAIN' order by start_date;

STEP_NAME                      STATE

------------------------------ ---------------

FILL_INTERIM_TABLE_STEP        SUCCEEDED

EXECUTE_2OF5_STEP              SUCCEEDED

EXECUTE_3OF5_STEP              SUCCEEDED

EXECUTE_1OF5_STEP              SUCCEEDED

EXECUTE_4OF5_STEP              RUNNING

EXECUTE_5OF5_STEP              SUCCEEDED

6 rows selected

SQL> SELECT step_name, state FROM USER_SCHEDULER_RUNNING_CHAINS WHERE CHAIN_NAME = 'CUTOFF__CHAIN' order by start_date;

STEP_NAME                      STATE

------------------------------ ---------------

SQL>

SQL> SELECT job_subname, status,actual_start_date, additional_info FROM USER_SCHEDULER_JOB_RUN_DETAILS WHERE JOB_NAME = 'CUTOFF__CHAIN_JOB' ORDER BY log_id DESC;

JOB_SUBNAME                 STATUS          ACTUAL_START_DATE                                 ADDITIONAL_INFO

--------------------------- --------------- ------------------------------------------------- ----------------------------------------------------------

                            SUCCEEDED       30/10/2011 16:22:32,068405 +02:00                 CHAIN_LOG_ID="333627"

EXECUTE_4OF5_STEP           SUCCEEDED       30/10/2011 16:22:37,258659 AFRICA/CAIRO           CHAIN_LOG_ID="333627", STEP_NAME="EXECUTE_4OF5_STEP"

EXECUTE_2OF5_STEP           SUCCEEDED       30/10/2011 16:22:37,198698 AFRICA/CAIRO           CHAIN_LOG_ID="333627", STEP_NAME="EXECUTE_2OF5_STEP"

EXECUTE_5OF5_STEP           SUCCEEDED       30/10/2011 16:22:37,318665 AFRICA/CAIRO           CHAIN_LOG_ID="333627", STEP_NAME="EXECUTE_5OF5_STEP"

EXECUTE_1OF5_STEP           SUCCEEDED       30/10/2011 16:22:37,108348 AFRICA/CAIRO           CHAIN_LOG_ID="333627", STEP_NAME="EXECUTE_1OF5_STEP"

EXECUTE_3OF5_STEP           SUCCEEDED       30/10/2011 16:22:37,206311 AFRICA/CAIRO           CHAIN_LOG_ID="333627", STEP_NAME="EXECUTE_3OF5_STEP"

FILL_INTERIM_TABLE_STEP     SUCCEEDED       30/10/2011 16:22:32,112917 AFRICA/CAIRO           CHAIN_LOG_ID="333627", STEP_NAME="FILL_INTERIM_TABLE_STEP"

SQL> SELECT PROCESS_NUMBER, LOG_MESSAGE, LOG_TIMESTAMP FROM CUTOFF_PROCESS_EXECUTION_LOG ORDER BY LOG_TIMESTAMP;

PROCESS_NUMBER LOG_MESSAGE                                  LOG_TIMESTAMP

-------------- -------------------------------------------- ----------------------------

             0 FillInterimTable started...                  30/10/2011 16:22:32,210113

             0 FillInterimTable finished...                 30/10/2011 16:22:37,076799

             2 ProcessInterimTable started for process 2    30/10/2011 16:22:37,200165

             3 ProcessInterimTable started for process 3    30/10/2011 16:22:37,229709

             1 ProcessInterimTable started for process 1    30/10/2011 16:22:37,338941

             4 ProcessInterimTable started for process 4    30/10/2011 16:22:37,530999

             5 ProcessInterimTable started for process 5    30/10/2011 16:22:37,631659

             3 ProcessInterimTable finished for process 3   30/10/2011 16:22:47,462734

             1 ProcessInterimTable finished for process 1   30/10/2011 16:22:48,243164

             5 ProcessInterimTable finished for process 5   30/10/2011 16:22:48,411946

             2 ProcessInterimTable finished for process 2   30/10/2011 16:22:48,470194

             4 ProcessInterimTable finished for process 4   30/10/2011 16:22:48,489575

12 rows selected

SQL>

As you can see above, a few configuration is enough in order to implement our design.

Please note that i have used cutoff parameter hard-coded in the programs above. This is because of restrictions of Oracle Chains. It is not possible to set job argument values on the fly with chains. I have made some tricks to avoid this situation. I will share this trick later J

To rollback everything:

DROP TABLE CUTOFF_PROCESS_EXECUTION_LOG;

DROP TABLE CUTOFF_INTERIM_TABLE;

DROP PROCEDURE CreateProcessExecutionLog;

DROP PROCEDURE FillInterimTable;

DROP PROCEDURE ProcessInterimTable;

BEGIN

  DBMS_SCHEDULER.drop_chain(chain_name => 'CUTOFF__CHAIN',force => true);

END;

/

BEGIN

  DBMS_SCHEDULER.purge_log(job_name => 'CUTOFF__CHAIN_JOB');

  DBMS_SCHEDULER.drop_job(job_name => 'CUTOFF__CHAIN_JOB');

END;

/

BEGIN

  DBMS_SCHEDULER.purge_log(job_name => 'CUTOFF__FILL_JOB');

  DBMS_SCHEDULER.drop_job(job_name => 'CUTOFF__FILL_JOB');

  DBMS_SCHEDULER.drop_program(program_name => 'CUTOFF__FILL_PRG');

END;

/

DECLARE

  vn_TotalProcessCount   PLS_INTEGER;

BEGIN

  vn_TotalProcessCount := 5;

  FOR vn_ProcessNumber IN 1..vn_TotalProcessCount LOOP

    DBMS_SCHEDULER.purge_log(job_name        => 'CUTOFF__EXEC' || vn_ProcessNumber || '_JOB');

    DBMS_SCHEDULER.drop_job(job_name         => 'CUTOFF__EXEC' || vn_ProcessNumber || '_JOB');

    DBMS_SCHEDULER.drop_program(program_name => 'CUTOFF__EXEC' || vn_ProcessNumber || '_PRG');

  END LOOP;

END;

/

Useful queries to check Scheduler objects:

SELECT * FROM USER_SCHEDULER_JOBS WHERE JOB_NAME LIKE 'CUTOFF%';

SELECT * FROM USER_SCHEDULER_JOB_ARGS WHERE JOB_NAME LIKE 'CUTOFF%';

SELECT * FROM USER_SCHEDULER_PROGRAMS WHERE PROGRAM_NAME LIKE 'CUTOFF%';

SELECT * FROM USER_SCHEDULER_PROGRAM_ARGS WHERE PROGRAM_NAME LIKE 'CUTOFF%';

SELECT * FROM USER_SCHEDULER_CHAINS WHERE CHAIN_NAME LIKE 'CUTOFF%';

SELECT * FROM USER_SCHEDULER_CHAIN_STEPS WHERE CHAIN_NAME LIKE 'CUTOFF%';

SELECT * FROM USER_SCHEDULER_CHAIN_RULES WHERE CHAIN_NAME LIKE 'CUTOFF%';

SELECT * FROM USER_SCHEDULER_RUNNING_JOBS WHERE JOB_NAME LIKE 'CUTOFF%';

SELECT * FROM USER_SCHEDULER_RUNNING_CHAINS WHERE CHAIN_NAME = 'CUTOFF__CHAIN' order by start_date;

SELECT * FROM USER_SCHEDULER_JOB_LOG WHERE JOB_NAME LIKE 'CUTOFF%' ORDER BY 1 DESC;

SELECT * FROM USER_SCHEDULER_JOB_RUN_DETAILS WHERE JOB_NAME LIKE 'CUTOFF%' ORDER BY log_id DESC;

SELECT * FROM SYS.SCHEDULER$_EVENT_LOG /*WHERE LOG_ID = 333551*/;