suhinrasheed
asked on
Calling shell script from Oracle
We need to call a UNIX shell script from an oracle 9i stored procedure synchronously i.e. the control should come back to the procedure once the script completes. Would you know of a method to do this?
sorry, above actually shows example in async mode.
>> We need to call a UNIX shell script from an oracle 9i stored procedure synchronously
for 9i , look at this:
http://web.njit.edu/info/limpid/DOC/appdev.920/a96590/adg11rtn.htm#1656
for 9i , look at this:
http://web.njit.edu/info/limpid/DOC/appdev.920/a96590/adg11rtn.htm#1656
You have to use Java for this, since Oracle PL\SQL does not support "host" calls directly. Here are the two files that we thought should work for this in our Oracle10 database on Linux, first the *.java file (that we named "oscommands.java") then the *.sql file (that we named: "oscommands.sql"). Just run these as SYS (or as a DBA-privileged user in your system). Following that are the two files we actually use now, "host.java" and "host.sql".
Note that the "host.java" file does have two values that are specific to our system that I have replaced with: [IP-ADDRESS] and [ORACLE_SID]. You will have to replace those variables with values that are valid in your system. Also, the grants in the "host.sql" file have [Oracle username]. You will have to replace that with the owner of this procedure in your system.
--the "oscommands.java" file:
CREATE OR REPLACE and COMPILE
JAVA SOURCE NAMED "OSCommands"
AS
import java.lang.* ;
import java.io.* ;
public class OSCommands
{
public static void RunCMD(String[] args)
{
try
{
Process m ;
String S = "" ;
m = Runtime.getRuntime().exec(
BufferedReader in =
new BufferedReader(new InputStreamReader(
m.getInputStream()));
while((S=in.readLine()) != null)
{
System.out.println(S);
}
}
catch(Exception ex)
{
ex.printStackTrace () ;
}
}
public static String copyFile(String sourceFile, String copyFile) {
InputStream source; // Stream for reading from the source file.
OutputStream copy; // Stream for writing the copy.
int byteCount; // The number of bytes copied from the source file.
/* Create the input stream. If an error occurs, end the program. */
try {
source = new FileInputStream(sourceFile
}
catch (FileNotFoundException e) {
return "Error: Can't find file \"" + sourceFile + "\".";
}
/* Create the output stream. If an error occurs, end the program. */
try {
copy = new FileOutputStream(copyFile)
}
catch (IOException e) {
return "Error: Can't open output file \"" + copyFile + "\".";
}
/* Copy one byte at a time from the input stream to the out put stream,
ending when the read() method returns -1 (which is the signal that
the end of the stream has been reached. If any error occurs, print
an error message. Also print a message if the file has bee copied
successfully. */
byteCount = 0;
try {
while (true) {
int data = source.read();
if (data < 0)
break;
copy.write(data);
byteCount++;
}
source.close();
copy.close();
return "Success: " + byteCount + " bytes copied.";
}
catch (Exception e) {
return "Error: Error occured while copying. "
+ byteCount + " bytes copied. " + e.toString();
}
}
public static String delFile(String fileName) {
boolean success = (new File(fileName)).delete();
if (!success) {
// Deletion failed
return "Error: Can't find or open file \"" + fileName + "\" to be deleted.";
}
return "Success: " + fileName + " has been deleted.";
}
public static String renameFile(String oldName, String newName) {
// File (or directory) with old name
File file = new File(oldName);
// File (or directory) with new name
File file2 = new File(newName);
// Rename file (or directory)
boolean success = file.renameTo(file2);
if (!success) {
// File was not successfully renamed
return "Error: File or directory could not renamed.";
}
// If no error return a successfull message
return "Success: " + oldName + " was successfully renamed to " + newName;
}
public static String touchFile(String fileName) {
File file = new File(fileName);
// Get the last modified time
long modifiedTime = file.lastModified();
// 0L is returned if the file does not exist
// Set the last modified time
long newModifiedTime = System.currentTimeMillis()
boolean success = file.setLastModified(newMo
if (!success) {
// operation failed.
return "Error: Touch of file was unsuccessfull.";
}
// If no error return a successfull message
return "Success: " + fileName + " was successfully touched";
}
}
/
-- end of "oscommands.java" file
-- the "oscommands.sql file:
create or replace PACKAGE OSCommands AS
--Package spec for java calls to OSCommands class
--This procedure runs an OS command
--i.e. exec OSCommands.runCMD('/bin/ba
PROCEDURE runCMD(OScmd IN VARCHAR2);
--This function copies a file
--i.e. select OSCommands.copyfile('/opt/
FUNCTION copyfile(sourceFile VARCHAR2, copyFile VARCHAR2) RETURN VARCHAR2;
--This function deletes a file
--i.e. select OSCommands.delfile('/tmp/t
FUNCTION delfile(fileName VARCHAR2) RETURN VARCHAR2;
--This function renames a file or directory
--i.e. select OSCommands.renamefile('/tm
FUNCTION renamefile(oldFile VARCHAR2, newFile VARCHAR2) RETURN VARCHAR2;
--This function touches(updates timestamp) of a file
--i.e. select OSCommands.touchfile('/tmp
FUNCTION touchfile(fileName VARCHAR2) RETURN VARCHAR2;
END OSCommands;
/
--** PACKAGE BODY Name: OSCOMMANDS
create or replace PACKAGE BODY Oscommands AS
--Package body for java calls to OSCommands class
--For full code refer to OSCommands java class
--
--This procedure runs an OS command
--i.e. exec OSCommands.runCMD('/bin/ba
PROCEDURE runCMD(OScmd IN VARCHAR2) as
-- This didn't work for Oracle10 on Linux as of 21-Sep-2005, so call another "wrapper" procedure
-- that does work for Oracle10 on Linux.
-- AS LANGUAGE JAVA
-- NAME 'OSCommands.RunCMD(java.la
begin
-- Note: this will only work for Linux utilities that are in the /bin directory:
host_command('/bin/'||OScm
end;
--This function copies a file
--i.e. select OSCommands.copyfile('/opt/
FUNCTION copyfile(sourceFile VARCHAR2, copyFile VARCHAR2) RETURN VARCHAR2
AS LANGUAGE JAVA
NAME 'OSCommands.copyFile(java.
--This function deletes a file
--i.e. select OSCommands.delfile('/tmp/t
FUNCTION delfile(fileName VARCHAR2) RETURN VARCHAR2
AS LANGUAGE JAVA
NAME 'OSCommands.delFile(java.l
--This function renames a file or directory
--i.e. select OSCommands.renamefile('/tm
FUNCTION renamefile(oldFile VARCHAR2, newFile VARCHAR2) RETURN VARCHAR2
AS LANGUAGE JAVA
NAME 'OSCommands.renameFile(jav
--This function touches(updates timestamp) of a file
--i.e. select OSCommands.touchfile('/tmp
FUNCTION touchfile(fileName VARCHAR2) RETURN VARCHAR2
AS LANGUAGE JAVA
NAME 'OSCommands.touchFile(java
END Oscommands;
/
create public synonym oscommands for oscommands;
grant execute on oscommands to public;
-- end of "oscommands.sql" file
-- "host.java" file:
CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED "Host" AS
import java.io.*;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
/*~~~~~~~~~~~~~~~~~~~~~~~~
~ File: host.java ~
~ Author: Aaron M. Brummitt ~
~ Date Created: 20051129-2254 ~
~ Date Modified: 20051201-1548 AMB ~
~ ~
~ Description: This class contains methods to execute operating system commands ~
~ ~
~ Known Issues/Limitations: Only commands which are in $PATH will be able to be ~
~ executed, unless the full path to the binary is included ~
~ ~
~ ~~~~~~~~~~~~~~~~~~~~~~~~~~
*/
public class Host
{
/*~~~~~~~~~~~~~~~~~~~~~~~~
~ executeCommand() requires one parameter and returns a string ~
~ ~
~ Params: ~
~ String p_sCommand is the command to be executed by the Operating System ~
~ ~
~ Return: ~
~ String a status ~
~ ~
~~~~~~~~~~~~~~~~~~~~~~~~~~
*/
public static String executeCommand(String p_sCommand) throws IOException
{
//local variable declations
Process p;
boolean bPrintError = true;
String sError= "";
String sLogError = "";
int iRetStat = -1;
String[] myCMD = {"/bin/sh", "-c", ""};
myCMD[2] = p_sCommand;
try
{
//run the command
p = Runtime.getRuntime().exec(
}//end try
catch(IOException e)
{
//catch any Interuption errors and report
System.out.println("IO Exception on Runtime.getRuntime().exec(
logCmd(p_sCommand, -1, e.getMessage());
return "\nIO Exception Occured in Host.executeCommand\n";
}//end catch IOException
try
{
//set up the error stream reader
BufferedReader stdError = new BufferedReader(new InputStreamReader(p.getErr
//read the error stream, record them if any
while ((sError = stdError.readLine()) != null)
{
if(bPrintError)
{
System.out.println("Errors
bPrintError = false;
}//end if iStatus
sLogError = sLogError + sError + " ";
}//end while read error loop
}//end try
catch(IOException e)
{
//catch any Interuption errors and report
System.out.println("IO Exception in error reading: " + e.getMessage());
return "\nIO Exception Occured in Host.executeCommand\n";
}//end catch(IOException)
try
{
//wait for the proccess to end
iRetStat = p.waitFor();
}//end try
catch(InterruptedException
{
//catch any Interuption errors and report
System.out.println("Interr
return "\nException Occured in Host.executeCommand\n";
}//end catch
//if there was an error, log it
if(sLogError != "")
{
logCmd(p_sCommand, iRetStat, sLogError);
return "\n Error encountered and logged, please check log\n";
}//end if sLogError
//everythings good
return "\nCommand " + p_sCommand + " successfully completed\n";
}//end executeCommand() Method
/*~~~~~~~~~~~~~~~~~~~~~~~~
~ logCmd() requires three parameters and returns void ~
~ ~
~ Params: ~
~ String p_sCmd is the command to be executed by the Operating System ~
~ int p_RetStat is the status returned from the Operating System ~
~ String p_sError is the error message returned form the Operating System ~
~ ~
~ Return: ~
~ VOID ~
~ ~
~ Credit(s): ~
~ Oracle Metalink website (http://metalink.oracle.com), ~
~ ~
~~~~~~~~~~~~~~~~~~~~~~~~~~
*/
public static void logCmd(String p_sCmd, int p_iRetStat, String p_sError)
{
System.out.println("Loggin
try
{
//create insert string
final String sInsert = "INSERT INTO app_error_log(form_name, err_code, err_desc, user_name, timestamp) VALUES (" +
"'Host.executeCommand', " + p_iRetStat + ",'" + p_sError.replaceAll("'", "''") + "', user, sysdate)";
//System.out.println(sInse
//connect to DB
//syntax examples for Oracle:
//Statement stmt = DriverManager.getConnectio
//Connection conn = DriverManager.getConnectio
Statement stmt = DriverManager.getConnectio
//execute the insert string
stmt.executeUpdate(sInsert
//close the connection
stmt.close();
}//end try
catch (SQLException e)
{
//catch any sql errors
System.out.println("SQLExc
}//end catch
}//end logCmd() Method
}//end class host
-- end of "host.java" file
-- "host.sql" file:
CREATE OR REPLACE PROCEDURE Host_Command (p_command IN VARCHAR2)
AS LANGUAGE JAVA
NAME 'Host.executeCommand (java.lang.String)';
/
-- end of "host.sql" file
Note that you will have to run a number of grants like this for the owner of the "host_command" procedure:
EXEC Dbms_Java.Grant_Permission
EXEC Dbms_Java.Grant_Permission
EXEC Dbms_Java.Grant_Permission
EXEC Dbms_Java.Grant_Permission
EXEC Dbms_Java.Grant_Permission
EXEC Dbms_Java.Grant_Permission
EXEC Dbms_Java.Grant_Permission
Note that the "host.java" file does have two values that are specific to our system that I have replaced with: [IP-ADDRESS] and [ORACLE_SID]. You will have to replace those variables with values that are valid in your system. Also, the grants in the "host.sql" file have [Oracle username]. You will have to replace that with the owner of this procedure in your system.
--the "oscommands.java" file:
CREATE OR REPLACE and COMPILE
JAVA SOURCE NAMED "OSCommands"
AS
import java.lang.* ;
import java.io.* ;
public class OSCommands
{
public static void RunCMD(String[] args)
{
try
{
Process m ;
String S = "" ;
m = Runtime.getRuntime().exec(
BufferedReader in =
new BufferedReader(new InputStreamReader(
m.getInputStream()));
while((S=in.readLine()) != null)
{
System.out.println(S);
}
}
catch(Exception ex)
{
ex.printStackTrace () ;
}
}
public static String copyFile(String sourceFile, String copyFile) {
InputStream source; // Stream for reading from the source file.
OutputStream copy; // Stream for writing the copy.
int byteCount; // The number of bytes copied from the source file.
/* Create the input stream. If an error occurs, end the program. */
try {
source = new FileInputStream(sourceFile
}
catch (FileNotFoundException e) {
return "Error: Can't find file \"" + sourceFile + "\".";
}
/* Create the output stream. If an error occurs, end the program. */
try {
copy = new FileOutputStream(copyFile)
}
catch (IOException e) {
return "Error: Can't open output file \"" + copyFile + "\".";
}
/* Copy one byte at a time from the input stream to the out put stream,
ending when the read() method returns -1 (which is the signal that
the end of the stream has been reached. If any error occurs, print
an error message. Also print a message if the file has bee copied
successfully. */
byteCount = 0;
try {
while (true) {
int data = source.read();
if (data < 0)
break;
copy.write(data);
byteCount++;
}
source.close();
copy.close();
return "Success: " + byteCount + " bytes copied.";
}
catch (Exception e) {
return "Error: Error occured while copying. "
+ byteCount + " bytes copied. " + e.toString();
}
}
public static String delFile(String fileName) {
boolean success = (new File(fileName)).delete();
if (!success) {
// Deletion failed
return "Error: Can't find or open file \"" + fileName + "\" to be deleted.";
}
return "Success: " + fileName + " has been deleted.";
}
public static String renameFile(String oldName, String newName) {
// File (or directory) with old name
File file = new File(oldName);
// File (or directory) with new name
File file2 = new File(newName);
// Rename file (or directory)
boolean success = file.renameTo(file2);
if (!success) {
// File was not successfully renamed
return "Error: File or directory could not renamed.";
}
// If no error return a successfull message
return "Success: " + oldName + " was successfully renamed to " + newName;
}
public static String touchFile(String fileName) {
File file = new File(fileName);
// Get the last modified time
long modifiedTime = file.lastModified();
// 0L is returned if the file does not exist
// Set the last modified time
long newModifiedTime = System.currentTimeMillis()
boolean success = file.setLastModified(newMo
if (!success) {
// operation failed.
return "Error: Touch of file was unsuccessfull.";
}
// If no error return a successfull message
return "Success: " + fileName + " was successfully touched";
}
}
/
-- end of "oscommands.java" file
-- the "oscommands.sql file:
create or replace PACKAGE OSCommands AS
--Package spec for java calls to OSCommands class
--This procedure runs an OS command
--i.e. exec OSCommands.runCMD('/bin/ba
PROCEDURE runCMD(OScmd IN VARCHAR2);
--This function copies a file
--i.e. select OSCommands.copyfile('/opt/
FUNCTION copyfile(sourceFile VARCHAR2, copyFile VARCHAR2) RETURN VARCHAR2;
--This function deletes a file
--i.e. select OSCommands.delfile('/tmp/t
FUNCTION delfile(fileName VARCHAR2) RETURN VARCHAR2;
--This function renames a file or directory
--i.e. select OSCommands.renamefile('/tm
FUNCTION renamefile(oldFile VARCHAR2, newFile VARCHAR2) RETURN VARCHAR2;
--This function touches(updates timestamp) of a file
--i.e. select OSCommands.touchfile('/tmp
FUNCTION touchfile(fileName VARCHAR2) RETURN VARCHAR2;
END OSCommands;
/
--** PACKAGE BODY Name: OSCOMMANDS
create or replace PACKAGE BODY Oscommands AS
--Package body for java calls to OSCommands class
--For full code refer to OSCommands java class
--
--This procedure runs an OS command
--i.e. exec OSCommands.runCMD('/bin/ba
PROCEDURE runCMD(OScmd IN VARCHAR2) as
-- This didn't work for Oracle10 on Linux as of 21-Sep-2005, so call another "wrapper" procedure
-- that does work for Oracle10 on Linux.
-- AS LANGUAGE JAVA
-- NAME 'OSCommands.RunCMD(java.la
begin
-- Note: this will only work for Linux utilities that are in the /bin directory:
host_command('/bin/'||OScm
end;
--This function copies a file
--i.e. select OSCommands.copyfile('/opt/
FUNCTION copyfile(sourceFile VARCHAR2, copyFile VARCHAR2) RETURN VARCHAR2
AS LANGUAGE JAVA
NAME 'OSCommands.copyFile(java.
--This function deletes a file
--i.e. select OSCommands.delfile('/tmp/t
FUNCTION delfile(fileName VARCHAR2) RETURN VARCHAR2
AS LANGUAGE JAVA
NAME 'OSCommands.delFile(java.l
--This function renames a file or directory
--i.e. select OSCommands.renamefile('/tm
FUNCTION renamefile(oldFile VARCHAR2, newFile VARCHAR2) RETURN VARCHAR2
AS LANGUAGE JAVA
NAME 'OSCommands.renameFile(jav
--This function touches(updates timestamp) of a file
--i.e. select OSCommands.touchfile('/tmp
FUNCTION touchfile(fileName VARCHAR2) RETURN VARCHAR2
AS LANGUAGE JAVA
NAME 'OSCommands.touchFile(java
END Oscommands;
/
create public synonym oscommands for oscommands;
grant execute on oscommands to public;
-- end of "oscommands.sql" file
-- "host.java" file:
CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED "Host" AS
import java.io.*;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
/*~~~~~~~~~~~~~~~~~~~~~~~~
~ File: host.java ~
~ Author: Aaron M. Brummitt ~
~ Date Created: 20051129-2254 ~
~ Date Modified: 20051201-1548 AMB ~
~ ~
~ Description: This class contains methods to execute operating system commands ~
~ ~
~ Known Issues/Limitations: Only commands which are in $PATH will be able to be ~
~ executed, unless the full path to the binary is included ~
~ ~
~ ~~~~~~~~~~~~~~~~~~~~~~~~~~
*/
public class Host
{
/*~~~~~~~~~~~~~~~~~~~~~~~~
~ executeCommand() requires one parameter and returns a string ~
~ ~
~ Params: ~
~ String p_sCommand is the command to be executed by the Operating System ~
~ ~
~ Return: ~
~ String a status ~
~ ~
~~~~~~~~~~~~~~~~~~~~~~~~~~
*/
public static String executeCommand(String p_sCommand) throws IOException
{
//local variable declations
Process p;
boolean bPrintError = true;
String sError= "";
String sLogError = "";
int iRetStat = -1;
String[] myCMD = {"/bin/sh", "-c", ""};
myCMD[2] = p_sCommand;
try
{
//run the command
p = Runtime.getRuntime().exec(
}//end try
catch(IOException e)
{
//catch any Interuption errors and report
System.out.println("IO Exception on Runtime.getRuntime().exec(
logCmd(p_sCommand, -1, e.getMessage());
return "\nIO Exception Occured in Host.executeCommand\n";
}//end catch IOException
try
{
//set up the error stream reader
BufferedReader stdError = new BufferedReader(new InputStreamReader(p.getErr
//read the error stream, record them if any
while ((sError = stdError.readLine()) != null)
{
if(bPrintError)
{
System.out.println("Errors
bPrintError = false;
}//end if iStatus
sLogError = sLogError + sError + " ";
}//end while read error loop
}//end try
catch(IOException e)
{
//catch any Interuption errors and report
System.out.println("IO Exception in error reading: " + e.getMessage());
return "\nIO Exception Occured in Host.executeCommand\n";
}//end catch(IOException)
try
{
//wait for the proccess to end
iRetStat = p.waitFor();
}//end try
catch(InterruptedException
{
//catch any Interuption errors and report
System.out.println("Interr
return "\nException Occured in Host.executeCommand\n";
}//end catch
//if there was an error, log it
if(sLogError != "")
{
logCmd(p_sCommand, iRetStat, sLogError);
return "\n Error encountered and logged, please check log\n";
}//end if sLogError
//everythings good
return "\nCommand " + p_sCommand + " successfully completed\n";
}//end executeCommand() Method
/*~~~~~~~~~~~~~~~~~~~~~~~~
~ logCmd() requires three parameters and returns void ~
~ ~
~ Params: ~
~ String p_sCmd is the command to be executed by the Operating System ~
~ int p_RetStat is the status returned from the Operating System ~
~ String p_sError is the error message returned form the Operating System ~
~ ~
~ Return: ~
~ VOID ~
~ ~
~ Credit(s): ~
~ Oracle Metalink website (http://metalink.oracle.com), ~
~ ~
~~~~~~~~~~~~~~~~~~~~~~~~~~
*/
public static void logCmd(String p_sCmd, int p_iRetStat, String p_sError)
{
System.out.println("Loggin
try
{
//create insert string
final String sInsert = "INSERT INTO app_error_log(form_name, err_code, err_desc, user_name, timestamp) VALUES (" +
"'Host.executeCommand', " + p_iRetStat + ",'" + p_sError.replaceAll("'", "''") + "', user, sysdate)";
//System.out.println(sInse
//connect to DB
//syntax examples for Oracle:
//Statement stmt = DriverManager.getConnectio
//Connection conn = DriverManager.getConnectio
Statement stmt = DriverManager.getConnectio
//execute the insert string
stmt.executeUpdate(sInsert
//close the connection
stmt.close();
}//end try
catch (SQLException e)
{
//catch any sql errors
System.out.println("SQLExc
}//end catch
}//end logCmd() Method
}//end class host
-- end of "host.java" file
-- "host.sql" file:
CREATE OR REPLACE PROCEDURE Host_Command (p_command IN VARCHAR2)
AS LANGUAGE JAVA
NAME 'Host.executeCommand (java.lang.String)';
/
-- end of "host.sql" file
Note that you will have to run a number of grants like this for the owner of the "host_command" procedure:
EXEC Dbms_Java.Grant_Permission
EXEC Dbms_Java.Grant_Permission
EXEC Dbms_Java.Grant_Permission
EXEC Dbms_Java.Grant_Permission
EXEC Dbms_Java.Grant_Permission
EXEC Dbms_Java.Grant_Permission
EXEC Dbms_Java.Grant_Permission
A simple OS batch file might do it..
let me know which os you are on and I can help out...
let me know which os you are on and I can help out...
To actonwang:
No, a PL\SQL procedure cannot directly call a batch file on Windows, or a shell script on UNIX or Linux. Maybe the business problem can be solved though by a batch file or shell script being run from an O/S scheduler, instead of from PL\SQL.
No, a PL\SQL procedure cannot directly call a batch file on Windows, or a shell script on UNIX or Linux. Maybe the business problem can be solved though by a batch file or shell script being run from an O/S scheduler, instead of from PL\SQL.
to markgeer:
I am trying to answer the original question and try to execute the script file NOT pl/sql.
look at the original question...
acton
I am trying to answer the original question and try to execute the script file NOT pl/sql.
look at the original question...
acton
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
markgeer:
I am messed up with other question. Ignore me on this :)
Acton
I am messed up with other question. Ignore me on this :)
Acton
ASKER
Hi Folks.Please make sure that if you can suggest a method with an example which doesnt involve Java or ProC,IT WOULD BE BEST.dbms_pipe package...can we use this and achieve the requirement.give me some inputs with an eg if possible
An external procedure, also sometimes referred to as an external routine, is a procedure stored in a dynamic link library (DLL), or libunit in the case of a Java class method. You register the procedure with the base language, and then call it to perform special-purpose processing.
if you want to use external procedure from oracle, it is unavoidable to use java or ProC/C because that it is what it is.
as a lot of links and examples above, this is not very difficult to do.
if you want to use external procedure from oracle, it is unavoidable to use java or ProC/C because that it is what it is.
as a lot of links and examples above, this is not very difficult to do.
In Oracle7 and Oracle8, we used a combination of DBMS_PIPE and a utility that Oracle used to include with the database software, named "daemon". That can work in Oracle9 too if you can find the source code for that "daemon" utility and compile it for your O/S. The source code used to be in the Oracle documentation for Supplied PL\SQL Packages, under DBMS_PIPE. Then you have to start that "daemon" process on your server (and keep it running all the time). It establishes a connection to Oracle (using the SCOTT/TIGER login unless you change that in the source code before you compile it) and waits for messages to be sent to it via DBMS_PIPE. When it receives a message (which must be a text string that is a valid command in your server O/S) it executes that text string, and returns "success" to PL\SQL. Note that "success" gets returned regardless of whether the actual O/S command succeeded or failed.
ASKER
markgeer
Can you just give me an example of this dbms_pipe method.Show me how you execute a small shellscript using this method.Shell script should be called from a Procedure.If you could show me this.full marks to you
Can you just give me an example of this dbms_pipe method.Show me how you execute a small shellscript using this method.Shell script should be called from a Procedure.If you could show me this.full marks to you
DBMS_PIPE allows asynchronous messaging between two or more sessions in the same instance. The data sent using dbms_pipe can be read only by another Oracle session. An OS program or a shell script can connect to Oracle database and receive data.
http://www.oracle-base.com/articles/misc/dbms_pipe.php
http://www.oracle-base.com/articles/misc/dbms_pipe.php
If you have access to Oracle8 documentation, the use of DBMS_PIPE plus the "daemon" executable was documented there in the Supplied PL\SQL Packages section. I'm not sure if it is in the Oracle9 documentation or not. An example from me is not enough. You also need the "daemon" executable compiled for your O/S and running on your server. I've never had that executable for UNIX - I just had it for Windows.
Do you have that executable?
Are you aware that the DBMS_PIPE-based way to execute "host" commands from PL\SQL was what Oracle supported for Oracle7 and 8.0, but starting with Oracle8.1, Oracle strongly promotes the use of Java instead. I'm no big fan of Java, but we did switch to a Java-based way to do "host" commands from PL\SQL when we upgraded to Oracle9 some years ago. (The DBMS_PIPE/daemon approach did also work for us with Oracle9 on WIndows.)
Is there a reason you do not want to use the Java-based method that Oracle encourages for Oracle9?
Do you have that executable?
Are you aware that the DBMS_PIPE-based way to execute "host" commands from PL\SQL was what Oracle supported for Oracle7 and 8.0, but starting with Oracle8.1, Oracle strongly promotes the use of Java instead. I'm no big fan of Java, but we did switch to a Java-based way to do "host" commands from PL\SQL when we upgraded to Oracle9 some years ago. (The DBMS_PIPE/daemon approach did also work for us with Oracle9 on WIndows.)
Is there a reason you do not want to use the Java-based method that Oracle encourages for Oracle9?
http://www.stanford.edu/dept/itss/docs/oracle/10g/appdev.101/b10795/adfns_ex.htm