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 + '"')

[VolkerBarth]

See that old question on cursor loops and statements within the loop that do an automatic commit (like DROP TABLE does).

Basically, you have to make sure the cursor is hold open, for details see Bruce's answer.

---

To add: You could also adapt the "close_on_endtrans" option but I would not recommend that.

[Breck_Carter]

> the "close_on_endtrans" option but I would not recommend that.

Why not? The FOR loop works just like close_on_endtrans = off, and FOR is often an excellent alternative to DECLARE OPEN FETCH.

In fact, I cannot recall a single time when implicitly closing a cursor on COMMIT or ROLLBACK was the desired behavior.

[VolkerBarth]

Well, because the option will affect all transactions (unless set temporarily or only connection-wise) and all cursors, and I guess most cursors are used "behind the curtain" in database applications, and I would be hesitant to possibly modify their behaviour.

Therefore, I strongly suggest to use a local cursor WITH HOLD or - even better - the great FOR loop. (I almost always use cursor loops with FOR, I only do not use them when the implicit WITH HOLD is not desired:...)