IT Community - Software Programming, Web Development and Technical Support

Run an exe from an oracle trigger

This is a discussion on Run an exe from an oracle trigger within the Database Support forums, part of the Web Development category; Hi! I want to run an exe using trigger. I have written a java class file loaded it to oracld ...


Go Back   IT Community - Software Programming, Web Development and Technical Support > Web Development > Database Support

Register FAQ Members List Calendar Mark Forums Read
  #1 (permalink)  
Old 08-06-2007, 12:45 PM
H2o H2o is offline
D-Web Analyst
 
Join Date: Jul 2007
Posts: 246
H2o is on a distinguished road
Default Run an exe from an oracle trigger

Hi!

I want to run an exe using trigger. I have written a java class file loaded it to oracld db using loadjava. Now I am accessing that class file from an oracle procedure.The code is.

Code:
import java.io.*;
import java.util.*;

public class CommandExection {

public static void Command(String commandline) {

try {

String line;

Process p = Runtime.getRuntime().exec(commandline);

BufferedReader input =

new BufferedReader

(new InputStreamReader(p.getInputStream()));

while ((line = input.readLine()) != null) {

System.out.println(line);

}

input.close();

}

catch (Exception err) {

err.printStackTrace();

}
}
an oracle procedure calling the Command method in CommandExection class is,

create or replace procedure CommandExection(path in varchar)
as
language java
name 'CommandExection.Command(java.lang.String)';

I executed the procedure and ended up with the error.

SQL> set serverout on size 1000000
SQL>
SQL> exec dbms_java.set_output(1000000)

PL/SQL procedure successfully completed.

SQL>
SQL> begin
2 CommandExection('D:exeapp.exe');
3 end;
4 /


java.io.IOException: The handle is invalid.
at oracle.aurora.java.lang.OracleProcess.create(Nativ e Method)
at oracle.aurora.java.lang.OracleProcess.construct(Or acleProcess.java:25)
at java.lang.Runtime.execInternal(Native Method)
at java.lang.Runtime.exec(Runtime.java:566)
at java.lang.Runtime.exec(Runtime.java:428)
at java.lang.Runtime.exec(Runtime.java:364)
at java.lang.Runtime.exec(Runtime.java:326)
at CommandExection.Command(CommandExection:12)

PL/SQL procedure successfully completed.

Please help me with this.
My objective is to run an exe from an oracle trigger. I am using oracle 10g
desperately waiting for the reply.
Thank you.
__________________
H2O

Without us, no one can survive..
Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!
Reply With Quote
Sponsored Links
  #2 (permalink)  
Old 08-08-2007, 07:47 AM
Venkat Venkat is offline
D-Web Master
 
Join Date: Mar 2007
Posts: 350
Venkat is on a distinguished road
Default Re: Run an exe from an oracle trigger

Read the following posted a few days ago....

http://www.discussweb.com/databases/...lling-exe.html

Bye
Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!
Reply With Quote
  #3 (permalink)  
Old 08-09-2007, 10:31 AM
H2o H2o is offline
D-Web Analyst
 
Join Date: Jul 2007
Posts: 246
H2o is on a distinguished road
Default Re: Run an exe from an oracle trigger

Thank you Venkat,

but i want to run an exe from a trigger.
How do I do that?

Please!!!!! Please!!!!. Help me with this..
__________________
H2O

Without us, no one can survive..
Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!
Reply With Quote
  #4 (permalink)  
Old 08-09-2007, 11:14 AM
theone theone is offline
D-Web Sr.Programmer
 
Join Date: Jun 2007
Posts: 129
theone is on a distinguished road
Default Re: Run an exe from an oracle trigger

>but i want to run an exe from a trigger.

YOU DO NOT DO IT FROM A TRIGGER.

> How do I do that?

EXECUTING EXTERNAL COMMANDS CAN BE DONE VIA JAVA STORED PROCS OR DBMS_SCHEDULER.
Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!
Reply With Quote
  #5 (permalink)  
Old 08-09-2007, 11:27 AM
H2o H2o is offline
D-Web Analyst
 
Join Date: Jul 2007
Posts: 246
H2o is on a distinguished road
Default Re: Run an exe from an oracle trigger

Thank you Theone,

You can see that I have written a java stored procedure and called it in an oracle procedure. I have ended up with an error saying.

java.io.IOException: The handle is invalid.
at oracle.aurora.java.lang.OracleProcess.create(Nativ e Method)
at oracle.aurora.java.lang.OracleProcess.construct(Or acleProcess.java:25)
at java.lang.Runtime.execInternal(Native Method)
at java.lang.Runtime.exec(Runtime.java:566)
at java.lang.Runtime.exec(Runtime.java:428)
at java.lang.Runtime.exec(Runtime.java:364)
at java.lang.Runtime.exec(Runtime.java:326)
at CommandExection.Command(CommandExection:12)

I am really very sorry theone. I am new to Oracle.
__________________
H2O

Without us, no one can survive..
Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!
Reply With Quote
  #6 (permalink)  
Old 08-09-2007, 11:55 AM
bluesky bluesky is offline
D-Web Analyst
 
Join Date: Jun 2007
Posts: 201
bluesky is on a distinguished road
Default Re: Run an exe from an oracle trigger

Have you looked at the Java code I pointed you to? You should be able to copy and paste that just like that and execute and use it. It works for both 9i and 10G.



Code:
Here is the basic code:

create or replace and compile Java Source named "OSCommand" as
-- java:        OS COMMAND
-- descr:       Executes an Operating System Command using the JAVA RTS
--
-- IN parameter:        os command to execute (including fully qualified path names)
-- OUT parameter:       returncode [\nstring]
--                      where string a max of 32000 chars of the output of the command
--                      (note that \n is used as separators in the string)
--
--                      returncode=-1   Java RTS error occurred (e.g. command does not exist)
--                      returncode=255  o/s command failed (e.g. invalid command params)
--
--
import java.io.*;
import java.lang.*;
public class OSCommand{


