execute() and prepared statements

[Rr Kumaran]

Hi All,

I have two questions in mind:

1. I know execute() is to return more than one result set which happens in case of executing store procedures that return more than one resultset. But how actually do we create oracle stored procedures that return more than one resultset ?

2. We know that a prepared statement is precompiled in database and so for any other jvm that invokes the same prepared statement created by first jvm, is the first jvm's created and precompiled prepared statement in database is resused by the consecutive jvm's or is it the jvm-context+database the precompiled prepared statement is used ?


Thanks & Regards,
Kumar.

[David O'Meara]

1. I know execute() is to return more than one result set which happens in case of executing store procedures that return more than one resultset. But how actually do we create oracle stored procedures that return more than one resultset ?
ps.clearParameters() wil clear the bound values, so that you can bind new variables to execute the PreparedStatement again.

2. We know that a prepared statement is precompiled in database and so for any other jvm that invokes the same prepared statement created by first jvm, is the first jvm's created and precompiled prepared statement in database is resused by the consecutive jvm's or is it the jvm-context+database the precompiled prepared statement is used ?
This is dependant on the Driver and Database. The correct behaviour is for the database to cache the compiled statement and reuse it for any requests (regardless of origin), but some databases don't support caching statements, and the Driver pretends to implement this support, and some Drivers don't hook into the database properly and therefore don't enable reusing precompiled statements.

Therefore the short answer is 'it depends', but I believe you don't have to worry about it if you're using Oracle and their supplied drivers.

Dave

[tommy k. lee]

When Oracle receives a SQL statement to execute, it first looks to see if the execution plan already exists in its cache. If the statement does exist in the cache, the previous execution plan is retrieved and used. This reduces the cost of recreating the execution plan, thus saving execution time. The cache can be viewed via the V$SQL table. Looking at the V$SQL table's SQL_TEXT column shows what statements are currently in cache.

One thing to note about how the cache works is that the SQL statements it places in the cache are case-sensitive. Therefore, the following two statements perform the same query, but are seen as two different queries:

SELECT * FROM V$SQL

SELECT * FROM v$sql

They are considered different because the first query references the V$SQL table in all uppercase letters, whereas the second uses all lowercase letters. To prove this, run the following query (note that two entries are returned):
SELECT * FROM V$SQL WHERE SQL_TEXT = 'select * from V$SQL'orSQL_TEXT = 'select * from v$sql'

This can flood the cache with the same query, thus making the cache less useful. To prevent this, always ensure that applications that issue the same query use the same letter case in the syntax.

[David O'Meara]

That's worth knowing.

I haven't had an issue with this since we tend to wrap database operations so that there is only a single version of the statement string, but I can see it being an issue.