Scan for user defined substitution variables - in SQL?

Follow / 6 Dec 2011 at 6:08am

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

Replies

  • Follow / 6 Dec 2011 at 5:57pm

    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

  • Follow / 7 Dec 2011 at 3:00am

    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

  • Follow / 8 Dec 2011 at 12:10am

    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.

  • Follow / 8 Dec 2011 at 12:38am

    > select SHIP_DATE from ORDERS where order_id = p_id;
    > It would not pick up SHIP_DATE as it would be checking for a space (or no
    other character) preceding the P_.

    This is a little different than substitution variables then. SQL*Plus will
    prompt for P_DATE if P_ were the variable prefix. There will need to be some
    clarification on implementation a little bit I think. I say this because the
    current implementation satisfies several of the requests and I'd like to spec
    out exactly what it is that will be in Toad so that we can reopen your idea in
    the pond and clarify it a little bit. There would need to be some activation
    characters like after whitespace, after an open paren, etc. Within strings or
    other identifiers would not prompt (unlike substitution variables).

    Will this ever get in your way? By that I mean setup will take a few moments to
    do and all may be well for days or weeks, but then you run a select statement
    against a table that has a column starting with P_ and you're prompted.
    Frustrated you now want a quick toggle to turn this on/off without needing to
    open options and do it. See where I'm going? We need a little more explanation
    to be sure that users are voting for what they think they are because it will
    certainly add bloat to the product for those that are uninterested.

    What about anything else in the SQL that does not fit any of the prefixes you've
    specified like references to other package functions? I assume those should just
    be left intact.

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

    They would not be the same implementation.

    > but if its scanning the PLSQL for variable names in the code then it seems
    more complicated than doing some pattern matching

    The SQL extraction from PL/SQL does not rely on rules based on normal string
    searching. We use the parser that is the core to the editor to break down the
    statement. We then check out all tables referenced in the from list and see if
    the identifier matches any columns. Next we look at locally defined identifiers
    and move up in scope until the entire PL/SQL object has been evaluated. If that
    fails we see if the unknown identifier is to a package variable, etc. Along the
    way we cache results of objects that were evaluated behind the scenes so that
    subsequent requests are quick and efficient. It is a rather complex process and
    requires no setup. With this method, if you inherit code from another team that
    does not use the same naming convention you do not need to modify options to
    have the variables handled correctly. It is most certainly more complicated than
    pattern matching, but extra work on Toad's part requires less from the user and
    will generate more accurate results. The feature is also used by other areas in
    Toad so the current behavior cannot be changed. For example you can explain plan
    and tune SQL that is embedded within PL/SQL. I think that there is expectation
    that you should just be able to place your caret on SQL (even in PL/SQL) and
    press CTRL+E to generate an explain plan without needing to jump through
    hoops locating the user defined variable prefix feature. It should just work.

    I'm about to head out for the day, but we can add additional comments to the
    idea to clarify a few of the points tomorrow.

    Thanks,

    Michael

  • Follow / 8 Dec 2011 at 5:47am

    -- There would need to be some activation characters like after whitespace, after
    -- an open paren, etc. Within strings or other identifiers would not prompt

    Agreed, the rules for identifying parameters would need to be clarified

    -- Will this ever get in your way? By that I mean setup will take a few moments to
    -- do and all may be well for days or weeks, but then you run a select statement
    -- against a table that has a column starting with P_ and you're prompted.

    It might, but 99% of the time I'd need this feature.  I'd use it multiple times
    daily.  Because I was thinking of this in a similar fashion to "Prompt for
    Substitution variables" I was thinking we could toggle this feature on/off there.
    Maybe "Enable extended Substitution variable matching".

    -- We need a little more explanation
    -- to be sure that users are voting for what they think they are because it will
    -- certainly add bloat to the product for those that are uninterested.

    Bloat/New Features, it's all in the eye.  :)  Thinking in terms of Substitution
    Variables, in essence I'm suggesting we could expand the set from just a colon
    to a user defined set.  If none were set up (which would/could be the default)
    then no one would be aware of the new addition.

    -- What about anything else in the SQL that does not fit any of the prefixes you've
    -- specified like references to other package functions? I assume those should just
    -- be left intact.

    That's a good one, qualified parameters in a procedure call wouldn't be replaced,
    I guess this goes back to the rules in your first point. We would need to create
    a test case which involved a complex piece of SQL and determine that the rules to
    identify the parameters worked.  If there are parameters that don't match any of the
    formats you've defined then you'll get the same error you do now.

    -- For example you can explain plan
    -- and tune SQL that is embedded within PL/SQL. I think that there is expectation
    -- that you should just be able to place your caret on SQL (even in PL/SQL) and
    -- press CTRL+E to generate an explain plan without needing to jump through
    -- hoops locating the user defined variable prefix feature.

    That is exactly one of my requirements, I want to be able to Explain Plan SQL
    without it being within PL/SQL, indeed, I want the exact features you have
    implemented within PL/SQL to be available for straight SQL.  As I mentioned before,
    it's rare, and often not possible as I'm viewing code in the SB and not in MOE,
    that I want to execute or explain plan SQL whilst I'm working in a package.

    -- It should just work.

    My point exactly.  It should just work, whether it's SQL within the scope of
    PL/SQL or SQL on it's own.  I can't see why you wouldn't want it to work either
    way.

  • Follow / 25 Jan 2012 at 8:11am

    So, are we re-opening this idea in the Pond?  Or submitting it as a new one?  Is there enough detail in this post now to clearly define my requirements?

    Happy to provide further clarification on this in order to get some traction.  :)

  • Follow / 25 Jan 2012 at 4:25pm

    Sorry, Richard! Completely forgot about this after our lengthy discussion. It's
    active now. I'll set a reminder to summarize our discussion and add a comment to
    it.

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

    Michael