Case sensitive object naming in PL/SQL
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
At the same time some people might create table
CREATE TABLE myTable(myColumn NUMBER);
which would become
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?
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.