The record does insert in a 11.1.0.6.0 database without error, so maybe this is a setting? -----Original Message----- From: Joel Slowik Sent: Wednesday, August 17, 2011 11:25 AM To: oracle-l@xxxxxxxxxxxxx Subject: insert using target table data in values clause This is what I am doing: drop table x; create table x (x varchar2(1), y varchar2(1)); drop table y; create table y (b varchar2(1), d varchar2(1)); insert into x (x,y) values (1,2); insert into y (b,d) values (3,4); insert into x tgt (x,y) values (5,(select tgt.x from y where rownum=1)); The behavior I expect for the last insert statement is ORA-00904: "TGT"."X": invalid identifier; and I get that behavior in DB_1. However, in DB_2 I get 1 row created. So far it looks like the error comes up in 10.2.0.5 x64 (db_1), and the insert goes through on 10.2.0.3 x32 (db_2). This looks like it's a bug that was address, does anyone know what this bug number might be? Here is the log: SQL> connect TEST/test@db_1 Connected. SQL> drop table x; Table dropped. SQL> create table x (x varchar2(1), y varchar2(1)); Table created. SQL> SQL> drop table y; Table dropped. SQL> create table y (b varchar2(1), d varchar2(1)); Table created. SQL> SQL> insert into x (x,y) values (1,2); 1 row created. SQL> insert into y (b,d) values (3,4); 1 row created. SQL> SQL> insert into x tgt 2 (x,y) 3 values (5,(select tgt.x from y where rownum=1)); values (5,(select tgt.x from y where rownum=1)) * ERROR at line 3: ORA-00904: "TGT"."X": invalid identifier SQL> disconnect Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> connect TEST/test@db_2 Connected. SQL> drop table x; Table dropped. SQL> create table x (x varchar2(1), y varchar2(1)); Table created. SQL> SQL> drop table y; Table dropped. SQL> create table y (b varchar2(1), d varchar2(1)); Table created. SQL> SQL> insert into x (x,y) values (1,2); 1 row created. SQL> insert into y (b,d) values (3,4); 1 row created. SQL> SQL> insert into x tgt 2 (x,y) 3 values (5,(select tgt.x from y where rownum=1)); 1 row created. SQL> disconnect Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production With the Partitioning, OLAP and Data Mining options SQL> spool off Confidentiality Note: This electronic message transmission is intended only for the person or entity to which it is addressed and may contain information that is privileged, confidential or otherwise protected from disclosure. If you have received this transmission, but are not the intended recipient, you are hereby notified that any disclosure, copying, distribution or use of the contents of this information is strictly prohibited. If you have received this e-mail in error, please contact Continuum Performance Systems at {203.245.5000} and delete and destroy the original message and all copies. -- //www.freelists.org/webpage/oracle-l