how to execute the procedure one after another in oracle sql developer using chains?

Question

I am a beginner to Oracle SQL. I am using Oracle SQL developer tool. I have two procedures called p1 and p2.

How to execute the procedures one by one in particular time daily using chains concept which means I should execute p1 first, once it completely executed. After that I have to execute p2 in that particular time daily.

In my case I have to execute at 3.00 clock. For your reference see the below sample code. Can anyone solve this issue? Thanks in advance

BEGIN 
    DBMS_SCHEDULER.CREATE_CHAIN (
       chain_name            =>  'my_chain1',
       rule_set_name         =>  NULL,
       evaluation_interval   =>  NULL,
       comments              =>  NULL);
    END;
    /

    BEGIN
     DBMS_SCHEDULER.DEFINE_CHAIN_STEP('my_chain1', 'stepA', 'P1');
     DBMS_SCHEDULER.DEFINE_CHAIN_STEP('my_chain1', 'stepB', 'P2');

    END;
    /

    BEGIN
     DBMS_SCHEDULER.DEFINE_CHAIN_RULE('my_chain1', 'TRUE', 'START stepA');
     DBMS_SCHEDULER.DEFINE_CHAIN_RULE('my_chain1', 'stepA COMPLETED', 'Start stepB');
     DBMS_SCHEDULER.DEFINE_CHAIN_RULE('my_chain1', 'stepB COMPLETED', 'END');
    END;
    /

    BEGIN
     DBMS_SCHEDULER.ENABLE('my_chain1');
    END;
    /

    BEGIN
     DBMS_SCHEDULER.CREATE_JOB (
       job_name        => 'chain_job_1',
       job_type        => 'CHAIN',
       job_action      => 'my_chain1',
       repeat_interval => 'freq=daily;byhour=13;byminute=0;bysecond=0',  //In my case I should execute afternoon 1.00 clock so I used 13.00 in byhour
       enabled         => TRUE);
    END;
    /

NOTE: Other than chain concept also welcome

Trudge

"Can anyone solve this issue?" - which issue? Tell us what's not working. – Jetta

Answer 1

I'm guessing issue is here:

DEFINE_CHAIN_STEP('my_chain1', 'stepA', 'P1');  
DEFINE_CHAIN_STEP('my_chain1', 'stepB', 'P2');

P1,p2 - have to be scheduler program (check dbms_scheduler.create_program) or scheduler chain.

But the question is do you really need scheduler chain. IMHO you are trying to do overengineering

   BEGIN
     DBMS_SCHEDULER.CREATE_JOB (
       job_name        => 'simple_plsqlBlock_job',
       job_type        => 'PLSQL_BLOCK',
       job_action      => 'begin p1;
                                 p2; 
                           end;',
       repeat_interval => 'freq=daily;byhour=13;byminute=0;bysecond=0',  //In my case I should execute afternoon 1.00 clock so I used 13.00 in byhour
       enabled         => TRUE);
    END;
    /

Scud