Use java. We do this to call external shell scripts from within a package.

follow the JDBC examples found in the docs.

Create a java stored procedure with similar patterns as the sql strored proc.

this is how I did it... BTW, you need the JVM installed ($ORACLE_HOME/javavm/install/initjvm.sql) have 50M of shared_pool at least when youinstall it (as sys). Again, read the docs on this.

HAve your own procedure call the java proc: example:

create or replace package body java_integration as

procedure integrate (business_id in varchar2, file_name in varchar2) is
language java name
'java_integration.integrate(java.lang.String, java.lang.String)';

end java_integration;
/

You can do with a proc, and not a pkg, but I prefer packages.

In my other code, simple SQL I call this one:
java_integration.integrate(V_business_id, V_file_name);

This call the above java proc, which then calls the java_integration program, of the same name..

in the code I have something like this: I pass in the arguments and the path of the executable (which I hard code in my case), and execute it.


java_integration.java:

public class java_integration
{
public static void main (String args [])
{
public static void integrate (String biz_id, String file_name)
{
try {
int exitValue;
Process proc;
String exec_stmt = "/integrate_external/RTNYC/integrate.sh";
exec_stmt += " " + biz_id + " " + file_name;
System.out.println(exec_stmt);
proc = Runtime.getRuntime().exec(exec_stmt);
proc.waitFor();
exitValue = proc.exitValue();
System.out.println("Exit condition: "+exitValue);
}
catch (Exception e)
{
System.out.println("failure in callnow");
System.out.println(e.getMessage());
}
}
}


This and the other procedure must be loaded and compiled:

loadjava -u DB_USER/DB_PASS@CONN_STR -resolve java_integration.class

then sqlplus USER/PWD
@java_integration.sql (contains the package above)

that's mostly it. good luck.