Jobs

Package for managing jobs

example

BEGIN
    pck_api_jobs.add('test','pck_api_audit.info', '[{"type":"VARCHAR2", "name": "p_message", "value":"Test job"}]', 'FREQ=WEEKLY; BYDAY=MON; BYHOUR=0; BYMINUTE=0; BYSECOND=0', 'Weekly Test Job');
    pck_api_jobs.run('test');
    pck_api_jobs.remove('test');
END;
/

SELECT *
FROM user_scheduler_job_run_details
WHERE job_name = 'TEST_JOB'
ORDER BY log_date DESC
FETCH FIRST 10 ROWS ONLY;

specification

CREATE OR REPLACE PACKAGE odbvue.pck_api_jobs AS -- Package for managing jobs

    PROCEDURE add ( -- Add a new job
        p_name        VARCHAR2, -- Job name
        p_program     VARCHAR2, -- Program name (PLSQL procedure)
        p_arguments   CLOB, -- JSON array of arguments,  format [] of {type, name, value}
        p_schedule    VARCHAR2, -- Schedule interval, https://docs.oracle.com/en/database/oracle/oracle-database/19/arpls/DBMS_SCHEDULER.html#ARPLS-GUID-73622B78-EFF4-4D06-92F5-E358AB2D58F3
        p_description VARCHAR2 -- Job description
    );

    PROCEDURE remove ( -- Remove a job
        p_name VARCHAR2 -- Job name
    );

    PROCEDURE enable ( -- Enable a job
        p_name VARCHAR2 -- Job name
    );

    PROCEDURE disable ( -- Disable a job
        p_name VARCHAR2 -- Job name
    );

    PROCEDURE run ( -- Run a job
        p_name VARCHAR2 -- Job name
    );

END;
/


-- sqlcl_snapshot {"hash":"69928ece873275b4fe048f350345da76a9826b79","type":"PACKAGE_SPEC","name":"PCK_API_JOBS","schemaName":"ODBVUE","sxml":""}

implementation

CREATE OR REPLACE PACKAGE BODY odbvue.pck_api_jobs AS

    PROCEDURE add (
        p_name        VARCHAR2,
        p_program     VARCHAR2,
        p_arguments   CLOB,
        p_schedule    VARCHAR2,
        p_description VARCHAR2
    ) AS
        v_cnt PLS_INTEGER;
        i     PLS_INTEGER;
    BEGIN
        remove(p_name);
        SELECT
            COUNT(*)
        INTO v_cnt
        FROM
            JSON_TABLE ( p_arguments, '$[*]'
                COLUMNS (
                    type VARCHAR2 PATH '$.type',
                    name VARCHAR2 PATH '$.name',
                    value VARCHAR2 PATH '$.value'
                )
            );

        dbms_scheduler.create_program(
            program_name        => upper(p_name)
                            || '_PROGRAM',
            program_type        => 'STORED_PROCEDURE',
            program_action      => p_program,
            number_of_arguments => v_cnt,
            enabled             => FALSE
        );

        i := 1;
        FOR a IN (
            SELECT
                *
            FROM
                JSON_TABLE ( p_arguments, '$[*]'
                    COLUMNS (
                        type VARCHAR2 PATH '$.type',
                        name VARCHAR2 PATH '$.name',
                        value VARCHAR2 PATH '$.value'
                    )
                )
        ) LOOP
            dbms_scheduler.define_program_argument(
                program_name      => upper(p_name)
                                || '_PROGRAM',
                argument_position => i,
                argument_name     => a.name,
                argument_type     => a.type,
                default_value     => a.value,
                out_argument      => FALSE
            );

            i := i + 1;
        END LOOP;

        dbms_scheduler.enable(upper(p_name) || '_PROGRAM');
        dbms_scheduler.create_schedule(
            schedule_name   => upper(p_name)
                             || '_SCHEDULE',
            start_date      => systimestamp,
            repeat_interval => p_schedule
        );

        dbms_scheduler.create_job(
            job_name      => upper(p_name)
                        || '_JOB',
            program_name  => upper(p_name)
                            || '_PROGRAM',
            schedule_name => upper(p_name)
                             || '_SCHEDULE',
            enabled       => FALSE,
            auto_drop     => TRUE,
            comments      => p_description
        );

        dbms_scheduler.enable(upper(p_name) || '_PROGRAM');
        dbms_scheduler.enable(upper(p_name) || '_JOB');
    END;

    PROCEDURE remove (
        p_name VARCHAR2
    ) AS
    BEGIN
        BEGIN
            dbms_scheduler.drop_job(upper(p_name)
                                    || '_JOB',
                                    TRUE);
        EXCEPTION
            WHEN OTHERS THEN
                NULL;
        END;

        BEGIN
            dbms_scheduler.drop_schedule(upper(p_name) || '_SCHEDULE');
        EXCEPTION
            WHEN OTHERS THEN
                NULL;
        END;

        BEGIN
            dbms_scheduler.drop_program(upper(p_name) || '_PROGRAM');
        EXCEPTION
            WHEN OTHERS THEN
                NULL;
        END;

    END;

    PROCEDURE enable (
        p_name VARCHAR2
    ) AS
    BEGIN
        dbms_scheduler.enable(upper(p_name) || '_JOB');
    END;

    PROCEDURE disable (
        p_name VARCHAR2
    ) AS
    BEGIN
        dbms_scheduler.disable(upper(p_name) || '_JOB');
    END;

    PROCEDURE run (
        p_name VARCHAR2
    ) AS
    BEGIN
        dbms_scheduler.run_job(upper(p_name) || '_JOB');
    END;

END;
/


-- sqlcl_snapshot {"hash":"39f69f981c1a2b0924173b0b481de3a301c96991","type":"PACKAGE_BODY","name":"PCK_API_JOBS","schemaName":"ODBVUE","sxml":""}

ADD

Add a new job

Argument name	In Out	Data type	Default value	Description
P_NAME	IN	VARCHAR2		Job name
P_PROGRAM	IN	VARCHAR2		Program name (PLSQL procedure)
P_ARGUMENTS	IN	CLOB		JSON array of arguments, format [{type, name, value}]
P_SCHEDULE	IN	VARCHAR2		Schedule interval, https://docs.oracle.com/en/database/oracle/oracle-database/19/arpls/DBMS_SCHEDULER.html#ARPLS-GUID-73622B78-EFF4-4D06-92F5-E358AB2D58F3
P_DESCRIPTION	IN	VARCHAR2		Job description

DISABLE

Disable a job

Argument name	In Out	Data type	Default value	Description
P_NAME	IN	VARCHAR2		Job name

ENABLE

Enable a job

Argument name	In Out	Data type	Default value	Description
P_NAME	IN	VARCHAR2		Job name

REMOVE

Remove a job

Argument name	In Out	Data type	Default value	Description
P_NAME	IN	VARCHAR2		Job name

RUN

Run a job

Argument name	In Out	Data type	Default value	Description
P_NAME	IN	VARCHAR2		Job name