Receiving ORA-904 when creating a functional index with CHR(0) (Doc ID 2708383.1)

Last updated on APRIL 17, 2023

Applies to:

Symptoms

Select/insert/update/delete on table which has function base index (nvl(c,chr(0)) fails with "ORA-00904: "UCHR": invalid identifier" 

SQL> create table test (a number, b varchar2(10),c nvarchar2(10));

Table created.

SQL> insert into test values (1,NULL,NULL);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from test;

A B C
---------- ---------- ----------
1

SQL> CREATE UNIQUE INDEX test_idx1 on test(nvl(b,chr(0)));

Index created.

SQL> select * from test;

A B C
---------- ---------- ----------
1

SQL> CREATE UNIQUE INDEX test_idx2 on test(nvl(c,chr(0)));

Index created.

SQL> select * from test;
select * from test
*
ERROR at line 1:
ORA-00904: "UCHR": invalid identifier

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.