Printable View
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.
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.
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.
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.
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.Quote:
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.
Quote:
Here I am taking about the table names, column names and all the database objects only, I am not talking about the data.
The file tnsnames.ora does not have anything to do with this question.Quote:
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.
Yes, when you enclose object names in double quotes, oracle will use them literally, otherwise they will get uppercased.Quote:
Originally posted by rkhatri
Basically I want to know "can I create a case sensitive database in Oracle ?"
So, if you, you also mustCode:create table "Bla" ("Col" number);
becauseCode:select "Col" from "Bla"
won't work.Code:select Col from Bla
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.
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.
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
If you create a table "Employee" then
select * from employee
You'll get ora-942 table or view does not exist
select * from "Employee" is the only way to address that table
Drop the double quotes.
If the software user is naming db objects, don't use double quotes!
First of all, SQLServer means case-sensitive *data*, not object names. You know - as opposed to Oracle, where the data is *always* (very unfortunately) case-sensitive.
I don't know of any database where the object names are case-sensitive.
You may want to do a little more digging into database details if you truly want to make a product that works across databases ( a really bad idea, IMHO )
- Chris
I have seen the 'case sensitivity' issue before, usually with software developped for multiple databases where the other database (say SQL Server) is case sensitive. Almost always the same situation (a software house with a canned project). I don't like it, but it is your coding headache.
As for the customer worry, why not just use a definition export as part of your installation process. Create the objects with case sensitivity, export the definitions (ROWS=N) and use that file for installation. If the client changes the installation procedure or objects afterward, they violated your support aggreement, yada yada yada.
As for the database, the Instance name may be case insensitive (depending on platform, release etc.) but the Oracle doesn't use databases the way other RDBMSs do. A SQL Server instance contains multiple databases which contain schemas. Oracle instances just contain schemas. SQL Server databases can be case sensitive but there is no corresponding object in Oracle.
jrpm - are you talking data or object names when you say you've seen the case-sensitivity issue before?
- Chris
MS Access!Quote:
Originally posted by chrisrlong
I don't know of any database where the object names are case-sensitive.
Is that a database? Just kidding... well maybe not.
I just pulled up an Access database, changed the case of one of the object names in one of my queries and ran it and it still ran fine. What makes you think it's case-sensitive?Quote:
Originally posted by marist89
MS Access!
- Chris