Home » Other » General » Reserved words (DB11.2.0.1.0, Win32)
Reserved words [message #461981] Tue, 22 June 2010 05:21 Go to next message
John Watson
Messages: 4557
Registered: January 2010
Location: Global Village
Senior Member
According to the SQL Language Reference, Quote:
Nonquoted identifiers cannot be Oracle Database reserved words
so this fails:
jw> create table x (date date);
create table x (date date)
                *
ERROR at line 1:
ORA-00904: : invalid identifier

but this succeeds:
jw> create table x (timestamp timestamp);

Table created.

Looking at v$reserved_words shows that this behaviour is as documented:
jw> select keyword,reserved from v$reserved_words where keyword in ('DATE','TIMESTAMP');

KEYWORD                        R
------------------------------ -
TIMESTAMP                      N
DATE                           Y
but why would DATE be reserved and TIMESTAMP not?
Re: Reserved words [message #461984 is a reply to message #461981] Tue, 22 June 2010 05:25 Go to previous messageGo to next message
Michel Cadot
Messages: 59118
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
but why would DATE be reserved and TIMESTAMP not?

Because Oracle currently tells as it! (and it may be, most likely, an historical reason.)
Anyway, any word that is in this view should not be used because at any time Oracle can change a value from N to Y.

Regards
Michel
Re: Reserved words [message #461989 is a reply to message #461984] Tue, 22 June 2010 05:39 Go to previous messageGo to next message
ramoradba
Messages: 2452
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
DATE is sql reserved word(ANSI standard reserved word)
TIMESTAMP is oracle pl/sql reserved word.


Quote:
RESERVED WORDS (SQL)
--------------------

SQL Reserved Words have special meaning in SQL, and may not be used for
identifier names unless enclosed in "quotes".

An asterisk (*) indicates words are also ANSI Reserved Words.


Quote:
RESERVED WORDS (PL/SQL)
-----------------------

PL/SQL Reserved Words have special meaning in PL/SQL, and may not be used
for identifier names (unless enclosed in "quotes").

An asterisk (*) indicates words are also SQL Reserved Words.

SQL> create table john_watson (date date);
create table john_watson (date date)
                          *
ERROR at line 1:
ORA-00904: : invalid identifier


SQL> create table john_watson ("date" date);

Table created.

SQL> desc john_watson
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------
 date                                               DATE

SQL>


sriram Smile

[Updated on: Tue, 22 June 2010 05:47]

Report message to a moderator

Re: Reserved words [message #461992 is a reply to message #461989] Tue, 22 June 2010 05:45 Go to previous messageGo to next message
John Watson
Messages: 4557
Registered: January 2010
Location: Global Village
Senior Member
Sussed! Thanks, Sriram. In some ways, my brain is too structured: I like to see a reason for everything.
Re: Reserved words [message #461993 is a reply to message #461992] Tue, 22 June 2010 05:48 Go to previous messageGo to next message
ramoradba
Messages: 2452
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
Quote:
I like to see a reason for everything

I like that! Even I am impressed with your post from the begining.Keep posting

sriram Smile
Re: Reserved words [message #462024 is a reply to message #461992] Tue, 22 June 2010 07:14 Go to previous messageGo to next message
rahulvb
Messages: 924
Registered: October 2009
Location: Somewhere Near Equator.
Senior Member
John Watson wrote on Tue, 22 June 2010 05:45
Sussed! Thanks, Sriram. In some ways, my brain is too structured: I like to see a reason for everything.


But its not a good idea to use the Reserve words as columns name s, as Matter of fact anyware in programming.
Re: Reserved words [message #462025 is a reply to message #462024] Tue, 22 June 2010 07:16 Go to previous messageGo to next message
ramoradba
Messages: 2452
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
he is not asking like "Why we should not use?"
Quote:
why would DATE be reserved and TIMESTAMP not?


Quote:
Reserved Words have special meaning in SQL (or) PL/SQL, and may not be used
for identifier names

Hope you got the point.

sriram Smile

[Updated on: Tue, 22 June 2010 07:18]

Report message to a moderator

Re: Reserved words [message #462027 is a reply to message #462025] Tue, 22 June 2010 07:28 Go to previous messageGo to next message
Michel Cadot
Messages: 59118
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't; could you explain.

Regards
Michel
Re: Reserved words [message #462036 is a reply to message #462027] Tue, 22 June 2010 08:27 Go to previous messageGo to next message
ramoradba
Messages: 2452
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
I mean to say is the "CONTEXT" is different

sriram
Re: Reserved words [message #462055 is a reply to message #462036] Tue, 22 June 2010 10:06 Go to previous messageGo to next message
Michel Cadot
Messages: 59118
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Which context?
Why DATE can't be used and TIMESTAMP can? Is this not the question?

Regards
Michel
Re: Reserved words [message #462075 is a reply to message #462055] Tue, 22 June 2010 12:22 Go to previous messageGo to next message
ramoradba
Messages: 2452
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
Michel Cadot wrote on Tue, 22 June 2010 20:36
Which context?
Why DATE can't be used and TIMESTAMP can? Is this not the question?

Regards
Michel


I never said DATE cannot be used where as timestamp can.
The ANSI reserved cannot be used directly you can use within a quote.
Quote:
Oracle SQL is a superset of
the American National Standards Institute (ANSI) and the International Standards.

PL/SQL, Oracle's procedural extension of SQL, is an advanced fourth-generation
programming language (4GL).


More over there is a difference B/W a keyword and a reserverd word.
for Ex:
Commit is a keyword,not an sql reserved word.
same as Timestamp.
http://www.petefreitag.com/tools/sql_reserved_words_checker/?word=commit
And here the question is Why DATE a SQL reserved word TIMESTAMP Not?
answer is DATE is an ANSI standard where as TIMESTAMP not.

sriram Smile

[Updated on: Tue, 22 June 2010 12:23]

Report message to a moderator

Re: Reserved words [message #462087 is a reply to message #462075] Tue, 22 June 2010 13:05 Go to previous messageGo to next message
Michel Cadot
Messages: 59118
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
And here the question is Why DATE a SQL reserved word TIMESTAMP Not?
answer is DATE is an ANSI standard where as TIMESTAMP not.

This does not answer the question.
There are many reserved words that are not ANSI.

Regards
Michel
Re: Reserved words [message #462092 is a reply to message #462087] Tue, 22 June 2010 13:24 Go to previous messageGo to next message
ramoradba
Messages: 2452
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
ofcourse...
But here He asking about DATE....
SO can you explain? Why? this does n`t answer the question?

sriram Smile
Re: Reserved words [message #462100 is a reply to message #462092] Tue, 22 June 2010 13:55 Go to previous messageGo to next message
Michel Cadot
Messages: 59118
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why this does not answer the question?
Because if there are non-ANSI keywords that are reserved words then the fact that TIMESTAMP is not an ANSI keyword is not a reason for not being reserved. It is just a matter of logic.

Can I explain why TIMESTAMP is not a keyword when DATE is one?
No I can't this is a question to ask to Oracle developers. Maybe they had just forgotten to make it reserved (I doubt about that) or maybe they do not need it to make it CURRENTLY as a reserved word but note that the simple fact that it is in the view proves that one day or another it might be one. And maybe DATE is a reserved word because at the time SQL syntax analyzer (and PL/SQL after it), far before SQL ANSI standard existed, was not smart enough to see the difference between DATE as an identifier and DATE as a datatype and so it is kept from version to version as a reserved word as any basic datatype that existed or have been introduced from V1 to V8i.

Regards
Michel
Re: Reserved words [message #466971 is a reply to message #462100] Wed, 21 July 2010 17:04 Go to previous message
sharond31
Messages: 2
Registered: July 2010
Junior Member
well said Michel.
I fully agree with you.

Previous Topic: ORA-00600: internal error code, arguments: [kokle_lob2lob13:input mismatch]
Next Topic: oracle development
Goto Forum:
  


Current Time: Wed Sep 17 20:58:14 CDT 2014

Total time taken to generate the page: 0.20213 seconds