OraFAQ Forum: Server Administration » Table Definition and Data problem

Home » RDBMS Server » Server Administration » Table Definition and Data problem

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

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

Report message to a moderator

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

Report message to a moderator

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.

Report message to a moderator

Previous Topic:	Outer join with 3 tables
Next Topic:	Table Definition and Data problem

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Oct 31 00:37:53 CDT 2025