Re: RE: Scan for user defined substitution variables - in SQL?

 Message from: R Squires

4.  Another reason this would be useful is, we are often investigating an error in Dev/Test /Prod, we have an error that indicates that there was an issue with an SQL statement at line 1234 in Package my_package.  Fortunately, our logging has provided us enough context information to know what values were used in the SQL that failed.  In this instance, we would copy the SQL from the package body (from the Schema Browser), paste this in to MOE and execute the SQL populating the various variables.

This would not be possible with the current approach, in this instance it wouldn't make sense to copy the entire procedure into MOE just to take advantage of TOAD populating the variables for us.


_______________________________________

Historical Messages 


Author: Richard Squires
Date: Wed Dec 07 14:10:33 PST 2011
4.  Another reason this would be useful is, we are often investigating an error in Dev/Test /Prod, we have an error that indicates that there was an issue with an SQL statement at line 1234 in Package my_package.  Fortunately, our logging has provided us enough context information to know what values were used in the SQL that failed.  In this instance, we would copy the SQL from the package body (from the Schema Browser), paste this in to MOE and execute the SQL populating the various variables.

This would not be possible with the current approach, in this instance it wouldn't make sense to copy the entire procedure into MOE just to take advantage of TOAD populating the variables for us.

__

Author: Richard Squires
Date: Tue Dec 06 17:00:31 PST 2011
I am talking about the execution of SQL within MOE without any context provided from being in PLSQL.  I would like to be able to execute:

select SHIP_DATE from ORDERS where order_id = p_id;

and for TOAD to recognise that P_ID is a variable (because I have told TOAD that when scanning for substitution variables that p_ denotes a variable in my settings).  It would not pick up SHIP_DATE as it would be checking for a space (or no other character) preceding the P_.

Instances where this would be useful:

1. A colleague sends me SQL to execute with the variable names in the SQL but not the whole package (note we have common naming conventions for variables and parameters that start with v_ or p_ for example)

2. When I copy the SQL from the PLSQL to a separate MOE tab to execute, correct or amend and copy back to the PLSQL.  In this instance I don't need to add and then remove all of the : from the SQL in order to paste it into my package.  I've not come across anyone yet that works on their queries within the context of their package (accidentally compiling a work in progress when you only meant to execute the SQL would be a risk along with editing a package that I have opened from a file when I am merely tuning/changing a query within it).

3. More often than not I'll be working on a query that has alot of substitution variables.  Currently I have to go through the entire query and prefix all the variables with a colon. And then, once I'm done, I need to remove them all again to copy the query back into PLSQL. This is time consuming.


The benefit of this approach is that the same implementation would for SQL and PLSQL.  It would be consistent in MOE and wouldn't just work within PLSQL - I feel this makes it a better and simpler solution.

I'm not sure how you've implemented the feature at the moment, but if its scanning the PLSQL for variable names in the code then it seems more complicated than doing some pattern matching for the code to be executed and TOAD recognising P_... or V_... as the same thing as a variable prefixed with a :.

Richard

__

Author: Michael Staszewski
Date: Tue Dec 06 07:57:07 PST 2011




 

Can you elaborate more on a use case? There were several requests over the years
to allow SQL embedded within PL/SQL to be executed intelligently. Perhaps I
misunderstood your request and thought that it was along the same lines. You
gave a cursor as one example in your Idea Pond request so I was thinking that
your user-defined variables were not real subst vars, but actually references to
PL/SQL identifiers that you wanted to be treated as subst vars when executed out
of context. If you are referring to non-embedded PL/SQL then why not just use
the SQL*Plus syntax and write correct code with & and &&? How would you expect
this to function? The only thing that I can think of is to have a list (in
options I guess) where you add all of your var prefixes. Those prefixes would
then be handled exactly the same as normal subst vars. Using your P_ example
from the Idea Pond, the following statement would prompt for 2 variables.

select SHIP_DATE from ORDERS where order_id = p_id;

Both P_DATE (from SHIP_DATE) and p_id satisfy the variable prefix test. Unless
I'm missing something I see this as one of those features that feels clumsy that
adds to the bloated feel of the product that is sometimes mentioned in our
surveys. If the SQL is not in PL/SQL then just use subst vars, no?

Michael


__

Author: Richard Squires
Date: Mon Dec 05 20:08:59 PST 2011
I was having a look at TOAD v11 Trial, particularly at the user defined substitution variable request in the Idea Pond:

http://toadfororacle.ideascale.com/a/dtd/Scan-for-user-defined-substitution-variables/46050-8477

I hadn't read the solution properly and didn't quite understand that this was ONLY going to work fro SQL that resides inside of PLSQL.  It wouldn't work for straight SQL executed within MOE.

Any chance we can get this reopened so that we can get a more complete solution that doesn't rely on matching PLSQL variables being specified in order to do the bind variable substitution?  I would actually use this feature more for straight SQL than I would SQL within PLSQL.

Regards

Richard

__
_______________________________________