Oracle Function returns a Ref Cursor to a ResultSet object - Performance issues

[Juan Bustamante]

My program calls an Oracle PL/SQL function which returns a ref cursor to a ResultSet object. I know that the function runs to completion relatively quickly when i run it in an anonymous block but when the call is made through my Java application the resultset takes about 30 mins to return. Below is a snippet of my code:

currentConnection = ConnectionManager.getInstance().getConnection();
reportStmt = currentConnection.prepareCall("BEGIN ? := ENVISION.PKG_WMS_TO_AP.F_REPORT_CI_SC_HOLDING(?,?); END;"); reportStmt.registerOutParameter(1, OracleTypes.CURSOR);
reportStmt.setString(2, invoice.getCrewHQ());
reportStmt.setDate(3, invoice.getWrCompletionDate());
reportStmt.execute();
reportRS = ((OracleCallableStatement) reportStmt).getCursor(1);


Through a debugger I see that the second last statement (reportStmt.execute()) runs quickly. It is only when I step into the last statement that the debugger takes up to 30 minutes.
Any thoughts?

[Avi Abrami]

Juan,
Just a suggestion. Insteead of this:

reportRS = ((OracleCallableStatement) reportStmt).getCursor(1);

Try this:

reportRS = (ResultSet) reportStmt.getObject(1)

Good Luck,
Avi.

[steve souza]

By adding the following line of code and putting the jamon jar in your class path you can get performance metrics for all of the method calls on Connections/Statements/ResultSets and the other jdbc interface method calls. In addtion you will get performance stats on all SQL issued. For a full accounting of what jamon can do for monitoring sql and jdbc look at the site mentioned below. If all goes well tonite I will release jamon 2.2.

   
import com.jamonapi.proxy.*;
 
// The following single line of code will monitor all methods called on Connection/Statement/ResultSet and track performance of all SQL.
currentConnection = MonProxyFactory.monitor(ConnectionManager.getInstance().getConnection());
 

[Juan Bustamante]

By adding the monitoring(ie. adding the code mentioned above), I received a class cast Exception when I am trying to retrieve the result set.

reportRS = ((OracleCallableStatement) reportStmt).getCursor(1);