• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Oracle JDBC and Cursor question

 
Henrik Engert
Ranch Hand
Posts: 70
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
We have a database class that handles building a callable statement (OracleCallableStatement). We register in and out parameters. We fetch the outparameter Cursor like this:



This cursor is then added to a List like this:



We then close the OracleCallableStatement before we access the Cursor. This works fine, but is this the correct way of doing things? Will there be memory complications since we do not access the cursor until after the CallableStatements has been closed?

I have to add that it works fine for our application that is a regular Java application, but it does not work if the code is run in a Servlet.

If we do not close the OracleCallableStatement like we do now, then I would have to call OracleCallableStatement.close every time we use the database class to access the database. That would be a pain to do, but now we call close() right away to generalize things.

Any suggestions?

Thanks.
[ December 04, 2006: Message edited by: Henrik Engert ]
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 34870
369
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Henrik,
It's risky. Calling close on a Statement is allowed to implicitly close the ResultSet. Then the cursor is pointint to nothing.

Who creates the cStmt? Usually the code that creates an object is the one the closes it.
 
Henrik Engert
Ranch Hand
Posts: 70
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,

I suspected it was risky since when switching to Oracle 10.0.2.2 from 10.0.2.1 we encountered a problem with closed ResultSets. Before we could call close on the statement and use it (the statement) afterwards for some odd reason, but it seems Oracle has fixed this.

We have a single class that builds these statements and executes them, then we have a finally block that closes the statement. After this we access the resultset etc. So it is general solution that's not good at all.

This forces me to write a seperate method that closes the statement which needs to be called from wherever I use the class that build/executes the statemements. The only problem with that will be whenever we only fetch small amounts from the resultset at a time.

Anyway, I just wanted to confirm my thoughts on the problem.

Thanks.

[ December 05, 2006: Message edited by: Henrik Engert ]
[ December 05, 2006: Message edited by: Henrik Engert ]
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic