Meaningless Drivel is fun!
The moose likes JDBC and Relational Databases and the fly likes Oracle JDBC and Cursor question Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "Oracle JDBC and Cursor question" Watch "Oracle JDBC and Cursor question" New topic

Oracle JDBC and Cursor question

Henrik Engert
Ranch Hand

Joined: Apr 26, 2005
Posts: 70
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?

[ December 04, 2006: Message edited by: Henrik Engert ]

Jeanne Boyarsky
author & internet detective

Joined: May 26, 2003
Posts: 33102

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.

[OCA 8 book] [Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Other Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, TOGAF part 1 and part 2
Henrik Engert
Ranch Hand

Joined: Apr 26, 2005
Posts: 70

I suspected it was risky since when switching to Oracle from 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.


[ December 05, 2006: Message edited by: Henrik Engert ]
[ December 05, 2006: Message edited by: Henrik Engert ]
I agree. Here's the link:
subject: Oracle JDBC and Cursor question
It's not a secret anymore!