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

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

From: [email protected] [mailto:[email protected]] On Behalf Of Richard Squires
Sent: Monday, December 05, 2011 11:09 PM
To: [email protected]
Subject: [toad] Scan for user defined substitution variables - in SQL?





Message from: R Squires


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


_______________________________________

Historical Messages


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

__
_______________________________________