Laurent Schneider

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

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 ?

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

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!

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…?

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

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

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?)

Come on, Mikito Harakiri, wasting our time is not only a side effect but the *sense* of dealing with SQL, programming languages and all the rest, isn’t t ?

Laurent,

thanks for that great link !

Σωκράτης