English

Error: DBMSCURSOR - Oracle: ORA-00904: invalid column name

Solution or Workaround

When using table name aliases in DBMSCURSOR statements:

dbmscursor cur1 declare oracle
select o1.id from oratable o1 for update of ch_ind

The following Oracle message occurs:

"ORA-00904: invalid column name"

Answer:

The DBMSCURSOR DECLARE option accepts a table alias, however, the cursor variable that contains a table alias cannot be accessed. The describe functions of all supported DBMS Application Programming Interfaces (API) return only 'true' column names to the Database Integrator. Table aliases are not returned as a part of the 'true' column name.

However, the APIs do substitute column aliases for 'true' column names.

Due to the design of DBI, table aliases should not be used in DBMSCURSOR statements.

Use column aliases instead to access columns with the same name. For example:

dbmscursor cur1 declare oracle
select oratable.id from oratable for update of ch_ind

Notes:

This is a known problem documented in the ArcInfo 7.0.4 ARCDOC under Troubleshooting-->Known problems-->Managing tabular data.