Case insensitive in oracle 12c [message #676872] |
Wed, 24 July 2019 13:24 |
evoradba
Messages: 144 Registered: April 2005 Location: Canada
|
Senior Member |
|
|
Hello
I have an oracle 12c running on linux, i would like to have the database case insensitive for searches, i do have set
SQL> SELECT *
FROM NLS_DATABASE_PARAMETERS
WHERE PARAMETER LIKE '%CHARACTERSET';
PARAMETER
--------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
NLS_NCHAR_CHARACTERSET
AL16UTF16
NLS_CHARACTERSET
AL32UTF8
our code then for searches i have a trigger set for when users log in it will enable the uft8
create or replace
trigger Connect
AFTER LOGON ON SCHEMA
DECLARE
BEGIN
EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_SORT=''BINARY_CI''';
EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_COMP=''LINGUISTIC''';
END
Connect;
/
however, this is ok but we have issues with loaders as they hang when we have the trigger in place as it uses the same user ID, the loaders ignore the indexes and do a table scan, i do know we can create indexes using
create index
nlsci1_gen_person
on
MY_PERSON
(NLSSORT
(PERSON_LAST_NAME, 'NLS_SORT=BINARY_CI')
but, we can't change the code for every single select / and loader
Is there anything else?
|
|
|
Re: Case insensitive in oracle 12c [message #676874 is a reply to message #676872] |
Wed, 24 July 2019 14:16 |
|
Michel Cadot
Messages: 68658 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
BlackSwan wrote on Wed, 13 February 2019 00:32When do you plan to start following the Posting Guidelines?
Michel Cadot wrote on Wed, 29 May 2019 21:47
As already told and repeated to you:
Michel Cadot wrote on Mon, 27 October 2014 21:49
...
Please How to use [code] tags and make your code easier to read.
Michel Cadot wrote on Mon, 27 October 2014 21:56
...
Please read and APPLY OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
BlackSwan wrote on Tue, 12 February 2019 16:03...
Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read
...
And format your query, if you don't know how to do it, learn it using SQL Formatter.
EdStevens wrote on Thu, 30 May 2019 13:54I think I have a pretty good idea, but until you format your code, as you've been asked repeatedly in multiple threads, I will say no more.
cookiemonster wrote on Thu, 30 May 2019 14:12Looks like the OP has fixed it according to the update.
Michel Cadot wrote on Thu, 30 May 2019 14:22
... and didn't bother to tell us what was the problem and solution.
|
|
|