Re: stored procedure

Oracle8i's execute_immediate procedure works this way. For prior versions, you'll need to open a cursor and parse the statement. Or create dbms_my_sql package and put your execute_immediate procedure there.

Check syntax errors in your procedure, also note that table_names are all uppercase, so your statement will return no rows if run as written.

As for the results, take into account that deallocate unused frees only the space above HWM.

Regards,
Karen Abgarian.

Alessandro Benati 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
Received on Wed Sep 15 1999 - 07:23:07 CDT