Database Extract Failing with Errors like ORA-00904 - invalid identifier / Invalid column name / Column not found (Doc ID 1122510.1)

Last updated on JULY 03, 2023

Applies to:

Symptoms

Database Extract (DbXtract) task may fail while extracting a mobile user with following errors:

[DataDirect][ODBC Oracle driver][Oracle]ORA-00904: "PRIMARY_TABLE"."X_CUSTOM_COLUMN": invalid identifier
Error while writing to file "" for target client "USERNAME".
SBL-SMI-00077: Component error, see the trace file for more information

With detailed logging levels in place for DbXtract component (as per details in <Note 477125.1>):

srvrmgr> change evtloglvl %=5 for comp DbXtract
srvrmgr> change evtloglvl sql%=1 for comp DbXtract
srvrmgr> change evtloglvl sqlparse%=4 for comp DbXtract

resulted logs will include information helping determine exact table name that caused this error ("PRIMARY_TABLE" - above is only its alias)

Note that for other RDBMS platforms used (MS SQL Server or IBM DB2), database error code and ODBC details would be of course different, but the other error lines would be identical:

- for instance in case of SQL Server:

[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name 'X_CUSTOM_COLUMN'.
Error while writing to file "" for target client "USERNAME".
SBL-SMI-00077: Component error, see the trace file for more information

It was also reported that a similar issue could occur instead of DbXtract level, during the Database Initialization step - in detailed upgwiz.log on client:

[Sybase][ODBC Driver][Adaptive Server Anywhere]Column 'X_CUSTOM_COLUMN' not found
[Siebel Database][ODBC Driver][Adaptive Server Anywhere]Column 'X_CUSTOM_COLUMN' not found

In that case the custom column(s) were in Siebel Repository (in Tools) and applied on server database, but other required steps were likely missed - changes not Activated etc.

-----

Some similar errors could be reported also on a custom extension table created (CX_*) - here the scenario and errors were like:

[DataDirect][ODBC Oracle driver][Oracle]ORA-00904: "MTX"."PAR_ROW_ID": invalid identifier

Changes

Recent configuration changes in Siebel Tools on Table object(s) identified in detailed log - custom extension tables (CX_*), custom extension columns for standard base tables (X_*).

Cause

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