Introduction
With 12c we have several new job types for our scheduler jobs. One of them is EXTERNAL_SCRIPT. The other new job types are SQL_SCRIPT and BACKUP_SCRIPT.
From Oracle 12.2 plsql packages and type reference
‘EXTERNAL_SCRIPT’
This specifies that the job is an external script that uses the command shell of the computer running the job. For Windows this is cmd.exe and for UNIX based systems the sh shell, unless a different interpreter is specified by prefixing the first line of the script with #!.
In the past we could run an external script using the EXECUTABLE job type. This type is still available.
‘EXECUTABLE’
This specifies that the job is going to be run outside the database using an external executable. External jobs are anything that can be executed from the command line of the operating system. Anydata arguments are not supported with a job or program type of EXECUTABLE. The job owner must have the CREATE EXTERNAL JOB system privilege before the job can be enabled or run.
In general both options could do the same thing. Execute something on the host OS.
So why should we change anything? Is there a difference?
Let’s find out.
schedule an EXTERNAL_SCRIPT
setup credentials first
We can create credentials using a dbms package or via SQL Developer.
dbms_credential.create_credential(credential_name => 'ORACLE_OS_CREDS',
username => 'oracle',
password => 'oracle',
comments => 'run scripts using oracle OS account');
In the developer VM box, the password is always oracle. That’s why I included it here. You need to use your own correct password.
For real world environments I suggest to create a specific OS account that is only allowed to execute the script and to do anything that needs to be done for this specific task but not more. This account might need the “Log On As Batch Job” Right under windows (support note #2065024.1).
For demonstration purposes I stay here with the oracle credentials.
setup a scheduled job to run a linux script
Of cause this works under windows too, but I did test it only using Oracle Linux.
Here I setup 4 slightly different examples how to run an EXTERNAL_SCRIPT job. After that we check and compare the output.
These are our for slightly different test scenarios.
- run a simple bash script.
- run a script, that has an error
- run a script with an error, but an exitcode=0
- same as 3. and use undocumented FAIL_ON_SCRIPT_ERROR argument
To test what happens if the script itself has an error, I added a change directory command pointing to a non existent directory.
This command will result in an error.
cd /abcd/efgh/ijk
Here is the command to schedule the 4 jobs. Each job has a slightly different name. The differences between one and the previous job are marked. (sorry new WP editor messed up syntax highlighting for code)
-- First test a script that does not produce an error
declare
v_jobname varchar2(200);
v_good_script clob;
begin
v_jobname := upper('DEMO_SCHEDULED_EXTERNAL_SCRIPT_NO_ERROR');
-- the following line breaks are important.
-- Do NOT remove them, they are part of the linux script.
v_good_script :=
$IF DBMS_DB_VERSION.VER_LE_12_2 $THEN
'#!/bin/bash
'||
$END
'echo "Job ok!"';
dbms_scheduler.create_job(job_name => v_jobname,
job_type => 'EXTERNAL_SCRIPT',
job_action => v_good_script,
credential_name => 'ORACLE_OS_CREDS',
enabled => false,
auto_drop => false
);
-- run the job
dbms_scheduler.enable(v_jobname);
end;
/
PL/SQL procedure successfully completed.
-- Now test a script that does produce an error
declare
v_jobname varchar2(200);
v_bad_script clob;
begin
v_jobname := upper('DEMO_SCHEDULED_EXTERNAL_SCRIPT_WITH_ERROR');
v_bad_script :=
$IF DBMS_DB_VERSION.VER_LE_12_2 $THEN
'#!/bin/bash
'||
$END
'cd /abcd/efgh/ijk';
dbms_scheduler.create_job(job_name => v_jobname,
job_type => 'EXTERNAL_SCRIPT',
job_action => v_bad_script,
credential_name => 'ORACLE_OS_CREDS',
enabled => false,
auto_drop => false
);
dbms_scheduler.enable(v_jobname);
end;
/
PL/SQL procedure successfully completed.
-- Now test a script that does produce an error but uses exit 0
declare
v_jobname varchar2(200);
v_bad_script clob;
begin
v_jobname := upper('DEMO_SCHEDULED_EXTERNAL_SCRIPT_WITH_ERROR_EXIT0');
v_bad_script := $IF DBMS_DB_VERSION.VER_LE_12_2 $THEN '#!/bin/bash '|| $END
'cd /abcd/efgh/ijk
exit 0';
dbms_scheduler.create_job(job_name => v_jobname,
job_type => 'EXTERNAL_SCRIPT',
job_action => v_bad_script,
credential_name => 'ORACLE_OS_CREDS',
enabled => false,
auto_drop => false
);
dbms_scheduler.enable(v_jobname);
end;
/
PL/SQL procedure successfully completed.
-- run script using attribute FAIL_ON_ERROR
declare
v_jobname varchar2(200);
v_bad_script clob;
begin
v_jobname := upper('DEMO_SCHEDULED_EXTERNAL_SCRIPT_WITH_ERROR_FAILONERROR');
v_bad_script := $IF DBMS_DB_VERSION.VER_LE_12_2 $THEN '#!/bin/bash '|| $END
'cd /abcd/efgh/ijk
exit 0';
dbms_scheduler.create_job(job_name => v_jobname,
job_type => 'EXTERNAL_SCRIPT',
job_action => v_bad_script,
credential_name => 'ORACLE_OS_CREDS',
enabled => false,
auto_drop => false
);
-- Make sure script errors result in a job error and are noticed.
dbms_scheduler.set_attribute(
name => v_jobname,
attribute => 'FAIL_ON_SCRIPT_ERROR',
value => true);
-- run the job
dbms_scheduler.enable(v_jobname);
end;
/
PL/SQL procedure successfully completed.
Note that all anonymous blocks executed successfully. This only means that we could create and start a scheduled job. It does not tell us, what the result of the job was.
To find the job result, we need to check the data dictionary view all_scheduler_job_run_details. The information is in there but only AFTER the job finished.
Results
The data dictionary gives us information about the result of the scheduler runs in the view ALL_SCHEDULER_JOB_RUN_DETAILS.
select replace(job_name,'DEMO_SCHEDULED_EXTERNAL_SCRIPT') as job_name,
log_date, status, output, error#, errors, additional_info
from all_Scheduler_job_run_details
where job_name like 'DEMO_SCHEDULED_EXTERNAL_SCRIPT%'
--and log_date >= sysdate - interval '5' minute
order by log_date desc
;
For presentation purposes I flipped rows and columns. So the next picture shows the columns from the DD view as rows.
Let’s go through the results step by step.
The first testcase did not have an error. status of the run = SUCCEEDED. The output column also shows the stdev output which is nice. So there is no need to spool the output into an extra file, just to be able to see later what happend. The same column is also used for jobs of type SQL_SCRIPT to return dbms_output.
The second testcase had an error. The job status correctly reported that the script errored with exit code = 1 (column error#). And we see what kind of error happen in the errors column.
"/tmp/job_2078996_3568888_script: line 2: cd: /abcd/efgh/ijk: No such file or directory "
We also see that the script itself was created as a file in the /tmp folder using job and log id for the file name “job_2078996_3568888_script”.
Also note that the additional_info column says
"EXTERNAL_LOG_ID="job_2078995_3568886", ORA-27369: job of type EXECUTABLE failed with exit code: Operation not permitted "
This is slightly misleading, since the job_type was EXTERNAL_SCRIPT and not EXECUTABLE. And “Operation not permitted” could lead us suspecting some issue with privs (might be the case when “cd” doesn’t work) or with the credentials (definitly not the case here).
The third testcase had an error in the script, however it finished with exit 0 (=success). It makes sense that in this case the job run status is also marked as SUCCEEDED. However the error “no such file or directory” still can be found in the errors column.
The forth testcase uses a new feature. I’m not sure if that is already there in the 12.1 db version, all my tests were done under 12.2. The all_scheduler_jobs view has a new column FAIL_ON_SCRIPT_ERROR. It defaults to FALSE. We can set this as an attribute for the scheduled job.
dbms_scheduler.set_attribute(
name => v_jobname,
attribute => 'FAIL_ON_SCRIPT_ERROR',
value => true);
This is currently undocumented, but it works, as above demo shows. I believe it is a documentation bug. The new column in the view is documented, but FAIL_ON_SCRIPT_ERROR is not in the list of allowed attributes.
The result is, that even with EXIT=0, the job status goes to FAILED. We also see a different error number 27382 instead of 1. 27382 seems to be the ORA-Error number that we also see in the additional_details column. This column now says “ORA-27382: job type EXTERNAL_SCRIPT has errors in the job action”. Not a major difference, but slightly better additional_details when we use this attribute.
Quite some interesting behaviour. So if we want to run a script that always exits with 0, we can still make our job go to FAILED if there are any errors inside that script.
I would guess that we find the same behaviour for the other new job_types SQL_SCRIPT and BACKUP_SCRIPT. I didn’t test it yet.
Comparison between EXTERNAL_SCRIPT and EXECUTABLE
Executable is the old way.
It requires to configure and start an external agent. Often this is a fairly complex task. It involves configuration of the listener, tnsnames.ora, extproc.ora, externaljob.ora and some other parts. Those are usually not under control of a developer. Additionally all external jobs that are executed using this agent run with the same OS privileges. Using credentials gives us a little more control.
Starting from 12.1 such external jobs can alternativly run with credentials. Same as I already showed for running external_scripts. So the credentials argument is only a half-baked one.
It is also difficult to track down an error in case something goes wrong. This is where EXTERNAL_SCRIPT seem much better then EXECUTABLE. I have to admit I did only some very short tests about this, but I never found a disadvantage for running a scheduled job as EXTERNAL_SCRIPT instead of EXECUTABLE.
So if you have a choice go with the newer option.
Integrate it into plsql including status check
How to setup a plsql procedure that is able to run an external_script, but also returns an exception, in case the script runs into errors?
Lets assume the external script is something like this:
# set environment export PATH=/usr/local/bin:$PATH export ORACLE_SID=XE export ORAENV_ASK=NO . /usr/local/bin/oraenv CLASSPATH=$CLASSPATH:$APEX_HOME/utilities CLASSPATH=$CLASSPATH:. export CLASSPATH cd /opt/jasper/report1 java -jar runJasperReport.jar exit
So this executes some jar file. I don’t want to wait until the java logic is finished, but I want to be informed if something basic goes wrong – like if the jar file couldn’t be found.
Here is an example that I used in some APEX application. After the job is started, it hangs around for a couple of seconds and checks if anything surprising did happen. The code to start the job itself is not included. But you can assume that it is done in the same packaged procedure. The script is in the v_script variable.
Do not copy it 1:1, but understand it and adapt it to your needs.
...
v_jobname varchar2(128);
r_job_details all_scheduler_job_run_details%rowtype;
r_job all_scheduler_jobs%rowtype;
c_max_check_job_tries constant binary_integer :=3;
v_message varchar2(1000);
v_script clob;
begin
...
-- check if the scheduled job did sucessfully start.
-- In case some error happend after starting the job, we might not notice that, since it was started in the background.
-- e.G. ORA-27369: Job of Typ EXECUTABLE not successfull with Exit-Code: No such file or directory
-- In such a case check scheduler data dictionary almost immediatly after job creation.
apex_util.pause(p_seconds => 0.5);
for i in 1..c_max_check_job_tries loop
-- constant is set to 3, so max. wait time = 3.5 seconds.
-- read info about scheduled job
-- started/running jobs are in scheduler_jobs, finished jobs including results are in scheduler_job_run_details
begin
select * into r_job from all_scheduler_jobs where job_name = upper(v_jobname);
logger.trace(p_message=>'Job "'||v_jobname||'" created with state='||r_job.state );
exception
when no_data_found then
-- Job might have stopped already, check run details!
r_job.state := 'NOT FOUND';
end;
if r_job.state in ('RUNNING','SCHEDULED') then
-- RUNNING+SCHEDULED => Looks ok, Job runs,just to make sure wait for another few seconds
apex_util.pause(p_seconds => 1);
else
begin
-- for any other state check details
select * into r_job_details from all_scheduler_job_run_details where job_name = upper(v_jobname);
logger.trace(p_message=>'Job Details "'||v_jobname||'" with status='||r_job_details.status );
if r_job_details.status= 'FAILED' or r_job.state= 'FAILED' then
v_message := 'Job "'|| v_jobname||'" with Error!';
if r_job_details.additional_info like '%ORA-27369%' or r_job_details.additional_info like '%ORA-27382%' then
v_message := v_message ||' Jobaction='||v_script;
end if;
pk_logging.pr_logError(p_message=>v_message);
apex_error.add_error( p_message => v_message,
p_additional_info => r_job_details.additional_info,
p_display_location => apex_error.c_inline_in_notification --apex_error.c_on_error_page
);
-- step out of loop and raise an error using the OS error message
Raise_application_error(-20001, r_job_details.errors);
end if;
exception
when no_data_found then
-- Job not started yet or just about to finish...
-- consider to wait a few sec first. Then raise an error if job still not there.
-- last try?
if i=c_max_check_job_tries then
logger.error(p_message=>'Job "'||v_jobname||'" was not started!');
Raise_application_error(-20001, 'Warning! Job "'|| v_jobname||'" wasn't started (yet)! Check application log!');
else
-- wait 1 second until job is hopefully created
apex_util.pause(p_seconds => 1);
end if;
end;
end if;
end loop get_job_info;
if r_job.state = 'SCHEDULED' then
-- still scheduled? inform user
Raise_application_error(-20001, 'Warning! Job "'|| v_jobname||'" needs longer than expected to start. Please monitor closely and informa administrator!');
end if;
...
18c issue: ORA-27369: job of type EXECUTABLE failed with exit code: No child processes
In 18c my external scripts encountered an error
10 Command not found
EXTERNAL_LOG_ID=”job_xxx”,
ORA-27369: job of type EXECUTABLE failed with exit code: No child processes
Something did change.
I found out that beginning with 18c we must not set the shell at the start of the script. Simply remove the line ‘#!/bin/bash’ . I changed the test code above and added conditional compiling, so that the tests scripts should be running in all db versions.
Security considerations
Running external jobs is always something where we need to take extra care – so that we do not put holes into our security defense system.
I carefully watch out for two major security risks:
1) If we add something dynamically to the script that we are executing, like an extra parameter, make sure to sanitize all the inputs. Otherwise we could get some kind of injection problem.
2) The agent/account that runs our script should only get the least needed privileges. So it shoud NOT be running under user oracle, like I did in the demo.
Create a separate account for that. Name the account to something that logically points to the task that it is supposed to do.
conclusion
The new job_type EXECUTE_SCRIPT is useful.
Some of the hurdles that developers face when trying to run a host command are lowered. The script itself does not need to be deployed on the database server.
Reacting to errors in the script is possible, but we need to check the correct columns and use the proper settings.
cleanup demo jobs
-- cleanup
-- remove all the jobs
execute dbms_scheduler.drop_job(job_name => 'DEMO_SCHEDULED_EXTERNAL_SCRIPT_NO_ERROR');
execute dbms_scheduler.drop_job(job_name => 'DEMO_SCHEDULED_EXTERNAL_SCRIPT_WITH_ERROR');
execute dbms_scheduler.drop_job(job_name => 'DEMO_SCHEDULED_EXTERNAL_SCRIPT_WITH_ERROR_EXIT0');
execute dbms_scheduler.drop_job(job_name => 'DEMO_SCHEDULED_EXTERNAL_SCRIPT_WITH_ERROR_FAILONERROR');
svenweller 
[…] also updated my older blog post “dbms_scheduler 12c – run EXTERNAL_SCRIPT” to considered those […]