Re: JDBC and a stored Cursor?

A copy of this was sent to Kevin Ternes <jkternes_at_firehawk.com> (if that email address didn't require changing) On Wed, 26 Jan 2000 03:26:52 -0600, you wrote:

>
>I can easily use JDBC to connect to an Oracle 8 database and execute
>stored functions. But what about stored cursors which are not
>encapsulated within a procedure or function?
>I am already using the following cursor from PL/SQL:
>

You use REF cursors for this. See
http://osi.oracle.com/~tkyte/ResultSets/index.html for examples (in java as well)

> CREATE OR REPLACE PACKAGE CA_CURSORS IS
>
> TYPE a_rectype IS RECORD (
> pid_ref pp.pid_ref%TYPE,
> pname pp.pname%TYPE,
> ssn pp.pal_ssn%TYPE,
> tin_ref pp.tin_ref%TYPE
> );
>
> CURSOR cur_a(ref_in in aa.aid_ref%type) RETURN a_rectype;
>
> END;
> /
>
> CREATE OR REPLACE PACKAGE BODY CA_CURSORS IS
>
> cursor cur_a(ref_in in a.aid_ref%type) RETURN a_rectype
> is
> select pp.pid_ref, pname, ssn, tintypid_ref from pp
>
> .... (humungous cursor snipped out) ....
>
> END;
> /
>
>but how do I use it from a Java program? I have tried to use a callable
>statement analogous to the way I would call a stored function:
>
> String query = "{ (?, ?, ?, ?) = call CA_CURSORS.CUR_A(?)}";
>
> // Prepare a PL/SQL call
> CallableStatement cs = conn.prepareCall(query);
>
> // Define I/O
> cs.registerOutParameter (1, OracleTypes.INTEGER);
> cs.registerOutParameter (2, OracleTypes.VARCHAR);
> cs.registerOutParameter (3, OracleTypes.VARCHAR);
> cs.registerOutParameter (4, OracleTypes.INTEGER);
> cs.setInt(5,1000001);
>
> // Execute
> cs.execute();
> String ssn = (String)cs.getObject(3);
>
>It compiles okay of course but when it runs I get SQL Exceptions which
>complain about unexpected tokens, "(" or "," , in the query string.
>I've tried different syntaxes for the out parameters with no better
>results.
>
>I can't seem to find an example of this being done anywhere. Is it even
>possible?
>
>-kt

--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Wed Jan 26 2000 - 05:24:19 CST