substitution variable behavior has changed slightly

Toad for Oracle - Beta
1

SELECT ‘&&test.’||’.er’ FROM dual;

in TOAD this now produces 2 dots

test…er

in SQLPlus it produces only 1 dot

Enter value for test: test
old 1: SELECT ‘&&test.’||’.er’ FROM dual
new 1: SELECT ‘test’||’.er’ FROM dual

‘TEST’

test.er

2

Hm, yeah that was me.

I was trying to do a “select * from &schema.&table” and noticed that the dot disappeared. So, I guess I am supposed to use 2 dots if I want to do that? I’ll change it back - but I’m curious if anyone knows the answer to this - Why does SQL*Plus swallow up one of the dots?

3

I have been down this path before, John. It’s configurable, but by default CONCAT is period so doubling is required. blogs.oracle.com/…/sqlplus_101_substitution_varia

4

It uses dot as a delimiter for the substitution variable

Which makes sense for something like &&schema_name._DATA which would resolve to MYSCHEMA_DATA

5

Thanks Mike. I was surprised that it was “broken” when I found it!

6

Yes, thanks Mike - I never knew the delimiter was configurable. Good to know.

7

Just don’t change it and expect Toad to recognize it with F9. :wink: Toad is hardcoded to the default.

8

Also good to know. I don’t usually assume any of the SET options work with F9, but good to have confirmation of the exact behavior in this case.