Home » Fusion Middleware & Colab Suite » Weblogic & Application Server » PL/SQL embeded Java stored Procedure returning a result set
PL/SQL embeded Java stored Procedure returning a result set [message #77096] Sun, 29 August 2004 18:03
Jacky
Messages: 3
Registered: January 2002
Junior Member
Hi, every one....

I would like to ask:

            CallableStatement cstm = conn.prepareCall(
                    "{ ? = call StoredProcedure.getUserName(?, ?)}");
            cstm.setInt(2, 1);
            cstm.setInt(3, 10);
            cstm.registerOutParameter(1, OracleTypes.CURSOR);

My getUserName is PL/SQL embeded Java stored procedure which return the JDBC return set as a refcursor...

How can I register the out paramenter in my program? I got the following error:

java.sql.SQLException: ORA-00600: internal error code, arguments: [[15419]], [[severe error during PL/SQL execution]], [[]], [[]], [[]], [[]], [[]], [[]]
ORA-06544: PL/SQL: internal error, arguments: [[pgm.c:pgmbiad2()]], [[102]], [[]], [[]], [[]], [[]], [[]], [[]]
ORA-06553: PLS-801: internal error [[0]]

And my PL/SQL embeded Java stored procedure is:

    public static ResultSet getUserName(int lowerUserID, int upperUserID) {
        Connection conn = null;
        PreparedStatement stmt = null;

        try {
            conn = new oracle.jdbc.driver.OracleDriver().defaultConnection();
            ((OracleConnection)conn).setCreateStatementAsRefCursor(true);
            stmt = conn.prepareStatement(
                    "select UR.LoginName from UserRecord UR where UR.UserID >= ? and UR.UserID <= ?");
            stmt.setInt(1, lowerUserID);
            stmt.setInt(2, upperUserID);

            ResultSet rs = stmt.executeQuery();

            return rs;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException Ingore) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException Ingore) {
                }
            }
        }

        return null;
    }
}

And...

PACKAGE "STOREDPROCEDURE" AS
type refcursor is ref cursor;

FUNCTION "GETUSERNAME" ("lowerUserID" in number, "upperUserID" in number) RETURN refcursor
AS language JAVA name 'ReturnRS.getUserName(int, int) return java.sql.ResultSet';

END;

Looking forward to your reply, Thanks
Previous Topic: Splash screen
Next Topic: outstanding session
Goto Forum:
  


Current Time: Thu Mar 28 05:10:14 CDT 2024