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

Breadcrumb

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

Question and Answer

Connor McDonald

Thanks for the question, Stepan.

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

Answered by: Connor McDonald - Last updated: January 07, 2016 - 12:12 pm UTC

Category: Database - Version: 11.2

Viewed 1000+ times

Whilst you are here, check out some content from the AskTom team: When local partitions aren't

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 we 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.

and you rated our response

  (4 ratings)

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

Reviews

probably an overstatement

January 06, 2016 - 4:20 pm UTC

Reviewer: Sokrates from Kaiserslautern, Germany

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

Followup  

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

January 06, 2016 - 8:32 pm UTC

Reviewer: Andrew from Michigan, USA

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

Followup  

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.

January 06, 2016 - 11:17 pm UTC

Reviewer: A reader

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

Followup  

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

January 07, 2016 - 10:40 am UTC

Reviewer: Stepan RYBAR

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

Followup  

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)

:-)