SQLJ-Generated Code Loaded into 10.2.0.4 DB Fails with 'Closed Statement' when Being Called. (Doc ID 758194.1)

Last updated on JULY 06, 2010

Applies to:

SQLJ - Version: 10.2.0.1 to 10.2.0.3 - Release: 10.2 to 10.2

Symptoms


When loading .class files generated by 10.2.x.x SQLJ into a 10.2.0.2 or 10.2.0.3 database and creating Java Stored Procedures to call these class files, calls to these Java Stored Procedures from an anonymous PL/SQL block are successful. However, when performing the same operation, but loading the .class files to a 10.2.0.4 database, the call to the Java Stored Procedure fails from an anonymous PL/SQL block fails with the following error:

ORA-29532: Java call terminated by uncaught Java exception: java.sql.SQLException: Closed
Statement: next


These .class files were generated using 10.2.x.x SQLJ (through version 10.2.0.4) with JDK 1.4.2. Either version of SQLJ worked against a 10.2.0.2 or 10.2.0.3 database but failed against 10.2.0.4. The same error is seen when using JDK 1.5 and loading the .class files to an 11.1.0.6 database.


The error can be seen by running following testcase:

  1. Create and populate test table from SQL*Plus:
    CREATE TABLE test_tab (data VARCHAR2(20));
    INSERT INTO test_tab(data) VALUES ('test1');
    INSERT INTO test_tab(data) VALUES ('test2');
    commit;

  2. Run SQLJ against the following 2 SQLJ files:

    <TestSQLJ.sqlj>
    package Test;

    import java.sql.Connection;
    import java.sql.ResultSet;
    import java.sql.SQLException;

    import oracle.jdbc.OracleConnection;
    import sqlj.runtime.ref.DefaultContext;

    public class TestSQLJ {
       public static ResultSet getFunc() throws SQLException {
          #sql iterator TItr(String);

          TItr tItr = null;

          ResultSet rs = null;
          DefaultContext dctx = DefaultContext.getDefaultContext();
          Connection conn = dctx.getConnection();
          ((OracleConnection) conn).setCreateStatementAsRefCursor(true);

          #sql [dctx] tItr = {
             SELECT DATA
             FROM TEST_TAB
          };
          rs = tItr.getResultSet();
          return rs;
       }
    }

    </TestSQLJ.sqlj>

    <TestSQLJ3.sqlj>

    package Test;

    import java.sql.Connection;
    import java.sql.ResultSet;
    import java.sql.SQLException;

    import oracle.jdbc.OracleConnection;
    import sqlj.runtime.ref.DefaultContext;

    public class TestSQLJ3 {
       public static ResultSet getProc() throws SQLException {
          #sql iterator DECycleItr(String);

          DECycleItr decItr = null;
          ResultSet rs = null;
          String data = "";

          DefaultContext dctx = DefaultContext.getDefaultContext();
          Connection conn = dctx.getConnection();
          ((OracleConnection) conn).setCreateStatementAsRefCursor(true);

          #sql rs = {
             VALUES(TEST2.GETFUNC())
          };
          if (rs.next())
          data = rs.getString("DATA");

          #sql [dctx] decItr = {
             SELECT :data FROM DUAL
          };
          return decItr.getResultSet();
       }
    }

    </TestSQLJ3.sqlj>

  3. Upload the .class files generated from SQLJ in Step 3 using LOADJAVA:

     

    C:\SQLJTest>loadjava -f -r -v -u scott/tiger@mySID TestSQLJ.class TestSQLJ3.class TestSQLJ$1TItr.class TestSQLJ3$1DECycleItr.class


  4. Run the following 2 SQL Scripts:

    <TEST.SQL>

     

    CREATE OR REPLACE PACKAGE test AS

      TYPE refcur IS REF CURSOR;

      FUNCTION getProc RETURN REFCUR;

    END test;
    /

    CREATE OR REPLACE PACKAGE BODY test AS

      FUNCTION getProc RETURN REFCUR
      IS
      LANGUAGE JAVA
        NAME 'Test.TestSQLJ3.getProc() return java.sql.ResultSet';

    END TEST;
    /

    </TEST.SQL>

    <TEST2.SQL>

    CREATE OR REPLACE PACKAGE test2 AS

      TYPE refcur IS REF CURSOR;

      FUNCTION getFunc RETURN REFCUR;

    END test2;
    /

    CREATE OR REPLACE PACKAGE BODY test2 AS

      FUNCTION getFunc RETURN REFCUR
      IS
      LANGUAGE JAVA
      NAME 'Test.TestSQLJ.getFunc() return java.sql.ResultSet';

    END TEST2;
    /

    </TEST2.SQL>

  5. Invoke the Java Stored Procedure from an anonymous PL/SQL block:

     

    DECLARE
      RetVal TEST.REFCUR;
    BEGIN
      RetVal := TEST.GETPROC;
      COMMIT;
    END;
    /



Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms