How to invoke a shell script from Stored Procedure?

This is acheived via creating an external Java procedure, which calls the shell script. Tested Sample Code is attached.

URL for reference is http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:952229840241

**********************************************************
Run this procedure as sys or equivalent user
Change the filename and Schema name to your requirement.
**********************************************************

begin
dbms_java.grant_permission
('ILM_TOOLKIT',
'java.io.FilePermission',
'/app/ilm_demo_reset/ilm_demo_reset.sh',
'execute');

dbms_java.grant_permission
('ILM_TOOLKIT',
'java.lang.RuntimePermission',
'*',
'writeFileDescriptor' );
end;
/


**********************************************************
Connect as the schema you want to create procedure.
Run the following code.
**********************************************************

create or replace and compile
java source named "Util"
as
import java.io.*;
import java.lang.*;

public class Util extends Object
{
public static int RunThis(String args)
{
Runtime rt = Runtime.getRuntime();
int rc = -1;

try
{
Process p = rt.exec(args);

int bufSize = 4096;
BufferedInputStream bis =
new BufferedInputStream(p.getInputStream(), bufSize);
int len;
byte buffer[] = new byte[bufSize];

// Echo back what the program spit out
while ((len = bis.read(buffer, 0, bufSize)) != -1)
System.out.write(buffer, 0, len);

rc = p.waitFor();
}
catch (Exception e)
{
e.printStackTrace();
rc = -1;
}
finally
{
return rc;
}
}
}
/

**********************************************************
Connect as the schema you want to create procedure.
Run the following code.
**********************************************************

create or replace
function RUN_CMD(p_cmd in varchar2) return number
as
language java
name 'Util.RunThis(java.lang.String) return integer';
/


**********************************************************
Connect as the schema you want to create procedure.
Run the following code.
Argument: p_cmd The shell you want to run.
**********************************************************

create or replace procedure RC(p_cmd in varchar2)
as
x number;
begin
x := run_cmd(p_cmd);
end;
/


*************************************************************
To run the command
*************************************************************
set serveroutput on size 1000000
exec dbms_java.set_output(1000000)
exec rc('/app/ilm_demo_reset/ilm_demo_reset.sh');









------------------------------------------------------------------------------------
There is another way to run shell scripts from PL/SQL using DBMS.SCHEDULER.

To use DBMS.SCHEDULER, first create a job as the db user that you want to use to use to invoke the shell script. To create a job in your own schema, you need to have the CREATE JOB privilege. A user with the CREATE ANY JOB privilege can create a job in any schema. If the job being created will reside in another schema, the job name must be qualified with the schema name. For a job of type EXECUTABLE (or for a job that points to a program of type EXECUTABLE), the job owner must have the CREATE EXTERNAL JOB system privilege before the job can be enabled or run. http://st-doc.us.oracle.com/11/111/appdev.111/b28419/d_sched.htm#i1000363

BEGIN

sys.dbms_scheduler.create_job(

job_name => 'RUN_TEST_SCRIPT',

job_type => 'EXECUTABLE',

job_action => '/app/oracle/product/tds/test.sh',

comments => 'Run my test.sh',

auto_drop => FALSE,

enabled => TRUE);

END;

/

Note: The job runs on creation, you could create the job with auto_drop => TRUE, but in this case we want to preserve the job so that it can be called later.

Then you can run the job to execute the shell script by running the following in PL/SQL

BEGIN

DBMS_SCHEDULER.RUN_JOB (

job_name => 'RUN_TEST_SCRIPT');

END;

/

Note: Be sure to include #!/bin/bash in the first line of the shell script that you are running. Here is the test script that I used:

#!/bin/bash

echo "You got to $0" > /tmp/test.log;

How to execute a PL-SQL procedure via URL?

Step 1: Create the procedure under your
Step 2: Grant execute on to anonymous;
Step 3: Edit $ORACLE_HOME/apex/core/wwv_flow_epg_include_local.sql


Add your in list. The sql has comments which are self explanatory. Please read them before modifying.

Step 4: Invoke the procedure with url with following convention

http://:/apex/.?p=

I have tested the working (without any parametes) and it works fine for me.

Tip: To see text returned on procedure execution use "htp.p" package htp.p('your message');

A sample procedure which prints back on html page:

create or replace procedure test_p
as
begin
htp.p('Welcome!');
-- your code
-- htp.p('End program')
end;

/