You need to take extreme care when using cursor sharing and dynamic SQL. You might think that opening a dynamic ref cursor would be treated the same as that same SQL using 'execute immediate'...but check the example below
SQL> drop table t;
Table dropped.
SQL>create table T (x number primary key,y date,z varchar2(10));
Table created.
SQL> insert into t
2 select rownum, sysdate+rownum,rownum
3 from all_objects
4 where rownum <= 5000; 5000 rows created. SQL> commit;
Commit complete.
SQL> alter system flush shared_pool;
System altered.
SQL> alter session set cursor_sharing = exact;
Session altered.
SQL>
SQL> DECLARE
2 rc SYS_REFCURSOR;
3 BEGIN
4 for i in 1 .. 5000 loop
5 open rc for 'SELECT /*+XXX*/ * from T where x = '||i;
6 end loop;
7 END;
8 /
PL/SQL procedure successfully completed.
SQL> select count(sql_text) from v$sql
2 where sql_text like '%XXX%';
COUNT(SQL_TEXT)
---------------
5002 <== as expected, no sharing yet
SQL> alter system flush shared_pool;
System altered.
SQL> alter session set cursor_sharing = force;
Session altered.
SQL> select count(sql_text) from v$sql
2 where sql_text like '%XXX%';
COUNT(SQL_TEXT)
---------------
1
SQL> DECLARE
2 rc SYS_REFCURSOR;
3 BEGIN
4 for i in 1 .. 5000 loop
5 open rc for 'SELECT /*+XXX*/ * from T where x = '||i;
6 end loop;
7 END;
8 /
PL/SQL procedure successfully completed.
SQL> select count(sql_text) from v$sql
2 where sql_text like '%XXX%';
COUNT(SQL_TEXT)
---------------
5002 <== HEY! NO SHARING!
SQL> alter system flush shared_pool;
System altered.
SQL> alter session set cursor_sharing = force;
Session altered.
SQL> select count(sql_text) from v$sql
2 where sql_text like '%XXX%';
COUNT(SQL_TEXT)
---------------
1
SQL> DECLARE
2 r number;
3 BEGIN
4 for i in 1 .. 5000 loop
5 execute immediate 'SELECT /*+XXX*/1 from T where x
='||i into r;
6 end loop;
7 END;
8 /
PL/SQL procedure successfully completed.
SQL> select count(sql_text) from v$sql
2 where sql_text like '%XXX%';
COUNT(SQL_TEXT)
---------------
3 <== This time we got sharing!
SQL>
