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 ...
| |||||||
| Register | FAQ | Members List | Calendar | Mark Forums Read |
| |||
| 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();
}
} 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.. |
| Sponsored Links |
| |||
| Read the following posted a few days ago.... http://www.discussweb.com/databases/...lling-exe.html Bye |
| |||
| >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. |
| |||
| 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.. |
| |||
| 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 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. |
![]() |
| Thread Tools | |
| Display Modes | |
| |
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 |