drop table in execute immediate

[Baron]

I am trying to build a cursor on systable to find all old tables, and then exceute drop table statement on all those tables:

declare tnames insensitive cursor for select table_name from systable where table_name like '%_old';

and then within the loop I execute the drop statement:

execute immediate ('drop table ' + tname) ;

I rceive an error stating that the cursor is not open!! Is this generally possible? Is there any other alternative?

[chris_keating · Accepted solution]

DROP TABLE has an implicit commit which closes the cursor . You will need to OPEN the cursor WITH HOLD .

[Baron · Accepted solution]

One more question, does the same work for dropping triggers?

declare tnames insensitive cursor for select trigname from systriggers;

execute immediate ('drop trigger ' + tname) ;

I receive a syntax error!!

[VolkerBarth · Accepted solution]

Do you supply the trigger name or the table name to the DROP TRIGGER statement? The trigger name is mandatory.

Are the trigger names unique? Otherwise, you need to specify the table (and possibly owner) name, too.

[Baron · Accepted solution]

Sorry for the inconvinience, the problem was that one of the triggers was including ',' in its name (in trigname).

Problem was solved after adding "" to the table name:

execute immediate ('drop trigger "' + tname + '"')