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

Last updated on AUGUST 19, 2017

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:

/* In the LOCAL database, create database link to remote instance ie*/
SQL> CREATE DATABASE Link E4026KDEV using 'REF1121U_CELCSOL1';

/*In the REMOTE database, create a copy of the scott.emp sample table */
SQL> CREATE TABLE empk as select * from scott.emp;

/*In the LOCAL database, test access and load code to memory*/
declare
v_emp scott.empk@E4026KDEV%ROWTYPE;
begin
select * into v_emp from scott.empk@E4026KDEV where empno = 7839;
dbms_output.put_line('empno: ' || v_emp.empno);
end;
/

/*In separate session on REMOTE database, add the following column */
SQL> alter table empk add (active varchar2(10));

/*Re-run the above anonymous PL/SQL block in the LOCAL session.  Should be able to re-run in sqlplus using "/" */

/*To clear the error, 4 options listed below.  For this, flush shared pool in any session to the LOCAL database*/
SQL> alter system flush shared_pool;

/*Re-run the anonymous block in LOCAL session, error should be alleviated*/

/*Now, test the other situation, with dropping the column from the remote session*/
SQL>  alter table empk drop column active;

/*Re-run PL/SQL anonymous block once again in LOCAL database*/


Named block:

/*For this test, if it was removed, use the REMOTE session to add column*/
SQL> alter table empk add (active varchar2(10));

/* In the LOCAL session, create the procedure */
Create procedure test_block AS
  v_emp scott.empk@E4026KDEV%ROWTYPE;
begin
  select * into v_emp from scott.empk@E4026KDEV where empno = 7839;
  dbms_output.put_line('empno: ' || v_emp.empno);
end;
/

/*call the procedure*/
SQL> call test_block();

/*In separate session on REMOTE database, drop column:
SQL> alter table empk drop column active;


/*Re-run PL/SQL code in LOCAL database */
SQL> call test_block();

/*After gettig the error, explicitly force a recompile to align timestamps of the DDL of both objects such that the remote object is older than the local*/
SQL> alter procedure test_block compile;

/*Re-run PL/SQL code in LOCAL database, error is gone*/
SQL> call test_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

Sign In with your My Oracle Support account

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

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms