Oracle-L: RE: naming conventions for Oracle/Unix vs. SQL Server

From: Jacques Kilchoer <Jacques.Kilchoer_at_quest.com>
Date: Mon, 29 Jul 2002 11:03:33 -0800
Message-ID: <F001.004A4F44.20020729110333@fatcity.com>

>-----Original Message-----
>From: Paula_Stankus_at_doh.state.fl.us [mailto:Paula_Stankus_at_doh.state.fl.us]
>Please help. I work in an organization where we have both
> SQL Server on NT and Oracle on Unix. SQL Server and
> developers who are used to GUI's in NT like column names
> to have mixed case with no underscores. The Unix folk -
> like myself prefer underscores and one case. Is there
> any reason not to adopt mixed case for Oracle? Is this really
> just what I am used to? I have been using this standard for so
> long that it maybe the reasons I adopted it do not any longer
> exist or are not as compelling as developer's today are
> more comfortable with mixed case. Help!

While it is possible to create table names that are reserved words or contain "special" characters when using double quotes around the names e.g.
create table "TABLE" ("COLUMN" varchar2 (20)) ; create table "joe's table" ("joe's data" varchar2 (20)) ;

I would not recommend it for the following reasons:

As other persons have pointed out, it makes coding more difficult in that object names have to be surrounded with double quotes;
You will run into difficulties when a third-party application is not smart enough to put double quotes around the column names;
You will run into difficulties when you hit an Oracle bug relating to "non-standard" names. Two of them that I know of so far: - Cannot use alter table move on an index-organized table with lowercase column names (in 8.0, 8.1, 9.0 and maybe 9.2) : see example below - Cannot have the "including" column for an index-organized table in lowercase in 8.1 (strangely enough it works in 8.0 and 9.0): see example below
- Also, I was unable to successfully implement a security policy (dbms_rls) on a table when the security policy function name was lowercase (but perhaps with some ingenuity it could be made to work)

**********************'

alter table move on index-organized table with lowercase column name error:

SQL*Plus: Release 9.0.1.0.1 - Production on Me Jul 24 10:07:12 2002 (c) Copyright 2001 Oracle Corporation. All rights reserved. Connecté à :
Oracle9i Enterprise Edition Release 9.0.1.1.1 - Production With the Partitioning option
JServer Release 9.0.1.1.1 - Production
SQL> create table book
2 ("book_id" number not null,
3 book_title varchar2 (40),
4 constraint book_pk primary key ("book_id") 5 )
6 organization index
7 tablespace users ;
Table créée.

SQL> alter table book move tablespace tools ; alter table book move tablespace tools
*
ERREUR à la ligne 1 :
ORA-00904: Nom de colonne non valide

**********************'

alter table move on index-organized table with lowercase column name error: SQL*Plus: Release 8.1.7.0.0 - Production on Lu Jul 29 10:59:06 2002 (c) Copyright 2000 Oracle Corporation. All rights reserved. Connecté à :
Oracle8i Enterprise Edition Release 8.1.7.2.1 - Production With the Partitioning option
JServer Release 8.1.7.2.1 - Production
SQL> create table my_table

  2     (id number (7) primary key,
  3      name varchar2 (20),
  4      "type" char(1) not null,
  5      purchase date
  6     )

  7 organization index
  8 including "type"
  9 overflow tablespace users ;
create table my_table
*
ERREUR à la ligne 1 :
ORA-25184: nom de colonne attendu

SQL> -- syntax correct when removing double quotes around including column name
SQL> create table my_table

  2     (id number (7) primary key,
  3      name varchar2 (20),
  4      type char(1) not null,
  5      purchase date
  6     )

  7 organization index
  8 including type
  9 overflow tablespace users ;
Table créée.

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jacques Kilchoer
  INET: Jacques.Kilchoer_at_quest.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Mon Jul 29 2002 - 14:03:33 CDT