- Community Home
- >
- Servers and Operating Systems
- >
- Operating Systems
- >
- Operating System - HP-UX
- >
- Re: Is there an option in Oracle that will return ...
Categories
Company
Local Language
Forums
Discussions
Forums
- Data Protection and Retention
- Entry Storage Systems
- Legacy
- Midrange and Enterprise Storage
- Storage Networking
- HPE Nimble Storage
Discussions
Discussions
Discussions
Forums
Forums
Discussions
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
- BladeSystem Infrastructure and Application Solutions
- Appliance Servers
- Alpha Servers
- BackOffice Products
- Internet Products
- HPE 9000 and HPE e3000 Servers
- Networking
- Netservers
- Secure OS Software for Linux
- Server Management (Insight Manager 7)
- Windows Server 2003
- Operating System - Tru64 Unix
- ProLiant Deployment and Provisioning
- Linux-Based Community / Regional
- Microsoft System Center Integration
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Community
Resources
Forums
Blogs
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО07-19-2003 06:32 AM
тАО07-19-2003 06:32 AM
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
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО07-19-2003 05:10 PM
тАО07-19-2003 05:10 PM
SolutionYou 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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО07-20-2003 09:19 AM
тАО07-20-2003 09:19 AM
Re: Is there an option in Oracle that will return the data in upper or lower case ?
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО07-20-2003 10:24 AM
тАО07-20-2003 10:24 AM
Re: Is there an option in Oracle that will return the data in upper or lower case ?
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО07-20-2003 10:23 PM
тАО07-20-2003 10:23 PM
Re: Is there an option in Oracle that will return the data in upper or lower case ?
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).
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО07-20-2003 11:25 PM
тАО07-20-2003 11:25 PM
Re: Is there an option in Oracle that will return the data in upper or lower case ?
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО07-21-2003 05:35 AM
тАО07-21-2003 05:35 AM
Re: Is there an option in Oracle that will return the data in upper or lower case ?
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО07-21-2003 09:45 AM
тАО07-21-2003 09:45 AM
Re: Is there an option in Oracle that will return the data in upper or lower case ?
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО07-21-2003 11:32 PM
тАО07-21-2003 11:32 PM
Re: Is there an option in Oracle that will return the data in upper or lower case ?
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;
/