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
Learning is not a spectator sport 
Got some thoughts? Leave a comment