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
Andy Flowers - 16 Aug 2004 23:45 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 ;
}
>I want my Java stored procedure to return (as an out parameter) a resultset
> back to my java program but getting an error
[quoted text clipped - 73 lines]
>
> thx