Re: compilation error

From: Matthias Gresz <GreMa_at_t-online.de>
Date: 8 Dec 1998 10:20:43 GMT
Message-ID: <74iuhr$6l6$3@news00.btx.dtag.de>

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 - 04:20:43 CST