JDBC - exception (ORA-17009: Closed statement) using cursor

lassetyr
lassetyr used Ask the Experts™
on
I have the following definitions in an oracle 8.1.7 database:

CREATE OR REPLACE TYPE "TELEAT"."ROAD_ID_TABLE" AS TABLE OF NUMBER;

CREATE OR REPLACE PROCEDURE "GET_ROWS"       (
  IdTable IN ID_TABLE,
  result OUT types.cursorType
)
AS

BEGIN

  OPEN result FOR
     SELECT
        ID,
        NAME,
        FEATTYP,
        FERRYTYPE,
        ROADCLASS,
        COUNTRY,
        ROUTENUM,
        METERS AS LENGTH,
        GEOMETRY,
        KPH,
        MINUTES
      FROM
        R_DATA
      WHERE
        ID IN (
          SELECT
            column_value
          FROM
            TABLE(
              CAST(IdTable AS ID_TABLE)
            )
          )
     ;
END;

The stored procedure is valid, and I have checked the cursor before the end of the procedure, and it contains the correct values.
However - I get an exception (ORA-17009: Closed statement)when I try to get the result in Java like this:

private Vector getRows(long[] ids) throws SQLException {
  Connection conn = getConnection();

  String sqlStr = "GET_ROWS";
  CallableStatement stmt = conn.prepareCall(sqlStr);
             
  oraqle.sql.ArrayDescriptor descriptor = ArrayDescriptor.createDescriptor("ID_TABLE", conn);
  oraqle.sql.ARRAY array = new ARRAY(descriptor, conn, ids);

  stmt.setArray(1, array);

  stmt.registerOutParameter(2,   oracle.jdbc.driver.OracleTypes.CURSOR);

  ResultSet rs = stmt.executeQuery();

  while(rs.next()) {
     
    Segment current = new Segment();
    current.setId(rs.getLong("ID")); //Exception is thrown here
    //do more here
    result.add(current);
  }
  return result;
}


Any ideas on what I am doing wrong?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
String sqlStr = "{call GET_ROWS(?, ?)}";

Author

Commented:

Yes - a glitch in converting my code to a more readable version...

But I solved my problem:
Replaced
   ResultSet rs = stmt.executeQuery();
with
   stmt.execute();
   ResultSet rs = (ResultSet) (stmt.getObject(2));

And it worked like a dream...

Commented:
to be more precise - you must use:

stmt.execute();
long result = stmt.getLong(2);

even if your approach is working - it is not correct
Learn SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

Commented:
because callable statements does not normally return result set

Author

Commented:
The result from my query is a cursor containing many rows where each has several values.
The complete code is like this:
stmt.execute();
ResultSet rs = (ResultSet) (stmt.getObject(2));
while(rs.next()) {
   
   Segment current = new Segment();
   current.setId(rs.getLong("ID"));
   current.setName(rs.getString("NAME"));
   current.setRouteName(rs.getString("ROUTENUM"));
   current.setRoadclass(rs.getInt("ROADCLASS"));
   current.setKph(rs.getInt("KPH"));
   current.setFeatureType(rs.getString("FEATTYP"));    
   current.setCountry(rs.getString("COUNTRY"));
   current.setFerrytype(rs.getInt("FERRYTYPE"));
   current.setMinutes(rs.getInt("MINUTES"));

   result.add(current);
}

Therefore - I must use a resultset...

Commented:

Dear expert(s),

A request has been made to delete this Q in CS:
http://www.experts-exchange.com/Community_Support/CleanUp/Q_20405062.html

Without a response in 72 hrs, a moderator will finalize this question by:

 - Saving this Q as a PAQ and refunding the points to the questionner

When you agree or disagree, please add a comment here.

Thank you.

modulo

Community Support Moderator
Experts Exchange

Commented:
no objection
Commented:
Saving this Q as a PAQ and refunding the points to the questionner as agreed.

modulo

Community Support Moderator
Experts Exchange

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial