Re: stored procedure

From: Ben Ryan <benryan_at_my-deja.com>
Date: Wed, 15 Sep 1999 07:14:56 GMT
Message-ID: <7rnh1a$srb$1@nnrp1.deja.com>

In article <37DEB9F0.FE51EA5F_at_gsisrl.it>,   Alessandro Benati <webmaster_at_gsisrl.it> wrote:
> I need to make a stored procedures that deallocates the unused space
for
> all tables of a given schema. I've tried to do something like this,
but
> it doesn't work:
>
> create procedure proc as
> tab_name varchar2;
> beegin
> declare cursor c1 is
> select table_name from all_tables where owner='myowner';
> open c1;
> loop
> fetch c1 into tab_name;
> dbms_sql.execute('ALTER TABLE ' || table_name || ' DEALLOCATE
UNUSED;');
>
> end loop;
> close c1;
> end proc;
>
> Where is the error?
> Thank you to anyone will help me.
> Alessandro Benati
>
>

Errors include

1) beegin
2) how the cursor c1 is declared
3) Use of the dbms_sql package, you have to invoke the open and close

routines

Suggest you post actual error messages returned.

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Wed Sep 15 1999 - 02:14:56 CDT