NEED HELP! Java Stored Procedure and database links cause errors (Oracle 9i)... how to fix?

Hello All,
  I have an issue from last week and I have looked everywhere to no avail. I have put in a TAR to Oracle but am not getting a response. In my experience, I have noted that little or no response on TARs is bad news. Anyways, here we go...

We switched from Oracle 8i to 9i and one of the neat new java features is that you can Resturn a result set directly from a java stored procedure without any of the workarounds that were needed in 8i.

We have the following setup... two databases both Oracle 9i. A java class with many static methods that return different types (strings, integeres and resultsets).

(this code is not perfect just threw it together on the fly....)

A small version of the class is posted here...
/* START JAVA */

import java.io.*;
import java.sql.*;
import oracle.jdbc.*;

public class TestMe {     

    private static Connection conn = null;      

    private static void getConnection() {

        if (conn == null) {
            try {
                OracleDriver ora = new OracleDriver();
                conn = ora.defaultConnection();
                conn.setAutoCommit(false);
            }
            catch (SQLException e) {
                conn = null;
            }
        }

    public static ResultSet getUserObjects() {

        ResultSet rset = null;
        Statement stmt = null;
        
        getConnection();
        
        try {
            String sql = null;
            ((OracleConnection)conn).setCreateStatementAsRefCursor(true);
            
            stmt = conn.createStatement();
            
            sql = "select " +
            " object_name " +
            " from " +
            " user_objects ";
            
            rset = stmt.executeQuery(sql);
            
        }
        catch (SQLException sqle) {
            e.printStackTrace();
            rset = null;
        }
        
        return (rset);
        

    } // end func
} // end class
/* END JAVA */
Here is the pl/sql package and wrapper to call this java stored procedure

/* START PL/SQL */

create or replace package my_util as
  type refcur_t is ref cursor;

  FUNCTION get_user_objects return refcur_t;      

end my_util;
/

create or replace package body my_util as

FUNCTION get_user_objects return refcur_t AS language java
name 'TestMe.getUserObjects() return java.sql.ResultSet';

end my_util;
/
/* END PL/SQL */
Now this all works fine if a databae link is not involved... for example.
You can execute the code as following in sql/plus and it works fine.

var z refcursor
exec :z := my_ticket.get_user_objects();

print z

and you get the result set... however, lets say the package is actually only accessible over a database link so you would execute it like this

var z refcursor
exec :z := my_ticket.get_user_objects_at_MYLINK();

java.sql.SQLException: ORA-06544: PL/SQL: internal error, arguments: [78404],
[], [], []
, [], [], [], []
ORA-06553: PLS-801: internal error [78404] ORA-06512: at line 1

I have found absolutely no helpful info on how to fix this. I am assuming this has to do with the database link since it works fine if the package is local. However, why does it only blow up if I'm returning a resultset. Every other type seems to work just fine. So the problem has been narrowed down even further... just resultsets. Can anyone help me out? Any help would be GREATLY appreciated.

Regards,

John Received on Tue May 07 2002 - 17:22:25 CEST