OraFAQ Forum: SQL & PL/SQL » how to call an exe from oracle trigger

Home » SQL & PL/SQL » SQL & PL/SQL » how to call an exe from oracle trigger (oracle9i)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

how to call an exe from oracle trigger [message #331644]

Fri, 04 July 2008 05:45

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

Report message to a moderator

Re: how to call an exe from oracle trigger [message #331647 is a reply to message #331644]

Fri, 04 July 2008 05:53

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

Report message to a moderator

Re: how to call an exe from oracle trigger [message #331649 is a reply to message #331647]

Fri, 04 July 2008 06:06

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

Report message to a moderator

Re: how to call an exe from oracle trigger [message #331650 is a reply to message #331649]

Fri, 04 July 2008 06:10

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.

Report message to a moderator

Re: how to call an exe from oracle trigger [message #331723 is a reply to message #331644]

Sat, 05 July 2008 00:12

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.

Report message to a moderator

Re: how to call an exe from oracle trigger [message #331734 is a reply to message #331723]

Sat, 05 July 2008 02:28

Frank
Messages: 7901
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

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

Report message to a moderator