Skip navigation.

Case sensitive object naming in PL/SQL

Hello,

This is my first entry here.
I would like to criticize criticism for case sensitive object naming in Oracle and PL/SQL.

As you might know - Oracle is converting object names to uppercase by default. Whenever you create object like
CREATE TABLE my_table(my_column NUMBER);
it would be created as MY_TABLE(MY_COLUMN NUMBER);
At the same time some people might create table
CREATE TABLE myTable(myColumn NUMBER);
which would become MYTABLE(MYCOLUMN NUMBER);

What is tricky about the above is the way Oracle is parsing queries. Is query 'SELECT * FROM myTable' the same as 'SELECT * FROM MYTABLE'? It is not :(
In addition you would never know if the column is USER_NAME (like in V$SESSION) or rather USERNAME. Oracle has fallen into this issue - so other people would fall into it as well.

You might point out that this is a standard to use case insensitive naming. Then why query parser is not case insensitive?

Some might say that my naming convention is not clear enough. I would answer 'If people can write the same thing in multiple ways - they would eventually - and in that way conventions that are not auto-enforced are pointless'.

Some people criticize the need of using " character for case sensitive naming. But is this an issue of developers approach or rather an Oracle issue?

Many of the Oracle / PL/SQL applications cannot support case insensitive naming in some fragments of their code in some versions of the applications or plugins for them... This is often hard-coded to query list of the tables, then query list of columns in a particular tables... after lower/upper case conversion...
Again: Is this an issue of the developer or rather an issue of the application? Have you ever heard about SQL injections where string other than expected is executed? How can I trust such application if it is executing something different than I have requested?

Drawback of using case sensitive object naming in Oracle:
- You need extra " around the name
- Faulty support in applications
- You need to be consistent - once you start it there is no easy way back (or is there?)

Have you something to add to the items above?

P.S.
Currently I am using case insensitive names for almost-everything and the case sensitive names for items that should never be modified by not-me.

The most important drawback

The most important drawback of using quoted notation (like: "Employees") is that there's no check for reserved words. You could have a table called "ORDER BY hiredate DESC", or a column called "SELECT whEre from", a date column called "DATE"...

So I'd rather have the conventional naming.

Drawback or a good thing?

You say that it is a drawback - but I might not agree (although I see your point).
If you start using case sensitive naming - stick with it. That is the same for variable naming, etc. - once you have a direction - keep on going there.
If I remember correctly then Oracle has some pseudo views with a columns like "NAME" or "TYPE". Although this is colored in Oracle SQL Developer and it is a reserved keyword... Is it good then?