Substitution Variables vs. Bind Variables

[Doug Volz]

I'm a newbie to PLSQL, having been using Query Reporter since 2006 but recently purchased PL/SQL Developer. One of the main reasons I'm sticking with AllAroundAutomations is the excellent ability to download into Excel for all rows and whilst doing so, maintaining the label vs. number formatting. I do a lot of support and ad-hoc reporting for my clients for their month-end accounting closes. All of the Oracle Products (unless you make special coding or formatting efforts) truncate the client's account numbers (such as a string of 0000 gets truncated into 0) and drop off leading zeros. And I primarily write using SQL*PLUS, not PL/SQL, and may have multiple Substitution Variables. Sometimes the same ones in different column selects or inline table selects. But on my last assignment I worked with other teams who were using the Oracle SQL Developer product and I had to constantly re-edit my code as Bind Variables so they could use the code in their tools, such as Oracle BI Publisher, Oracle SQL Developer product or (gasp) TOAD. Any suggestions? I like Substitution Variables as I only enter them in once while running the SQL code. Am using the SQL window in PL/SQL Developer and similar with Query Reporter. Thanks, Doug

 

[Gustavo]

You can use the "bind-variable" version in the Test Window, so you don't have switch between versions everytime. Unless you're running multiple queries (multi-tab output), in which case you'll have to stick to the SQL Window.

 

[Doug Volz]

Thanks Gustavo, I guess I'll have to have two versions of everything ... rats. With and without Bind Variables (&_variable vs. :_variable formats). I write SQL and not PL/SQL so the Test Window is not much use for me (unless I am mistaken). Perhaps I'll figure out a way to quickly edit the text files, to change the variable formats. Thanks, Doug

 

[Hilarion]

The Test Window does allow you to execute plain SQL queries with or without bind variables. In this case you have to remember not to include the trailing semicolon. The results will be shown in the "SQL Output" tab and it will not be paged (e.g. all data will be fetched). The Test Window does not support substitution variables, so it's a good testing place for queries prepared for other applications that also do not.

As far, as the support for substitution variables in other application goes, I know that the Oracle SQL Developer (OSD) application is able to work with them, at least in a Worksheet window, as it tries to emulate at least a part of the SQL*Plus features. At least in some cases it's even closer to SQL*Plus syntax, than the PL/SQL Developer (PLD).
For example the SQL*Plus uses double ampersand (&&) to indicate auto-define of a value for that substitution variable. The OSD does the same, but the PLD treats it as a literal ampersand (so no substitution is done). In SQL*Plus to treat the ampersand as a literal you would use the "set escape" to set an escape character, which by default would be a backslash, so "\&x" would be treated as "&x" in SQL*Plus and in OSD. The PLD would however still treat it as a substitution and the backslash as a literal character.
That's why PLD can also be a pain in the ass when using it to create scripts for SQL*Plus.

I do not know how the other applications (e.g. the TOAD) handle the substitution variables, but it may be a bit similar to PLD, e.g. they may work only in specific window types or work modes of those applications (like the PLD does not handle the substitution variables in the Program Window, Test Window or Explain Plan Window).

 

[Doug Volz]

Thanks very much for your advice! With Bind Variables I was able to use the Test Window and download the results into Excel. Excellent!