Home » SQL & PL/SQL » SQL & PL/SQL » how to call an exe from oracle trigger (oracle9i)
how to call an exe from oracle trigger [message #331644] Fri, 04 July 2008 05:45 Go to next message
pratibha.peshwa
Messages: 3
Registered: July 2008
Junior Member
urgent help required

I have a table in oracle9i, whose values are being updated in some time interval.
I need to call an exe (developed in c#.net) on update event of this table.
i.e write a trigger for update event of table in which i need to
call an external process (c#.net exe file).

If any body have any idea regarding this, please reply.


Regards
Pratibha Peshwa
email: Pratibha.Peshwa@relianceada.com
Re: how to call an exe from oracle trigger [message #331647 is a reply to message #331644] Fri, 04 July 2008 05:53 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
you have to create a procedure with lang java in oracle
that will return you the run environment of O.S (i.e. commnad prompt)

Then you can call the external exe from this.

Regards,
Rajat
Re: how to call an exe from oracle trigger [message #331649 is a reply to message #331647] Fri, 04 July 2008 06:06 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
CREATE OR REPLACE AND 
COMPILE JAVA SOURCE NAMED Command 
AS
import java.io.*;
import java.util.*;
public class Command
{
 public static void run(String cmdText)
 throws IOException, InterruptedException
 {
  int rtn;
   Runtime rt = Runtime.getRuntime();
   Process prcs = rt.exec(cmdText);
   rtn = prcs.waitFor();
 }
}



Then you can publish a PL/SQL procedure to be used in your applications.



CREATE OR REPLACE PROCEDURE 
runoscommand(cmd IN VARCHAR2)
AS LANGUAGE JAVA
NAME 'Command.run(java.lang.String)'



Now you can use the above procedure to run any batch files.

Regards,
Rajat Ratewal
Re: how to call an exe from oracle trigger [message #331650 is a reply to message #331649] Fri, 04 July 2008 06:10 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
That procedure does create a teeny tiny security loophole.....

You'd be unmeasurabley better off modifying that procedure not to accept the executable as a parameter, and having a different version of it for each executable you want to run.
Re: how to call an exe from oracle trigger [message #331723 is a reply to message #331644] Sat, 05 July 2008 00:12 Go to previous messageGo to next message
pratibha.peshwa
Messages: 3
Registered: July 2008
Junior Member
thanx a lot for the reply, it realy helped me.

but it is giving error for compiling command class :

error - insuficient privilages : ora01030

i need to know that what all privilages are required by Ora user to compile it.

Re: how to call an exe from oracle trigger [message #331734 is a reply to message #331723] Sat, 05 July 2008 02:28 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
How can you ever say that the reply "really helped" if you didn't have the chance to test it?!

Please show us a copy-paste of your sqlplus session in which you try to compile your code, including the response from the database and you retrieving the error.

[Updated on: Sat, 05 July 2008 02:34]

Report message to a moderator

Re: how to call an exe from oracle trigger [message #331763 is a reply to message #331644] Sat, 05 July 2008 06:35 Go to previous messageGo to next message
pratibha.peshwa
Messages: 3
Registered: July 2008
Junior Member

sqlplus session
*************************************************
create or replace package RUNCMD as
procedure runoscmd(cmd IN VARCHAR2);
end RUNCMD;

create or replace package body RUNCMD as
PROCEDURE
runoscmd(cmd IN VARCHAR2)
AS LANGUAGE JAVA
NAME 'Command.run(java.lang.String)';
end RUNCMD;

execute RUNCMD.runoscmd('e:\OracleListener.exe 1 2');
BEGIN RUNCMD.runoscmd('e:\OracleListener.exe 1 2'); END;
***************************************************

your your information

package name : RUNCMD
procedure name : runoscmd
called file with arguments : 'e:\OracleListener.exe 1 2'
oracle user name : PPESHWA

##### error encountered ######
ERROR at line 1:
ORA-29532: Java call terminated by uncaught Java exception:
java.io.IOException: The handle is invalid.ORA-06512: at "PPESHWA.RUNCMD", line 0
ORA-06512: at line 1





Re: how to call an exe from oracle trigger [message #331777 is a reply to message #331763] Sat, 05 July 2008 08:34 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
This is not the transcript of a sqlplus session.

Also, I don't see an Insufficient privileges error, nor an ORA-01030.

[Updated on: Sat, 05 July 2008 08:35]

Report message to a moderator

Re: how to call an exe from oracle trigger [message #331955 is a reply to message #331777] Sun, 06 July 2008 22:47 Go to previous message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
Check Whether the procedure you created is in valid state or not.

Check this out:-

Java Error

Regards,
Rajat Ratewal
Previous Topic: Help? Arithmetic operation of columns of different table
Next Topic: Need a query for updating a table with 1 billion records
Goto Forum:
  


Current Time: Sat Dec 10 09:19:39 CST 2016

Total time taken to generate the page: 0.06126 seconds