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?

On Nov 24, 12:58 pm, Siv <siv.han... (AT) gmail (DOT) com> wrote:
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 --