Oracle DB: How can I write query ignoring case?

You can use either lower or upper function on both sides of the where condition

Select * from table where upper(table.name) like upper('IgNoreCaSe');

Alternatively, substitute lower for upper.

Use ALTER SESSION statements to set comparison to case-insensitive:

alter session set NLS_COMP=LINGUISTIC;
alter session set NLS_SORT=BINARY_CI;

If you're still using version 10gR2, use the below statements. See this FAQ for details.

alter session set NLS_COMP=ANSI;
alter session set NLS_SORT=BINARY_CI;