I am using Oracle 11g, do not have admin access or authority to make any changes to configuration, and would like to have case-insensitive searches of varchar2 and CLOB columns.
I have tried putting the following before my SELECT query:
ALTER SESSION SET NLS_SORT = BINARY_CI;
ALTER SESSION SET NLS_COMP = LINGUISTIC;
This results in an error message: ORA-00922; missing or invalid option.
Also, the method I'm using for searching the CLOB is case-sensitive:
SELECT * /* I don't use * in production, I'm using it here for brevity */
WHERE dbms_lob.instr(columnA,'searchWord') >= 1
Any advice appreciated.
^ _ ^
I'm not familiar with Oracle, but see if you can do something similar to the following:
where LCase(columnA) like '%searchword%'
This will work, but at a huge cost. Forcing Oracle to convert all text to lowercase is CPU intensive. And, I'm not sure that would work with CLOB columns.
^ _ ^
Been doing more Google searching on this subject, and I'm not finding much. A lot of sites indicate the solution that gives me the "mising or invalid option" error message is the best, but it's not working, for me.
Any other suggestions?
^ _ ^