I read this post by Laurent Schneider yesterday. In the comment section Tom Kyte already explained what the issue was about, but I’ll expand this explanation a little.
The question was why should the apparently invalid statement below work? I mean there is no such column nor number as “1.x”), yet the statement works ok:
SQL> select 1.x from dual; X ---------- 1
The column header gives a good hint what happened above. Oracle has treated the X as the column alias.
Let’s remove the “x” and see:
SQL> select 1. from dual; 1. ---------- 1
Now all works as expected, “1.” is treated as number ( 1. = 1.0 )
But the question remains, how come Oracle splits up the string “1.x” and decides that the “x” is the column and “1.” is the number part – considering that there’s no whitespace between the 1. and x?
The answer is that apparently the string tokenizer used by Oracle SQL parser is smart enough that it doesn’t rely only on whitespace for recognizing token delimiters. It is also able to use character class analysis for understanding where a literal ends and the next token (like column alias) starts.
So, it is possible to write valid SQL statements without using any whitespace at all. For example:
SQL> select*from"DUAL"; D - X SQL> select"DUMMY"from"DUAL"; D - X
Of course you could always use comments for replacing whitespace below…
SQL> alter/**/system/**/switch/**/logfile; System altered.
All the info above has quite limited use (unless your keyboard’s space bar is broken), but nevertheless, it illustrates some internal workings and flexibility of Oracle’s SQL text parser…
NB! I am running one more Advanced Oracle Troubleshooting training in 2018! You can attend the live online training and can download personal video recordings too. The Part 1 starts on 29th January 2018 - sign up here!