This week's giveaway is in the Android forum.
We're giving away four copies of Android Security Essentials Live Lessons and have Godfrey Nolan on-line!
See this thread for details.
The moose likes JDBC and the fly likes Oracle Function returns a Ref Cursor to a ResultSet object - Performance issues Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Android Security Essentials Live Lessons this week in the Android forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Oracle Function returns a Ref Cursor to a ResultSet object - Performance issues" Watch "Oracle Function returns a Ref Cursor to a ResultSet object - Performance issues" New topic
Author

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

Juan Bustamante
Greenhorn

Joined: Aug 14, 2006
Posts: 2
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
Ranch Hand

Joined: Oct 11, 2000
Posts: 1121

Juan,
Just a suggestion. Insteead of this:

Try this:

Good Luck,
Avi.
steve souza
Ranch Hand

Joined: Jun 26, 2002
Posts: 860
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.



http://www.jamonapi.com/ - a fast, free open source performance tuning api.
JavaRanch Performance FAQ
Juan Bustamante
Greenhorn

Joined: Aug 14, 2006
Posts: 2
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);
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Oracle Function returns a Ref Cursor to a ResultSet object - Performance issues
 
Similar Threads
JDBC & oracle function
Closing cursor in a stored procedure when invoked from a callable statement
Procedure returning Ref Cursor.
Calling Remote DB Stored Procedure which returns Ref Cursor
Reading nested oracle cursors getCursor(col) throws ORA-01002 "Fetch out of sequence"