Q:

Not able to use <> in Function based Indexes in Oracle

We are trying to create a function based index which has <> (not equal to) in the where clause but get an error that say 
ORA-00907: missing right parenthesis

Is there any problem in using the <> clause ? Can it be made to work in someway.
 CREATE INDEX IX_TEST_TABLE ON TEST_TABLE ((NVL(COL_A, 0) <> NVL(COL_B, 0));

A:

You could use the result of that comparison in a case statement to come up with an actual value, with a supported data type, rather than a boolean - which has been noted already isn't supported y Oracle as an SQL data type. What that value is doesn't really matter as long as you're consistent; you could use Y/N, 0/1, etc.
Depending on your data spread and selectivity of how you'll query, you could use a bitmap index:
create bitmap index ix_test_table on test_table
  (case when nvl(col_a, 0) <> nvl(col_b, 0) then 1 else 0 end);

And then query on the same case, of course:
select * from test_table
where case when nvl(col_a, 0) <> nvl(col_b, 0) then 1 else 0 end = 1;
select * from test_table
where case when nvl(col_a, 0) <> nvl(col_b, 0) then 1 else 0 end = 0;

Or if it's very selective only include the small subset of rows that you're interested in by utilising the fact that null values are not included in the index:
create index ix_test_table on test_table
  (case when nvl(col_a, 0) <> nvl(col_b, 0) then 1 end);

select * from test_table
where case when nvl(col_a, 0) <> nvl(col_b, 0) then 1 end = 1;

You'd need to evaluate which is appropriate for your data.

