Re: "Ref cursor is invalid" error on JDBC getObject(): Help?!?

From: C. Ferguson <c_ferguson_at_rationalconcepts.com>
Date: Fri, 25 Jan 2002 20:17:00 GMT
Message-ID: <3C51BDD9.316FB945@rationalconcepts.com>

Hi,

   try:

      statement.registerOutParameter(1, OracleTypes.CURSOR);

     Then after the execute(),
      ResultSet answer = (ResultSet)statement.getObject(1);
      while (answer.next()) {
             int id = answer.getInt(1);
             System.out.println("active workflow queue id is " + id);
      }

    Your function is returning a cursor (result set) to the java procedure. It is up to you to traverse through that result set and pull out your answers.

regards,
Cindy

"Zacharias J. Beckman" wrote:

> I've got a short stored function that returns a ref cursor. This seems
> to work fine in PL/SQL:
>
> CREATE or replace package workflow_query
> is
> type entity_cursor is ref cursor return active_workflow_queue%ROWTYPE;
> function find_entities_in_workflow(v_entity_name VARCHAR, v_user_id NUMBER) return entity_cursor;
> END workflow_query;
> /
>
> CREATE or replace package body workflow_query
> as
> function find_entities_in_workflow (
> v_entity_name VARCHAR,
> v_user_id NUMBER
> ) return entity_cursor
> IS
> v_entities entity_cursor;
> BEGIN
> OPEN v_entities FOR select id from active_workflow_queue;
> return v_entities;
> END;
>
> END workflow_query;
> /
>
> However, when I try to use the function in JDBC, I'm getting peculiar
> error: "Ref cursor is invalid." The error occurs as soon as I try to use
> getObject() to get the cursor. The JDBC is pretty simple:
>
> statement = connection.prepareCall("{? = call workflow_query.find_entities_in_workflow(?,?)}");
> statement.registerOutParameter(1, Types.OTHER);
> statement.setString(2, query.getEntityName());
> statement.setInt(3, query.getAuthorID().intValue());
> statement.execute();
> println("getObject(1) returns: " + statement.getObject(1));
>
> It blows up on the last line. If anyone has a clue what's going on (or
> why the cursor is invalid) I'd really appreciate hearing your thoughts.
> Thanks!
> ---
> guru_at_creativesun.com
> Z. J. Beckman
Received on Fri Jan 25 2002 - 14:17:00 CST