Unable To Query Or Update/Insert In Pl/Sql After DDL on Source Table (Doc ID 1240984.1)

Last updated on NOVEMBER 08, 2010

Applies to:

Oracle Server - Standard Edition - Version: 11.2.0.1 to 11.2.0.1 - Release: 11.2 to 11.2
Information in this document applies to any platform.

Symptoms

It's not possible to query or send DML statements over a database link if the source table definition is changed.

Note:

This behavior reproduce in the Standard Edition only.

Changes

The issue could be reproduced using the following steps using loopback database link.

SQL> create table temp ( temp1 varchar2(1) );

Table created.

SQL> create database link ORC1120U@tc connect to tc identified by tc using 'ORC1120U';

Database link created.

SQL> begin update temp@ORC1120U@tc set temp1 = 'Y'; end;
/ 2

PL/SQL procedure successfully completed.

SQL> alter table temp add temp2 varchar2(1) ;

Table altered.

SQL> begin update temp@ORC1120U@tc set temp2 = 'N'; end;
/ 2
begin update temp@ORC1120U@tc set temp2 = 'N'; end;
*
ERROR at line 1:
ORA-06550: line 1, column 35:
PL/SQL: ORA-00904: "TEMP2": invalid identifier
ORA-06550: line 1, column 7:
PL/SQL: SQL Statement ignored


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