How to run external scripts from DBMS_SCHEDULER

In this post, I am going to explain how to run operating system level script files with using DBMS_SCHEDULER. All tests have been done in Oracle 19c. Generally, we use to schedule PL/SQL blocks or stored procedures for our daily jobs. In addition, different kinds of program types can also be executed by DBMS_SCHEDULER. The basic working logic is shown from the below diagram.

DBMS_SCHEDULER diagram

So, let’s create a sample shell script file in the server.

[root@db1 ~]# su - oracle
Last login: Sun Apr 18 14:27:20 +03 2021
[oracle@db1 ~]$ cd /opt/oracle
[oracle@db1 oracle]$
[oracle@db1 oracle]$
[oracle@db1 oracle]$ touch test.sh
[oracle@db1 oracle]$ echo '#!/bin/sh' > test.sh
[oracle@db1 oracle]$ echo 'echo Welcome to $1' >> test.sh
[oracle@db1 oracle]$
[oracle@db1 oracle]$ cat test.sh
#!/bin/sh
Welcome to $1 blog
[oracle@db1 oracle]$ chmod +x test.sh
[oracle@db1 oracle]$ ls -l test.sh
-rwxr-xr-x. 1 oracle oinstall 29 Apr 18 15:05 test.sh

As it can be seen, it is a basic shell script file that prints the given parameter to the screen.

Note that the following privileges has to be granted in order to use the required packages.

GRANT CREATE JOB TO DB_USER;

GRANT CREATE EXTERNAL JOB TO DB_USER;

GRANT CREATE CREDENTIAL TO DB_USER;

Before we start, credential needs to be created. Credentials provide an interface for authenticating local and remote external jobs. Basically, it is a database object that holds username and password of the operating system user. Furthermore, You can display the system credentials from the [DBA|ALL|USER]_CREDENTIALS table.

BEGIN

    DBMS_CREDENTIAL.CREATE_CREDENTIAL
    (
        CREDENTIAL_NAME => 'CREDENTIAL_TEST',
        USERNAME        => 'oracle',
        PASSWORD        => 'password'
    );

END;
SQL> SET LINESIZE 200
SQL> COLUMN CREDENTIAL_NAME FORMAT A20
SQL> COLUMN USERNAME FORMAT A20
SQL> COLUMN WINDOWS_DOMAIN FORMAT A20
SQL> COLUMN ENABLED FORMAT A20
SQL>
SQL> SELECT CREDENTIAL_NAME, USERNAME, WINDOWS_DOMAIN, ENABLED FROM DBA_CREDENTIALS;

CREDENTIAL_NAME      USERNAME             WINDOWS_DOMAIN       ENABLED
-------------------- -------------------- -------------------- --------------------
CREDENTIAL_TEST      oracle                                    TRUE

BEGIN
    
    --Step 1, create the program that points to the shell script file that you want to run.
    --If no argument required, set the NUMBER_OF_ARGUMENTS parameter to 0 and continue to the step 2.
    DBMS_SCHEDULER.CREATE_PROGRAM
    (
        PROGRAM_NAME => 'PROG_SHELL',
        PROGRAM_TYPE => 'EXECUTABLE',
        PROGRAM_ACTION => '/opt/oracle/test.sh',
        NUMBER_OF_ARGUMENTS => 1,      
        ENABLED => FALSE
    );

    --Define the program argument if required.
    DBMS_SCHEDULER.DEFINE_PROGRAM_ARGUMENT 
    (
        PROGRAM_NAME      => 'PROG_SHELL',
        ARGUMENT_POSITION => 1,
        ARGUMENT_NAME     => 'PARAM1',        
        ARGUMENT_TYPE     => 'VARCHAR2',
        DEFAULT_VALUE     => 'any default value'
    );
    
    DBMS_SCHEDULER.ENABLE(name=>'PROG_SHELL');
    
    --Step 2, define the schedule
    --I set the interval value as daily, you can change it for your schedule.
    SYS.DBMS_SCHEDULER.CREATE_SCHEDULE
    (
      SCHEDULE_NAME    => 'TEST_SCHEDULE',
      REPEAT_INTERVAL  => 'FREQ=DAILY; BYDAY=MON,TUE,WED,THU,FRI; BYHOUR=10; BYMINUTE=0; BYSECOND=0;'
    );    
    
    --The last step is creating the job that call the executable.
    DBMS_SCHEDULER.CREATE_JOB
    (
        JOB_NAME     => 'JOB_TEST',
        PROGRAM_NAME => 'PROG_SHELL',
        START_DATE => SYSTIMESTAMP,
        REPEAT_INTERVAL => 'TEST_SCHEDULE',
        END_DATE => NULL,
        CREDENTIAL_NAME => 'CREDENTIAL_TEST',
        COMMENTS => 'Program to run shell script',
        ENABLED => TRUE,
        AUTO_DROP => FALSE
    );
    
    --Setting the program argument value.
    DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE
    (
        JOB_NAME =>'JOB_TEST', 
        ARGUMENT_POSITION => 1, 
        ARGUMENT_VALUE => 'http://www.oracleplsqltr.com/'
    );    

END;

Now, it is time to execute the job manually.

BEGIN

    DBMS_SCHEDULER.RUN_JOB('JOB_TEST');

END;
SQL> SET LINESIZE 200
SQL> COLUMN LOG_DATE FORMAT A35
SQL> COLUMN JOB_NAME FORMAT A10
SQL> COLUMN STATUS FORMAT A10
SQL> COLUMN CREDENTIAL_NAME FORMAT A20
SQL> COLUMN ERROR# FORMAT A10
SQL> COLUMN ERRORS FORMAT A10
SQL> COLUMN OUTPUT FORMAT A40
SQL> COLUMN ERROR# FORMAT 99999999
SQL>
SQL> SELECT
  2      T.LOG_DATE,
  3      T.JOB_NAME,
  4      T.STATUS,
  5      T.CREDENTIAL_NAME,
  6      T.ERROR#,
  7      T.ERRORS,
  8      T.OUTPUT
  9  FROM DBA_SCHEDULER_JOB_RUN_DETAILS T WHERE JOB_NAME = 'JOB_TEST';

