Why don't you try this within SQL*Plus.
/* EXPLAIN PLAN query */
set echo off
set verify off
set pagesize 0
set feedback off
select lpad(' ',2*(level-1))||
operation||
' '||
options||
' '||
object_name||
' '||
decode(id, 0, 'Cost = '||position) "Query Plan"
from plan_table
start with id = 0
and statement_id = '&1'
connect by prior id = parent_id
and statement_id = '&1'
;
> -----Original Message-----
> From: GreMa_at_t-online.de (Matthias Gresz) [SMTP:GreMa_at_t-online.de]
> Posted At: Tuesday, December 08, 1998 5:21 AM
> Posted To: server
> Conversation: compilation error
> Subject: Re: compilation error
>
> kal121_at_yahoo.com schrieb:
> >
> > Why doesn't Oracle like the following SQL statement? Is there an
> easier way to
> > do this? I don't want to have to re-type this scipt everytime I need
> it. Is
> > there a way to "parameterize" a script if this doesn't work? Thanks!
> >
> > SQL> PROCEDURE get_plan(stmt_id VARCHAR2) IS
> 2
> 3 BEGIN 4
> 5 SELECT
> 6
> > LPAD(' ',2*Level)||Operation||' '||Options||' '||Object_Name
> Execution_Plan
> > 7 FROM plan_table
> 8 WHERE Statement_Id = 'stmt_id'
> 9 CONNECT BY PRIOR ID
> > = Parent_ID and Statement_ID = 'stmt_id'
> 10 START WITH ID=0;
> 11 12 END;
> > 13 / PROCEDURE get_plan(stmt_id VARCHAR2) IS * ERROR at line 1:
> ORA-00900:
> > invalid SQL statement
>
> Within a procedure you can only use the SELECT ... INTO. If you want
> your procedure to return values like in MS-SQL, take a look at REF
> CURSORS.
>
>
> HTH
> Matthias
> --
> Matthias.Gresz_at_Privat.Post.DE
>
> Always log on the bright side of life.
> http://www.stone-dead.asn.au/movies/life-of-brian/brian-31.htm
Received on Tue Dec 08 1998 - 07:18:39 CST
