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 DECEMBER 16, 2019

Applies to:

PL/SQL - Version 8.1.7.4 to 12.2.0.1 [Release 8.1.7 to 12.2]
Information in this document applies to any platform.
***Checked for relevance on 30-Apr-2014***
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.


Symptoms

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



SAMPLE SCENARIOS
--------------------------
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.

Changes

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.

Cause

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
Symptoms
Changes
Cause
Solution


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