·   PLSQL – Syntax – Jobs

 

- Info:

  - This tutorial shows how to create job which is used to perform certain task at regular intervals

  - This is done by calling SUBMIT function.

 

- Parameters:

  

Name

Description

 

job

Job ID automaticly set by Oracle.

 

what

SQL or  PL/SQL program, function or procedure which should be executed by the job:

what => 'INSERT INTO MYTABLE(NAME) VALUES (''John'');'

what => 'DBMS_OUTPUT.PUT_LINE(''Job executed'');'

 

next_date 

Defines when to execute job for the first time.

You don't have to wait becuse each job can be executed manually at any time.

If this date is in the past job will be executed immediately

 

interval

This  formula is evaluated at the end of the job to calculate when to re-execute the job.

If set to NULL job will not be re-executed.

 

no_parse

TRUE     parse associated procedure only first time when job is executed

FALSE   parse associated procedure every time job is executed

 

- Example:

  - Create "PL/SQL Applications" Test.sql.

 

    Test.sql

    DECLARE

      X NUMBER;

    BEGIN

      SYS.DBMS_JOB.SUBMIT

        (

          job       => X

         ,what      => 'DBMS_OUTPUT.PUT_LINE(''Job executed'');'

         ,next_date => to_date('02.10.2006 03:50:00','dd.mm.yyyy hh24:mi:ss')

         ,interval  => 'trunc(sysdate)+1+3/24+50/1440'   --Re-execute tomorrow at 03:50

         ,no_parse  => TRUE

        );

      SYS.DBMS_OUTPUT.PUT_LINE('Job Number is: ' || TO_CHAR(x));

    END;

    /

    

    COMMIT;