Question Re: drop table in execute immediate in Technology Q&A

drop table in execute immediate

Baron — Thu, 07 Mar 2019 07:21:40 GMT

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?

Re: drop table in execute immediate

VolkerBarth — Thu, 07 Mar 2019 07:39:35 GMT

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.

Re: drop table in execute immediate

chris_keating — Thu, 07 Mar 2019 07:41:55 GMT

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

Re: drop table in execute immediate

Breck_Carter — Thu, 07 Mar 2019 08:53:59 GMT

> 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.

Re: drop table in execute immediate

VolkerBarth — Thu, 07 Mar 2019 09:12:54 GMT

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:...)

Re: drop table in execute immediate

Baron — Fri, 08 Mar 2019 04:25:02 GMT

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!!

Re: drop table in execute immediate

VolkerBarth — Fri, 08 Mar 2019 06:13:48 GMT

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.

Re: drop table in execute immediate

Baron — Fri, 08 Mar 2019 08:38:13 GMT

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