Running external programs from the scheduler

October 17, 2016February 5, 2017 Connor McDonald dba, scheduler

Although I normally use the job or scheduler facility to run database-centric style processes, most commonly PL/SQL programs, there is nothing to stop you from using the scheduler to gain control over tasks that might normally need to be done outside of the database. And of course, in the world of virtualization, cloud and other such innovations, the ability to initiate and perform tasks from within the database becomes increasingly useful, since often access to the underlying OS layer is either prohibited or restricted.

For example, here’s a simple demo of performing an original-mode export straight out of the scheduler:



SQL> exec DBMS_SCHEDULER.CREATE_JOB(JOB_NAME => 'CONNOR_JOB',JOB_TYPE => 'EXECUTABLE',JOB_ACTION =>'c:\oracle\product\12.1.0.2\bin\exp.exe', NUMBER_OF_ARGUMENTS => 4);

PL/SQL procedure successfully completed.

SQL> exec DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE('CONNOR_JOB', 1, 'userid=connor/connor@np12');

PL/SQL procedure successfully completed.

SQL> exec DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE('CONNOR_JOB', 2, 'file="c:\temp\dump.dmp"');

PL/SQL procedure successfully completed.

SQL> exec DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE('CONNOR_JOB', 3, 'owner=scott');

PL/SQL procedure successfully completed.

SQL> exec DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE('CONNOR_JOB', 4, 'log="c:\temp\CONNOR_JOB.log"');

PL/SQL procedure successfully completed.

SQL> exec DBMS_SCHEDULER.RUN_JOB(JOB_NAME => 'CONNOR_JOB', USE_CURRENT_SESSION => TRUE);

PL/SQL procedure successfully completed.

SQL> select job_name, status from user_scheduler_job_run_details order by log_date;

JOB_NAME                       STATUS
------------------------------ ------------------------------
CONNOR_JOB                     SUCCEEDED

Note that depending on your platform, you need to have the external job agent running. For example, on this Windows machine, I stopped the “OracleJobScheduler” service, and re-performed the above steps, and you can see the error that comes back.



SQL> exec DBMS_SCHEDULER.DROP_JOB(JOB_NAME => 'CONNOR_JOB');

PL/SQL procedure successfully completed.

SQL> exec DBMS_SCHEDULER.CREATE_JOB(JOB_NAME => 'CONNOR_JOB',JOB_TYPE => 'EXECUTABLE',JOB_ACTION =>'c:\oracle\product\12.1.0.2\bin\exp.exe', NUMBER_OF_ARGUMENTS => 4);

PL/SQL procedure successfully completed.

SQL> exec DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE('CONNOR_JOB', 1, 'userid=connor/connor@np12');

PL/SQL procedure successfully completed.

SQL> exec DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE('CONNOR_JOB', 2, 'file="c:\temp\dump.dmp"');

PL/SQL procedure successfully completed.

SQL> exec DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE('CONNOR_JOB', 3, 'owner=scott');

PL/SQL procedure successfully completed.

SQL> exec DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE('CONNOR_JOB', 4, 'log="c:\temp\CONNOR_JOB.log"');

PL/SQL procedure successfully completed.

SQL> exec DBMS_SCHEDULER.RUN_JOB(JOB_NAME => 'CONNOR_JOB', USE_CURRENT_SESSION => TRUE);
BEGIN DBMS_SCHEDULER.RUN_JOB(JOB_NAME => 'CONNOR_JOB', USE_CURRENT_SESSION => TRUE); END;

*
ERROR at line 1:
ORA-27370: job slave failed to launch a job of type EXECUTABLE
ORA-27300: OS system dependent operation:accessing job scheduler service failed with status: 2
ORA-27301: OS failure message: The system cannot find the file specified.
ORA-27302: failure occurred at: sjsec 5
ORA-27303: additional information: The system cannot find the file specified.
ORA-06512: at "SYS.DBMS_ISCHED", line 209
ORA-06512: at "SYS.DBMS_SCHEDULER", line 594
ORA-06512: at line 1

SQL> select job_name, status from user_scheduler_job_run_details order by log_date;

JOB_NAME                       STATUS
------------------------------ ------------------------------
CONNOR_JOB                     FAILED
CONNOR_JOB                     SUCCEEDED

dbms_output and the scheduler

June 27, 2016March 17, 2017 Connor McDonald 12c, dba, scheduler

One of the nifty things in 12c is the ability to pick up DBMS_OUTPUT output from your scheduler jobs. So if you haven’t built an extensive instrumentation or logging facility, you’ll still have some details you can pick up from the scheduler dictionary views. Let’s look at an example


SQL> create or replace
  2  procedure do_stuff is
  3  begin
  4    dbms_output.put_line('Commenced');
  5    dbms_lock.sleep(30);
  6    dbms_output.put_line('Working');
  7    dbms_lock.sleep(30);
  8    dbms_output.put_line('Done');
  9  end;
 10  /

Procedure created.

SQL>
SQL> begin
  2      dbms_scheduler.create_job (
  3         job_name           =>  'OUTPUT_DEMO',
  4         job_type           =>  'PLSQL_BLOCK',
  5         job_action         =>  'do_stuff;',
  6         start_date         =>  sysdate,
  7         enabled            =>  true,
  8         comments           =>  'Output demo');
  9  end;
 10  /

