Oracle Database Discussions

Hi there!

I got a doubt here about index creation and I hope you can help me here, please.

I´m trying to create an index in a table, specifying which this index must be in lowercase.

The table has a lot of columns but I just need the index for three columns only in lowercase.

I tried something:

SQL> create table teste(a varchar2(10),
2 b varchar2(10));

Table created.

SQL> insert into teste values ('teste','Teste');

1 row created.

SQL> insert into teste values ('Teste','testE');

1 row created.

SQL> insert into teste values ('TeSte','tEsTe');

1 row created.

SQL> insert into teste values ('tesTe','TesTe');

1 row created.

SQL> commit;

Commit complete.

------------------------
-- First Attempt --
------------------------

SQL> create index teste_ix on teste (lower(a,b));
create index teste_ix on teste (lower(a,b))
*
ERROR at line 1:
ORA-00909: invalid number of arguments

-----------------------------
-- Second Attempt --
-----------------------------

SQL> create index teste_ix on teste ((lower(a,b)));
create index teste_ix on teste ((lower(a,b)))
*
ERROR at line 1:
ORA-00909: invalid number of arguments

--------------------------
-- Third Attempt --
--------------------------

SQL> create index teste_ix on teste (UPPER(a,b));
create index teste_ix on teste (UPPER(a,b))
*
ERROR at line 1:
ORA-00909: invalid number of arguments

-------------------------------------------------
-- tried to cut off the parenthesis --
-------------------------------------------------

SQL> create index teste_ix on teste lower(a,b);

Index created.


Thanks in advance!