Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Drop inside a cursor in Oracle

Re: Drop inside a cursor in Oracle

From: Mark D Powell <Mark.Powell_at_eds.com>
Date: Sat, 24 Nov 2007 15:30:53 -0800 (PST)
Message-ID: <fa1a4e66-c1ae-4aae-ab39-4a1dbe76afe9@p69g2000hsa.googlegroups.com>


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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US