When you try to run a UNIX shell script directly from DBMS_SCHEDULER it runs as the UNIX owner nobody:nobody, which means it will likely have permissions problems and give the error ORA-27369. It is possible to configure DBMS_SCHEDULER so it will run as the oracle user (Metalink Doc ID 389685.1) but if you can't or don't want to do that you can do this:
First you have to give Java permission to access the script:
begin
dbms_java.grant_permission
('SYS',
'java.io.FilePermission',
'[path]/test.sh',
'execute');
dbms_java.grant_permission
('SYS',
'java.lang.RuntimePermission',
'*',
'writeFileDescriptor' );
end;
After that create the Java procedure itself:
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;
}
}
}
/
Then a function that calls the java procedure:
create or replace
function RUN_CMD(p_cmd in varchar2) return number
as
language java
name 'Util.RunThis(java.lang.String) return integer';
/
And finally the stored procedure:
create or replace procedure RC(p_cmd in varchar2)
as
x number;
begin
x := run_cmd(p_cmd);
end;
/
After that it can be run from DBMS_SCHEDULER like this:
SQL>BEGIN
DBMS_SCHEDULER.create_job (
job_name => 'MyJob',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN rc(''[path]/test.sh''); END;',
start_date => SYSTIMESTAMP,
end_date => NULL,
enabled => TRUE,
comments => 'UNIX shell script run from stored procedure');
END;
/
PL/SQL procedure successfully completed.
Of course it can also be set up with a schedule, etc. like any other stored procedure.