        public static String Run(String Command){

                Runtime rt = Runtime.getRuntime();
                int     rc = -1;

                try{

                        Process p = rt.exec( Command );
                        int bufSize = 32000;
                        int len = 0;
                        byte buffer[] = new byte[bufSize];
                        String s = null;

                        BufferedInputStream bis = new BufferedInputStream( p.getInputStream(), bufSize );
                        len = bis.read( buffer, 0, bufSize );

                        rc = p.waitFor();

                        if ( len != -1 ){
                                s = new String( buffer, 0, len );
                                return( s );
                        }

                        return( rc+"" );
                }

                catch (Exception e){
                        e.printStackTrace();
                        return(  "-1\ncommand[" + Command + "]\n" + e.getMessage() );
                }

        }
}
/
show errors

create or replace function OSexec( cCommand IN string ) return varchar2 is
-- function:    OS EXEC
-- descr:       PL/SQL wrapper for the Java OSCOMMAND stored proc
--
language        JAVA
name            'OSCommand.Run(java.lang.String) return java.lang.String';
/
show errors


-- Punching a hole into the Java VM sandbox. The following must be run as 
-- sysdba. Substitute SCOTT with the applicable schema that owns the OSEXEC
-- and OSCOMMAND stored procs.
declare
        SCHEMA  varchar2(30) := 'SCOTT';
begin
        dbms_java.grant_permission(
                SCHEMA,
                'SYS:java.io.FilePermission',
                '<<ALL FILES>>',
                'execute'
        );

        dbms_java.grant_permission(
                SCHEMA,
                'SYS:java.lang.RuntimePermission',
                'writeFileDescriptor',
                '*'
        );

        dbms_java.grant_permission(
                SCHEMA,
                'SYS:java.lang.RuntimePermission',
                'readFileDescriptor',
                '*'
        );

commit;
end;
/

-- example: running the Unix/Linux date command to get the current date and time
SQL> select OSexec('/usr/bin/date') as STDOUT from dual;

STDOUT
----------------------------------------------------
Fri Sep  1 08:09:34 SAST 2006

1 row selected.

SQL>
Do you understand why this must not be called from a trigger? The issue about what a trigger is designed to do and the issue about triggers firing before a commit. There are even instances where a trigger can fire TWICE for the very same row as Oracle can restart a transaction in certain circumstances.

Do you understand a better approach would be to submit a job in the trigger and have the transaction commit/rollback that job - so only committed rows results in scheduled job?

The job can be an external job using DBMS_SCHEDULER. Or you can create a procedure that runs the external command via Java - and then submit jobs to run this procedure.
Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!
Reply With Quote
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Setup a clock with defined alarm times which trigger another event itbarota HTML, CSS and Javascript Coding Techniques 1 03-04-2008 09:57 PM
How can I setup a clock with defined alarm times which trigger another event? kingmaker HTML, CSS and Javascript Coding Techniques 1 01-19-2008 04:18 AM
Can we have a commit statement inside a trigger? if no why cant we? Mramesh Database Support 6 09-11-2007 03:08 AM
Storing the output from trigger in a text file sureshbabu Database Support 3 07-27-2007 06:44 AM
how to create trigger in MYSQL Jeyaseelansarc Database Support 5 07-10-2007 11:53 PM


All times are GMT -7. The time now is 04:53 AM.


Copyright ©2004 - 2007, DiscussWeb. All Rights Reserved.

SEO by vBSEO 3.0.0