Re: passing a cursor to a stored procedure from Java

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Thu, 22 Jul 1999 20:09:36 GMT
Message-ID: <37977a2f.33971608@newshost.us.oracle.com>

A copy of this was sent to "Michael Waelde" <mwaelde_at_intershop.de> (if that email address didn't require changing) On Thu, 22 Jul 1999 16:45:17 +0200, you wrote:

>Hello all,
>how can I pass a cursor as an in/out-parameter to a procedure from Java?
>I tried the following but it didn't work.
>When I call the procedure from commandline it works fine.
>
>...
>ResultSet refcur = null;
>CallableStatement statement = conn.prepareCall("{ ? = call sp_CustomerVerify
>(?,?)}");
>statement.registerOutParameter(1, OracleTypes.CURSOR);
>statement.setObject(2, refcur);
>statement.setString(3, custno);
>statement.execute();
>...
>
>Michael
>

You can get a result back from a stored procedure. It would look like this:

create or replace package types
as

    type cursorType is ref cursor;
end;
/  

create or replace function sp_ListEmp return types.cursortype as

    l_cursor types.cursorType;
begin

    open l_cursor for select ename, empno from emp order by ename;  

    return l_cursor;
end;
/    

import java.sql.*;
import java.io.*;
import oracle.jdbc.driver.*;

class curvar
{
  public static void main (String args [])

                     throws SQLException, ClassNotFoundException
  {
      String driver_class = "oracle.jdbc.driver.OracleDriver";
      String connect_string = "jdbc:oracle:thin:@slackdog:1521:oracle8";

      String query = "begin :1 := sp_listEmp; end;";
      Connection conn;

      Class.forName(driver_class);
      conn = DriverManager.getConnection(connect_string, "scott", "tiger");

      CallableStatement cstmt = conn.prepareCall(query);
      cstmt.registerOutParameter(1,OracleTypes.CURSOR);
      cstmt.execute();
      ResultSet rset = (ResultSet)cstmt.getObject(1);

      while (rset.next ())
        System.out.println( rset.getString (1) );
      cstmt.close();

  }
}

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Thu Jul 22 1999 - 15:09:36 CDT