I have been using Toad for a very long time. Somewhere around version 10 Toad for Oracle changed the way it evaluated variables for table owner.
The syntax of course is owner.table name. In my unix environment native sql resolves a variable with the actual owner name.
For instance say I initialize a variable $SCHEMA to the table owner JOE. I can then write sql against the his tables with the basic Oracle syntax
$SCHEMA.JOES_TABLE.
However in Toad for Oracle it requires me to either value the variable to “JOE.” (name followed by a period) or even worse write the sql to have double periods
following the variable (select * from &&SCHEMA…JOES_TABLE).
This requires me to define a second variable for schema name if is part of the data returned. I also have to explain to each new junior team members that our unix and windows sql behave differently.
It’s not a major issue but still don’t understand why a 3rd Party tool would add such a requirement that is outside of Oracle’s syntax, especially when earlier versions already had it correct.
Prior releases did not have an override to this, but I would like to suggest that in future releases that either syntax be acceptable or an override be provided where you could just pass
an owner name to a variable and write the sql accordingly and Toad would resolve correctly. &&SCHEMA.JOES_TABLE
Just a thought!
Thanks!
The double period is necessary if SET CONCAT is set to '.' which is the default in SQLPlus. Toad's script engine handles this properly. See the following examples. Example 1 uses '.' for CONCAT and example 2 uses ';' for its value. In each you can see Toad's output in the background and SQLPlus in the foreground.
Example 1:
set concat '.'
select '&var1.TEST' from dual;
select '&var2..TEST' from dual;
select '&var3.&var4.TEST' from dual;
Example 2:
set concat ';'
select '&var1.TEST' from dual;
select '&var2..TEST' from dual;
select '&var3.&var4.TEST' from dual;
So I think that we're OK for script execution unless I'm missing something. The problem comes in for F9. Since F9 does not emulate SQL*Plus behavior and has no knowledge of the CONCAT value you intend to use it assumes '.' and requires double periods if you want to insert a single. See sections 9.6 and 9.7 in this link. https://blogs.oracle.com/opal/entry/sqlplus_101_substitution_varia#9_6
If I'm missing the issue please clarify.
Thanks,
Michael
Hi Michael, Thanks for reply.
I wouldn't say you are missing something, but I still look at it differently.
The Oracle doc points toward variables and examples that are actually used as literals for file names or in a statement.
Neither of which changes basic syntax for parsing owner.tablename.
I ran a similar test in our Linux environment to better illustrate the differences.
In native sql I recreated your test and valued a variable SCHEMA to a TMX (no trailing period)
I then ran the following: select distinct '$SCHEMA.TEST' from $SCHEMA.ACCOUNT;
The output below shows the literal kept the period between the variable and the dot TEST(unlike your example)
and resolved the owner table name.
No worries I guess I am the only one that complained about this. 
I ran a similar test in our Linux environment to better illustrate the differences.
You are mixing technologies though, right? It looks like you're using Korn shell variables in your SQL and not using Oracle's variable syntax. I don't see where $variable_name is Oracle syntax in the docs.
Could you have your scripts setup to get the variable in the form you’re using and then use Oracle syntax in the SQL? This should allow you to use your code in both places. You’d just need to comment your variable define statement when running in Toad or change its assignment.
define var1 = $var1
select ‘&&var1…test’ from &&schema…account;
I am using the unix sqlplus client. So yes the variables are formatted per that OS system. So I would not say I am mixing anything.
As I said earlier Toad versions allowed what I had come to know as standard Oracle format owner.tablename whether the owner was hard coded or passed as a variable.
I guess that was changed to mimic the sqlplus variable handling you describe. What was not taken into account was that folks like me were using Toad variables like an OS system variable which the early Versions of Toad understood. Progress…lol
Also the ONLY time it is really an issue for me is if I am using the &&SCHEMA variable as part of the data returned or part of a where predicate.
In which case I just stop being lazy and create a second variable of the schema name without passing the extra period. And I know I could always code my toad scripts to employ the &&SCHEMA…Tablename. But to me a double period implies something else. So I am being stubborn here too.
So no worries I just figured I make my case as possible option for future releases.
Thanks for your help Michael!
The double period change was fixed a few releases back because of the difference between SQLPlus and Toad, I do remember the change quite well. Scripts written for Toad could not be executed using SQLPlus because of the mishandling of periods. This is not the first time a Toad-ism has been corrected causing grief, but I think Toad should always try to follow the rules of the road and in time everyone will re-learn how to do it and be better off for it.
I am using the unix sqlplus client. So yes the variables are formatted per that OS system. So I would not say I am mixing anything.
No, but I think if you try to minimize your dependence on platform specific syntax your scripts will be more compatible elsewhere. I think doing the DEFINE var = $os_var and then using native SQL*Plus syntax elsewhere will help things. The double period thing is weird, I'll give you that, but it is the way Oracle says it must be done.
Michael
Chiming in late, but that mix of shell and SqlPlus variables intrigues me. Joe, a one million dollar question: I see an exclamation mark (!) in your screenshot, are you using a here-document? If so, then you are effectively “mixing technologies”. [:)]
community.oracle.com/…/855475
Hi Andre, Here’s my fifty cent answer to your million Dollar question. [:)]
Yes I am using a “here” document. However it is my take that a here document in KShell is just a streamed literal, which still needs to parsed by sqlplus and is subject to the rules of it’s OS Client.
So if you mean I am mixing technologies by expecting a Windows tool to handle variables like a Unix/Linux client, I suppose you are correct.
That being said Toad handles variables the same as the Windows version of sqlplus. Both require a variable to include a period after the variable(&&OWNER.) or including a double period between the OWNER…TABLENAME in the sql itself.
Since Toad is a windows tool I understand it should handle variables the same as it’s respective Oracle’s OS client. .
Actually it is Oracle’s fault for not having consistent Syntax across OS systems. So I blame Oracle!! lol
here document in KShell is just a streamed literal, which still needs to parsed by sqlplus and is subject to the rules of it's OS Client.
...but the variables you're using are parsed and handled by the shell. The substitutions are made to the string literal before sending it to SQLPlus. What SQLPlus is receiving is a string literal that it understands void of any variables. You've taken variable handling out of the SQL script and moved its processing to the shell. There is no difference between SQLPlus on Linux compared to Windows here. There's nothing wrong with the approach other than you've locked your scripts into Linux. If you need compatibility with Windows you need to have your SQL be SQLPlus compatible.
Yes Michael and Andre you are right. I have done some additional testing and confirmed the OS system does the resolution of the owner name variable BEFORE streaming, so sqlplus has a valid owner.table_name.
Thanks!
So back to my earlier suggestion. If you do something like…
define var = $var_value
…
select ‘&&SCHEMA…test’ from…
…at start of your SQL script you can still use your here document and the SQL will largely be compatible elsewhere. You’d only need to change the DEFINE for your vars when executing on Windows.
