My Oracle Support Banner

ORA-00904, ORA-00932, ORA-01007 Over DBlink From PL/SQL When Remote Table Altered (Doc ID 1139934.1)

Last updated on JANUARY 30, 2022

Applies to:

PL/SQL - Version to [Release 8.1.7 to 12.2]
Information in this document applies to any platform. The scenario explained and presented here involves both a local and remote database instance and timestamp checking of remote dependencies. It is presumed that a database link can be created between the two environments.


Executing an anonymous PL/SQL that executes a SQL against a remote (via a DBLink) works, BUT if a new column(s) has been added to the remote table AND the dblink session has not been restarted, the following error is returned.

ORA-01007: variable not in select list

The same PL/SQL block, doing remote SQL returns the following error if a column in the remote table is dropped:

ERROR at line 1:
ORA-00932: inconsistent datatypes: expected - got -

If re-running a named PL/SQL block (ie procedure), adding a new column generated the same error as the anonymous block, but dropping a column generates:

ORA-00904: "A1"."ACTIVE": invalid identifier
ORA-02063: preceding line from E4026KDEV

Anonymous block:

**Depending on the order in which the column is added versus dropped in the named-block test, the error may not occur with the named block until starting a new session wherein the timestamp information is determined to be out of synchronization and require the explicit compilation.


After invoking the anonymous block or named block in a local session (loading it into memory), the remote object was altered and the caused the local block (anonymous or named block) to have a timestamp which was older than the remote table.


To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!

In this Document

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