insert using target table data in values clause

From: Joel Slowik <jslowik_at_cps92.com>
Date: Wed, 17 Aug 2011 11:24:31 -0400
Message-ID: <7FCAE6F848605649B090362F7C518C4804845F10_at_cpsexchange.cps92.com>

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_at_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_at_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.

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Aug 17 2011 - 10:24:31 CDT