Toad World® Forums

Ability to execute SQL embedded within PL/SQL


#1

It has come up several times before to have a method for executing or otherwise
extracting SQL embedded within PL/SQL. The extracted SQL should convert all
references to PL/SQL identifiers into bind variables. Any INTO clause should be
stripped, etc. The SQL should be syntactically and logically correct to execute
on its own outside of the PL/SQL without error.

I have this done (mostly) internally and it’s just sort of lying there with no
home. How do we surface this? A right-click item to copy it to clipboard or
execute may be appropriate. What about keyboard shortcut for executing? One idea
would be to hijack CTRL+Enter|Shift+F9 so that it operates as it
currently does unless invoked within PL/SQL in which case it could execute the
embedded SQL at caret using the new method. This would mean that CTRL+Enter
could no longer be used to compile PL/SQL or execute anonymous blocks though
unless it was setup to fall back on current behavior if there is no SQL at
caret. Other suggestions?

Michael


#2

Extremely cool! It hadn’t even occurred to me you could do this. It would
be a noticeable time saver for me.

Options to surface? Well, a toolbar button (or a dropdown arrow option on the
green arrow button), of course. But also something on the keyboard. Some
connection to F9 (execute) would be good, so ShiftF9 is a fine candidate in my
opinion. But if there are reasons not to, I can get used to ‘most
anything.

Nate Schroeder

Enterprise Services - Data Management Team

Monsanto Company

800 N. Lindbergh Blvd. LC4D - Saint Louis, MO - 63167

314-694-2592


#3

I would love more than anything to use Shift+F9 for this. :slight_smile:

Shift+F9 has a strong following though so any changes there are usually met
with brutal opposition. I didn’t “pond” this item since it’s going to
make it into the produce anyway so I’m hoping to find some consensus on how to
surface it.

In its current form it will just replace all PL/SQL identifier references with
binds. It would be nice if it could locate PL/SQL constant references and just
use the constant value instead of making those binds, but that will be in
version 2 most likely.

Michael


#4

I vote for the shift-F9/ctrl-enter option too. I also wouldn’t mind
seeing what Jeff said about the options under the Refactor menu to copy
to clipboard or send to an Editor. Good stuff!!

Ed
[TeamT]

On 1/31/2011 5:08 PM, Michael Staszewski wrote:

I would love more than anything to use Shift+F9 for this. :slight_smile:

Shift+F9 has a strong following though so any changes there are usually
met with brutal opposition. I didn’t “pond” this item since it’s going
to make it into the produce anyway so I’m hoping to find some consensus
on how to surface it.

In its current form it will just replace all PL/SQL identifier
references with binds. It would be nice if it could locate PL/SQL
constant references and just use the constant value instead of making
those binds, but that will be in version 2 most likely.

Michael


#5

That’s already part of the request and will surely be there to extract and use elsewhere. The big question is how to surface an execute from PL/SQL feature. Shift+F9 seems to be the likely candidate so I’ll shoot for that and it can always be tweaked later if needed.

Michael