| Table Definition and Data problem [message #370967] |
Mon, 24 July 2000 18:38  |
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  |
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 |
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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
