Re: java stored procedure returning cursor?

From: Thomas J. Kyte <tkyte_at_us.oracle.com>
Date: Tue, 04 Apr 2000 16:47:13 GMT
Message-ID: <8cd6ab$8fb$1@nnrp1.deja.com>

In article <38EA0D66.583009FF_at_mdintouch.com>, Eddie Hernandez <hernandez_at_mdintouch.com> wrote:
> From a JSP file I would like to call a java stored procedure and have
it
> return a resultset which I am assuming is a cursor in oracle. Is this
> possible? I was able to have a PL/SQL function return a cursor to a
JSP
> via a Callable Statement but can't get it to work with a Java Stored
> Procedure.
>
> thanks in advance,
> -Eddie Hernandez
> Chief Systems Architect
> MDinTouch.com, Inc.
>
>

You cannot return a result set directly from a JAVA stored procedure to a
client. There is a very easy way to do this, but you won't be opening the
result set at the java layer, rather you'll have PLSQL open the result set
(cursor variable) for you..

The approach is to let the java stored procedure return a string -- this string
is the query you would have passed to:

    statement.execute( "select * from emp" );     ResultSet resultSet = statement.getResultSet();

statement.execute (it is query in the above example). You'll just forget the
subsequent linesof code as well, we won't need it. Then, since ALL java stored procedures can be wrapped with a SQL binding layer -- we can open the
result
set at THAT layer. For example, say you have a class loaded:

class Foo {

    public static void get_result_set(String[] the_result_set) {

        the_result_set[0] = "select * from emp";

    }
}

So, instead of have a result set as an out parameter, this has a String as an
out parameter. We set the string to our query (instead of doing the above
lines of jdbc calls).

Then, at the sql binding layer we code:

create or replace package Foo
as

    type rc is ref cursor;
    procedure foo( p_refcur in out rc ); end;
/

create or replace package body Foo
as

    procedure get_result_set( p_result_set out varchar2 )     as language java
    name 'Foo.get_result_set( java.lang.String[] )';

    procedure foo( p_refcur in out rc )     as

        l_query varchar2(1024);
    begin

        get_result_set( l_query );
        open p_refCur for l_query;

    end;
end;
/

So, the stored procedure Foo.foo calls the java, gets the query and makes a ref
cursor out of it. We can then code at the java client side something like the
following:

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

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

      String query  = "begin Foo.foo( :1 ); end;";
      int         rsetCount;

    DriverManager.registerDriver (new oracle.jdbc.driver.OracleDriver ());

      System.out.println( "connecting..." );     Connection conn =

      DriverManager.getConnection
        ("jdbc:oracle:thin:@aria:1521:ora8i", "tkyte", "tkyte");

      System.out.println( "connected..." );

      Statement stmt = conn.createStatement ();

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

      ResultSet rset = (ResultSet)cstmt.getObject(1);

      for (rsetCount = 0; rset.next (); rsetCount++ )
         System.out.println( rset.getString( "ename" ) );

      rset.close();
      cstmt.close();

  }
}

--
Thomas Kyte tkyte_at_us.oracle.com
Oracle Service Industries http://osi.oracle.com/~tkyte/index.html --
Opinions are mine and do not necessarily reflect those of Oracle Corp

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Tue Apr 04 2000 - 11:47:13 CDT