Home » Developer & Programmer » JDeveloper, Java & XML » Need help returning int from Java SP to PL/SQL SP (Oracle 10g on Windows 2003)
Need help returning int from Java SP to PL/SQL SP [message #450674] Thu, 08 April 2010 11:46 Go to next message
dkranes
Messages: 25
Registered: February 2008
Location: Upstate New York
Junior Member
I need to be able to capture the code from my PL/SQL SP and act accordingly. How can I do this? Here is all my code:


JAVA SP:
CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED Command
AS
import java.io.*;
import java.util.*;
public class Command{
public static int run(String cmdText)
throws IOException, InterruptedException, Exception
{
int rtn;
try {
Runtime rt = Runtime.getRuntime();
Process prcs = rt.exec(cmdText);
rtn = prcs.waitFor();
}
catch (Exception e) {
      //System.out.println("Transaction failed: " + e.getMessage());
      return -1;
       
} 
   return rtn;
}
}
/ 



The Java returns a desired code for me now but how do I get it back to my PL/SQL? Here is the next part:

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



Here is the snipit of PL/SQL code that calls runoscommand above. It is part of a Stored Procedure. The return code needs to return to runoscommand and then to my PL/SQL Stored Procedure.


IF p_comp_fmt = 'Z' THEN
    loscmd := 'zip -j -1 ' || l_zip_dir || '\' || p_comp_fname || ' ' || l_zip_dir || '\' || p_filename ;
    
    -- Compress the file.
    runoscommand(loscmd);
 
    utl_file.fremove(p_dir, p_filename);
        
   ELSIF p_comp_fmt = 'G' THEN
    loscmd := 'gzip -f ' || l_zip_dir || '/' || p_filename || ' ' || l_zip_dir || '/' || p_comp_fname;
 
    -- Compress the file.
    runoscommand(loscmd);
 
    utl_file.fremove(p_dir, p_filename);
   END IF;



David

Re: Need help returning int from Java SP to PL/SQL SP [message #450731 is a reply to message #450674] Fri, 09 April 2010 00:11 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
A stored procedure typically does not return a value; a function would.
Now, I have not worked with Java in the database, so I cannot tell you 100% certain that it would work, but I'd say you should create your wrapper as a FUNCTION rather than a PROCEDURE.

Hope this helps
Previous Topic: insert command
Next Topic: OAF Programming insert row VO,but inner-table no data show?
Goto Forum:
  


Current Time: Fri Oct 18 13:24:38 CDT 2019