| 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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
