-1

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 !!

Anyname Donotcare
  • 11,113
  • 66
  • 219
  • 392
  • 1
    I would definitely avoid using double quotes to enforce lower-case table and column names. While it may be aesthetically unpleasing to see "CITies" in your ORM, the pain of having to use double quotes on every SQL statement is a lot worse. And there is no other way. – Tony Andrews Feb 22 '16 at 14:28
  • you are searching in the wrong direction, You have to do changes in your ORM instead of to figure how to change the ORACLE behavore. Take a look at this link : http://stackoverflow.com/questions/6680957/resolving-naming-convention-conflict-between-entities-in-ef4-and-our-database-st – Walid El Oubaha Feb 22 '16 at 14:52
  • @WalidElOubaha Coversely - refer to http://stackoverflow.com/questions/10807935/how-to-set-table-names-and-columns-as-case-sensitive-in-oracle-11g which suggests that you can use any (unquoted) case and it will work – MT0 Feb 22 '16 at 15:11
  • @MT0 Yes ofcorse, as you've shown in your answer, but you have to query the table with a quoted string. – Walid El Oubaha Feb 22 '16 at 15:17

2 Answers2

5

[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).

MT0
  • 143,790
  • 11
  • 59
  • 117
  • I want to lower case , without using `double quotes` in my queries – Anyname Donotcare Feb 22 '16 at 14:31
  • If I create table like this :`CREATE TABLE "tEsT"`.I want to write query like this : `SELECT * FROM tEsT` – Anyname Donotcare Feb 22 '16 at 14:32
  • @AnynameDonotcare - you can't do that. Why don't you want to just create it unquoted - then you can use any case in your queries, as MTO has shown? – Alex Poole Feb 22 '16 at 14:34
  • Just use `CREATE TABLE test (...)` and write `SELECT * FROM test` - you do not need to worry that oracle is converting everything to upper-case as it will happen in the background. – MT0 Feb 22 '16 at 14:35
  • 1
    @AlexPoole: The problem is `EF` as `ORM` mapping the table names and fields as they exist in the db , and it's a huge effort to rename all my entities in the application in addition to that , if i rename my entities , the navigation properties as plural need to be renamed as well !! – Anyname Donotcare Feb 22 '16 at 14:37
  • @MT0: If i use `CREATE TABLE test (...)` then the table name will be `TEST` and the `ORM` will use it as `TEST` not `test` – Anyname Donotcare Feb 22 '16 at 14:38
  • Take a look here, you have to change in ORM instead of Oracle. http://stackoverflow.com/questions/6680957/resolving-naming-convention-conflict-between-entities-in-ef4-and-our-database-st – Walid El Oubaha Feb 22 '16 at 14:54
  • @AnynameDonotcare [This question](http://stackoverflow.com/questions/9445678/entity-framework-with-mysql-table-capitalization-issue-between-linux-and-window) suggests adding an annotation to your model object for the table name. Will that work? – MT0 Feb 22 '16 at 15:34
  • @MT0 this. Will work just magic if i use code first design , the problem which made me search a http://stackoverflow.com/q/35265170/418343way to fix it in the db is : i use db first design – Anyname Donotcare Feb 22 '16 at 16:19
1

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.

Alex Poole
  • 183,384
  • 11
  • 179
  • 318
  • If you could advice me :) accept the pluralization names of my entities according to my `ORM` ex:`CITies` .. Or create views and synonyms for all my tables ? – Anyname Donotcare Feb 22 '16 at 15:08
  • 2
    Depends how many tables you have, and how much of a maintenance headache you want to have. Also worth checking if the behaviour of the ORM can be changed, either to uppercase the pluralised name or to not quote the identifiers when talking to the DB. – Alex Poole Feb 22 '16 at 15:11