File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

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

 
Juan Bustamante
Greenhorn
Posts: 2
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1141
1
Java Oracle
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Juan,
Just a suggestion. Insteead of this:

Try this:

Good Luck,
Avi.
 
steve souza
Ranch Hand
Posts: 862
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.

 
Juan Bustamante
Greenhorn
Posts: 2
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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);
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic