If you have a support contract with Oracle, I'd log a TAR on this-- it appears to be a legitimate problem. I believe there's a workaround, however.
The workaround:
Declare a stored procedure, say, get_foo that takes as an IN parameter whatever you're binding (in this case an integer) and returns a REF CURSOR that is the result of your select statement. Replace the text of your SQL statement with "{call get_foo( ? )}" and you should be set.
(potential workaround) I did a experiment along the lines of the one below with Oracle's ODBC driver and verified that it behaves properly (only 1 parse) when a prepared select statement is executed multiple times (I used the ODBCTest sample from the MDAC SDK (url below)). It may be that if you use the Microsoft OLE DB to ODBC provider with the Oracle ODBC driver that you problems go away, but I haven't tested that idea.
My code:
create table foo( col NUMBER );
-- Note that I'm just inserting back into foo rather than returning the results of the select
-- statement like you'd want to do.
create or replace procedure get_foo( value NUMBER )
AS
BEGIN
insert into foo( select * from foo where col = value );
END;
/
What I did:
- Using the "Microsoft OLE DB RowsetViewer", which is one of the sample apps in Microsoft's MDAC SDK <</code>
http://www.microsoft.com/data >, <code>I executed row OLE DB API calls with the Oracle OLE DB provider. When I prepared the statement "insert into foo values( ? )" and executed it a series of times with different parameters, tkprof shows that all is well:
insert into foo
values
( :1 )
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 2 0.00 0.00 1 1 6 2
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.00 0.00 1 1 6 2
Misses in library cache during parse: 0
Misses in library cache during execute: 1
Optimizer goal: CHOOSE
Parsing user id: 35
If I instead use a select statement, "select * from foo where col = ?", I get the erroneous behavior
select *
from
foo where col = :1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 13 0.00 0.00 0 0 0 0
Execute 6 0.00 0.00 0 0 0 0
Fetch 6 0.00 0.00 0 10 24 10
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 25 0.00 0.00 0 10 24 10
Misses in library cache during parse: 1
Parsing user id: 35
If I change to use a stored procedure that does the selecting, all is well again.
BEGIN get_foo(:1); END;
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 3 0.00 0.00 0 0 0 3
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 0 0 0 3
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 35
INSERT INTO FOO SELECT * FROM FOO WHERE COL = :b1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 3 0.00 0.00 0 4 21 56
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 0 4 21 56
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 35 (recursive depth: 1)
Justin