Is there a way in Oracle to make a column not case sensitive?

Question

I want to select rows from a table called Users where the column Logon is equal to "foo" - However, I also want to return "Foo" or "FOO".

I could do something like:

SELECT Id, Name FROM Users WHERE UPPER(Logon) = 'FOO';

And then convert my parameter to uppercase. However, in our code we have literally hundreds of spots where we'd have to update this.

Is there a way to make the table schema itself case-insensitive so these queries will just work without modification? Thanks!

UPDATE

I'd rather not change case-sensitivity in the entire database or at the session level. Changing SQL queries is hard since we use the .NET Entity Framework and have LINQ queries against this table all over the place. It doesn't appear the EF supports automatically converting case unless you want to change every LINQ query as well.

Answer 1

I'd rather not change case-sensitivity in the entire database or at the session level.

Is there a way to make the table schema itself case-insensitive so these queries will just work without modification

Yes, it is possible but from version Oracle 12cR2 and above. You could define it on many levels (column, table, schema):

-- default
CREATE TABLE tab2(i INT PRIMARY KEY, name VARCHAR2(100));

INSERT INTO tab2(i, name) VALUES (1, 'John');
INSERT INTO tab2(i, name) VALUES (2, 'Joe');
INSERT INTO tab2(i, name) VALUES (3, 'Billy');

SELECT /*csv*/ *
FROM tab2
WHERE name = 'jOHN' ;
/*
"I","NAME"
no rows selected
*/

SELECT /*csv*/
       column_id,
       column_name,
       collation
FROM   user_tab_columns
WHERE  table_name = 'TAB2'
ORDER BY column_id;
/*
"COLUMN_ID","COLUMN_NAME","COLLATION"
1,"I",""
2,"NAME","USING_NLS_COMP"
*/

Column-level:

CREATE TABLE tab2(i INT PRIMARY KEY, name VARCHAR2(100) COLLATE BINARY_CI);

INSERT INTO tab2(i, name) VALUES (1, 'John');
INSERT INTO tab2(i, name) VALUES (2, 'Joe');
INSERT INTO tab2(i, name) VALUES (3, 'Billy');

SELECT /*csv*/ *
FROM tab2
WHERE name = 'jOHN' ;
/*
"I","NAME"
1,"John"
*/

-- COLUMN LEVEL

SELECT /*csv*/
       column_id,
       column_name,
       collation
FROM   user_tab_columns
WHERE  table_name = 'TAB2'
ORDER BY column_id;
/*
"COLUMN_ID","COLUMN_NAME","COLLATION"
1,"I",""
2,"NAME","BINARY_CI"
*/

Table-level:

CREATE TABLE tab2(i INT PRIMARY KEY, name VARCHAR2(100)) 
DEFAULT COLLATION BINARY_CI;

Schema-level:

CREATE USER myuser IDENTIFIED BY myuser
DEFAULT TABLESPACE users
DEFAULT COLLATION BINARY_CI;

Answer 2

Answering my own question because I didn't feel that either of the proposed answers really addressed the issue.

Oracle does not support the concept of a case-insensitive column type, and case sensitivity can only be controlled at the database or session level. There's a few ways around this, such as making the column virtual or reading through a view, but each of them would also require you to cast the right operand as well (such as WHERE X = UPPER(:p1).

I ended up just updating my database (which was a list of usernames from Active Directory) to have the correct cases, so I no longer have to compare case insensitive.

Answer 3

I don't think you can do it just for one column. You can try the following approach : make your Logon column virtual as UPPER(s_Logon) (create s_Logon, copy all the values from existing Logon column , drop Logon, create it as virtual). I believe it's gonna work for SELECTs, but for insert/updates you will need to access 's_Logon'. Hope that makes sense.