Running external programs from the 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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s