Re: naming conventions for Oracle/Unix vs. SQL Server
hi! from my experience of haveing the same data or data model on different DB brands and platforms it is wise wiser wisest - cause you never know what the management is going to buy next - to stick to the following naming conventions for identifiers: + all identifiers in uppercase + not more than 30 chars (even if MS$SQL offers you 128) (if there's an old DB2 on the mainframe around, even: 18) + start with a char + do NOT - even not in quotes - use reserved words: INSERT, VIEW etc. + be careful with other characters than letters and numbers many developers use '_' only this will make life easier daniel -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Daniel Wisser INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: naming conventions for Oracle/Unix vs. SQL Server
Unless you are talking about Java in and Oracle database, in which case it is case sensitive. e.g. select owner, object_name, object_type from dba_objects where object_type like 'JAVA%'; Jared "Karniotis, Stephen" <[EMAIL PROTECTED]> Sent by: [EMAIL PROTECTED] 07/29/2002 09:15 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> cc: Subject: RE: naming conventions for Oracle/Unix vs. SQL Server Paula: The mixed case for Oracle does not matter, as Oracle is not case sensitive. The column names are stored in uppercase within the data dictionary. However, for SQL Server, the case sensitivity of column names is crucial. Another black eye for MSFT. Thank You Stephen P. Karniotis Product Architect Compuware Corporation Direct: (248) 865-4350 Mobile: (248) 408-2918 Email: [EMAIL PROTECTED] Web:www.compuware.com -Original Message- Sent: Monday, July 29, 2002 11:59 AM To: Multiple recipients of list ORACLE-L Guys, 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! The contents of this e-mail are intended for the named addressee only. It contains information that may be confidential. Unless you are the named addressee or an authorized designee, you may not copy or use it, or disclose it to anyone else. If you received it in error please notify us immediately and then destroy it. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
Re: naming conventions for Oracle/Unix vs. SQL Server
I come from a Unix/Shell/Perl/Java background where exact case match is important. Which is why it's s apparent that MS's lack of case sensitivity bugs me :) I recently had a similar discussion with a developer, who was absolutely puzzled that case-sensitivity was an issue because his only experience was with Microsoft. Suzy STEVE OLLIG wrote: > > Suzy - it isn't just MS_LAND that uses mixed-case. i've seen more than one > non-M$ shop take advantage of that in their namingConvetions. shell > scripts, perl, java, and even other non-M$ databases - Sybase on HP-UX for > example. > > i do however agree with the rest of the posts - probably not a good idea in > oracleLand ;) > > funny how passionate some can be about small things like this. we had a > rather heated debate on whether this: > > try { > stuff(); > more.stuff(); > } > > or this: > > try > { > stuff(); > more.stuff(); > } > > was to be in our java standards recently. FWIW, the former won out. > > -Original Message- > Sent: Monday, July 29, 2002 12:39 PM > To: Multiple recipients of list ORACLE-L > > Definately underscores, if simply just to break the habit of developers > assuming that mixed-case means something outside of MS-land. While > SQL-Server does allow/display/use objects in mixed-case format, forcing > that into Oracle *can* be done, but it's a bad idea. The Oracle data > dictionary stores object definitions upper-case, and allows those > objects to be referenced in any mixed case. > > Forcing object creation in Oracle as mixed-case is a DDL hack using "" > around the object name. At which point, the object can only be accessed > in the exact case it was created enclosed with "". For example > > SQL> create table "Test" (id number); > > SQL> desc test > ERROR: > ORA-04043: object "test" does not exist > > SQL> desc "test" > ERROR: > ORA-04043: object "test" does not exist > > SQL> desc Test > ERROR: > ORA-04043: object "test" does not exist > > SQL desc "Test" > Name Null?Type > - --- > ID NUMBER > > > [EMAIL PROTECTED] wrote: > > > > Guys, > > > > 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! > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Suzy Vordos > INET: [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] (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). > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: STEVE OLLIG > INET: [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] (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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Suzy Vordos INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: naming conventions for Oracle/Unix vs. SQL Server
Suzy - it isn't just MS_LAND that uses mixed-case. i've seen more than one non-M$ shop take advantage of that in their namingConvetions. shell scripts, perl, java, and even other non-M$ databases - Sybase on HP-UX for example. i do however agree with the rest of the posts - probably not a good idea in oracleLand ;) funny how passionate some can be about small things like this. we had a rather heated debate on whether this: try { stuff(); more.stuff(); } or this: try { stuff(); more.stuff(); } was to be in our java standards recently. FWIW, the former won out. -Original Message- Sent: Monday, July 29, 2002 12:39 PM To: Multiple recipients of list ORACLE-L Definately underscores, if simply just to break the habit of developers assuming that mixed-case means something outside of MS-land. While SQL-Server does allow/display/use objects in mixed-case format, forcing that into Oracle *can* be done, but it's a bad idea. The Oracle data dictionary stores object definitions upper-case, and allows those objects to be referenced in any mixed case. Forcing object creation in Oracle as mixed-case is a DDL hack using "" around the object name. At which point, the object can only be accessed in the exact case it was created enclosed with "". For example SQL> create table "Test" (id number); SQL> desc test ERROR: ORA-04043: object "test" does not exist SQL> desc "test" ERROR: ORA-04043: object "test" does not exist SQL> desc Test ERROR: ORA-04043: object "test" does not exist SQL desc "Test" Name Null?Type - --- ID NUMBER > [EMAIL PROTECTED] wrote: > > Guys, > > 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! -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Suzy Vordos INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: STEVE OLLIG INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: naming conventions for Oracle/Unix vs. SQL Server
Title: RE: naming conventions for Oracle/Unix vs. SQL Server >-Original Message- >From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] >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: a) As other persons have pointed out, it makes coding more difficult in that object names have to be surrounded with double quotes; b) You will run into difficulties when a third-party application is not smart enough to put double quotes around the column names; c) 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.
Re: naming conventions for Oracle/Unix vs. SQL Server
Definately underscores, if simply just to break the habit of developers assuming that mixed-case means something outside of MS-land. While SQL-Server does allow/display/use objects in mixed-case format, forcing that into Oracle *can* be done, but it's a bad idea. The Oracle data dictionary stores object definitions upper-case, and allows those objects to be referenced in any mixed case. Forcing object creation in Oracle as mixed-case is a DDL hack using "" around the object name. At which point, the object can only be accessed in the exact case it was created enclosed with "". For example SQL> create table "Test" (id number); SQL> desc test ERROR: ORA-04043: object "test" does not exist SQL> desc "test" ERROR: ORA-04043: object "test" does not exist SQL> desc Test ERROR: ORA-04043: object "test" does not exist SQL desc "Test" Name Null?Type - --- ID NUMBER > [EMAIL PROTECTED] wrote: > > Guys, > > 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! -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Suzy Vordos INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: naming conventions for Oracle/Unix vs. SQL Server
Title: RE: naming conventions for Oracle/Unix vs. SQL Server Paula: The mixed case for Oracle does not matter, as Oracle is not case sensitive. The column names are stored in uppercase within the data dictionary. However, for SQL Server, the case sensitivity of column names is crucial. Another black eye for MSFT. Thank You Stephen P. Karniotis Product Architect Compuware Corporation Direct: (248) 865-4350 Mobile: (248) 408-2918 Email: [EMAIL PROTECTED] Web: www.compuware.com -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Monday, July 29, 2002 11:59 AM To: Multiple recipients of list ORACLE-L Subject: RE: naming conventions for Oracle/Unix vs. SQL Server Guys, 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! The contents of this e-mail are intended for the named addressee only. It contains information that may be confidential. Unless you are the named addressee or an authorized designee, you may not copy or use it, or disclose it to anyone else. If you received it in error please notify us immediately and then destroy it.
RE: naming conventions for Oracle/Unix vs. SQL Server
The only way to insert a column name in mixed case with Oracle is to enclose the create statements column names in "double-quotes".. This also means that when selecting from the created table, you have to enclose the mixed case column name in double quotes as well.. Ad-hoc type queries could go wrong all the time if people forget to do this.. There may be better reasons not to - but this is the one that jumps to mind for me.. HTH Mark === Mark Leith | T: +44 (0)1905 330 281 Sales & Marketing | F: +44 (0)870 127 5283 Cool Tools UK Ltd | E: [EMAIL PROTECTED] === http://www.cool-tools.co.uk Maximising throughput & performance -Original Message- [EMAIL PROTECTED] Sent: 29 July 2002 16:59 To: Multiple recipients of list ORACLE-L Guys, 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! -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mark Leith INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: naming conventions for Oracle/Unix vs. SQL Server
Title: RE: naming conventions for Oracle/Unix vs. SQL Server Paula, Someone will correct me if I'm wrong, but if you create tables and columns with mixed case within Oracle, then your developers will need to refer them surrounded by double quotes: like: SQL> create table "NameTable"("FirstName" varchar2(10)); Table created. SQL> descr "NameTablee"ERROR:ORA-04043: object "NameTablee" does not exist SQL> descr "NameTable" Name Null? Type - --- FirstName VARCHAR2(10) SQL> select firstname from "NameTable";select firstname from "NameTable" *ERROR at line 1:ORA-00904: invalid column name SQL> select "FirstName" from "NameTable"; no rows selected SQL> Really Sucks! Tom Mercadante Oracle Certified Professional -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]Sent: Monday, July 29, 2002 11:59 AMTo: Multiple recipients of list ORACLE-LSubject: RE: naming conventions for Oracle/Unix vs. SQL Server Guys, 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!
RE: naming conventions for Oracle/Unix vs. SQL Server
Title: RE: naming conventions for Oracle/Unix vs. SQL Server Guys, 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!