Showing results for 
Search instead for 
Do you mean 

GeoMedia returns error ORA-00904: invalid identifier when Modify Schema Definition is used

by Technical Evangelist on ‎02-18-2016 11:56 AM - edited on ‎04-06-2016 06:18 PM by Moderator (392 Views)

Symptoms

Modify Schema Definition returns the following error when selecting a secured table:

 

ERROR at line 1:

ORA-00904: "NEXTVER": invalid identifier

ORA-06512: at "WMSYS.LT", line 12136

ORA-06512: at line 1

 

The same error occurs if you use Workspace Manager's DBMS_WM.BeginDDL.  NEXTVER is the column name in this particular case but the same error can occur with other column names.

Diagnosis

A session trace is usually required to determine the exact cause of the issue.   In most cases, it is due to an index on one of the Workspace Manager columns in the secured table.  Modify Schema Definition calls DBMS_WM.BeginDDL and that is where the problem is.

 

BeginDDL creates a staging table _LTS to handle the ddl operations. The staging table does not contain Workspace Manager columns (with the exception of WM_VALID). Any indexes on the _LT table are recreated on the staging table _LTS.  In this case, an index was placed on _LT.NEXTVER so beginDDL was trying to recreate the index on the _LTS table.  Since the NEXTVER column does not exist in _LTS,  the error ORA-00904: "NEXTVER": invalid identifier is returned.

Solution

To fix this problem, you need to determine what column is causing the problem and if it is related to an index.  If so, the following will will fix the problem:

 

  1. As the user that owns the table, run the following:
    exec wmsys.lt_ctx_pkg.allowDDLOperation('true');

  2. Find the index on NEXTVER,
    SELECT INDEX_NAME FROM USER_IND_COLUMN WHERE TABLE_NAME='GEONAME_FEATURE_LT' AND COLUMN_NAME='NEXTVER';

    then drop it:
    DROP INDEX <indexname>;
     
  3. If the index drops OK then:
    exec wmsys.lt_ctx_pkg.allowDDLOperation('false');

 

4. Then try beginDDL or Modify Schema Definition again.  It should work correctly this time.

Contributors