Re: Problem Related to Case Sensitivity in Oracle....

Hi Awadesh,
there are two solutions for your problem. The first one is using trigger for converting the text to upper case. The second one is enforcing a check constraint that reject a text value that is not in upper case (my preference). Example: constraint CK_UPCASE check (upper(field_name)=field_name). HIH

Regards,

Alex Wijoyo

At 05:10 25/11/2000 -0800, you wrote:
>Dear Sir/Madam,
>
> I am new to Oracle. I Have a Problem Related with the Case Sensitive
>Nature of Oracle 8i.
>
> I Had an Application in VB and MS-Access as Back-End. We Were Facing
>Problem with the MS-Access Database as it has become so Large (about 950
>MB). Now we have transferred MS-Access Database to Oracle-8i. But This
>created a problem for Us...I am describing my problem...
>
>We Have A Company Table which Contains Fields Like "CompanyName",
>"ContactPerson", "Address", "City", "State", "Pin", "Country", "Phone",
>"Fax" etc....
> This table has UNIQUE key indexed on "CompanyName" Field. But It
> Allows
>Two Companies to be entered with the same Name (With Different case).
>For Example... I Have Already a Record With CompanyName = "ABC Overseas".
>Now When I am entering another Record with the Name "abc overseas". Oracle
>Allows to do that...But this doesn't happen with MS-Access or SQL (They
>Treat these two companies same). I just want to know that how to make
>oracle to Treat these two companies as SAME. I don't want these two
>companies to be entered.
>
> I Can use query "select Companyname from company where
> UPPER(companyname)
>= 'UPPER("abc overseas")' to check whether a company already exist with
>this Name... But This makes Application Too slow as there are about 50,000
>Records in the table and application is being used by so many users.
>
> So Please suggest me the way to tackle this problem. Is there any
> Setting
>in Oracle by which Oracle will not be Case Sensitive.
>
> Ur Any help or support can make my work very easy...
>
> Please Suggest me ASAP...
>
> Thanks and Regards
> -AwadheshDear Mr. Avinash,
>
> I am new to Oracle. I Have a Problem Related with the Case Sensitive
>Nature of Oracle 8i.
>
> I Had an Application in VB and MS-Access as Back-End. We Were Facing
>Problem with the MS-Access Database as it has become so Large (about 950
>MB). Now we have transferred MS-Access Database to Oracle-8i. But This
>created a problem for Us...I am describing my problem...
>
>We Have A Company Table which Contains Fields Like "CompanyName",
>"ContactPerson", "Address", "City", "State", "Pin", "Country", "Phone",
>"Fax" etc....
> This table has UNIQUE key indexed on "CompanyName" Field. But It
> Allows
>Two Companies to be entered with the same Name (With Different case).
>For Example... I Have Already a Record With CompanyName = "ABC Overseas".
>Now When I am entering another Record with the Name "abc overseas". Oracle
>Allows to do that...But this doesn't happen with MS-Access or SQL (They
>Treat these two companies same). I just want to know that how to make
>oracle to Treat these two companies as SAME. I don't want these two
>companies to be entered.
>
> I Can use query "select Companyname from company where
> UPPER(companyname)
>= 'UPPER("abc overseas")' to check whether a company already exist with
>this Name... But This makes Application Too slow as there are about 50,000
>Records in the table and application is being used by so many users.
>
> So Please suggest me the way to tackle this problem. Is there any
> Setting
>in Oracle by which Oracle will not be Case Sensitive.
>
> Ur Any help or support can make my work very easy...
>
> Please Suggest me ASAP...
>
> Thanks and Regards
> -Awadhesh
>--
>Please see the official ORACLE-L FAQ: http://www.orafaq.com
Received on Mon Nov 27 2000 - 02:06:18 CST