This discussion is archived
1 Reply Latest reply: Oct 26, 2012 1:36 PM by spajdy RSS

DBMS SCHEDULAR CONFUSION

Tina Newbie
Currently Being Moderated
GURUS,

I have to create a DBMS schedulars for stand alone procedure , package.procedure , and anonymous block.
I have some confusion in PL_SQL block and STORED PROCEDURE used.
As you can see job_action in both blocks below is 'package.procedure', however in one I can see job type is 'plsql_block' and in another its ' stored_procedure'.

Can you help me to clear my confusion?

BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => 'my_new_job2',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN SALES_PKG.UPDATE_SALES_SUMMARY; END;',
schedule_name => 'my_saved_schedule');
END;
/
-------------------------------------------------
BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => 'update_sales',
job_type => 'STORED_PROCEDURE',
job_action => 'OPS.SALES_PKG.UPDATE_SALES_SUMMARY',
start_date => '28-APR-03 07.00.00 PM Australia/Sydney',
repeat_interval => 'FREQ=DAILY;INTERVAL=2', /* every other day */
end_date => '20-NOV-04 07.00.00 PM Australia/Sydney',
job_class => 'batch_update_jobs',
comments => 'My new job');
END;
/
  • 1. Re: DBMS SCHEDULAR CONFUSION
    spajdy Pro
    Currently Being Moderated
    What confuse you ?
    PLSQL_BLOCK - this mean that job_action is anonymous PL/SQL block. So job_action is anonymous block
    DECLARE
      --variable declaration
     ...
    BEGIN
     -- main code
     ...
    END;
    or
    BEGIN
     -- main code
     ...
    END;
    STORED_PROCEDURE - this mean that job_action is stand alone procedure or procedure in package

    Scheduler job also could use program or chain.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points