Skip to Main Content
  • Questions
  • Why should I NOT use database object's names in "CamelCase" (surrounded by quotes) manner?

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Stepan.

Asked: January 06, 2016 - 12:14 pm UTC

Last updated: January 07, 2016 - 12:12 pm UTC

Version: 11.2

Viewed 10K+ times! This question is

You Asked

Good afternoon!

I developed simple Oracle application in 11.2. I used for database object (tables, columns, views) names in "CamelCase" manner. But our admins refused to accept it, because "CamelCase" database object's names "cause problems to maintain Oracle database". They did not say why.

Can I ask You, if it is true, that "CamelCase" database object's names in quotes makes admins hard work? Why? I guessed, that it is just up to developer, if he want to use 'select * from "MyTable"' or 'select from MY_TABLE'.

I, as a developer, have many reasons why to use database object's names surrounded by quotes and CamelCase like:
a) keeping the same names for XML elements and attributes the same in XML, Java apllication and Oracle,
b) easy to migrate among databases in case rarely used reserved words in quotes, which (except ROWID) enable to use it almost anywhere,
c) enabling to use unabbreviated names due not using underscore in "MY_TABLE" (I heard, that 12.2 should have up to 160 characters of database objects names)
etc.

Thank You for Your answer in advance, Stepan

and Connor said...

There is no reason why you should cannot use camel case besides convention, ie, most people dont.

So for that reason alone, I generally dont recommend it - because (like any software product) you generally dont want to be in a "niche" community... because you're more likely to be the one that finds idiosyncratic bugs etc. If 99% of oracle customers use case-insenstive names, then they are not assisting you in any way when it comes bug discovery/resolution etc.

It can be a lonely place on a forum asking for help when no-one else does what you do :-)

The other reason I personally am not a fan, is that you now potentially can have a table: EMP, Emp, emp being three different things. Sounds ominous to me :-)

But the statement "cause problems to maintain Oracle database" is probably an overstatement.

Rating

  (4 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

probably an overstatement

Sokrates, January 06, 2016 - 4:20 pm UTC

true, but there may occur surprises, for example
sokrates@12.1 > create table EMP(i int);

Table created.

sokrates@12.1 > create table "Emp"(i int);

Table created.

sokrates@12.1 > create table "emp"(i int);

Table created.

sokrates@12.1 > exec dbms_stats.gather_table_stats(null, 'EMP'); dbms_stats.gather_table_stats(null, 'Emp'); dbms_stats.gather_table_stats(null, 'emp')

PL/SQL procedure successfully completed.

sokrates@12.1 > set null never
sokrates@12.1 > select table_name, last_analyzed from user_tables;

TABLE_NAME                     LAST_ANALYZED
------------------------------ --------------------
EMP                            2016-jan-06 17:17:08
Emp                            never
emp                            never

REM maybe not expected

Chris Saxon
January 07, 2016 - 1:25 am UTC

Yup... a nice reinforcement of:

"The other reason I personally am not a fan, is that you now potentially can have a table: EMP, Emp, emp being three different things. Sounds ominous to me :-) "

Others (especially DBAs) will not like you

Andrew, January 06, 2016 - 8:32 pm UTC

DBAs will have to revise lots of their scripts to accommodate your coding style. To expand on the example posted by Sokrates, here is what a DBA's automated script might look like and the result when CamelCase names are used:
SQL> create table emp  (i int);

Table created.

SQL> create table "Emp"(i int);

Table created.

SQL> create table "emp"(i int);

Table created.

SQL> 
SQL> select table_name from user_tables;

TABLE_NAME
------------------------------
EMP
Emp
emp

3 rows selected.

SQL> 
SQL> begin
  2  for x in (select table_name from user_tables)
  3  loop
  4  begin
  5    dbms_stats.gather_table_stats(null, x.table_name); 
  6  end;
  7  end loop;
  8  end;
  9  /

PL/SQL procedure successfully completed.

SQL> 
SQL> set null None
SQL> select table_name, last_analyzed from user_tables;

TABLE_NAME                     LAST_ANALYZED
------------------------------ --------------------
EMP                            06 Jan 2016 15:23:57
Emp                            None
emp                            None

3 rows selected.


IMNSHO, your should should have a set of standards, coding and database, that you should be following. The best standards are those that are written by and 'owned' by all concerned - developers and DBAs.

Forking your own standards that vary too far from the conventions make life harder for every one who comes after you.
Connor McDonald
January 07, 2016 - 1:29 am UTC

Its like the old quote:

"Always code your application as if the next person to maintain it is a psychopathic killer who knows where you live"

(source unknown)

It is not an Overstatement but a true one.

A reader, January 06, 2016 - 11:17 pm UTC

I would disagree with Conner respectfully.
Using those mixed cases with quotes is indeed a maintenance and a coding nightmare.

Just imagine how your select statement will be
Just imagine the where clauses where columns will need to be always case sensitive, and have to be provided with double quotes.

Do not go that route, never ever.
IMHO.

Keep it simple and case insensitive. Camel case is recipe for disaster.


Connor McDonald
January 07, 2016 - 1:28 am UTC

"I would disagree with Conner respectfully."

Dont worry - thousands do :-)

"Just imagine how your select statement will be
Just imagine the where clauses where columns will need to be always case sensitive, and have to be provided with double quotes. "

I think this is actually one of the "non-issues" with camelcase, in that, your SQL will simply fail to parse. The other reviews (eg the dbms_stats example) are perhaps the more ominous ones, ie, everything *looks* ok, but actually is not

Stepan RYBAR, January 07, 2016 - 10:40 am UTC

call dbms_stats.gather_table_stats(null, '"EMP"'); 
call dbms_stats.gather_table_stats(null, '"Emp"'); 
call dbms_stats.gather_table_stats(null, '"emp"'); 

begin
  for x in (select table_name from user_tables)
  loop
    begin
      dbms_stats.gather_table_stats(null, '"' || x.table_name || '"'); 
    end;
  end loop;
end;
/


Is it so hard to handle strings as strings and not numerics? So why the dbms_stats.gather_table_stats, I guess, does not encapsulate input parameter with double quotes inside itself? Is it not a bug of such procedure? Because the above code is not ideal, but usable. So the quick win scripts of devs and admins should limit the power of Oracle?

It is the similar devs work like: a) quick win CSV parsers with "choose delimiters char, which will never be inside Your data" (yes, SQL*Loader too), or b) "we will never store other than our national alphabet, so why Unicode", or c) "there will not be [a-zA-Z0-9/]+ in UNC"...
Yes, I want to be a psychopatic killer knowing the address of such devs and admins in such cases ;-)

"What is not forbidden, is allowed." What about the reverse statement in real life, would You enjoy it? So why not in IT?
Connor McDonald
January 07, 2016 - 12:12 pm UTC

"What is not forbidden, is allowed."

There's no law (where I live) that says you cannot swim in crocodile-infested waters. But I probably wouldn't do it because

i) very few other people seem to be doing it
ii) something might break (aka me)

:-)