Run shell script on Oracle 11g

Question

I have an oracle database:

Oracle Database 11g Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
"CORE   11.2.0.1.0  Production"
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

and I would like to run my script stored on db server.

path: /d01/oracle/scripts/super_skripta.sh

The content of my script (test script):

IME=$(date +"%s")

touch /d01/oracle/scripts/$IME

Can someone explain me (step by step) how can I run this script from a oracle database?

I tried to create an executable program like this:

begin
dbms_scheduler.create_program
(
program_name => 'SUPER_SKRIPTA',
program_type => 'EXECUTABLE',
program_action => '/d01/oracle/scripts/super_skripta.sh',
enabled => TRUE,
comments => 'super skripta'
);
end;
/

and then create a job:

BEGIN
    DBMS_SCHEDULER.CREATE_JOB (
            job_name => '"WOLFY"."JOB_SS"',
            program_name => '"WOLFY"."SUPER_SKRIPTA"',
            start_date => NULL,
            repeat_interval => NULL,
            end_date => NULL,
            enabled => FALSE,
            auto_drop => FALSE,
            comments => '',

            job_style => 'REGULAR');




    DBMS_SCHEDULER.SET_ATTRIBUTE( 
             name => '"WOLFY"."JOB_SS"', 
             attribute => 'logging_level', value => DBMS_SCHEDULER.LOGGING_OFF);



    DBMS_SCHEDULER.enable(
             name => '"WOLFY"."JOB_SS"');
END;

but it does not work...

Answer 1

Environment:

[oracle@o61 ~]$ ls -l /home/oracle/myscript*
-rwxr-xr-x. 1 oracle oinstall 44 Mar 25 17:38 /home/oracle/myscript.sh
[oracle@o61 ~]$ cat /home/oracle/myscript.sh
#!/bin/bash
touch /home/oracle/myscript.out
[oracle@o61 ~]$

[oracle@o61 ~]$ ls -l $ORACLE_HOME/bin/extjob*
-rwsr-x---. 1 root   oinstall 1254574 Nov  1 21:01 /u01/app/oracle/product/11.2.0/dbhome_1/bin/extjob
-rwx------. 1 oracle oinstall 1254574 Nov  1 21:01 /u01/app/oracle/product/11.2.0/dbhome_1/bin/extjobo
-rwsr-x---. 1 root   oinstall 1254292 Oct 19 14:54 /u01/app/oracle/product/11.2.0/dbhome_1/bin/extjobO
-rw-------. 1 oracle oinstall 1254292 Oct 19 14:54 /u01/app/oracle/product/11.2.0/dbhome_1/bin/extjoboO

[oracle@o61 ~]$ grep -v "^#" $ORACLE_HOME/rdbms/admin/externaljob.ora

run_user = oracle
run_group = oinstall

Then create a job that is executed once then dropped:

[oracle@o61 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Mon Mar 25 17:42:13 2019

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

begin
  dbms_scheduler.create_job
  (
    job_name => 'RUN_SCRIPT',
job_type => 'EXECUTABLE',
job_action => '/home/oracle/myscript.sh',
enabled => true,
auto_drop => true
  );
end;
 11  /

PL/SQL procedure successfully completed.

SQL>
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@o61 ~]$ ls -l /home/oracle/myscript.*
-rw-r--r--. 1 oracle oinstall  0 Mar 25 17:42 /home/oracle/myscript.out
-rwxr-xr-x. 1 oracle oinstall 44 Mar 25 17:38 /home/oracle/myscript.sh
[oracle@o61 ~]$

Answer 2

STEP 1 - Ensure that the file can be ran by the Oracle Database

The file mentioned by the parameter job_action is ran as the account that runs the Listener (usually oracle).

The OS must determine that said account can execute the program. The Read and Execute bits must be set on the file in such a way that the account can read and execute the file. The account also needs the ability to access the file's directory (in some cases, all parent directories). All other OS limitations are also applied.

For Security reason, all environment variables, including PATH, are cleared prior to executing that file. PATH is set to a minimal value (RTFM). But, it is safer to assume that it doesn't exist. This also means that programs such as touch could potentially throw an error because it it not in the new PATH. I recommend that all Shell Scripts must either explicitly define PATH or explicitly call a program (eg /usr/bin/touch).

sysexecv (a C/C++ command) will only run a Shell Script when the file defines the Shell. That is: the first line must be a she-bang ( #!) line.

Also, you should keep the executable separate from any parameters for that executable. eg use /usr/local/bin/myapp with 2+ parameters instead of /usr/local/bin/myapp -p 20 with 0+ parameters

Step 2 - create DBMS_SCHEDULAR entry

out-of-scope for this HOWTO

Step 3 - Collect Errors

If you still have problems, you must provide all oracle errors encountered and all relevent log entries in addition to the DDLs for creating the entry and a copy of the script itself.

With out that information, posts that explain

it doesn't work

are about as informative as

my car is broken.