OracleCallableStatement with a Ref Cursor
Hello All,
I have an issue with OracleCallableStatement with a Ref Cursor.
I have pasted my code and lines of concern below. I appreicate your help in advance.
Oracle Stored Proc -
-- Hdr
TYPE Email_Cursor is REF CURSOR;
procedure PROC_EMAIL_RECEIVED
(
P_USERNAME INVARCHAR2,
cursor_out outEmail_Cursor
);
- Body
Open cursor_out For
SELECT
EMAIL_FROM, MAIL_SUBJECT, MAIL_BODY
FROM
MEMBER a, EMAIL_RECEIVED b
WHERE
Upper(a.USERNAME) = upper(P_USERNAME)
AND a.MEMBER_ID = b.MEMBER_ID
ORDER BY
b.EFF_TIMESTAMP;
Java Bean
public Result getAll() throws SQLException, NamingException {
try {
connectToDb();
boolean hadResults = true;
String strQuery = "{call PKG_QUERY_MEMBER.PROC_EMAIL_RECEIVED(?, ?)}";
CallableStatement cstmt = mConn.prepareCall(strQuery);
ResultSet cursorResultSet = null;
String userNameLoggedIn = "demo";
cstmt.setString(1, userNameLoggedIn );
cstmt.registerOutParameter(2, OracleTypes.CURSOR );
try {
hadResults = cstmt.execute();
System.out.println(" Select Email results : " + hadResults);
} catch (SQLException ex) {
System.out.println("Error with Select Email " + ex);
ex.printStackTrace();
}
cursorResultSet=(ResultSet)((OracleCallableStatement)cstmt).getCursor(2);
int resultNumber = cursorResultSet.getFetchSize();
System.out.println(" resultNumber : " + resultNumber);
SQLWarning resultError = cursorResultSet.getWarnings();
System.out.println(" resultError : " + resultError);
while(cursorResultSet.next()){
System.out.println(" EMAIL_FROM : " + cursorResultSet.getString(1));
System.out.println(" MAIL_SUBJECT : " + cursorResultSet.getString(2));
System.out.println(" MAIL_BODY : " + cursorResultSet.getString(3));
}
try{
returnResult = ResultSupport.toResult(cursorResultSet);
} catch (Exception ex) {
System.out.println("Error with Select Email " + ex);
ex.printStackTrace();
}
return returnResult;
} finally {
close();
}
}
My problem seems to be that the System out print lines work and display the correct data. When I convert to a
Result with << returnResult = ResultSupport.toResult(cursorResultSet); >>
the column headers are the only data loaded in my dataTable.
The data is null.
I would really appreciate your help...this one has got me going crazy....
Also..
The << int resultNumber = cursorResultSet.getFetchSize();>>
Always returns 10.....not the real result of the select.
Thanks
Phil
