case sensitive oracle database

[rkhatri]

I have to create a case-sensitive database in Oracle.

Here I am taking about the table names, column names and all the database objects only, I am not talking about the data.

I want to create a case sensitive database.

Then I will create table EMPLOYEE

I want whenever somebody writes

SELECT * FROM employee

it should say, no table with this name

if somebody writes
SELECT * FROM EMPLOYEE

it should give result.

First of all I want to know, is it possible in Oracle ?

Thanks.

[svk]

Yes it is possible. But, the other way round.

If you create a table "EMPLOYEE" (in upper case), people will be able to select from it even if they refer to it as "employee".

But, if you create a table as "employee" (in lower case), people will need to refer to it as "employee" only.

This applies to column names as well.

[rkhatri]

Thanks svk for your reply.

Basically I want to know "can I create a case sensitive database in Oracle ?"

Here I am not talking about the tables only.

Thanks.

[akhadar]

I don't think it really matters
See below TNSNAMES.ORA file

TEST.CRB.SE.COM.SA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = SRV_HDQ_DB1)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = rict)
)
)
Even though i Created the database as RICT still i can able to use the lowercase.Also from the user point of view as it depends on the connecting string not the SID.

[slimdave]

Originally posted by rkhatri
Thanks svk for your reply.

Basically I want to know "can I create a case sensitive database in Oracle ?"

Here I am not talking about the tables only.

Thanks.

Is this for a school science project or something? Perhaps you could let us in on the reason(s) why you want to do this.

[stecal]

Here I am taking about the table names, column names and all the database objects only, I am not talking about the data.

Originally posted by akhadar
I don't think it really matters
See below TNSNAMES.ORA file

TEST.CRB.SE.COM.SA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = SRV_HDQ_DB1)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = rict)
)
)
Even though i Created the database as RICT still i can able to use the lowercase.Also from the user point of view as it depends on the connecting string not the SID.

The file tnsnames.ora does not have anything to do with this question.

[TomazZ]

Originally posted by rkhatri
Basically I want to know "can I create a case sensitive database in Oracle ?"

Yes, when you enclose object names in double quotes, oracle will use them literally, otherwise they will get uppercased.
So, if you

create table "Bla" ("Col" number);

, you also must

select "Col" from "Bla"

because

 select Col from Bla

won't work.

[rkhatri]

We are developing a software product. This product will be run on Oracle.

Our worry is - if we create a case insensitive database and develop all our programs according to that (means use lower and upper cases in our programs to select the tables) and when we ship the product to our client, if he creates a case sensitive database, in that case our programs will not work properly(e.g. if table name is EMPLOYEE and in the select statement we write -> select * from employee
then this will not work if the database is case sensitive).

That is the reason I want to know first of all that
IS IT POSSIBLE TO CREATE A CASE SENSITIVE DATABASE IN ORACLE ?

for your informantion creating a case sensitive database is possible in Sql*server, it is done at the installation level, and after that you create as many databases on that installation, all will be case sensitive.

Also, here I do not want to go in the discussion of Oracle v/s SQL*server

Thanks.

[slimdave]

No-one in their right mind creates an Oracle database with case sensitivity in the object names -- I have never, ever seen it done.

Are your providing the database creation scripts yourselves?

Anyway, I would just create your product the regular way, with case-insensitive object names.

[davey23uk]

but if you so select * from EMPLOYEE or select * from employee you get the same results - doesnt matter what case you put in - no matter if you created the table in upper case or not