How to Upload Java Batch Class in Oracle Pl/sql
Calling a batch file thru PL/SQL
Can nosotros phone call a batch file or a batch command thru Oracle Stored procedures?
Any help would be appreciated.
Comments
-
Yous tin can
1) Create an External Library call in C, and wrap information technology in a PL/SQL call.
2) Run coffee from within the database, if you have the right version, and wrap it in a PL/SQL telephone call.Maybe other things that I don't know nigh.
Expert Luck
Eric Kamradt -
Even better is this I found on AskTom.oracle.com
I pasted the page here for your viewing pleasure.
------------------------------------------------------------------------------------------------------------------------------------- You Asked Hello. I followed the examples from the recent thread on the issue and take found limited success. I can get the coffee to run commands such as mkdir, rmdir and ls, merely when trying to execute a crush script or create a file via "repeat hullo >> steven.out" it fails. When I call the java class from the OS, the crush script runs fine. I have assigned read, write and execute permissions on the shell script and the /usr/bin directory. I have assigned writeFilePermissions besides. Please could y'all have a quick look over what I have done and maybe suggest what I accept forgotten or done wrong? Java Course: import java.io.*; public grade ExecCommand { public ExecCommand() { } static public int runCommand(Cord cmd) throws IOException { // get-go command running Process proc = Runtime.getRuntime().exec(cmd); // wait for control to terminate attempt { proc.waitFor(); } grab (InterruptedException e) { Arrangement.err.println("process was interrupted"); render -one; } // check its exit value if (proc.exitValue() != 0) { System.err.println("exit value was non-nada"); return -ii; } // render success return 0; } public static void main(String args[]) throws IOException { Cord cmdString = args[0]; Organisation.out.println("command string is: " + cmdString); try { //run a command int retVal = runCommand(cmdString); System.out.println("return value is: " + retVal); } catch (IOException e) { System.err.println(e); } } } ** Note: when the phone call fails, it is always a -2 returned. Procedure in the Database: CREATE OR REPLACE Role Exec_Command(Command IN STRING) RETURN NUMBER IS Language JAVA NAME 'ExecCommand.runCommand(java.lang.String) return int'; / The code: DECLARE RetVal NUMBER; Begin RetVal := SOSSIX.EXEC_COMMAND ( '/usr/bin/mkdir /dwelling/stevenh/extracts/mydir' ); END; -- creates a directory OK. The code: DECLARE RetVal NUMBER; BEGIN RetVal := SOSSIX.EXEC_COMMAND ( '/usr/bin/sh /home/stevenh/extracts/post_files.sh c' ); END; -- does not produce anything. A log file should be created when the trounce script is run. The value c is a required parameter. The following grants have been issued: exec dbms_java.grant_permission('SOSSIX','java.io.FilePermission','/home/stevenh/extra cts/-','read'); exec dbms_java.grant_permission('SOSSIX','coffee.io.FilePermission','/dwelling house/stevenh/actress cts/-','execute'); exec dbms_java.grant_permission('SOSSIX','java.io.FilePermission','/home/stevenh/extra cts/-','write'); exec dbms_java.grant_permission('SOSSIX','coffee.io.FilePermission','/usr/bin/*','execut e'); exec dbms_java.grant_permission('SOSSIX','java.io.FilePermission','/tmp/sh*','read,exe beautiful'); exec dbms_java.grant_permission('SOSSIX','coffee.lang.RuntimePermission','*','writeFileD escriptor'); exec dbms_java.grant_permission('SOSSIX','coffee.lang.RuntimePermission','*','readFileDe scriptor'); GRANT JAVADEBUGPRIV TO SOSSIX; GRANT JAVAIDPRIV TO SOSSIX; GRANT JAVASYSPRIV TO SOSSIX; GRANT JAVAUSERPRIV TO SOSSIX; GRANT JAVA_ADMIN TO SOSSIX; GRANT JAVA_DEPLOY TO SOSSIX; If I am on the AIX system, I can run: java ExecCommand "sh post_files.sh c" The shell script runs and the log file is created. Return lawmaking is 0. Many cheers for whatever aid given. Steven. -------------------------------------------------------------------------------- and we said... Well, first of all a 'control' like: echo 'hullo world' >> /tmp/foo will *never work* since they are non a command but rather a string that the shell parses and interprets. This thing runs commands -- it does NOT piece of work as a vanquish! echo 'hello world' would work simply >> -- that is a shell thing, menstruum. 99.99% of the time what goes wrong with vanquish scripts is: o the ORACLE software owner does non accept rx on the directories o the ORACLE software owner does not have rx on the script (call up, it runs every bit oracle) o the shell script tries to write or read a file ORACLE is not allowed to. I WOULD STRONGLY encourage you to NOT grant permission on /usr//bin/* -- that lets you lot run the sh and that lets yous do Annihilation (warning lights should be going off) Was this response helpful to you? Let us know! Reviews -------------------------------------------------------------------------------- May 01, 2002 Reviewer: A reader Thanks, I granted execute on /usr/bin/sh every bit I had to include the sh in the control cord when running the class standalone otherwise it wouldn't work. "o the ORACLE software owner does not take rx on the directories o the ORACLE software owner does not accept rx on the script (think, it runs as oracle) o the shell script tries to write or read a file ORACLE is not allowed to. " I used chmod 777 on the directory the shell script is in and the same for the shell script itself. I know the access rights are lax at the moment but I am merely trying to get the damn affair working through the database at the moment. Followup: and and then did it piece of work? you don't say. -
Even so even better is this every bit found on
http://www.ckamco.com/Execute_Command.nullTom was non totally correct
echo something >> t.txt
is a beat out command, merely you can still execute it from PL/SQL
simply execute a shell. See under examples below.Install notes
--------------------i) Compile ExecCommand.java
2) load it into a oracle schema from a command line.
loadjava -user USER/[email protected] ExecCommand.class
three) Compile the script/package Execute_Command.sql
four) Y'all may demand some permissions, but I don't know what they are as I
tested this in a schema that had all rights.5) Run a exam. Try TEST_Execute_Command.sql.
Good Luck
Eric Kamradt------------------------------
ExecCommand.coffee ======================== import coffee.io.*; /** * Run System commands and serve upward the output as getters. This was * written to run from inside a Oracle database. * * Freeware - use at your own gamble * * @author Eric Alexander Kamradt ([e-mail protected]) */ public form ExecCommand { /** * Separator used between lines of returned text and exception. */ public static final Cord C_SEPARATOR = "\northward"; /** * The Cord holder for the Results of the last 'run' */ protected static String g_Result_Text = ""; /** * The String holder for the Exception Text of the terminal 'run' */ protected static Cord g_Exception_Text = ""; /** * You can examination this programme from the command line. */ public static void main(String args[]) throws IOException { String cmdString = args[0]; System.out.println("command string is: " + cmdString); //run a command int retVal = run (cmdString); System.out.println("return value is: " + retVal); } /** * Constructor, non really used. */ public ExecCommand() { ; } /** * Return the concluding commands result to the caller. */ public static String getResultText () { return g_Result_Text; } /** * Render the last commands Exception text to the caller. */ public static String getExceptionText () { render g_Exception_Text; } /** * Old runCommand written past unknown. */ static public int runCommand(String cmd) throws IOException { // start command running Process proc = Runtime.getRuntime().exec(cmd); // look for control to finish try { proc.waitFor(); } take hold of (InterruptedException e) { Organisation.err.println("process was interrupted"); return -one; } // check its exit value if (proc.exitValue() != 0) { System.err.println("exit value was not-zero"); return -2; } // return success return 0; } /** * Convert the text of an Exception to a cord. */ private static String execeptionToString ( Exception p_Execption ) { StringWriter v_StringWriter = new StringWriter (); PrintWriter v_PrintWriter = new PrintWriter ( v_StringWriter ); p_Execption.printStackTrace ( v_PrintWriter ); v_PrintWriter.affluent (); return v_StringWriter.toString (); } /** * Read all from a BufferReader into a Cord. */ private static String BufferedReaderToString ( BufferedReader p_BufferedReader ) throws IOException { StringBuffer v_StringBuffer = new StringBuffer (); int v_Length = 0; String v_Line = p_BufferedReader.readLine (); if ( v_Line != cypher ) { v_StringBuffer.append ( v_Line ); v_StringBuffer.suspend ( C_SEPARATOR ); } while ( v_Length != v_StringBuffer.length () ) { v_Length = v_StringBuffer.length (); v_Line = p_BufferedReader.readLine (); if ( v_Line != null ) { v_StringBuffer.append ( v_Line ); v_StringBuffer.append ( C_SEPARATOR ); } } return v_StringBuffer.toString(); } /** * run command. This command was meant to be called from a Peel and Squeal * (PL/SQL) wrapper from inside an Oracle database. */ private static int run ( String p_Command ) { int v_Return_Code = 2; Process v_Process = goose egg; /* * Initialize the upshot information. */ g_Result_Text = ""; g_Exception_Text = ""; effort { Runtime v_Runtime = Runtime.getRuntime (); v_Process = v_Runtime.exec ( p_Command ); BufferedReader v_BufferedReader = new BufferedReader ( new InputStreamReader ( v_Process.getInputStream () ) ); BufferedReader v_ErrorBufferedReader = new BufferedReader ( new InputStreamReader ( v_Process.getErrorStream () ) ); int v_Length = 0; String v_Line; /* * Get the Standard Err Text and ...... * Become the Standard Out text */ g_Exception_Text = BufferedReaderToString ( v_ErrorBufferedReader ); g_Result_Text = BufferedReaderToString ( v_BufferedReader ); v_Process.waitFor (); } catch( Exception e ) { if ( g_Exception_Text.length () > 0 ) { g_Exception_Text += "\n"; } g_Exception_Text += execeptionToString ( e ); v_Return_Code = -two; } /* * Ensure the fault code reflects whatsoever not-execution of the command. */ if ( v_Return_Code != -2 && ( v_Process == cypher || v_Process.exitValue () != 0 ) ) { v_Return_Code = -2; } return v_Return_Code; } } Execute_Command.sql ========================== CREATE OR Supplant PACKAGE Execute_Command -- ----------------------------------------------------------------------------- -- Package : Execute_Command -- Purpose : To run operating organisation commands from PL/SQL, control results may -- be viewed. -- Author : Eric Alexander Kamradt ([email protected]) 01-May-2002 -- ----------------------------------------------------------------------------- IS -- ------------------------------------------------------------------------- -- Function : Run -- Purpose : To run operating arrangement commands from PL/SQL, control results may -- exist viewed. -- Author : Eric Alexander Kamradt ([email protected]) 01-May-2002 -- ------------------------------------------------------------------------- Function Run ( p_Command IN VARCHAR2 ) Render NUMBER; -- -- ------------------------------------------------------------------------- -- Office : Get_Exception_Text -- Purpose : Returns the Exception text from the last control 'run'. -- Author : Eric Alexander Kamradt ([e-mail protected]) 01-May-2002 -- ------------------------------------------------------------------------- FUNCTION Get_Exception_Text Return VARCHAR2; -- -- ------------------------------------------------------------------------- -- Role : Get_Result_Text -- Purpose : Returns the result text from the final command 'run'. -- Author : Eric Alexander Kamradt ([electronic mail protected]) 01-May-2002 -- ------------------------------------------------------------------------- Role Get_Result_Text Render VARCHAR2; -- END Execute_Command; / show errors PACKAGE Execute_Command; CREATE OR REPLACE Packet Trunk Execute_Command IS -- ------------------------------------------------------------------------- -- Function : Run -- Purpose : To run operating system commands from PL/SQL, control results may -- exist viewed. -- Author : Eric Alexander Kamradt ([email protected]) 01-May-2002 -- ------------------------------------------------------------------------- Role Run ( p_Command IN VARCHAR2 ) RETURN NUMBER IS Linguistic communication Coffee NAME 'ExecCommand.run (java.lang.Cord) return int'; -- -- ------------------------------------------------------------------------- -- Part : Get_Exception_Text -- Purpose : Returns the Exception text from the terminal command 'run'. -- Author : Eric Alexander Kamradt ([electronic mail protected]) 01-May-2002 -- ------------------------------------------------------------------------- FUNCTION Get_Exception_Text Render VARCHAR2 IS Linguistic communication JAVA NAME 'ExecCommand.getExceptionText() return Cord'; -- -- ------------------------------------------------------------------------- -- Function : Get_Result_Text -- Purpose : Returns the effect text from the concluding control 'run'. -- Author : Eric Alexander Kamradt ([e-mail protected]) 01-May-2002 -- ------------------------------------------------------------------------- FUNCTION Get_Result_Text RETURN VARCHAR2 IS LANGUAGE Coffee NAME 'ExecCommand.getResultText() render Cord'; -- Cease Execute_Command; / show errors Packet Trunk Execute_Command; Exam ===================== DECLARE -- ----------------------------------------------------------------------------- -- Examination Script : TEST_Execute_Command.sql -- Purpose : To examination executing organization commands on my laptop from the dataabse. -- Author : Eric Alexander Kamradt ([electronic mail protected]) 01-May-2002 -- ----------------------------------------------------------------------------- Procedure Run_Command ( p_Command IN VARCHAR2 ) IS -- ----------------------------------------------------- -- Procedure : Out -- Purpose : Output multiple lines as separated past linefeeds 'CHR(ten)'. -- Author : Eric Kamradt ([email protected]) 21-Dec-2001 -- ----------------------------------------------------- PROCEDURE out ( p_Text IN VARCHAR2 ) IS v_Pos INTEGER; l_Text VARCHAR2 ( 32000 ) := p_Text; l_Output VARCHAR2 ( 255 ); Brainstorm -- <<Text_Loop>> LOOP -- IF l_Text IS Cipher So Leave Text_Loop; Finish IF; -- v_Pos := INSTR ( l_Text, CHR(x) ); -- IF v_Pos > 0 Then l_Output := SUBSTR ( SUBSTR ( l_Text, 1, v_Pos - 1 ), 1, 255 ); l_Text := SUBSTR ( l_Text, v_Pos + 1 ); ELSE l_Output := l_Text; l_Text := NULL; End IF; -- -- Remove any carriage return characters -- DBMS_OUTPUT.PUT_LINE ( REPLACE ( l_Output, CHR(thirteen) ) ); -- END LOOP Text_Loop; -- Stop Out; Begin Out ( '.'); Out ( '.'); Out ( '----- Running Command ----------'); Out ( p_Command ); Out ( 'Command Returned :' || Execute_Command.Run ( p_Command ) ); Out ( '----- Start Results ----------'); Out ( Execute_Command.Get_Result_Text ); Out ( '----- Start Exception ----------'); Out ( Execute_Command.Get_Exception_Text ); Out ( '----- Cease ----------'); Stop Run_Command; BEGIN -- -- I'm running Windows, and running cygwin, so I tin can -- run some Unix commands like......... -- Run_Command ( 'ls -la'); Run_Command ( 'dir'); -- -- This should throw an fault. -- Run_Command ( 'xx'); Run_Command ( 'echo What time is information technology?'); -- -- Shell out run run pipe commands. -- Run_Command ( 'bash -c "echo My proper name is TwoPigs > t.txt"'); Run_Command ( 'cat t.txt'); Run_Command ( 'true cat initAIR.ora'); Run_Command ( 'env'); Run_Command ( 'pwd'); END; / Results of tests ---------------------- SQL> @c:\temp\t.sql . . ----- Running Control ---------- ls -la Control Returned :ii ----- Start Results ---------- total 55 drwxr-xr-10 3 Organization Administ 4096 May 1 12:04 . drwxr-xr-x 51 SYSTEM Administ 8192 April 8 14:10 .. -rw-r--r-- 1 Organization Administ 1536 Apr 28 12:52 PWDAIR.ora drwxr-xr-x 2 Arrangement Administ 0 Apr 8 xiv:08 archive -rw-r--r-- ane Arrangement Administ 42 Apr 8 14:47 initAIR.ora -rwxr-xr-ten 1 Organization Administ 31744 April eight 14:08 oradba.exe -rw-r--r-- ane System Administ 386 Apr xix 09:22 oradim.log -rw-r--r-- 1 SYSTEM Administ 6560 Apr 15 15:32 sqlnet.log -rw-r--r-- one SYSTEM Administ 20 May i 12:19 t.txt ----- Start Exception ---------- ----- Finish ---------- . . ----- Running Control ---------- dir Command Returned :2 ----- Start Results ---------- PWDAIR.ora archive initAIR.ora oradba.exe oradim.log sqlnet.log t.txt ----- Start Exception ---------- ----- Stop ---------- . . ----- Running Control ---------- xx Command Returned :-two ----- Start Results ---------- ----- Showtime Exception ---------- java.io.IOException at oracle.aurora.java.lang.OracleProcess.create(OracleProcess.java) at oracle.aurora.java.lang.OracleProcess.construct(OracleProcess.java) at java.lang.Runtime.execInternal(Runtime.coffee) at java.lang.Runtime.exec(Runtime.java) at java.lang.Runtime.exec(Runtime.java) at java.lang.Runtime.exec(Runtime.java) at ExecCommand2.run2(ExecCommand2.java:136) at ExecCommand2.run(ExecCommand2.java:116) ----- Stop ---------- . . ----- Running Command ---------- echo What time is it? Command Returned :ii ----- Start Results ---------- What time is information technology? ----- Kickoff Exception ---------- ----- End ---------- . . ----- Running Command ---------- bash -c "repeat My name is TwoPigs > t.txt" Command Returned :2 ----- Start Results ---------- ----- Start Exception ---------- ----- Cease ---------- . . ----- Running Command ---------- cat t.txt Control Returned :2 ----- Start Results ---------- My name is TwoPigs ----- Start Exception ---------- ----- End ---------- . . ----- Running Command ---------- cat initAIR.ora Control Returned :2 ----- Start Results ---------- IFILE='C:\oracle\admin\AIR\pfile\init.ora' ----- Start Exception ---------- ----- Stop ---------- . . ----- Running Command ---------- env Command Returned :2 ----- Start Results ---------- ALLUSERSPROFILE=C:\Documents and Settings\All Users.WINNT COMMONPROGRAMFILES=C:\Program Files\Common Files COMPUTERNAME=ERIC-425 COMSPEC=C:\WINNT\system32\cmd.exe NUMBER_OF_PROCESSORS=i ORACLE_SID=air Os=Windows_NT OS2LIBPATH=C:\WINNT\system32\os2\dll; PATH=/cygdrive/c/oracle/oraForms6i/bin:/cygdrive/c/oracle/ora81Demos/bin:/cygdrive/c/oracle/ora81/bin:/cygdrive/c/oracle/ora81/Apache/Perl/5.00503/bin/mswin32-x86:/cygdrive/c/Programme Files/Oracle/jre/1.1.vii/bin:/cygdrive/c/oracle/oraForms/bin:/cygdrive/c/West PATHEXT=.COM;.EXE;.BAT;.CMD;.VBS;.VBE;.JS;.JSE;.WSF;.WSH PROCESSOR_ARCHITECTURE=x86 PROCESSOR_IDENTIFIER=x86 Family 6 Model xi Stepping 1, GenuineIntel PROCESSOR_LEVEL=half dozen PROCESSOR_REVISION=0b01 PROGRAMFILES=C:\Program Files SYSTEMDRIVE=C: SYSTEMROOT=C:\WINNT TEMP=/cygdrive/c/WINNT/TEMP TMP=/cygdrive/c/WINNT/TEMP USERPROFILE=C:\Documents and Settings\Default User.WINNT WF_RESOURCES=C:\oracle\ora81\WF\RES\WFUS.RES WINDIR=C:\WINNT WV_GATEWAY_CFG=C:\oracle\ora81\Apache\modplsql\cfg\wdbsvr.app TERM=cygwin LOGONSERVER=\\ERIC-425 HOME=/ ----- Beginning Exception ---------- ----- End ---------- . . ----- Running Command ---------- pwd Command Returned :2 ----- Beginning Results ---------- /cygdrive/c/oracle/ora81/DATABASE ----- Start Exception ---------- ----- End ---------- PL/SQL procedure successfully completed.
This word has been closed.
Source: https://community.oracle.com/tech/developers/discussion/56840/calling-a-batch-file-thru-pl-sql
0 Response to "How to Upload Java Batch Class in Oracle Pl/sql"
Post a Comment