SQL Window: Multiple executions can be dangerous

[Eric Prudhon]

Hello,

In 7.0.0.1050, multiple statements executions in SQL Window is a new feature that can not be disabled. The problem we have is that it does execute any statement in the SQL Window, including DML and DDL statements! It could lead to data/objects loss, undesired commits (through DDL) etc...

We are using the AutoSelect statement option, so generally, we have not this problem... except when the cursor is positionned at the end of the buffer! In this case, all statements are executed again even if the normal behaviour (from our point of view) would be to auto-select the last statement.

We considere this very dangerous as when you have used a SQL Window for years to "buffer" many various statements for execution "1 by 1" (for massive executions we have Command windows), it does not come to your mind after a version upgrade that all your statements risk to be launched at once!
There should be a specific command for this (CTRL-F8?), or at least the multiple execution should be an option (disabled by default).

Thanks
Eric Prudhon

 

[ScottMattes]

I agree. I have co-workers that have many multiple SQLs in one window, all of different types (Select, Update, Delete, etc). If'n they accidentally had all the SQLs execute at once there'd be an uproar!

So far I have put off 'pushing' them to v7 and I think that I'd better wait a while longer.

 

[Marco Kalter]

In 7.0.0.1050, multiple statements executions in SQL Window is a new feature that can not be disabled.

We'll consider an extra preference option for this. Right now you can enable the "AutoSelect Statement" option. This way only the statement at the cursor location will be selected and executed. In this case you need to explicitly select multiple statements if you want to execute them all.

 

[ScottMattes]

Marco,
the original post said that they are using AutoSelect and that there seems to be a bug (see the second paragraph in the original post).

 

[ScottMattes]

Marco,
the original post said that they are using AutoSelect and that there seems to be a bug (see the second paragraph in the original post).

 

[Marco Kalter]

Aha, I see. We'll fix that!

 

[Eric Prudhon]

Thanks for your feedback Scott and Marco.

So we are waiting at least for the fix of the AutoSelect before using 7.0.0.

 

[Marco Kalter]

I can confirm that this is fixed in 7.0.1, which will be available later this week (probably tomorrow).

 

[Eric Prudhon]

Thanks Marco, I confirm this is fixed in 7.0.1
In a future version, an option to disable multiple executions would be helpful when not using AutoSelect. (at least to disable DML/DDL statements)

 

[MarkP]

The new AutoSelect behaviour (I've just installed 7.0.1), selects too many statements for me.

I've many saved SQL Windows formatted

;statement
;
;statement
;
;statement
;
;statement
;

and it doesn't seem to like these at all.

Kind regards, Mark.

 

[Marco Kalter]

Can you provide a real example?

 

[MarkP]

Thank you, Marco,

There's an example in the post.

Kind regards, Mark.

 

[ScottMattes]

Marco,
I get 'bad' results from the following

;SELECT * FROM dual
;
;SELECT COUNT(*) FROM dual
;
;SELECT * FROM all_tab_cols
;

No matter where I place the cursor before pressing execute I get an error box saying "ora-00911 invalid character".

If I manually select what I want and press execute there is no error.

 

[rbrooker]

why program like this? whats the advantage?

i understand this :

statement;
statement;
statement;

or this

statement
;
statement
;
statement
;

but

;statement
;statement
;statement
;

is something i have not encountered before.
i dont understand.

 

[MarkP]

(From "Diary of a Legacy Programmer". ;-))

I'm a keen user of laptop-keyboard-shortcuts, and developed that idiom for use in SQL Windows in the days when PLSQLDev's handling of word breaks and line ends wasn't as it is now.

To overcome the need to work at the end of a line, I moved the end of the line to the start and relied on AutoSelect.

AutoSelect might be a very simple feature, but it's jolly handy.

Kind Regards, Mark.