insert using target table data in values clause
- From: "Joel Slowik" <jslowik@xxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 17 Aug 2011 11:24:31 -0400
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
Other related posts:
