Java example:
Java class
---------
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
public class JCommand {
public static int execute(String cmd) {
try
{
Process process = Runtime.getRuntime().exec(cmd);
int exitVal = process.waitFor();
return (exitVal);
}
catch (Exception e)
{
//System.out.println( "Exception: " + e.toString());
return 1 ;
}
}
}
PL/SQL function
---------------
1.
2.
3.
4.
5.
6.
7.
8.
CREATE OR REPLACE
FUNCTION OS_Command(
p_Cmd IN VARCHAR2
)
RETURN NUMBER
AS LANGUAGE JAVA
NAME 'JCommand.execute(java.lang.String) return int';
/
Now either add Java class to Oracle CLASSPATH or use loadjava utility.
Usage example for my live code:
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
IF Os_Command(
'/usr/bin/lp -d Paper_Payer ' || v_BATCH_FILE
) = 0
THEN
v_JOB_LOG_DETAIL_ID := Logger.Write_Log(
v_JOB_LOG_ID,
'S',
'Submitted batch file ' || v_BATCH_FILE ||
' to printer.'
);
ELSE
v_JOB_LOG_DETAIL_ID := Logger.Write_Log(
v_JOB_LOG_ID,
'E',
'Failed to submit batch file ' || v_BATCH_FILE ||
' to printer.'
);
END IF;
There is a catch though. Oracle process starts in so-called "clean" environment. As a result OS command you are trying to execute might not be in the PATH or some libraries it needs might not be in LD_LIBRARY_PATH. Then you need to use ENVS parameter in corresponding SID_DESC of LISTENER.ORA. For example:
1.
2.
3.
4.
5.
6.
(SID_DESC =
(SID_NAME = xxx)
(ORACLE_HOME = /u01/home/oracle/product/ 8 . 1 . 7 )
(GLOBAL_DBNAME = hmdbqa.hmonline.com)
(ENVS = 'PATH=/usr/bin:/usr/local/bin:/u01/home/oracle/product/8.1.7',)
)
SY
