15 thoughts on “select 1.x from t1

  1. Thomas Kyte

    No, for the same reason that

    select ‘a’x from dual

    would not fail.

    select 1.
    from dual;

    works – 1. is a valid literal.

    select ‘literal’name
    from dual

    is valid (whitespace is many times optional)… hence

    select 1.x from dual

    is.

    ops$tkyte%ORA10GR2> select*from dual;

    D

    X

  2. Pingback: Can you write a working SQL statement without using any whitespace? « Tanel Poder’s blog: Core IT for geeks and pros

  3. Matthias Rogel

    So, what does
    SQL > select 1.dummy from “DUAL””1”;
    select 1.dummy from “DUAL””1”
    *
    ERROR at line 1:
    ORA-03001: unimplemented feature

    tells us ?

  4. Ed Rusu

    Can you guys explain why the following statement works?

    select SYSDATE@! from dual;

    SYSDATE@!
    ———
    14-JAN-08

    SQL> select SYSDATE@!-4 from dual;

    SYSDATE@!
    ———
    10-JAN-08

  5. Laurent Schneider Post author

    @matthias
    Probably in Oracle 17, “DUAL””1” will be a table called DUAL”1, in Oracle 11 and before, it is not possible to have a double quote in a table name

    @ed
    No idea !

  6. Pingback: Blog » Blog Archive » Oracle, white spaces and unexpected behaviour

  7. Claudia Zeiler

    For what it’s worth

    SQL> select sysdate from dual;

    SYSDATE
    ———
    15-JAN-08

    SQL> select sysdate@ from dual;
    select sysdate@ from dual
    *
    ERROR at line 1:
    ORA-00923: FROM keyword not found where expected

    SQL> select sysdate@! from dual;

    SYSDATE@!
    ———
    15-JAN-08

    SQL> select sysdate@1 from dual;
    select sysdate@1 from dual
    *
    ERROR at line 1:
    ORA-00923: FROM keyword not found where expected

    And I don’t understand anything any more!

  8. Marco Gralike

    Currently I don’t have a database at hand, so I can really test it, but regarding:

    select 1.dummy from “DUAL””1″;

    Counting that the use of double quotas is a standard thing while for instance looking into the syntax of a dumpfile, generated by “exp”. I would expect that “1” is regarded as a database link alias or such. With SQL*Net V1 this was the place to use the connection descriptor starting with the protocol (t for tcpip, d for dec, n for names – if I remember correctly). Therefore maybe the ORA-03001: unimplemented feature.

    Laurent are you sure you can’t use double quotes in a table name? Didn’t we wander these paths before…?

  9. Stefan P Knecht

    Another little oddity. If you’re setting event 10053 and run

    select sysdate@! from dual;

    All you get is an empty tracefile (on 10.2.0.3) — seems the optimizer is choking on it ?

    Perhaps the “!” character has a special meaning (other than executing commands in your shell on *nix systems) ?

    Stefan

  10. Ed Rusu

    Maybe we should have a separate discussion on sysdate@! thing 😉
    It was found when sysdate was used with db link.
    I.e. SQL> select sysdate from dual@db_link;
    produces the following SQL in remote database:
    SELECT SYSDATE@! FROM “DUAL” “A1”

    Ed

  11. Vadim Tropashko

    Sure there is an entertaining (and time consuming) explanation for each and every oddity, but how many developers really write a query without whitespace? I’m not aware of any modern programming language where whitespace is not a fundamental part of the syntax. Ditto for identifiers in quotes. Those should be deprecated (although was there a case when anything has been deprecated in SQL?)

Comments are closed.