Home » RDBMS Server » Server Administration » Table Definition and Data problem
Table Definition and Data problem [message #370967] Mon, 24 July 2000 18:38 Go to next message
Suresh.N
Messages: 41
Registered: March 2000
Member
Hi,

I want to clarify about one doubt.

I konw very well Oracle Records ie Data is Case sensitive.

Like

"suresh" and "SURESH" are differnent. right?

I have one table called Test(Name Varcahr2(10));

I have given the primary key .

I have entered two record like

suresh and SURESH.

Ok.

I want to implement the Record are not case sensitive.

If i insert record like 'suresh' first it should not to allow to enter 'SURESH'.

I don not want to user Upper case or Lower case functions.

Is there any possible to do like this ?

Can u suggest me.

Thanks
Suresh.N
Re: Table Definition and Data problem [message #370968 is a reply to message #370967] Wed, 26 July 2000 04:23 Go to previous messageGo to next message
Geoff Devall
Messages: 1
Registered: July 2000
Junior Member
I believe the best way is to store the data twice. Once with the name you want to use, like Suresh and a second column which stores only the upper case value "SURESH".

The primary key is included on the second column. This will work but is not to efficient - someone may know something I don't, but this is the way I have seen this implemented in the past

Geoff Devall
Re: Table Definition and Data problem [message #370976 is a reply to message #370967] Thu, 27 July 2000 16:37 Go to previous message
Andrew again...
Messages: 270
Registered: July 2000
Senior Member
Create an "Insert or Update" trigger on the table.
something like the following:

CREATE OR REPLACE TRIGGER mytab_ins_upd
BEFORE INSERT
ON mytab
FOR EACH ROW
DECLARE
number_row INTEGER;
found_ok BOOLEAN;

CURSOR c1 (
v_col_name IN CHAR
)
IS
SELECT 1
FROM mytab
WHERE UPPER (col_name) = UPPER (v_col_name)
FOR UPDATE;
BEGIN
--++++++++++++++++++++++++++++++++++++++++++++++++
-- Check to see if name of any case mix if found
--++++++++++++++++++++++++++++++++++++++++++++++++
OPEN c1 (:NEW.col_name);
FETCH c1 INTO number_row;
found_name := c1%FOUND;
CLOSE c1;

IF found_name
THEN
RAISE_APPLICATION_ERROR (-20501, 'Name already found in my_tab', TRUE);
END IF;
END;
/

For the update case, you would need to exclude the current row you are updating from getting found by the cursor. e.g.
"and name != :new.name" or refer to the row by rowid would be better.
Previous Topic: Outer join with 3 tables
Next Topic: Table Definition and Data problem
Goto Forum:
  


Current Time: Fri Oct 30 03:33:12 CDT 2020