Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Outline for sql statements in the stored procedure

601585Dec 26 2007 — edited Oct 15 2009

I've been exploring the capability of outline but found some problem.
The outline for SQL statements located "in" the stored procedure is created,
but not reused.

See following example.

select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi
PL/SQL Release 10.2.0.3.0 - Production
CORE    10.2.0.3.0      Production
TNS for Linux: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production

-- Enable "create outline"
alter session set create_stored_outlines = true;

-- Create stored prodecure 
create or replace procedure outline_proc
authid current_user
is
  v_cnt number;
begin
  select count(*) into v_cnt from pl_test p1;
end;
/

-- Enable "Use outline"
alter session use_stored_outlines = true;

-- Execute it several times
exec outline_proc;
exec outline_proc;
...

-- Check user_outline
select category, used, sql_text from user_outlines;
CATEGORY	USED	       SQL_TEXT
------------       -----------       --------------------------------------------------
DEFAULT	          UNUSED	SELECT COUNT(*) FROM PL_TEST P1

Is this an expected behavior? If so, is there any way to reuse outline under this situtation?

Thanks in advance.

Version info added...
Message was edited by:
Dion_Cho

Typo... :(
Message was edited by:
Dion_Cho

Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Nov 12 2009
Added on Dec 26 2007
2 comments
472 views