Solved

How to get Return String from Oracle Procedure  in Java class

Posted on 2004-04-02
3
2,110 Views
Last Modified: 2007-12-19
Hi

Iam Calling Stored Procedure  (Oracle 9i, J2ee)
Object of Stored procedure to insert Data into a table in this process it makes 2 calls to other tables get relevant data
finally insert Row into target table.

In my procedure i have appended Error messages with separated by ","

How can i get this return string

Here is my code
strSql = "{? = call package.proc_name(?,?,?,?,?,?,?,?)}";
cstmt = connection.prepareCall(strSql);
cstmt.setLong(1, Long.parseLong(form.searchYear));
..............................

cstmt.executeUpdate();

How to get return string from Procedure

-Lakshmana
0
Comment
Question by:LakshmanaRavula
3 Comments
 
LVL 5

Assisted Solution

by:mudumbe
mudumbe earned 75 total points
ID: 10744320
You should be able to use:

cstmt.registerOutParameter(1,Types.CHAR);

cstmt.executeUpdate();

String result = cstmt.getString(1);
0
 
LVL 8

Accepted Solution

by:
william_jwd earned 75 total points
ID: 10746830
try this,

OracleCallableStatement ocs = (OracleCallableStatement) conn.prepareCall( "{ ? = call Proc_Name(?)}");
        ocs.setString(2,"DTD_REFERENCE");
        ocs.registerOutParameter(1,OracleTypes.LONGVARCHAR);
        ocs.execute();
        String str = (String) ocs.getString(1);
0
 
LVL 1

Assisted Solution

by:Dazman
Dazman earned 100 total points
ID: 10751891
Heres a runnable example, my Java class will only set up the callable statement if its not already done (saves parse time!):

SQL> REM create the function that the java will call
SQL> create or replace function j_test(a in varchar2)
  2    return varchar2
  3  is
  4  begin
  5    return 'hello from Oracle';
  6  end;
  7  /

Function created.

SQL> REM now create the java.
SQL> CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED "JavaTest" AS
  2  // Packages for JDBC related Classes
  3  import oracle.jdbc.driver.*;
  4  import oracle.sql.*;
  5  import java.sql.*;
  6  import java.sql.Connection;
  7  import java.sql.SQLException;
  8  
  9  public class JavaTest
 10  {
 11    /* Static variables */
 12    private static Connection conn;        /** Connection to the remote server. */
 13    private static CallableStatement stmt; /** Statement to call Geneva with. */
 14  
 15    private static String host = "147.149.178.73";
 16    private static String portNum = "1521";
 17    private static String sid = "spgd1";
 18    private static String uname = "lindled";
 19    private static String pword = "duaij6";
 20  
 21    public static void connect()
 22    throws SQLException
 23    {
 24      // Establish a connection if we do not have one.
 25      if (conn == null)
 26      {
 27        // Register the Oracle JDBC driver.
 28        DriverManager.registerDriver (new oracle.jdbc.driver.OracleDriver());
 29        // Establish a connection to the database.
 30        conn = DriverManager.getConnection("jdbc:oracle:thin:@" + host + ":"
 31                                           + portNum + ":" + sid,
 32                                          uname, pword);
 33      }
 34    }
 35    
 36    
 37    /**
 38     * This method prepares the anonymous block to send to Geneva.
 39     */
 40    private static void prepareCall()
 41    throws SQLException
 42    {
 43      // If the statment has not been initialised then initialise it.
 44      if (stmt == null)
 45      {
 46        stmt = conn.prepareCall("begin ? := j_test(?); end;");
 47        stmt.registerOutParameter(1, OracleTypes.LONGVARCHAR);
 48      }
 49    }
 50    
 51    
 52    public static void sendRequest()
 53    throws SQLException
 54    {
 55      // Connect.
 56      connect();
 57      
 58      // Create a callable statement.
 59      prepareCall();
 60      
 61      // Register the input.
 62      stmt.setString (2, "hello world");
 63      
 64      try
 65      {
 66        // Run the call.
 67        stmt.execute();
 68      }
 69      catch(SQLException e)
 70      {
 71        conn = null;
 72        stmt = null;
 73        throw e;
 74      }
 75      
 76      // Obtain the output value.
 77      System.out.println(stmt.getString(1));
 78      
 79    }
 80  }
 81  /

Java created.

SQL> REM create a wrapper for the java so i can test it from SQLplus
SQL> create or replace procedure call_java
  2  is language java
  3  name 'JavaTest.sendRequest()';
  4  /

Procedure created.

SQL> REM this will fail as the java state is changed
SQL> exec call_java;
BEGIN call_java; END;

*
ERROR at line 1:
ORA-29549: class LINDLED.JavaTest has changed, Java session state cleared
ORA-06512: at "LINDLED.CALL_JAVA", line 0
ORA-06512: at line 1


SQL> REM enable output
SQL> set serverout on
SQL> exec dbms_java.set_output(10000);

PL/SQL procedure successfully completed.

SQL>
SQL> REM call the java class we should see "hello from Oracle" come back
SQL> exec call_java;
hello from Oracle

PL/SQL procedure successfully completed.

SQL>
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many‚Ķ
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

832 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question