parse procedure

[elaine3839]

When I tried to create a procedure, I got warning message that 'Procedure created with parsing errors'. So how should I debug my code? Any tools available?

Thanks!

[sambavan]

Could you post the procedure and your database version?

Since this would be better answered in the development forum, I'm moving it to the development forum from the administration forum...


Sam

[elaine3839]

My database version is Oracle 8.1.7 under Win2K.

The procedure I was trying to create should be able to change the tablespace for all indexes that an user has.

CREATE or REPLACE procedure chg_ind_tbs as
cursor c1 is select index_name from user_indexes;
BEGIN
FOR result IN C1 LOOP
ALTER INDEX result rebuild tablespace tbs_name;
END LOOP;
END;

[dmitriy]

Hi,

Replace string

ALTER INDEX result rebuild tablespace tbs_name;

in your code with

execute immediate 'ALTER INDEX '||result.index_name||' rebuild tablespace tbs_name';

(If "tbs_name" in your code is a tablesapace name, not a parameter.)


Hope this helps,

[elaine3839]

Thanks Dmitriy! It's now working...

[elaine3839]

Shall I execute this procedure using

execute chg_ind_tbs ?

I tried to do that, but it seems that it was not working -- hanging there w/o doing anything.

Any idea?