LOG_DATE                            JOB_NAME   STATUS     CREDENTIAL_NAME         ERROR# ERRORS     OUTPUT
----------------------------------- ---------- ---------- -------------------- --------- ---------- ----------------------------------------
18/04/2021 18:27:08,259122 +03:00   JOB_TEST   SUCCEEDED  CREDENTIAL_TEST              0            Welcome to http://www.oracleplsqltr.com/


Our script has been run successfully by Oracle scheduler. Furthermore, as indicated in the diagram, the first and the second steps are optional. So, you can write the above job that includes a program and a schedule like below.

BEGIN
    
    DBMS_SCHEDULER.CREATE_JOB
    (
        JOB_NAME            => 'JOB_TEST',
        START_DATE          => SYSTIMESTAMP,
        REPEAT_INTERVAL     => 'FREQ=DAILY; BYDAY=MON,TUE,WED,THU,FRI; BYHOUR=10; BYMINUTE=0; BYSECOND=0;',
        JOB_TYPE            => 'EXECUTABLE',        
        JOB_ACTION          => '/opt/oracle/test.sh',
        NUMBER_OF_ARGUMENTS   => 1,
        CREDENTIAL_NAME     => 'CREDENTIAL_TEST',
        COMMENTS            => 'Program to run shell script',
        ENABLED             => FALSE,
        AUTO_DROP           => FALSE
    );

    DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE
    (
        JOB_NAME =>'JOB_TEST', 
        ARGUMENT_POSITION => 1, 
        ARGUMENT_VALUE => 'http://www.oracleplsqltr.com/'
    );    
    
    DBMS_SCHEDULER.ENABLE(name=>'JOB_TEST');

END;

Consequently, every dba and developer can take the advantage of DBMS_SCHEDULER package easily. Besides, you can display currently running jobs in the database from DBA_SCHEDULER_RUNNING_JOBS table. In addition, for job logs you can select DBA_SCHEDULER_JOB_RUN_DETAILS table.

BEGIN
    
    DBMS_SCHEDULER.DROP_JOB(JOB_NAME => 'JOB_TEST');

    DBMS_SCHEDULER.DROP_PROGRAM(PROGRAM_NAME => 'PROG_SHELL');

    DBMS_CREDENTIAL.DROP_CREDENTIAL('CREDENTIAL_TEST');
    
END;

On windows platforms, OracleJobSchedulerSID service must be running in order to run OS commands via DBMS_SCHEDULER.

Repeat Interval Examples

In this section, you will find some useful examples of REPEAT_INTERVAL parameter.

--Run every Monday, all of them are equivalent
FREQ=DAILY; BYDAY=MON;

FREQ=WEEKLY; BYDAY=MON;

FREQ=YEARLY; BYDAY=MON;
--Run Every 12 hours
FREQ=HOURLY; INTERVAL=12;
--Run the first and last day of each month
FREQ=MONTHLY; BYMONTHDAY=1,-1;
--Run every Friday of each month
FREQ=MONTHLY; BYDAY=FRI;
--Run on the first and last Friday of each month
FREQ=MONTHLY; BYDAY=1FRI,-1FRI;
--Run on the last day of every month
FREQ=MONTHLY; BYMONTHDAY=-1;
--Run on April 15th, both of them are equivalent
FREQ=YEARLY; BYMONTH=APR; BYMONTHDAY=15;

FREQ=YEARLY; BYDATE=0415;
--Run every they at 16:30:00, 17:30:00 and 18:30:00
FREQ=DAILY; BYHOUR=16,17,18; BYMINUTE=30; BYSECOND=0;
--Without BYSETPOS the following interval creates a set of four date/times
--1th day at 10 A.M., 1th dat at 17P.M., 16th day at 10 A.M., 16th day at 17P.M.
--By using BYSETPOS, you specify picking the 1st and 4th occurrence of the set.
--As a result, run 1st day at 10 A.M. and 16th day at 17 P.M.
FREQ=MONTHLY; BYMONTHDAY=1,16; BYHOUR=10,17; BYSETPOS=1,4

Moreover, you can also use the following script in order to test your REPEAT_INTERVAL parameter.

DECLARE
    V_START_DATE DATE := TRUNC(SYSDATE);
    V_DATE_AFER DATE;
    V_RUNDATE DATE;
    V_ITERATION NUMBER := 10;
    V_REPEAT_INTERVAL VARCHAR2(4000) := 'FREQ=MONTHLY; BYDAY=FRI;';
BEGIN

    V_DATE_AFER := V_START_DATE;

    FOR REC IN 1..V_ITERATION
    LOOP
    
        DBMS_SCHEDULER.EVALUATE_CALENDAR_STRING(
                                                CALENDAR_STRING => V_REPEAT_INTERVAL, 
                                                START_DATE => V_START_DATE, 
                                                RETURN_DATE_AFTER => V_DATE_AFER, 
                                                NEXT_RUN_DATE => V_RUNDATE
                                               );  
    
        DBMS_OUTPUT.PUT_LINE(TO_CHAR(V_RUNDATE,'DD/MM/YYYY HH24:MI:SS'));
    
        V_DATE_AFER := V_RUNDATE;
    
    END LOOP;

END;

I hope this will help you 🙂

Posted in DBA
Leave a Reply

Your email address will not be published.