Case Sensitivity in Oracle

[jbayly]

Is it at all possible to turn off the case sensitivity in Oracle, so any querys are not reliant on the case.

 

[lewisp]

You can use UPPER or LOWER in your queries, but beware the performance may be affected.

 

[jbayly]

This is what i have been doing so far, however i need to account for this in all of my Business Objects User Prompts and its is come somewhat laborious.

Is there any way of stipulating this at the database level, without holding all data in the database as either upper or lower case?

Many thanks

 

[oradba101]

Hi, jbayly

I believe because Oracle is multi-platform that this is not possible as UNIX will distinguish data that is uppercase and lowercase. For example:

LAST_NAME FIRST_NAME DOB
--------- ---------- -----------
SMITH JOHN 01-JAN-1950
smith john 01-JAN-1950

Even if you set the primary key to the above three columns, I believe that UNIX would count them as two different records whereas Windows would not allow the secind row to be saved.

Regards,

William Chadbourne
Oracle DBA

 

[SantaMufasa]

J,

The direct answer to your question ("Is there any way of stipulating this at the database level, without holding all data in the database as either upper or lower case?"), the answer is 'No'. As Lewis mentions, we typically use either UPPER() or LOWER()...a possible hassle, yes...but preferrable over the database re-creation hassles if you change your sort preferences.


Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.

 

[dbtoo2001]

Did you ask BOB?

http://www.forumtopics.com/busobj/viewtopic.php?t=16835&highlight=case+insensitive+prompts

 

[SantaMufasa]

David (DBToo),

I read through the link, and besides the focus there being on DB2 (no pun intended), I cannot see how that link helps J. Did I miss something important?

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.

 

[dbtoo2001]

I offered up the link because jbayly may not be aware of that resource (they didn't mention posting at any other sites.) The link is to rather old information, and there may be new information that could be obtained through Business Objects resources. The question posted here, can Oracle force case?, has been thoroughly answered by you, william and lewis.

Thought I would just throw in the possibility that someone in the BO community may help with a better BO solution.

jbayly, there is a business objects forum at this site, too. I didn't see any solutions posted there.

Business Objects should have a solution by, since this is a common problem which has existed since the time they were founded.

So, dave, I don't think you missed anything.

 

[jbayly]

Thanks for all your help, it seems I will have to keep going with changing my objects.

Although I did find a handy update tool on the BO forum.