I am using Oracle 11.2.0.3.0, Enterprise Edition
BIND_AWARE hint is not applied to cursors declared in PL/SQL functions. It DOES work in procedures.
Example below - the only difference is one is a procedure, and one is a function. When cursors are created, the one created from within the procedure gets V$SQL.IS_BIND_AWARE = 'Y' immediately, while the one created from within the function is 'N'.
How do I fix that?
CREATE OR REPLACE PROCEDURE PROC_BIND_TEST(v_region_id number) as
cursor
c1 is select /*+ bind_aware */ /*+ proc_bind_test */ count(1)
from sales where region_id = v_region_id
begin
for row in c1 loop
null;
end loop;
end;
/
CREATE OR REPLACE FUNCTION FUNCTION_BIND_TEST(v_region_id number)
return number
as
cursor
c1 is select /*+ bind_aware */ /*+ function_bind_test */ count(1)
from sales where region_id = v_region_id
begin
for row in c1 loop
null;
end loop;
return 0;
end;
/
exec proc_bind_test(12345);
select /*+ bind_aware */ function_bind_test(12345) from dual;
SELECT first_load_time , SQL_ID, CHILD_NUMBER, IS_BIND_AWARE, SQL_FULLTEXT
FROM V$SQL
where sql_text like '%bind_test%'
order by FIRST_LOAD_TIME desc;