Feature Request: Test a SQL

[ScottMattes]

When I select a query in a proc/func/package and right click and select Test, I would like the resulting query window to be a SQL Window (i.e. so that I can select part of the displayed query and hit F8 and just the selected part executes).

I sat there for about 10 minutes trying to figure out how I was getting the exact same results when I selected parts of the UNIONed query and pressed F8.

 

[Bo Pedersen]

It's in situations like this, that I'm really glad for the 'Change Window to' functionality.

Bo Pedersen

 

[Marco Kalter]

The Test function converts PL/SQL variables (e.g. from the where clause) to bind variables. The SQL Window does not support bind variables, unlike the Test Window.

 

[mike]

I copy SQL to a new window from packages quite often also. I think that it would be less intuitive to select test and not have a test window show up.

My solution was to create a small macro to copy, open a SQL window and then paste. I assigned it to a hot key for quick access.

 

[Makker]

I learned just last week that the sql-window has some form of bindvariables. But they should be prefixed with '&'.
Is the suggestion of Scott not be solved with the following enhancements?
1. Create a preference to be able to define the bind-variable-character (so that you can change ampersant in colon)
2. Handle bind-variables in SQL-window the same as in Test window, since in SQL-window they are asked for in a pop-up so you can't review the contents during execution.
3. Create a pop-up menu-option 'Execute as SQL' or something to execute selected code or the code where the cursor is in. So that you can point a cursor definition or a sql-statement and that the sql is extracted as is to a new sql-window. It would be nice that in case of a cursor definition that the cursor-parameters are transformed into bind-variables, so that you can fill-in test values for it.

I would love to have this enhancement, since I'm also regularly extract seperate sqls from cursors during debugging. It would also be nice to have a button that when debugging a cursor that a separate sql-window would be created with the exact sql and the exact bind-variables filled in so that the result from the cursor could be reviewed before it is executed within the debugger. Because often that is actually what I'm trying to do when I'm debugging: check if my sql's are executed with the right conditions, right variables and give the result I expect.

Regards,
Martien

 

[ScottMattes]

Along those lines, I'd really like to have the automated conversion of a selected SQL to use the variable names and just prepend the : instead of changing them to :var1, :var2, etc. It makes it harder than it needs to be for correlating what was in the SQL to begin with.

Thank you.

p.s. Bo, yup, but it would still have taken just as long figuring it out so that I knew to switch to a SQL Window.

 

[Marco Kalter]

Using substitution variables would work, though the values you enter will literally be inserted in the SQL text that is sent to the server. We could create a preference for this.

Leaving the variable names is not always possible, because PL/SQL expressions (e.g. record.field or array(index)) are not always valid variable names. We can of course enhance this, and leave the name if it is a valid variable name.

I have added both suggestions to the list of enhacnement requests.

 

[ScottMattes]

I wouldn't mind if the record.field was changed to &record_field, not one little bit