Example Script to create Database Jobs (DBMS_SCHEDULER.CREATE

Sunday, April 14, 2019

Example Script to create Database Jobs (DBMS_SCHEDULER.CREATE_JOB)

In this post, we have a given an example script to schedule a database job to run a PLSQL procedure for every 2 minutes once.

Wrapper Procedure:

CREATE OR REPLACE PROCEDURE schedule_job

                 (pov_errbuf             OUT VARCHAR2,

                  pon_retcode            OUT NUMBER,

                  piv_job_name           IN VARCHAR2,

                  piv_comments           IN VARCHAR2,

                  pid_start_date         IN VARCHAR2,

                  piv_repeat_frequency   IN VARCHAR2,

                  piv_repeat_interval    IN VARCHAR2,

                  pid_end_date           IN VARCHAR2,

                  piv_program_name       IN VARCHAR2,

                  piv_enabled            IN VARCHAR2 DEFAULT 'Y',

                  piv_auto_drop          IN VARCHAR2 DEFAULT 'N'

                 )

IS

le_end_of_program    EXCEPTION;

lb_enabled           BOOLEAN;

lb_auto_drop         BOOLEAN DEFAULT FALSE;

lv_repeat_interval   VARCHAR2(200);

lv_dummy_char        VARCHAR2(200);

lrec_job             all_scheduler_jobs%ROWTYPE;

position_            NUMBER;

BEGIN

    position_ := 10;

    IF piv_enabled = 'Y'

    THEN

      lb_enabled := TRUE;

    END IF;



    position_ := 20;

    IF piv_auto_drop = 'Y'

    THEN

      lb_auto_drop := TRUE;

    END IF;



    position_ := 30;

    -- prepare the repeat interval

    lv_repeat_interval := 'FREQ='||piv_repeat_frequency||';INTERVAL='||piv_repeat_interval;



    position_ := 40;

    -- check is job already exists

    BEGIN

      SELECT job_name

        INTO lv_dummy_char

        FROM dba_scheduler_jobs

       WHERE UPPER(job_name) = piv_job_name;



       pov_errbuf := 'Job already exists.';

       RAISE le_end_of_program;



    EXCEPTION

      WHEN NO_DATA_FOUND THEN

        NULL;

      WHEN OTHERS THEN

        pov_errbuf := 'Error while validating Job.Error:'||SQLERRM;

        RAISE le_end_of_program;

    END;



    position_ := 50;

    -- check is any database object already exists with same name

    BEGIN

      SELECT object_type

        INTO lv_dummy_char

        FROM dba_objects

       WHERE UPPER(object_name) = piv_job_name;



       pov_errbuf := 'A Database Object of type '||lv_dummy_char||' already exists.Error:'||SQLERRM;

       RAISE le_end_of_program;



    EXCEPTION

      WHEN NO_DATA_FOUND THEN

        NULL;

      WHEN OTHERS THEN

        pov_errbuf := 'Error while validating Job.Error:'||SQLERRM;

        RAISE le_end_of_program;

    END;

    position_ := 60;

    -- Job defined by an existing program and inline schedule.

    DBMS_SCHEDULER.create_job (

       job_name        => piv_job_name,

       job_type        => 'PLSQL_BLOCK',

       job_action      => 'BEGIN '||REPLACE(piv_program_name,'''','''''')||'; END;',

       start_date      => TO_DATE(pid_start_date,'RRRR/MM/DD HH24:MI:SS'),

       repeat_interval => lv_repeat_interval,

       end_date        => TO_DATE(pid_end_date,'RRRR/MM/DD HH24:MI:SS'),

       enabled         => lb_enabled,

       comments        => piv_comments

       );



EXCEPTION

    WHEN le_end_of_program THEN

      pon_retcode :=   1;

      DBMS_OUTPUT.PUT_LINE(pov_errbuf);

    WHEN OTHERS THEN

      pov_errbuf := 'Unhandled Error while Creating job at position '||position_||'Error:'||SQLERRM;

      pon_retcode :=   2;

END schedule_job;

Sample Package:

CREATE OR REPLACE PROCEDURE TEST_MAIN

IS

BEGIN

NULL;

END;

Testing Script:

SET SERVEROUTPUT ON;

DECLARE

lv_errbuf VARCHAR2(4000);

ln_retcode NUMBER;

BEGIN

xxif_manage_schedules_pkg.schedule_job

   (pov_errbuf             => lv_errbuf,

    pon_retcode            => ln_retcode,

    piv_job_name           => 'TEST_SHAREORACLEAPPS',

    piv_comments           => 'Job defined inline schedule.',

    pid_start_date         => TO_CHAR(SYSDATE,'RRRR/MM/DD HH24:MI:SS'),

    piv_repeat_frequency   => 'MINUTELY',

    piv_repeat_interval    => '2',

    pid_end_date           => TO_CHAR(SYSDATE+1,'RRRR/MM/DD HH24:MI:SS'),

    piv_program_name       => 'TEST_MAIN()',

    piv_enabled            => 'Y',

    piv_auto_drop          => 'Y'

    );

dbms_output.put_line(lv_errbuf);

END;