1 Reply Latest reply: Oct 26, 2012 3:36 PM by spajdy RSS

    DBMS SCHEDULAR CONFUSION

    Tina
      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
          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.