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
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!
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
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: 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
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 -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
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
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
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).
