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 184.108.40.206 to 220.127.116.11 [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.
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.
The same PL/SQL block, doing remote SQL returns the following error if a column in the remote table is dropped:
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-02063: preceding line from E4026KDEV
**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