Re: problems compiling procedure

KL,

If you are using SQL*Plus, you should insert "CREATE OR REPLACE" before you start defining the procedure:

SQL> CREATE OR REPLACE PROCEDURE get_plan(stmt_id VARCHAR2) <ret> IS <ret>
BEGIN <ret>
SELECT LPAD(' ',2*Level)||Operation||' '||Options||' '||Object_Name Execution_Plan <ret>
FROM plan_table <ret>
WHERE Statement_Id = 'stmt_id' <ret>
CONNECT BY PRIOR ID = Parent_ID and Statement_ID = 'stmt_id' <ret> START WITH ID=0;<ret>
END;<ret>
/<ret>

<ret> signifies a carriage return.

CREATE OR REPLACE tells the database to either create a new procedure called get_plan or to replace an existing one. For me, it is normal practise to CREATE OR REPLACE everything, that way, if I get compile errors, I don't have to explicitly DROP the package/procedure/function before recompiling it. (i.e. you should use the whole string CREATE OR REPLACE, not either of the two words "CREATE" or "REPLACE".

Even still, this will not work. Any SELECT statement within a procedure or function should be selecting INTO something (i.e. a Pre-defined variable): e.g. assuming x_var is a pre-defined procedure variable: SELECT x INTO x_var FROM...... Even better would be to stick your select statment into an explicit CURSOR and use that to do the selecting for you. You can then cope with many issues like no data found, too many rows, etc.

For more info on Cursors, see the Oracle Documentation, or get a hold of "PL/SQL Programming" by Steve Feuerstein (check the spelling on the surname).

If you have any more probs, let me know on:

    c h a z z WHOISAT e a r t h l i n g STOP n e t

(By removing the spaces and replacing WHOISAT with @ and STOP with a dot.)

Hope this helps,

Chazz

kal121_at_yahoo.com wrote:

> Does anyone know why this doesn't work? And how can I get around this?
> Thanks, KL
>
> 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
>
> -----------== Posted via Deja News, The Discussion Network ==----------
> http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
Received on Wed Dec 09 1998 - 11:09:08 CST