JDBC and a stored Cursor?

I can easily use JDBC to connect to an Oracle 8 database and execute stored functions. There are plenty of examples of that on the net. But what about stored cursors which are not encapsulated within a procedure or function?

I am already using the following cursor from PL/SQL:

    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
--
J. Kevin Ternes
jkternes at mindspring dot com Received on Wed Jan 26 2000 - 03:37:28 CST