Re: case-insensitive issues

<stvchien0_at_gmail.com> a écrit dans le message de news: qrsiv25j7v98dk3dbhja93ebhpkud6l40q_at_4ax.com...
| Hi,
|
| We're currently porting the database from MS SQL Server 2000/2005 to
| Oracle 10g2. After doing some research, we know that we can handle the
| case-insensitivity search. However, how about "INSERT"? Let's say that
| we have the tables as below.
|
| CREATE TABLE MyTable (
| ObjId NUMBER(2) NOT NULL,
| ObjType VARCHAR2(16) NOT NULL,
| Desc VARCHAR2(128));
|
| ALTER TABLE MyTable ADD CONSTRAINT PK_MyTable PRIMAY KEY(ObjId,
| ObjType);
|
| How could I do a "case-insensitive" INSERT?
|
| For example, the second statement below will get rejected by
| Oracle...
|
| INSERT INTO MyTable(ObjId, ObjType) VALUES(1, 'TYPE1');
| INSERT INTO MyTable(ObjId, ObjType) VALUES(2, 'type1');
|
| Is the FUNCTION-BASED index the only way to go?
|
| Thanks for any advise!
|
| - Steve

I don't see why the second insert should be rejected when the primary is on (ObjId, ObjType) and the 2 statements have a different objid.

Apart from using a fbi, you can also use a trigger to force upper case:

SQL> create or replace trigger my_trig
  2 before insert or update of objtype on mytable   3 for each row
  4 begin
  5 :new.objtype := upper(:new.objtype);   6 end;
  7 /

Trigger created.

SQL>
SQL> INSERT INTO MyTable(ObjId, ObjType) VALUES(1, 'TYPE1');

1 row created.

SQL> INSERT INTO MyTable(ObjId, ObjType) VALUES(1, 'type1'); INSERT INTO MyTable(ObjId, ObjType) VALUES(1, 'type1') *
ERROR at line 1:
ORA-00001: unique constraint (MICHEL.PK_MYTABLE) violated

Regards
Michel Cadot Received on Thu Mar 15 2007 - 11:54:58 CDT