Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Drop inside a cursor in Oracle
On Nov 24, 12:58 pm, Siv <siv.han..._at_gmail.com> wrote:
> I have a database with many user defined types. Sometimes I want to
> drop them all, without the tedious work of dropping in the correct
> order. So I figured a stored procedure with a cursor could do the job
> for me.
>
> I declared a cursor which selects type_name from user_types and tries
> to drop the type inside the cursor loop. The compiler complains about
> unexpected drop statement.
>
> My code:
> create or replace procedure drop_all_types
> is
> curr_type varchar2(100);
> cursor finn_typer is
> select type_name from user_types;
>
> begin
> open finn_typer;
> loop
> fetch finn_typer into curr_type;
> exit when finn_typer%notfound;
> drop type curr_type force; -- this is where my
> compiler complains
> end loop;
> close finn_typer;
> end;
>
> How do I fix this? I've done this before, many years ago... but have
> forgotten how.
> Anyone care to guide me, please?
You cannot perform native DDL statements from within PL/SQL. PL/SQL supports only DML statements. You need to use dynamic SQL via the execute immediate statement or via the dbms_sql package. See your PL/ SQL manual for details.
Remember that dropping object types will invalidate stored code that references the types.
HTH -- Mark D Powell -- Received on Sat Nov 24 2007 - 17:30:53 CST
![]() |
![]() |