Home » SQL & PL/SQL » SQL & PL/SQL » Case insensitive in oracle 12c (RDBMS 12.2..0.1 on linux)
Case insensitive in oracle 12c [message #676872] Wed, 24 July 2019 13:24 Go to next message
evoradba
Messages: 126
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 Go to previous message
Michel Cadot
Messages: 67235
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

BlackSwan wrote on Wed, 13 February 2019 00:32
When 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:54
I 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:12
Looks 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.
Previous Topic: How to get the file name from directory
Next Topic: How to use sysdate as a column header
Goto Forum:
  


Current Time: Thu Jul 09 04:30:01 CDT 2020