18c scheduler EXTERNAL_SCRIPT with error ORA-27369: job of type EXECUTABLE failed with exit code: No child processes

After an upgrade to oracle database version 18.3 from 12.2 I encountered this error for a scheduled job of type EXTERNAL_SCRIPT. The job failed.

error code: 10 – Command not found

The view all_scheduler_job_details had additional information.

ORA-27369: job of type EXECUTABLE failed with exit code: No child processes

Last year I wrote about using scheduled job type EXTERNAL_SCRIPT instead of EXECUTABLE. Even the simplest demo code block from that time was not running anymore in 18c.

Here is a very simple shell script that needs to run

#!/bin/bash
echo “Job ok!”;

I execute this script using a scheduled job. The job runs as user oracle (via credential ORACLE_OS_CREDS).

-- First test a script that should 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 := '#!/bin/bash
echo "Job ok!"';

  -- create and run the job
  dbms_scheduler.create_job(job_name   => v_jobname,
                               job_type   => 'EXTERNAL_SCRIPT',
                               job_action => v_good_script,
                               credential_name => 'ORACLE_OS_CREDS',
                               enabled    => true,
                               auto_drop  => true
                               );
 end;
 /

After several failed trails that including checking credentials, privs on OS level extproc settings, etc. I found a simple reason.

We are not allowed anymore to set the shell in the first line. So the script needed to change.

echo “Job ok!”;

In case you didn’t know, this first line is called Shebang.

I didn’t find any notice in the documentation about that. So it might be a bug or some intented stealth change.
Oracle 12c doc
Oracle 18c doc
Both say the same thing.

‘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 #!.

Oracle Documentation

It is definitly not working in my environment. In case you encounter the same issue, here is my suggested solution.

To circumvent the issue I added some conditional compiling so that the same code is running in 12.2 and in 18.3.
In 12.2 it adds the shell call as the first line of the script – in 18c it avoids it.
I didn’t test which shell really is used to run the script. The difference between bash and sh is usually not relevant for my scripts.

-- 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');
    -- conditional compile used because shebang not allowed anymore in 18c
    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;
 /

I also updated my older blog post “dbms_scheduler 12c – run EXTERNAL_SCRIPT” to considered those findings.

You might also be interested in a post by Markus Fletchner: File ownership after patching or relinking Oracle RDBMS software

He describes issues with scheduled jobs of type EXECUTABLE after patching the database to 18 because of changed permissions. I first suspected I have the same problem, but it turned out differently. Still an interesting read.

I hope this post will help some others to waste less time, when encountering this error.