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 8.1.7.4 to 12.2.0.1 [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.
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.
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
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 |