In 12c, Oracle has improved the DBMS_SCHEDULER package functionality adding some interesting and useful new features. One of this new features is the capability to run external scripts as part of our job action or even better the capability to write our own custom scripts and run them by invoking the shell interpreter (in case of unix environments) or the command prompt (in windows environments). This custom script can be directly writed inside the job definition. This new job type is named 'EXTERNAL_SCRIPT'. This definition specifies that the job is an external script that uses the command shell of the computer running the job.
Creating the test environment
First of all, we will create a sample user to be used for testing purposes and grant some privileges to that user:
SQL> CREATE USER extuser
2 IDENTIFIED BY "welcome1"
3 DEFAULT TABLESPACE USERS
4 QUOTA UNLIMITED ON USERS;
User created.
SQL> GRANT CREATE SESSION TO extuser;
Grant succeeded.
SQL> GRANT CREATE JOB TO extuser;
Grant succeeded.
SQL> GRANT CREATE EXTERNAL JOB TO extuser;
Grant succeeded.
SQL> GRANT CREATE CREDENTIAL TO extuser;
Grant succeeded.As these type of jobs run as external executables, we will need to authenticate our procedure to the underlying operating system. This task is performed defining a database objects named "credential."
A credential stores a "host user name + password" pair in a dedicated database object. You set the "credential_name" attribute during the job definition process. The job's external executable then runs with the user name and password specified by that credential.
This job credential will be created in our new EXTUSER user schema:
SQL> BEGIN
2 DBMS_CREDENTIAL.create_credential(
3 credential_name => 'oracle_os_credentials',
4 username => 'oracle',
5 password => 'nueva123'
6 );
7 END;
8 /
PL/SQL procedure successfully completed.Running simple external programs
The following job will create a new subdirectory in "/tmp" directory (named "/tmp/external") and then create a new file named "run_example.txt".
All the process logic will be contained in the "script_file" variable.
SQL> DECLARE
2 script_file varchar2(32000) ;
3 BEGIN
4 script_file := '#!/bin/bash
5 export PATH=$PATH:/bin
6 cd /tmp
7 mkdir external
8 cd external
9 touch run_example.txt';
10
11 DBMS_SCHEDULER.create_job(
12 job_name => 'Z_EXTJOB_01',
13 job_type => 'EXTERNAL_SCRIPT',
14 job_action => script_file,
15 credential_name => 'oracle_os_credentials',
16 enabled => TRUE);
17 END;
18 /
PL/SQL procedure successfully completed.We can query the USER_SCHEDULER_JOB_RUN_DETAILS view to verify execution results:
SQL> COL job_name FORMAT a15
SQL> COL status FORMAT a10
SQL> SELECT job_name,
2 status,
3 error#,
4 to_char(log_date,'yyyy-mm-dd hh24:mi') log_date
5 FROM user_scheduler_job_run_details
6 ORDER BY 1;
JOB_NAME STATUS ERROR# LOG_DATE
--------------- ---------- ---------- ----------------
Z_EXTJOB_01 SUCCEEDED 0 2014-12-10 14:28The output above verifies that job has run successfully.
On the operating system, verify that both new subdirectory and file were created as expected:
[oracle@ora12c ~]$ cd /tmp/external
[oracle@ora12c external]$ ls -l
total 0
-rw-rw-rw- 1 oracle oinstall 0 Dec 10 14:28 run_example.txt
[oracle@ora12c external]$Take a look at "run_example.txt" timestamp, it matches LOG_DATE field value of Z_EXTJOB_01 job execution.
Running binary programs
An interesting choice is to use shell scripts to wrap or spawn binary program executions.
In the "/tmp/external" directory, create and edit a C language program file ("test.c") as follows:
#include
main() {
printf("this is a test\n") ;
return 0 ;
}
Compile and run the program as follows:
[oracle@ora12c external]$ cc test.c -o test.exe
[oracle@ora12c external]$ ./test.exe
this is a test
[oracle@ora12c external]$ echo $?
0
[oracle@ora12c external]$Verify that return code is "0" as expected, this return code will be handled in the future by the DBMS_SCHEDULER package.
Now let's create our wrapper shell script ("test.sh") as follows:
#!/bin/bash
./test.exe 1>test.1 2>test.2
rc=`echo $?`
if [ $rc -ne 0 ]
then
echo "with errors" > test.out
exit $rc
else
echo "succesfully" > test.out
exit 0
fiMake sure that the "test.sh" shell script is executable.
In this example:
- STDOUT will be directed and logged in "test.1" file.
- STDERR will be directed and logged in "test.2" file.
- Script output will be logged in "test.out" file.
Create a new database job in EXTUSER schema that runs "test.sh" as external script:
SQL> DECLARE 2 scriptfile varchar2(32000) ; 3 BEGIN 4 scriptfile := '#!/bin/bash 5 cd /tmp/external 6 ./test.sh' ; 7
8 DBMSSCHEDULER.createjob( 9 jobname => 'ZEXTJOB02', 10 jobtype => 'EXTERNALSCRIPT', 11 jobaction => scriptfile, 12 credentialname => 'oracleoscredentials', 13 enabled => TRUE); 14 END; 15 /
PL/SQL procedure successfully completed.
Let's verify job execution in database:
SQL> COL job_name FORMAT a15
SQL> COL status FORMAT a10
SQL> SELECT job_name,
2 status,
3 error#,
4 to_char(log_date,'yyyy-mm-dd hh24:mi') log_date
5 FROM user_scheduler_job_run_details
6 ORDER BY 1;
JOB_NAME STATUS ERROR# LOG_DATE
--------------- ---------- ---------- ----------------
Z_EXTJOB_01 SUCCEEDED 0 2014-12-10 14:28
Z_EXTJOB_02 SUCCEEDED 0 2014-12-10 14:31And now take a look at "/tmp/external" directory:
[oracle@ora12c ~]$ cd /tmp/external
[oracle@ora12c external]$ ls -l
-rw-rw-rw-. 1 oracle oinstall 0 Nov 27 11:58 run_example.txt
-rw-rw-r--. 1 oracle oinstall 73 Nov 28 10:17 test.c
-rwxrwxr-x. 1 oracle oinstall 6424 Nov 28 10:18 test.exe
-rwxr-x---. 1 oracle oinstall 170 Nov 28 10:19 test.sh
-rw-rw-rw-. 1 oracle oinstall 0 Nov 28 10:28 test.2
-rw-rw-rw-. 1 oracle oinstall 15 Nov 28 10:28 test.1
-rw-rw-rw-. 1 oracle oinstall 12 Nov 28 10:28 test.out
drwxrwxrwx. 2 oracle oinstall 4096 Nov 28 10:29 .
[oracle@ora12c external]$ cat test.out
succesfully
[oracle@ora12c external]$As you can see, now it's very easy to schedule custom shell scripts in Oracle database.
Useful tip
You can clean the xxx_SCHEDULER_JOB_RUN_DETAILS views by executing the following procedure as SYSDBA:
SQL> SELECT COUNT(*)
2 FROM dba_scheduler_job_run_details;
COUNT(*)
----------
117
SQL> EXEC DBMS_SCHEDULER.purge_log;
PL/SQL procedure successfully completed.
SQL>
SQL> SELECT COUNT(*)
2 FROM dba_scheduler_job_run_details;
COUNT(*)
----------
0In future posts, we will address other DBMS_SCHEDULER enhancements introduced in 12c version.