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?

asked 07 Mar '19, 07:21

Baron's gravatar image

Baron
2.1k134146175
accept rate: 46%


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

permanent link

answered 07 Mar '19, 07:41

Chris%20Keating's gravatar image

Chris Keating
7.8k49128
accept rate: 32%

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

(08 Mar '19, 04:25) Baron
Replies hidden

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.

(08 Mar '19, 06:13) Volker Barth

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

(08 Mar '19, 08:38) Baron

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.

permanent link

answered 07 Mar '19, 07:39

Volker%20Barth's gravatar image

Volker Barth
40.1k361549819
accept rate: 34%

edited 07 Mar '19, 07:43

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

(07 Mar '19, 08:53) Breck Carter
Replies hidden

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

(07 Mar '19, 09:12) Volker Barth
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×12
×9

question asked: 07 Mar '19, 07:21

question was seen: 1,470 times

last updated: 08 Mar '19, 08:38