SQL & PL/SQL

Hello,

Update: I can reproduce the issue in a much simpler setup then initially reported.

Basically it all boils down to opening a ref cursor in PL/SQL and passing it to a simple Java stored procedure, which fetches one row from the cursor. The first time the java procedure is called, it works as desired, however after the java procedure returns, the cursor state becomes unusable and further attempts to fetch result in ORA-01002.

-- drop table a;
create table a (x number);
insert into a(x) values (1);
insert into a(x) values (2);
insert into a(x) values (3);

-- java procedure which fetches one row from the ref cursor
create or replace procedure fetch_one_java(p_cursor in out sys_refcursor)
as language java
name 'FetchTest.fetch_one_java(java.sql.ResultSet[])';
/

create or replace and compile java source named "FetchTest" as
public class FetchTest {
    public static void fetch_one_java(java.sql.ResultSet[] p_cursors) {
        System.out.println("JAVA::fetch_one_java start");
        java.sql.ResultSet p_cursor = p_cursors[0];
        try {
            p_cursor.next();
            double l_a = p_cursor.getDouble(1);
            System.out.println("JAVA::fetch_one_java l_a = " + l_a);
        } catch (Exception e) {
            System.out.println("JAVA::fetch_one_java got exception");
            e.printStackTrace();
        }
    }
}
/



create or replace procedure test_fetch
  as
    v_cursor sys_refcursor;
  begin
    open v_cursor for 'select * from A';
    dbms_output.put_line('PL/SQL call fetch_one_java'); 
    fetch_one_java(v_cursor);
    dbms_output.put_line('PL/SQL call fetch_one_java again'); 
    fetch_one_java(v_cursor);
    close v_cursor;
  end;
/

set serveroutput on;

call dbms_java.set_output(5000);

begin test_fetch; end;
/

Output:

...

PL/SQL call fetch_one_java
JAVA::fetch_one_java start
JAVA::fetch_one_java l_a = 1.0
PL/SQL call fetch_one_java again
JAVA::fetch_one_java start
JAVA::fetch_one_java got exception
oracle.jdbc.driver.OracleSQLException: ORA-01002: fetch out of sequence
        at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java)
        at oracle.jdbc.driver.T2SConnection.check_error(T2SConnection.java)
        at oracle.jdbc.driver.T2SStatement.checkError(T2SStatement.java)
        at oracle.jdbc.driver.T2SStatement.fetch(T2SStatement.java)
        atoracle.jdbc.driver.OracleResultSetImpl.close_or_fetch_from_next(OracleResultSetImpl.java)
        at oracle.jdbc.driver.OracleResultSetImpl.next(Native Method)
        at FetchTest.fetch_one_java(FetchTest:6)

Any ideas?

Thanks!

Edited by: lkrylov on Mar 25, 2010 6:38 AM