Slightly Weird Oracle Stuff

JPEG to PDF    Send article as PDF   

I knew you could do this:

SQL> select 1234567890 as abc from dual;

       ABC
----------
1234567890

or

SQL> select 1234567890 abc from dual;

       ABC
----------
1234567890

But I didn’t know that this worked as well:

SQL> select 1234567890abc from dual;

       ABC
----------
1234567890

So I did a bit of playing and discovered that there is a difference if the alias is D or F but no other single character:

SQL> select 1234567890d, 1234567890f, 1234567890p from dual;

1234567890D 1234567890F          P
----------- ----------- ----------
 1.235E+009  1.235E+009 1234567890

This shows the values in Scientific notation when D or F is used as an alias in this manner, but not if used in this manner:

SQL> select 1234567890 d, 1234567890 f, 1234567890 p from dual

         D          F          P
---------- ---------- ----------
1234567890 1234567890 1234567890

Then it gets stranger:

SQL> select 1234567890df from dual;

         F
----------
1.235E+009


SQL> select 1234567890fd from dual

         D
----------
1.235E+009


SQL> select 1234567890fa from dual

         A
----------
1.235E+009

I get the impression that a trailing F or D on a number means “display as floating point or decimal” then the F/D is dropped and the A used as a label. I can’t find this in the docs though.

Works with strings as well but the F/D thing doesn’t appear with strings. Doesn’t work – for obvious reasons – with column names.

UPDATE: Thanks to Maxim on the Oracle-L list, the answer is here

UPDATE 2: Thanks to Jonathan Lewis also on the Oracle-L list, it seems that Tanel Poder has also come across this. On his blog here.

Cheers,

4 thoughts on “Slightly Weird Oracle Stuff”

    1. Thanks William. I’ve updated my post with links to the docs explaining this and also to Tanel’s blog where he discusses many more weird parsing examples.

      Cheers,
      Norm.

Leave a Reply