Re: Syntax of setting Ref Cursor in procedure from JDBC

From: Chuck Simpson <chuckls_at_cox-internet.com>
Date: Sun, 28 Nov 2004 15:12:39 -0600
Message-Id: <pan.2004.11.28.21.12.39.32272@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 Sun Nov 28 2004 - 15:12:39 CST