=========================================
Execute operating system commands from PL/SQL
Execute operating system commands in Oracle
=========================================
You can execute OS command from your PL/SQL program
/*
============= STEP 1 ==================
Grant Java executable permission to user
where you want to create java program
========================================
*/
begin
-- this grants read privilege on STDIN
dbms_java.grant_permission(
grantee => 'SCOTT',
permission_type => 'SYS:java.lang.RuntimePermission',
permission_name => 'readFileDescriptor',
permission_action => null
);
-- this grants write permission on STDOUT
dbms_java.grant_permission(
grantee => 'SCOTT',
permission_type => 'SYS:java.lang.RuntimePermission',
permission_name => 'writeFileDescriptor',
permission_action => null
);
-- this grants execute privilege for the 'ls -la' command
dbms_java.grant_permission(
grantee => 'SCOTT',
permission_type => 'SYS:java.io.FilePermission',
permission_name => '/bin/ls',
permission_action => 'execute'
);
-- this grants execute privilege for ALL shell commands: VERY DANGEROUS!
dbms_java.grant_permission(
grantee => 'SCOTT',
permission_type => 'SYS:java.io.FilePermission',
permission_name => '<<ALL FILES>>',
permission_action => 'execute'
);
end;
============= STEP 2 ==================
Create java program
========================================
CREATE OR REPLACE JAVA SOURCE NAMED "XXHostCommand"
AS import java.io.*;
import oracle.jdbc.*;
import oracle.sql.*;
import java.sql.*;
public class XXHostCommand {
private static oracle.sql.Datum exec(String sCommand, Datum data, int iReturnType) throws Exception {
Process p = null;
InputStreamReader pStdOutR = null;
InputStream pStdOutS = null;
OutputStreamWriter pStdInW = null;
OutputStream pStdInS = null;
Writer paramClobWriter = null;
OutputStream paramBlobOStream = null;
Reader paramClobReader = null;
InputStream paramBlobIStream = null;
Datum tempLob;
char[] caStdInBuffer = null;
char[] caStdOutBuffer = null;
byte[] baStdInBuffer = null;
byte[] baStdOutBuffer = null;
int iCharsRead = 0;
Connection con = DriverManager.getConnection("jdbc:default:connection:");
if (iReturnType == oracle.jdbc.OracleTypes.BLOB) {
tempLob = BLOB.createTemporary(con, true, BLOB.DURATION_CALL);
paramBlobOStream = ((BLOB)tempLob).setBinaryStream(0L);
baStdOutBuffer = new byte[((BLOB)tempLob).getChunkSize()];
} else if (iReturnType == oracle.jdbc.OracleTypes.CLOB) {
tempLob = CLOB.createTemporary(con, true, CLOB.DURATION_CALL);
paramClobWriter = ((CLOB)tempLob).setCharacterStream(0L);
caStdOutBuffer = new char[((CLOB)tempLob).getChunkSize()];
} else {
throw new IllegalArgumentException("ReturnType must be CLOB or BLOB");
}
if (data != null) {
if (data instanceof oracle.sql.BLOB) {
paramBlobIStream = ((BLOB)data).getBinaryStream();
baStdInBuffer = new byte[((BLOB)data).getChunkSize()];
} else if (data instanceof oracle.sql.CLOB) {
paramClobReader = ((CLOB)data).getCharacterStream();
caStdInBuffer = new char[((CLOB)data).getChunkSize()];
} else {
throw new IllegalArgumentException("Argument for Standard Input must be of type CLOB or BLOB");
}
}
p = Runtime.getRuntime().exec(sCommand);
iCharsRead = 0;
if (data != null) {
if (data instanceof oracle.sql.BLOB) {
pStdInS = p.getOutputStream();
while ((iCharsRead = paramBlobIStream.read(baStdInBuffer, 0, baStdInBuffer.length)) != -1) {
pStdInS.write(baStdInBuffer, 0, iCharsRead);
}
paramBlobIStream.close();
pStdInS.flush();
pStdInS.close();
} else {
pStdInW = new OutputStreamWriter(p.getOutputStream());
while ((iCharsRead = paramClobReader.read(caStdInBuffer, 0, caStdInBuffer.length)) != -1) {
pStdInW.write(caStdInBuffer, 0, iCharsRead);
}
paramClobReader.close();
pStdInW.flush();
pStdInW.close();
}
}
p.waitFor();
iCharsRead = 0;
if (iReturnType == oracle.jdbc.OracleTypes.CLOB) {
if (p.exitValue() == 0) {
pStdOutR = new InputStreamReader(p.getInputStream());
} else {
pStdOutR = new InputStreamReader(p.getErrorStream());
}
while ((iCharsRead = pStdOutR.read(caStdOutBuffer, 0, caStdOutBuffer.length)) != -1) {
paramClobWriter.write(caStdOutBuffer, 0, iCharsRead);
}
pStdOutR.close();
paramClobWriter.flush();
paramClobWriter.close();
} else {
if (p.exitValue() == 0) {
pStdOutS = p.getInputStream();
} else {
pStdOutS = p.getErrorStream();
}
while ((iCharsRead = pStdOutS.read(baStdOutBuffer, 0, baStdOutBuffer.length)) != -1) {
paramBlobOStream.write(baStdOutBuffer, 0, iCharsRead);
}
pStdOutS.close();
paramBlobOStream.flush();
paramBlobOStream.close();
}
return tempLob;
}
public static Datum execClob(String sCommand, CLOB dataClob) throws Exception {
return exec(sCommand, dataClob, oracle.jdbc.OracleTypes.CLOB);
}
public static Datum execClob(String sCommand, BLOB dataBlob) throws Exception {
return exec(sCommand, dataBlob, oracle.jdbc.OracleTypes.CLOB);
}
public static Datum execBlob(String sCommand, BLOB dataBlob) throws Exception {
return exec(sCommand, dataBlob, oracle.jdbc.OracleTypes.BLOB);
}
public static Datum execBlob(String sCommand, CLOB dataClob) throws Exception {
return exec(sCommand, dataClob, oracle.jdbc.OracleTypes.BLOB);
}
public static Datum execClob(String sCommand) throws Exception {
return exec(sCommand, null, oracle.jdbc.OracleTypes.CLOB);
}
public static Datum execBlob(String sCommand) throws Exception {
return exec(sCommand, null, oracle.jdbc.OracleTypes.BLOB);
}
}
/
============= STEP 3 ======================
Create Package specification :xx_os_command
===========================================
CREATE OR REPLACE PACKAGE xx_os_command
IS
FUNCTION exec_CLOB (p_command IN varchar2, p_stdin IN BLOB)
RETURN CLOB;
FUNCTION exec_CLOB (p_command IN varchar2, p_stdin IN CLOB)
RETURN CLOB;
FUNCTION exec_BLOB (p_command IN varchar2, p_stdin IN BLOB)
RETURN BLOB;
FUNCTION exec_BLOB (p_command IN varchar2, p_stdin IN CLOB)
RETURN BLOB;
FUNCTION exec_CLOB (p_command IN varchar2)
RETURN CLOB;
FUNCTION exec_BLOB (p_command IN varchar2)
RETURN BLOB;
END xx_os_command;
--alter java source "xx_os_command" compile
============= STEP 4 ======================
Create Package body :xx_os_command
===========================================
CREATE OR REPLACE PACKAGE BODY xx_os_command
IS
FUNCTION exec_CLOB (
p_command IN varchar2,
p_stdin IN BLOB
)
RETURN CLOB
IS
LANGUAGE JAVA
NAME 'XXHostCommand.execClob(java.lang.String, oracle.sql.BLOB) return oracle.sql.CLOB';
FUNCTION exec_CLOB (
p_command IN varchar2,
p_stdin IN CLOB
)
RETURN CLOB
IS
LANGUAGE JAVA
NAME 'XXHostCommand.execClob(java.lang.String, oracle.sql.CLOB) return oracle.sql.CLOB';
FUNCTION exec_BLOB (
p_command IN varchar2,
p_stdin IN BLOB
)
RETURN BLOB
IS
LANGUAGE JAVA
NAME 'XXHostCommand.execBlob(java.lang.String, oracle.sql.BLOB) return oracle.sql.BLOB';
FUNCTION exec_BLOB (
p_command IN varchar2,
p_stdin IN CLOB
)
RETURN BLOB
IS
LANGUAGE JAVA
NAME 'XXHostCommand.execBlob(java.lang.String, oracle.sql.CLOB) return oracle.sql.BLOB';
FUNCTION exec_CLOB (p_command IN varchar2)
RETURN CLOB
IS
LANGUAGE JAVA
NAME 'XXHostCommand.execClob(java.lang.String) return oracle.sql.CLOB';
FUNCTION exec_BLOB (p_command IN varchar2)
RETURN BLOB
IS
LANGUAGE JAVA
NAME 'XXHostCommand.execBlob(java.lang.String) return oracle.sql.BLOB';
END xx_os_command;
============= STEP 5 ======================
Now you are ready to run host command using
java package
===========================================
--==== File listing from "/usr/tmp" directiory
SELECT DBMS_LOB.SUBSTR (os_command.exec_clob('ls /usr/tmp'),
32000,
1)
Output
FROM DUAL;
--==== Run bourne shell script listing
SELECT DBMS_LOB.SUBSTR (os_command.exec_clob ('bash /usr/tmp/test.sh'),
32000,
1)
Output
FROM DUAL;
--==== Run korn shell script listing
SELECT DBMS_LOB.SUBSTR (os_command.exec_clob ('bash /usr/tmp/test.sh'),
32000,
1)
Output
FROM DUAL;
--==== call executable program with two or more parameter
SELECT DBMS_LOB.SUBSTR (os_command.exec_clob ('/cbs_x80_lic F5060007 001919'),
32000,
1)
Output
FROM DUAL;
Visit for more information http://blog.optiosys.com/
No comments:
Post a Comment