There seems to be a bug related to expression based (function based) indexes when the optimizer creates a plan for inserts over a database link. The symptom is ORA-00904: … : invalid identifier.

I’m on a project of consolidating databases and copying tables over a database link. In this process we have to replace the current primary and foreign key values with new ones to avoid duplicates in the resulting target database.

Basically we’re creating mapping-tables with old and new keys and doing lookups for all primary and foreign keys as we’re inserting over a database link.

Suddenly we started getting errors like these:

ORA-00904: "A2"."SYS_NC00003$": invalid identifier 
ORA-02063: preceding line from

What we had done just before, was to add an expression-based index to speed up a lookup.

This is an Exadata quarter-rack where we have several databases, and a reproduction of the problem was created as below:

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production
CORE 12.1.0.2.0 Production
TNS for Linux: Version 12.1.0.2.0 - Production
NLSRTL Version 12.1.0.2.0 - Production

5 rows selected.

SQL> select owner, db_link, username, host from dba_db_links
 2 where db_link = 'MYLINK';

OWNER  DB_LINK USERNAME HOST 
------ ------- -------- ------ 
K      MYLINK  K        MYHOST 

1 row selected.

SQL> select * from v$version@MYLINK;

BANNER CON_ID 
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production
CORE 12.1.0.2.0 Production
TNS for Linux: Version 12.1.0.2.0 - Production
NLSRTL Version 12.1.0.2.0 - Production

5 rows selected.

We have a table with id-columns, where the id’s will be in both number and varchar format. Id and id_as_varchar will not have the same values, since they have different meanings.

SQL> create table t1 (
 2     id number(10) primary key,
 3     id_as_varchar2 varchar2(20)
 4 );

Table created.

SQL> insert into t1
 2 select rownum, rownum from dual connect by level <= 5; 5 rows created. SQL> commit;

Commit complete.

I’ve also created the table t1 empty on the other side of the db-link as a target for our insert operation:

SQL> select * from t1;

        ID ID_AS_VARCHAR2 
---------- -------------------- 
         1 1 
         2 2 
         3 3 
         4 4 
         5 5 

5 rows selected.

SQL> select * from t1@MYLINK;

no rows selected

SQL> create table t2 (
 2     id number(10) primary key,
 3     new_id number(10)
 4 );

Table created.

We have a table with new id’s, typically generated from the old one with an offset value:

SQL> insert into t2
 2 select id, id + 10 as new_id from t1;

5 rows created.

SQL> commit;

Commit complete.

SQL> select * from t2;

 ID NEW_ID 
---------- ---------- 
 1 11 
 2 12 
 3 13 
 4 14 
 5 15 

5 rows selected.

We’re doing joins to repopulate the columns with new id’s before we insert then into the target database over a dblink:

SQL> select t2_id.new_id, t2_charid.new_id new_char_id
 2 from t1, t2 t2_id, t2 t2_charid
 3 where t1.id = t2_id.id
 4 and t1.id_as_varchar2 = to_char(t2_charid.id(+));

    NEW_ID NEW_CHAR_ID 
---------- ----------- 
        11 11 
        12 12 
        13 13 
        14 14 
        15 15 

5 rows selected.

Checking that the table t1 on the other side of the link is empty:

SQL> delete t1@MYLINK;

0 rows deleted.

SQL> commit;

Commit complete.

We want to do an insert over a db-link like this:

SQL> insert into k.t1@MYLINK
 2 (id, id_as_varchar2)
 3 select t2_id.new_id, t2_charid.new_id new_char_id
 4 from t1, t2 t2_id, t2 t2_charid
 5 where t1.id = t2_id.id
 6 and t1.id_as_varchar2 = to_char(t2_charid.id(+));

5 rows created.

SQL> rollback;

Rollback complete.

Since we have millions of rows, so we create an index:

SQL> create unique index t2_idx on t2 ( to_char(id), new_id );

Index created.

But now, running the same insert statement, we get an ORA-00904 claiming an invalid identifier on a column “A2″.”SYS_NC00003$” (the name may vary)

SQL> insert into k.t1@MYLINK
 2 (id, id_as_varchar2)
 3 select t2_id.new_id, t2_charid.new_id new_char_id
 4 from t1, t2 t2_id, t2 t2_charid
 5 where t1.id = t2_id.id
 6 and t1.id_as_varchar2 = to_char(t2_charid.id(+));
insert into k.t1@MYLINK
*
ERROR at line 1:
ORA-00904: "A2"."SYS_NC00003$": invalid identifier 
ORA-02063: preceding line from MYLINK 

SQL> rollback;

Rollback complete.

Drop the index:

SQL> drop index t2_idx;

Index dropped.

Now the insert works again

SQL> insert into k.t1@MYLINK
 2 (id, id_as_varchar2)
 3 select t2_id.new_id, t2_charid.new_id new_char_id
 4 from t1, t2 t2_id, t2 t2_charid
 5 where t1.id = t2_id.id
 6 and t1.id_as_varchar2 = to_char(t2_charid.id(+));

5 rows created.

SQL> rollback;

Rollback complete.

Workaround #1

Add a column for a pre-calculated to_char-value:

SQL> alter table t2 add to_char_id varchar2(20);

Table altered.

SQL> update t2 set to_char_id = to_char(id);

5 rows updated.

SQL> commit;

Commit complete.

Create a regular index on the char-column:

SQL> create unique index t2_idx2 on t2(to_char_id, new_id);

Index created.

Rewrite to use the to_char_column:

SQL> insert into k.t1@MYLINK
 2 (id, id_as_varchar2)
 3 select t2_id.new_id, t2_charid.new_id new_char_id
 4 from t1, t2 t2_id, t2 t2_charid
 5 where t1.id = t2_id.id
 6 and t1.id_as_varchar2 = t2_charid.to_char_id(+);

5 rows created.

SQL> rollback;

Rollback complete.

That works ok.

Workaround #2:

Use the original column but rewrite the query to an ANSI-join:

SQL> create unique index t2_idx on t2(to_char(id), new_id);

Index created.

SQL> insert into k.t1@MYLINK
 2 (id, id_as_varchar2)
 3 select t2_id.new_id, t2_charid.new_id new_char_id
 4 from t1
 5 join t2 t2_id
 6 on (t1.id = t2_id.id)
 7 left join t2 t2_charid
 8 on (t1.id_as_varchar2 = to_char(t2_charid.id));

5 rows created.

SQL> rollback;

Rollback complete.

ANSI-join works OK.

Cleanup:

SQL> drop table t1;

Table dropped.

SQL> drop table t2;

Table dropped.

And don’t forget to drop the table t1 on the other side of the db-link.

Our choice of workaround came down to option #2, adding a new column. It gave the least amount of rewrite in our case, since we have several houndred of these insert-statements and we could populate the column easily at the same time as we populated the table.