Re: Syntax of setting Ref Cursor in procedure from JDBC

Chuck,

Thanks a lot for this .. It worked..

I really appreciate your help.

Thanks
atul

Chuck Simpson <chuckls_at_cox-internet.com> wrote in message news:<pan.2004.11.28.21.12.39.32272_at_cox-internet.com>...
> On Sun, 28 Nov 2004 05:22:08 -0800, Atul wrote:
>
> > Hi,
> >
> > I am calling a SP from java. Now the problem is that the SP has 3
> > Parameters- :
> >
> > 2 IN String variables
> > 1 IN OUT variable which is a REF CURSOR
> >
> > Java Code
> >
> > String cmrQuery="{call cust_db.get_cntr_num_sp(?,?,?)}"; <<--IS SYNTAX
> > CORRECT
> > cstmt = con.prepareCall(cmrQuery);
> > cstmt.setQueryTimeout(QUERY_TIMEOUT) ; cstmt.setString(1,mcn_base);
> > cstmt.setString(2,mcn_sfx);
> > cstmt.setString(3,NULL); <<-- PROBLEM IS HERE
> > cstmt.registerOutParameter(1, OracleTypes.CURSOR); rs
> > =cstmt.executeQuery();
> >
> >
> > SO now my question is -:
> >
> > 1. Is the calling syntax correct of the SP with these 3 parameters ? 2.
> > While using setString() of IN OUT Ref Cursor what will be parameter?? (
> > i.e . will it be null or we can leave it empty? since we r not passing
> > anything in the ref cursor and if we dont set it then it will say a
> > variable not bound error.!!)
> >
> > Any help or clarification will be highly helpful.. I have been banging
> > my head on this one for some time now..
> >
> > Thanks in advance
> > atul
>
> Atul,
>
> The procedure call syntax is good. However, when used this way the 3rd
> parameter must be set using setNull(). Also you must register the "3rd"
> parameter as an OUT parameter instead of the "1st".After the call executes
> then read the out parameters. See modified example below:
>
> cstmt = con.prepareCall("{call cust_db.get_cntr_num_sp(?,?,?)}");
> cstmt.setQueryTimeout(QUERY_TIMEOUT);
> cstmt.registerOutParameter(3, OracleTypes.CURSOR);
> cstmt.setString(1,mcn_base);
> cstmt.setString(2,mcn_sfx);
> cstmt.setNull(3, OracleTypes.CURSOR);
> cstmt.execute();
> ResultSet rs = cstmt.getObject(3);
> ... read ResultSet contents ...
> cstmt.close();
>
> Chuck
Received on Tue Nov 30 2004 - 03:58:57 CST