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

From: Zacharias J. Beckman <guru_at_creativesun.com>
Date: Fri, 25 Jan 2002 11:39:05 -0800
Message-ID: <20020125.113848.243268139.639@creativesun.com>

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 - 13:39:05 CST