CODEWITHSUNDEEP
oracle-databasestored-procedurescoldfusionplsqlcursor
Joe C
Joe C

Reputation: 3546

closing a ref cursor from coldfusion

I'm writing stored procedures and functions in Oracle which return ref cursors to ColdFusion and everything works fine. But everything I've read says that I need to explicitly close a cursor or ref cursor when I'm done with it, else I risk running out of handles later.

But if I close the cursor at the end of the function/stored proc, the data is not returned to ColdFusion.

So how do I close the ref cursor once I'm done with it?

Upvotes: 1

Views: 718

Answers (2)

J.T.
J.T.

Reputation: 2616

Whenever the database connection is closed, the cursor will also be closed. In the CF admin, you have a limit to the number of database connections. If you use cfqueryparam for every value passed to Oracle, you will allow reuse of the same connection for the same query (unless a timeout has been reached). You have to make sure the cursor pool limit in Oracle and the connection limit in CF admin are in agreement. It sounds like this app has never had a problem, so that is probably the case.

Upvotes: 2

Justin Cave
Justin Cave

Reputation: 231751

Your ColdFusion application will be responsible for closing the cursor that is returned to it.

I'm not a ColdFusion expert so I'm not completely sure about the syntax you'd need. But client languages that interact with databases invariably have some sort of object or structure to represent a result set that you fetch from. When you're done fetching the data, there will be some sort of close method that closes the result set. That will also close the REF CURSOR in the database. Make certain that your ColdFusion application always calls the close method even if there is an exception while you're fetching the data-- the most common cause of cursor leaks is that the client application doesn't close cursors in the exception handler.

Upvotes: 0

Related Questions