For those e keeping score at home. I asked Oracle Support about this subject; "Oracle Support For ''INSERT INTO...SELECT'' & DATATYPES Over DB Link"
======= ANSWER ======= There are no such datatypes which should not be used for "insert into select" So, In the statement Insert into t1 select * from t2 ;
As long as the column datatypes defined in t2 exist in t1 , you can use them to insert...
Following statement over dblink is valid
insert into t1 select * from t2@(protected) ; ---- > works, again you have to ensure all the column datatyptes of t2 of other datatypes match with that of table t1 .
Chris Marquez Oracle DBA
-- --Original Message-- -- From: Marquez, Chris Sent: Fri 5/20/2005 4:40 PM To: oracle-l@(protected) Subject: insert into select & datatypes?
Anyone no if any/all Oracle datatypes that are NOT supported by "insert into select...".
How about "insert into select...from t1@(protected)" over db link?
insert into SCOTT.B_[LOB_TABLE | XMLTYPE_TABLE | USERDEFINEDTYPE_TABLE | LONG _TABLE | RAW_TABLE] select * from SCOTT.A_[LOB_TABLE | XMLTYPE_TABLE | USERDEFINEDTYPE _TABLE | LONG_TABLE | RAW_TABLE] ;