PL/SQL procedure successfully completed.

After my job completes, I can now query the dictionary


SQL> select OUTPUT
  2  from DBA_SCHEDULER_JOB_RUN_DETAILS
  3  where job_name = 'OUTPUT_DEMO';

OUTPUT
-----------------------------------------------------
Commenced
Working
Done

The column is a VARCHAR2(4000), so what happens if you exceed that ? Let’s change our PL/SQL procedure


SQL> create or replace
  2  procedure do_stuff is
  3  begin
  4    for i in 1 .. 1000 loop
  5      dbms_output.put_line(i||' Commenced');
  6    end loop;
  7  end;
  8  /

Procedure created.

SQL>
SQL> begin
  2      dbms_scheduler.create_job (
  3         job_name           =>  'OUTPUT_DEMO',
  4         job_type           =>  'PLSQL_BLOCK',
  5         job_action         =>  'do_stuff;',
  6         start_date         =>  sysdate,
  7         enabled            =>  true,
  8         comments           =>  'Output demo');
  9  end;
 10  /

PL/SQL procedure successfully completed.

SQL>
SQL> select output
  2  from DBA_SCHEDULER_JOB_RUN_DETAILS
  3  where job_name = 'OUTPUT_DEMO';

OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
Commenced
Working
Done

1 Commenced
2 Commenced
3 Commenced
4 Commenced
5 Commenced
6 Commenced
...
144 Commenced
145 Commenced
146 Commenced
147 Commenced
148 Commenced
149 Commenced
150 Commenced
151 Comm


2 rows selected.

You can see that the output is truncated. However, all of the output IS retained in another column BINARY_OUTPUT which is a blob column. So whilst a basic query seems cryptic


SQL> select BINARY_OUTPUT
  2  from DBA_SCHEDULER_JOB_RUN_DETAILS
  3  where job_name = 'OUTPUT_DEMO';

BINARY_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
436F6D6D656E6365640A576F726B696E670A446F6E65
3120436F6D6D656E6365640A3220436F6D6D656E6365640A3320436F6D6D656E6365640A3420436F6D6D656E6365640A3520436F6D6D656E6365640A3620436F6D
6D656E6365640A3720436F6D6D656E

you can cast it back to a string to get what you need.


SQL> select utl_raw.cast_to_varchar2(BINARY_OUTPUT)
  2  from DBA_SCHEDULER_JOB_RUN_DETAILS
  3  where job_name = 'OUTPUT_DEMO';

UTL_RAW.CAST_TO_VARCHAR2(BINARY_OUTPUT)
----------------------------------------------------------------------------------------------------------------------------------
Commenced
Working
Done

1 Commenced
2 Commenced
3 Commenced
4 Commenced
5 Commenced
...
990 Commenced
991 Commenced
992 Commenced
993 Commenced
994 Commenced
995 Commenced
996 Commenced
997 Commenced
998 Commenced
999 Commenced
1000 Commenced


2 rows selected.

Understanding scheduler syntax

April 1, 2016March 29, 2017 Connor McDonald dba, scheduler

When using strings such as “FREQ=DAILY; BYDAY=MON,TUE,WED,THU,FRI; BYHOUR=9,10″ within the scheduler, sometimes its not readily apparent how this will translate to actual dates and times of the day that the scheduled activity will run. To help you understand, a nice little utility is to use EVALUATE_CALENDAR_STRING”


SQL>  set serveroutput on size 999999
SQL>
SQL> declare
  2    l_start_date TIMESTAMP;
  3    l_next_date TIMESTAMP;
  4    l_return_date TIMESTAMP;
  5  begin
  6    l_start_date := trunc(SYSTIMESTAMP);
  7    l_return_date := l_start_date;
  8
  9    for ctr in 1..10 loop
 10      dbms_scheduler.evaluate_calendar_string(
 11        'FREQ=DAILY; BYDAY=MON,TUE,WED,THU,FRI; BYHOUR=9,10',
 12        l_start_date, l_return_date, l_next_date
 13        );
 14      dbms_output.put_line('Next Run on: ' ||
 15        to_char(l_next_date,'mm/dd/yyyy hh24:mi:ss')
 16        );
 17      l_return_date := l_next_date;
 18    end loop;
 19  end;
 20  /
Next Run on: 03/31/2016 09:00:00
Next Run on: 03/31/2016 10:00:00
Next Run on: 04/01/2016 09:00:00
Next Run on: 04/01/2016 10:00:00
Next Run on: 04/04/2016 09:00:00
Next Run on: 04/04/2016 10:00:00
Next Run on: 04/05/2016 09:00:00
Next Run on: 04/05/2016 10:00:00
Next Run on: 04/06/2016 09:00:00
Next Run on: 04/06/2016 10:00:00

PL/SQL procedure successfully completed.

Learning is not a spectator sport

Connor McDonald's Oracle blog

scheduler

Running external programs from the scheduler

dbms_output and the scheduler

Understanding scheduler syntax