How to lower case objects in Oracle database?

Question

Is there any way to name my Tables,Columns in the Oracle DB in lower case letters?

I search a lot and some recommendations say i should use double quotes in the creation like this :

CREATE TABLE "test" (a number); 

The problem here is :

It forces me to decorate all my tables with double quotes through querying !

If i write :

SELECT * FROM test ;  

I will get syntax error.

I want to name all my tables , fields as lower case because i will use this in my model through ORM (Entity Framework) which use (Pluralization and Singularization ) feature , so if i have Table like this :

CITY according to Oracle convention .

The equivalent will be CITY entity and the pluralization as navigation property will be CITies !!

Answer 1

[TL;DR] The simplest thing to do is to never use double quotes around object names and just let oracle manage the case-sensitivity in its default manner.

Oracle databases are, by default, case sensitive; however, they will also, by default, convert everything to upper-case so that the case sensitivity is abstracted from you, the user.

CREATE TABLE tEsT ( column_name NUMBER );

Then:

SELECT COUNT(*) FROM test;
SELECT COUNT(*) FROM Test;
SELECT COUNT(*) FROM TEST;
SELECT COUNT(*) FROM tEsT;

Will all give the same output and:

SELECT * FROM USER_TABLES;

Outputs:

TABLE_NAME
----------
TEST 

(Note the table name is in upper case).

If you use double quotes then oracle will respect your use of case in the table name:

CREATE TABLE "tEsT" ( column_name NUMBER );

and:

SELECT * FROM USER_TABLES;

Outputs:

TABLE_NAME
----------
TEST 
tEsT

(Note: there are now two tables named TEST and tEsT and oracle has respected the case sensitivity of the second one).

Answer 2

It's a really horrible and arguably unnecessary hack but I suppose you could create a lower-case (are therefore quoted) synonym for your unquoted table name:

create table test (a number);
create synonym "test" for test;

Then all of these would work:

select * from "TEST";
select * from TEST;
select * from TeSt;
select * from test;
select * from "test";

Or if you really, really want to do the same with column names you could use a view:

-- drop synonym "test";
create view "test" as
select a as "a" from test;

Your ORM might be happier with a view if it's using the data dictionary to find the columns. But it still might not like it. If it does then it can use the quoted lower-case names, while all your other queries canuse the base unquoted identifiers.

It still seems likely to cause confusion at some point though.