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 -> Drop inside a cursor in Oracle

Drop inside a cursor in Oracle

From: Siv <siv.hansen_at_gmail.com>
Date: Sat, 24 Nov 2007 09:58:59 -0800 (PST)
Message-ID: <db7c1bcc-d00d-4b5a-ba42-2c131597b3b6@y5g2000hsf.googlegroups.com>


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? Received on Sat Nov 24 2007 - 11:58:59 CST

Original text of this message

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