Re: case-insensitive issues

From: EdStevens <quetico_man_at_yahoo.com>
Date: 15 Mar 2007 11:56:07 -0700
Message-ID: <1173984967.844564.180280@e65g2000hsc.googlegroups.com>

On Mar 15, 10:35 am, stvchi..._at_gmail.com wrote:
> 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

Methinks there's something else you're not showing. First, your CREATE TABLE statement, as posted, doesn't work ...

SQL> CREATE TABLE MyTable (

  2                        ObjId   NUMBER(2)    NOT NULL,
  3                        ObjType VARCHAR2(16) NOT NULL,
  4                        Desc    VARCHAR2(128)
  5                        );
                      Desc    VARCHAR2(128)
                      *

ERROR at line 4:
ORA-00904: : invalid identifier

Duh, 'desc' is a reserved word. Correct and try again:

SQL> CREATE TABLE MyTable (

  2                        ObjId   NUMBER(2)    NOT NULL,
  3                        ObjType VARCHAR2(16) NOT NULL,
  4                        Desx    VARCHAR2(128)
  5                        );

Table created.

SQL> --
SQL> ALTER TABLE MyTable
  2 ADD CONSTRAINT PK_MyTable
  3 PRIMARY KEY(ObjId, ObjType);

Table altered.

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

1 row created.

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

1 row created.

SQL> --
SQL> col objtype for a10
SQL> col desx for a20
SQL> select * from mytable;

     OBJID OBJTYPE    DESX
---------- ---------- --------------------
         1 TYPE1
         1 type1

2 rows selected.

SQL> Received on Thu Mar 15 2007 - 13:56:07 CDT