Re: Resultset from Java Stored Procedure

From: Andy Flowers (notsupplied_at_nowayhose.com)
Date: 08/17/04 

Date: Mon, 16 Aug 2004 22:45:26 GMT

Might sound obvious, but where is the out parameter array ResultSet[] rs
initialised ?

How long is the result array ?

What happens if the Java stored procedure is changed to...

     public static void tokenizeString( String str, int[] r, ResultSet[]
rs )
    {
          /* code here */

          ((OracleConnection)con).setCreateStatementAsRefCursor(true);
          st = con.createStatement();
          rs1 = st.executeQuery("select empno, ename from emp");

          rs = new ResultSet[1]; /init result set...
          rs[0] = rs1 ;
    }

"MAB" <bad-email@nowhere.com> wrote in message
news:2np3vpF13hhdU1@uni-berlin.de...
>I want my Java stored procedure to return (as an out parameter) a resultset
> back to my java program but getting an error
>
> Here is the Java stored procedure ( only relevant parts of code are shown.
>
> public class StrTokenDB2
> {
> public static Connection con = null ;
> public static Statement st = null ;
> public static ResultSet rs1 = null ;
>
> public static void tokenizeString( String str, int[] r, ResultSet[]
> rs )
> {
> /* code here */
>
> ((OracleConnection)con).setCreateStatementAsRefCursor(true);
> st = con.createStatement();
> rs1 = st.executeQuery("select empno, ename from emp");
>
> rs[0] = rs1 ;
>
>
> }
>
> }
>
>
> Here is how I publish it
>
> CREATE OR REPLACE PACKAGE Emp1 AS
>
> TYPE rfc IS REF CURSOR;
>
> PROCEDURE STokenizer( ip_str IN VARCHAR2, op_rows OUT NUMBER, op_rst OUT
> rfc ) ;
>
> END Emp1;
>
>
>
> CREATE OR REPLACE PACKAGE BODY Emp1 AS
>
> PROCEDURE STokenizer( ip_str IN VARCHAR2, op_rows OUT NUMBER, op_rst OUT
> rfc ) AS LANGUAGE java name 'StrTokenDB2.tokenizeString(java.lang.String,
> int[], java.sql.ResultSet[] )' ;
>
> END Emp1;
>
>
> and here is how I call it in my program ( again only relevant parts are
> shown)
>
> cs = con.prepareCall("{call EMP1.STokenizer(?,?,?)}");
> cs.setString(1,str);
> // This worked fine
> cs.registerOutParameter(2,Types.INTEGER);
> // This also worked fine
> cs.registerOutParameter(3,OracleTypes.CURSOR);
> // Until I added this
>
> cs.executeUpdate() ;
> // get an error here
>
>
> /* This is the error I get
>
> ORA-00932: inconsistent datatypes: expected OUT Conversion failed
> ORA-06512: at "SCOTT.EMP1", line 170
> ORA-06512: at line 1 */
>
> // Final two statements
>
> System.out.println( cs.getInt(2) + " Rows inserted") ;
> //
> This worked fine
>
> rs = ((OracleCallableStatement)cs).getCursor(3) ; // It
> doesn't even get here so I cant say about this statement :(
>
>
> Would appreciate any help.
>
> thx
>
>