DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 17

Thread: case sensitive oracle database

  1. #1
    Join Date
    May 2001
    Posts
    16

    case sensitive oracle database

    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.

  2. #2
    Join Date
    Jul 2000
    Posts
    521
    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.
    svk

  3. #3
    Join Date
    May 2001
    Posts
    16
    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.

  4. #4
    Join Date
    May 2001
    Posts
    736
    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.

  5. #5
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    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.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  6. #6
    Join Date
    May 2002
    Posts
    2,645
    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.
    Last edited by stecal; 08-31-2003 at 03:05 PM.

  7. #7
    Join Date
    Jul 2001
    Location
    Slovenia
    Posts
    422
    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
    Code:
    create table "Bla" ("Col" number);
    , you also must
    Code:
    select "Col" from "Bla"
    because
    Code:
     select Col from Bla
    won't work.
    Tomaž
    "A common mistake that people make when trying to design something completely
    foolproof is to underestimate the ingenuity of complete fools" - Douglas Adams

  8. #8
    Join Date
    May 2001
    Posts
    16
    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.

  9. #9
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    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.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  10. #10
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width