topic Re: Is there an option in Oracle that will return the data in upper or lower case ? in Operating System - HP-UX

Is there an option in Oracle that will return the data in upper or lower case ?

Henrique Silva_3 — Sat, 19 Jul 2003 13:32:51 GMT

I have no access to the query itself, just to the data.

This app has to authenticate users based on their emails, however, some emails were entered manually while others automatically, and instead of using SEA, some emails look like this :

Joe.Doe@domain.com while others look like
joe.doe@domain.com

NOW, here is my problem.

I would like to be able to authenticate the users by having returned data being either upper or lower case. Is there a global option that will do that.

NOW, the conundrum here is that if this is a global option, then ALL my data for all other apps will be returned upper or lower case also, and that is not acceptable.

So, how can I achieve this without having to go to the app owner that needs this to simply add a function on his query toupper() or tolower() to compare the entries ? This will require code modification and we do not own the code. They do not have the same problem with Sql Server. It seems to work there, but I think it is because sqlserver is NOT case sensitive as Oracle is. Am I wrong here ?

ALso, is there even such an option on oracle to do this ? Is there an option to do this ONLY FOR A SESSION ? That would seem to cure the problem, for we could do it only for that particular schema !!!!!

Anyways, please help me understand my options so that I can pass it along to the app owner here !!!

Thanks,

Henrique

Re: Is there an option in Oracle that will return the data in upper or lower case ?

Brian Crabtree — Sun, 20 Jul 2003 00:10:08 GMT

Henrique,

You could do something like the following:

select * from contact where upper(email) = upper(:1);

That should make your application do the work. "lower" would work as well.

Thanks,

Brian

Re: Is there an option in Oracle that will return the data in upper or lower case ?

Wodisch — Sun, 20 Jul 2003 16:19:27 GMT

Hello Henrique,

how about build a "view" for those queries where you need everything in upper or lower case?
And the view is basically just a simple "select", but with "lower()" resp. "upper()" on those columns...
Then your data tables can stay as they are.

FWIW,
Wodisch

Re: Is there an option in Oracle that will return the data in upper or lower case ?

Yogeeraj_1 — Sun, 20 Jul 2003 17:24:01 GMT

hi,

A view should solve the "problem".

If you want it for a particular user, you can create the view "locally" for the user so that it takes precedence however this depends on the application design (is using public synonyms, etc).

you can also rename the table and create the view with the same name.

hope this helps!

regards
Yogeeraj

Re: Is there an option in Oracle that will return the data in upper or lower case ?

Reinhard Burger — Mon, 21 Jul 2003 05:23:36 GMT

Hi Hendrique

All the given answers work fine but be aware of one improtant thing :
Using functions (Like upper or lower) in views and/or select statements can impact the time needed to retrieve the data out of ORACLE as it may be that ORACLE is not able to use indexes on data which has been transformed by a function. As far as i know it must be ORACLE 9 or at least 8i to use indexes also on functions.
This means if the username in your db is indexed and you select it by using a function then you will end up with a full table scan. This does not matter if there just a few rows in the table., but generally it can be a problem. ( I learned it the hard way with big tables).

Re: Is there an option in Oracle that will return the data in upper or lower case ?

Graham Cameron_1 — Mon, 21 Jul 2003 06:25:28 GMT

To answer your specific questions:

There is no "global" or sessionwise Oracle parameter which will automatically upshift data on retrieval.

The above postings are correct but if you can't change the source they may not help you.

Do you have the option to add a trigger to the table to upshift on insert/update, so that the data is always stored in upper case ?
We do this on several tables (also we remove control chars, they seem to creep in all over the place). I can post example code if this would help.

-- Graham

Re: Is there an option in Oracle that will return the data in upper or lower case ?

Henrique Silva_3 — Mon, 21 Jul 2003 12:35:56 GMT

Thank you all for your answers.

In my case, a view seems to be the way to go, since I have no access to the app code, and I am REALLY, not supposed to change the table structures that belongs to the schemas. The app will not be supported if they find any foreign object in there. The way we are solving this is by only adding a few things that we need, and IF we need to supply a dump of the DB for support purposes, we drop those objects, since they are mostly indexes.

I am running 8i, and the field in particular is a uniquely indexed field, so, performance should not be a problem :-)

Thanks again and Graham, would you supply my with some examples of your code as well ?

Cheers,

Henrique

Re: Is there an option in Oracle that will return the data in upper or lower case ?

Yogeeraj_1 — Mon, 21 Jul 2003 16:45:47 GMT

HI,

Just to summarise on the possibilities,

In SQLserver it is all or nothing. Either every search is case insensitive or not. That is not necessarily a good thing either.

The ways to do the case insensitive search in Oracle would be:

A. function based indexes.
create index emp_upper_idx on emp(upper(ename));

B. a shadow column that is indexed and maintained by a trigger.

e.g.

alter table emp add upper_name varchar2(30);
create trigger emp_trigger
after insert or update on emp for each row
begin
:new.upper_ename := upper(:new.ename);
end;
/
create index upper_ename on ename(upper_ename);

select * from emp where upper_ename = 'KING';

C. intermedia.

D. using an index organized table you maintain.

create table upper_ename
( ename varchar2(30),
rid rowid, primary key (ename,rid) ) organization index;

create trigger emp_trigger
after insert or update on emp for each row
begin
if (updating and nvl(:old.ename,'x') <> nvl(:new.ename,'x'))
then
delete from upper_ename where
ename = upper(:old.ename) and rid = :old.rowid;
insert into upper_ename values
(upper(:new.ename),:new.rowid );
elsif ( inserting )
then
insert into upper_ename values
(upper(:new.ename),:new.rowid );
end if;
end;

and then query:

select * from
emp where rowid in ( select rid
from upper_ename
where ename = 'KING' );

or
select emp.*
from emp, upper_emp
where emp.rowid = upper_emp.rid
and upper_emp.ename = 'KING';

(thats much like a function based index but your application must be aware of it).

I believe the last solution(D - trigger) can still help....

hope this helps too!

regards
Yogeeraj

Re: Is there an option in Oracle that will return the data in upper or lower case ?

Graham Cameron_1 — Tue, 22 Jul 2003 06:32:12 GMT

Henrique

There are 2 files.

I attach the larger one and put the smaller one in line (because I can't seem to attach > file).

The attachment is a function which removes control chars and optionally upshifts.

Inline below is an example trigger from one of our tables, STREET.

Hope this helps.

-- Graham

CREATE OR REPLACE TRIGGER TRG_STREET_PRINTABLE
BEFORE INSERT OR UPDATE
ON STREET
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW
BEGIN
-- Remove all control chars from potentially vulnerable fields, by calling
-- the corresponding function.
-- The 2nd argument ('Y') means remove CR from input, and the 3rd (also 'Y') means upshift.
:new.DEPENDANT_LOCALITY := fn_remove_control_chars (:new.DEPENDANT_LOCALITY, 'Y', 'Y') ;
:new.DEPENDANT_THOROFARE := fn_remove_control_chars (:new.DEPENDANT_THOROFARE, 'Y', 'Y') ;
:new.DOUBLE_DEPENDANT_LOCALITY := fn_remove_control_chars (:new.DOUBLE_DEPENDANT_LOCALITY, 'Y', 'Y') ;
:new.THOROFARE := fn_remove_control_chars (:new.THOROFARE, 'Y', 'Y') ;
END;
/