For queries using upper or other functions in the where clause, that cannot use "normal" B-Tree indexes:

SQL>  create index idx_func on toto (upper(value));

Index created.

SQL> select * from toto where upper(value)='TEST';
..
SQL> @last
    ---------------------------------------------------------
    | Id  | Operation                   | Name     | E-Rows |
    ---------------------------------------------------------
    |   0 | SELECT STATEMENT            |          |        |
    |   1 |  TABLE ACCESS BY INDEX ROWID| TOTO     |      1 |
    |*  2 |   INDEX RANGE SCAN          | IDX_FUNC |      1 |
    ---------------------------------------------------------

Did not try, I'm wrong. This one is working:

create table users (
  id int not null primary key,
  login varchar(60) not null,
  pwhash varchar(60) not null
);
create unique index users_login_uk on users(lower(login));

INSERT into users values (1, 'foo', 'bar');
INSERT into users values (2, 'FOO', 'bar');

For example, it can be used for enforcing data constraints in your database.

create table users (
  id int not null primary key,
  login varchar(60) not null,
  pwhash varchar(60) not null,
  unique(lower(login))
);