Skip to main content

Hibernate 3 and Oracle 9i cursors : how does it work?

Here is a post I found on the hibernate forum. It explains why cursors opened are not released on session.close() :

OK. Here's the result of this thread:

For the environment, JDK1.4.2_01, Oracle 9i server, oracle 9i jdbc driver (ojdbc14.jar) and oci connection to the database (not thin driver);

There's no problem with hibernate's prep stmt cache nor with Oracle Jdbc driver with regard to cursors remaining open after Session.close().

Every Session.iterate() and Session.load() calls cause a prep stmt to be created on the server side. And after you close the session with Session.close(), hibernate explicitly calls appropriate driver methods to close the resultsets and prepared statements.

However, after you close the session, you won't see the cursors de-allocated (or closed) on the server side. While it seems to be a bug, it actually is not. The driver maintains a list of prep statements and open cursors in its memory and as soon as you re-use the same connection by opening a session on that very same connection, you'll see that the previous cursors and prep statements will go away.

To check this behaviour, open a session, run a few Sesson.load(), Session.iterate() methods and then call Session.close(). Run the below query and you'll see that the cursors are still there (in the statistic 'opened cursors current').

select n.*,s.* from v$sesstat s, v$statname n where s.statistic#=n.statistic# and sid=23 and like '%cursor%'

However, in the same program, after the above calls, if you
call SessionFactory.getSession() and grab the same jdbc connection from the pool;
as soon as you call Session.load() or Session.iterate(), you'll see that the number of open cursors will be 0 or decrease dramatically. This means that the jdbc driver is smart enough to track the status of the cursor and prep stmt and reports the closed cursors in the next round-trip to the database, hence reducing network round-trips, which is a good thing for performance.

Bulent Erdemir


Rahul said…
Thanks for the great information in your blog Selenium Training in Chennai

Popular posts from this blog

DCcduino usb drivers (CH340 / CH341 chipset)

I've just received my first arduino platform. It's a DCcduino board (a clone of Arduino Uno). As I had some difficulties to have it recognised by my MacBook, I decided to share its drivers. This card has a CH340 USB-to-serial chip. You can find drivers for this chip on the web site of the chinese manufacturer, here :

Or download it directly from my Google Drive. The archive contains drivers for Mac, Linux and Windows platforms.

I hope this will help somebody.

UPDATE for Mac users with Yosemite :

Please, follow this extra instructions :
Install the CH340 driverRun the command in Terminal: sudo nvram boot-args="kext-dev-mode=1"Reboot

The great alternative to JRebel

I'm an old user and addict of JRebel. I started to use it on open source projects and in professional contexts. From the beginning, I've been convinced that the licensing mode was wrong because of its lifetime. Asking for license renewal each year is boring. So, I decided to look for FREE alternative solutions and finally I recently found one.

This solution is efficient for maven projects developed with Eclipse. It is base on :

Hotswap Agent project : Source Lookup plugin for Eclipse :
I tested it with Java 7 & Java 8. I work on web applications that run on Tomcat. I developed wih Spring (IoC), sometimes Hibernate and Vaadin

Hotswap Agent installation consists on the deployment of a patch for your JVM. You just have to download the corresponding patch here : Then, download the hotswap-agent.jar from here :…

How to secure REST services exposed with Jersey (JAX-RS) using Spring Security

This is the challenge I had to perform. I have to open services to business partners and I want to secure them. I use a Java based application with Jersey API (JAX-RS).

The first thing to consider is : what kind of solution offers the best compromise between security and the effort I'll have to provide to maintain this solution?
After some discussions with developers and experts, the conclusion is : expose your services over https and use Basic authentication (Digest authentication and certificate based authentication are too complex for partners)

So, how to implement that? I delegate "https" to my Apache http servers. But I still need to handle authentication (and authorization of course). After long hours on Google, I understood that it is possible to manage security with Jersey by many many ways. Here is a short list :

Delegate simple authentication and authorization to your container (Tomcat) or to your frontend (Apache)Delegate authentication to your container or fro…