Errors while using driving_site hints

I have a Oracle 9i setup with a IBM WebSphere application server. I use database pools to maintain connections to the database. I have two databases and I maintain a connection pool to one of them which in turn has a database link to the other database.

To tune queries for performance, we use the driving_site hint when running queries. However, if I use a PreparedStatement to run such a query, it runs fine the first time. The second time onwards it starts giving errors which are either of "fetch out-of-sequence" or "all variables not bound".

If I change my query to use a Statement object instead of PreparedStatement, it works fine. If I get a new connection to the database and not get a connection from the database pool, even that works fine. Also, if I drop the driving_site hint, there's no problem, although the performance drops.

So looks like it's got something to do with connections that are pooled and I use the driving_site hint in the queries on those connecitons. I tried closing the ResultSet, Statement and the Connection, but nothing works.

Has anyone seen this before ? Any suggestions for the problem ? Any help would be greatly appreciated.

Thanks,
Abhijit Received on Tue Feb 01 2005 - 14:01:38 CST