This content has been marked as final. Show 7 replies
I've never really got to grips with how Oracle allows some words that you would think should be reserved as object names. Consider this...
SQL> CREATE TABLE DECODE (TYPE VARCHAR2(10), OBJECT VARCHAR2(20))Regards,
Hello. I'm one of the tech writers responsible for the Oracle SQL Reference.
The reserved word list has been frozen for eons, so as not to break existing customer code. If we keep adding reserved words, it's very likely that we will pick something already used by a customer. It's not hard to imagine, for example, a customer writing his/her own function called DECODE or REGEXP_LIKE.
The SQL Ref. has a section "How Oracle Database Resolves Schema Object References" (in Chapter 2) that describes how the database deals with ambiguous database object names. Hope this helps.
If you want to check which words are reserved, V$_RESERVED_WORD in the database might be more reliable than the documentation. There are also some words in the list which can be used, but we must assume that if we do so we might have problems in a future version.
A small correction, Kevin. The words listed in V$RESERVED_WORDS are, for reasons unknown to me, not all reserved words. They are keywords. The list of truly reserved words in the SQL Reference is correct. I'll look into the V$RESERVED_WORDS issue and report back if I can find out why it is populated with keywords that are not reserved.
As Diana pointed out, the list of SQL reserved words has been frozen for a while. However, for PL/SQL, the current list of reserved keywords can be found by querying the v$reserved_words view. More than 200 new PL/SQL keywords (e.g., regexp_like) have been added to this view in 10g.
v$reserved_words only gives the keyword and length. Try x$kwddef, which gives the keyword, length, and type. A type of (x mod 4)=1 means a keyword, and a type of (x mod 4)=2 means a reserved word.
I'd just like to say 'Hi!' to Diane and Geoff. It's not often we get visitors from Oracle round these parts. It's nice to get some inside dope for once. Thanks for dropping by.