Q:

How to make WHERE clause case insensitive in oracle sql database? 

select * from table_Name where name ="red"

I need to fetch both "red" and "RED". 
For example: I need to use both upper and lower in same statement. 
How can I do this? 

A:

You could use case insensitive parameter at session 'NLS_SORT=BINARY_CI'.  There are two parameters at session level:

NLS_COMP
NLS_SORT

Let's see a demo:
Normal scenario:
SQL> with names as
    (
     select 'Vishnu' name from dual
    )
    -- Your query starts here
    select * from names where name='vIsHnU';

no rows selected

Case insensitive approach:
SQL> alter session set nls_comp='LINGUISTIC';

Session altered

SQL> alter session set nls_sort='BINARY_CI';

Session altered

SQL> with names as
    (
     select 'Vishnu' name from dual
    )
    -- Your query starts here
    select * from names where name='vIsHnU';

NAME  
------
Vishnu

One more example:
SQL> with names as
    (
     select 'red' name from dual union all
     select 'RED' from dual
    )
    -- Your query starts here
    select * from names where name='rEd';

NAME
----
red
RED

To improve the performance, you could also create a case insensitive INDEX.  
For example:
create index names_ci_indx on names(NLSSORT(name,'NLS_SORT=BINARY_CI'));

Now, there are ways to improve performance of above transaction.  Please read Oracle – Case Insensitive Sorts & Compares

