Re: java stored procedure returning cursor?

I currently have PL/SQL procedures that process queries and return cursors to JavaServer Pages. I was looking to completely replace the PL/SQL procedures with Java Stored Procedures. Your solution is a hybrid solution in that you have the SQL defined in Java and the PL/SQL still having to open the cursor and return it to the calling program. This is just adding an extra layer I don't really need.

According to Oracle, everything you do in PL/SQL you can do in Java Stored Procedures....apparently they missed having Java Stored Procedures return cursors. Its pretty disappointing but oh well.....

anyway, thanks for help,
-Eddie

"Thomas J. Kyte" wrote:

> 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 - 12:33:31 CDT