ArrayIndexOutOfBounds in Oracle8i 8.1.7 JDBC Drivers and Ref Cursor

From: The Nomad <nobody_at_nowhere.com>
Date: Mon, 09 Apr 2001 21:38:51 GMT
Message-ID: <LZpA6.11633$nD5.1764831@typhoon.tampabay.rr.com>

Help!

I'm getting an ArrayIndexOutOfBounds using the Oracle8i JDBC thin drivers under the following circumstances:

1. Create a view linking two tables - the view will have a LONG RAW column. Here is the view definition:

CREATE OR REPLACE VIEW SSSTREEATTRS AS
  SELECT A.TREEENTRYID, A.ENTRYPARENT, B.ATTRID, B.ATTRCATEGORY, B.ATTRSTRINGVALUE, B.ATTRINTVALUE, B.ATTRFLOATVALUE, B.ATTRBINARYVALUE     FROM SSSBATREE A, SSSENTRYATTR B WHERE A.TREEENTRYID = B.TREEENTRYID (+)
/

Note: ATTRBINARYVALUE is the LONG RAW column.

2) Create a package as follows:

CREATE OR REPLACE PACKAGE SSSTYPES
AS
  TYPE SSSTREEATTRSCURSORTYPE IS REF CURSOR RETURN SSSTREEATTRS%ROWTYPE; END;
/

3) Create a stored function that returns rows from the view:

CREATE OR REPLACE FUNCTION SSSBATREE_GETROOTS RETURN SSSTYPES.SSSTREEATTRSCURSORTYPE AS
  c1 SSSTYPES.SSSTREEATTRSCURSORTYPE;
BEGIN
  OPEN c1 FOR SELECT * FROM SSSTREEATTRS WHERE ENTRYPARENT IS NULL;   RETURN c1;
END;
/

---

Here is a snippet of JDBC Code:

  public static void main( String args[] ) {     try {
      Connection con = getConnection(); // Gets an available connection to thin driver

      try {
        CallableStatement stmt = con.prepareCall("{? = call
SSSBATREE_GETROOTS}");
        try {
          stmt.registerOutParameter(1, oracle.jdbc.OracleTypes.CURSOR);
          stmt.execute();
          ResultSet rs = (ResultSet)stmt.getObject(1);
          try {
              while (rs.next()) {
                System.out.println("TreeId: " + rs.getInt(1));
              }
          } finally {
            rs.close();
          }
        } finally {
          stmt.close();
        }
      } finally {
        releaseConnection(con); // Returns connection to pool
      }

    } catch (Exception ex) {
   ex.printStackTrace();
    }
  }

Running this, I get:

TreeId: 1
java.lang.ArrayIndexOutOfBoundsException: 1  at oracle.jdbc.ttc7.NonPlsqlTTCColumn.unmarshal(NonPlsqlTTCColumn.java)  at
oracle.jdbc.ttc7.NonPlsqlTTCDataSet.unmarshalRow(NonPlsqlTTCDataSet.java)  at oracle.jdbc.ttc7.TTIrxd.unmarshal(TTIrxd.java)  at oracle.jdbc.ttc7.TTC7Protocol.fetch(TTC7Protocol.java)  at
oracle.jdbc.driver.OracleStatement.fetchNextColumns(OracleStatement.java)  at oracle.jdbc.driver.OracleInputStream.needBytes(OracleInputStream.java)  at oracle.jdbc.driver.OracleInputStream.close(OracleInputStream.java)  at
oracle.jdbc.driver.OracleResultSetImpl.prepare_for_new_row(OracleResultSetIm pl.java)
 at oracle.jdbc.driver.OracleResultSetImpl.next(OracleResultSetImpl.java)  at
com.mycompany.repository.AnOracleRepository.main(AnOracleRepository.java:56)

If I use JDBC dynamic SQL directly against the view, everything is fine:

  public static void main( String args[] ) {     try {

      Connection con = getConnection(); // Gets an available connection
      try {
        Statement stmt = con.createStatement();
        try {
          ResultSet rs = stmt.executeQuery("SELECT * FROM SSSTREEATTRS WHERE
ENTRYPARENT IS NULL");
          try {
              while (rs.next()) {
                System.out.println("TreeId: " + rs.getInt(1));
              }
          } finally {
            rs.close();
          }
        } finally {
          stmt.close();
        }
      } finally {
        releaseConnection(con); // Returns connection to pool
      }

    } catch (Exception ex) {
   System.out.println(ex.toString());
    }
  }

Running this, I get:

TreeId: 1
...
TreeId: 9

---

I've also tried doing a getXXX on each returned column, and I get the ArrayIndexOutOfBounds as soon as I try to get the LONG RAW field.

Help! This is killing me.

TIA Received on Mon Apr 09 2001 - 16:38:51 CDT