Re: How to set a bind variable in Java?

You didn't show your corresponding Java code which tries to retrieve the REF CURSOR, so my internal optimizer is in guess mode :)

You should use an SQLJ iterator or ResultSet to bind REF CURSOR to. Something like this (SQLJ syntax):

import java.sql.*;
....
// define an iterator for your cursor, SQLJ will expand it into actual
// class definition.

#sql public static iterator MyIter (int col1, String col2, java.sql.Date col3);

// note that MyIter is strongly typed - it has all the columns and their types
// defined. If you do not know which columns will be in the REF CURSOR,
// you should use java.sql.ResultSet instead of SQLJ iterator:
//
// ResultSet rc = null;
//

MyIter rc = null;
....

#sql rc = { VALUES( rs_sp_getmodrecs_accounts1(...) ) };

while (rc.next())
 { // process resultset here, if you use the named iterator, you can access each    // column by its name, like rc.col1(), rc.col2(), etc.  }

rc.close();

...

If you use pure JDBC, the code will look a lot more complicated since SQLJ hides all the JDBC details and expands its #sql statements into actual JDBC calls on translation. Anyway, you should use java.sql.ResultSet or its derivatives for REF CURSORs.

--
Vladimir Zakharychev (bob@dpsp-yes.com)                http://www.dpsp-yes.com
Dynamic PSP(tm) - the first true RAD toolkit for Oracle-based internet applications.
All opinions are mine and do not necessarily go in line with those of my employer.


"jacob nikom" <jacob_nikom_at_ieee.org> wrote in message
news:6eebd424.0202191659.c309ffd_at_posting.google.com...
> Hi,
>
> I have a problem with stored function in PL/SQL returning
> refcursor to Java in Oracle9 on Solaris. When I run the
> Java program which calls the stored function it complains:
> "java.sql.SQLException: ORA-01006: bind variable does not exist"
>
> My stored function runs very well in PL/SQL, but when I call
> it I have to set up a bind variable:
>
> VARIABLE rc REFCURSOR
> EXECUTE :rc :=
rs_sp_getmodrecs_accounts1('jacob',TO_DATE('12-FEB-02','DD-MON-YY'),TO_DATE('14-FEB-02','DD-MON-YY')
)
> print rc
>
> I think the reason for the complain is that I did not set up the
> bind variable rc in Java. I don't know how to do it. Do you know
> how to solve this problem?
>
> Thank you,
>
> Jacob Nikom