Store outline not used in PL/SQL

From: <matta576_at_gmail.com>
Date: 5 Apr 2007 14:21:12 -0700
Message-ID: <1175808072.599879.194150@o5g2000hsb.googlegroups.com>

Is it normal that a stored outline would not be used by SQL that is contained in a PL/SQL block? I have a query that is a cursor in a PL/ SQL package. I would like to have this query use an outline, but I can't seem to make it do so. This is 10.2.0.2 on Linux x86_64. I put together this simple example with an anonymous block that demonstrates the behavior...

tmx_at_rnetdev> create outline emp on
2 select employee_id from employee where employee_id = :uname;

Outline created.

tmx_at_rnetdev>
tmx_at_rnetdev> select name, sql_text, used from user_outlines where name
= 'EMP';
NAME
SQL_TEXT
USED

EMP                            select employee_id from employee where
employee_id = :uname                      UNUSED

tmx_at_rnetdev>
tmx_at_rnetdev> variable uname varchar2(10)

tmx_at_rnetdev>
tmx_at_rnetdev> exec :uname := 'ADMIN';

PL/SQL procedure successfully completed.

tmx_at_rnetdev>
tmx_at_rnetdev> alter session set use_stored_outlines = true;

Session altered.

tmx_at_rnetdev>
tmx_at_rnetdev> set serveroutput on
tmx_at_rnetdev> begin

  2 for i in (select employee_id from employee where employee_id = :uname) loop
  3 dbms_output.put_line(i.employee_id);   4 end loop;
  5 end;
  6 /
ADMIN PL/SQL procedure successfully completed.

tmx_at_rnetdev>
tmx_at_rnetdev>
tmx_at_rnetdev> select name, sql_text, used from user_outlines where name

= 'EMP';
NAME
SQL_TEXT
USED

EMP                            select employee_id from employee where
employee_id = :uname                      UNUSED

tmx_at_rnetdev>
tmx_at_rnetdev> select employee_id from employee where employee_id = :uname;

EMPLOYEE_ID

ADMIN tmx_at_rnetdev>
tmx_at_rnetdev> select name, sql_text, used from user_outlines where name
= 'EMP';
NAME
SQL_TEXT
USED

EMP                            select employee_id from employee where
employee_id = :uname                      USED

Thanks,
Matt Received on Thu Apr 05 2007 - 16:21:12 CDT