| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Join in the SQL Plus COPY command
Unfortunately this does not work as well. It either gives the same message, or the "ORA-01002: fetch out of sequence". Here is the experiment (this table does not have a LONG field, but just shows that the join does not work):
SQL> drop table test1; SQL> drop table test2; SQL> drop table test3; SQL> create table test1(col1 number, col2 number, col3 number); SQL> insert into test1 values(1,201,301); SQL> insert into test1 values(2,202,302); SQL> insert into test1 values(3,203,303); SQL> create table test2(col1 number, col4 number, col5 number); SQL> insert into test2 values(1,401,501); SQL> insert into test2 values(2,402,502); SQL> insert into test2 values(3,403,503); SQL> commit;
Commit complete.
SQL> create table test3 as
2 select t1.col2, t1.col3, t2.col4, t2.col5 3 from test1 t1 4 , test2 t2 5 where t1.col1 = t2.col1 6 and 0=1;
Table created.
SQL>
SQL> select t1.col2, t1.col3, t2.col4, t2.col5
2 from test1 t1
3 , test2 t2
4 where t1.col1 = t2.col1;
COL2 COL3 COL4 COL5 ---------- ---------- ---------- ----------
201 301 401 501
202 302 402 502
203 303 403 503
SQL>
SQL> insert into test3
2 select t1.col2, t1.col3, t2.col4, t2.col5 3 from test1 t1 4 , test2 t2 5 where t1.col1 = t2.col1;
3 rows created.
SQL> select * from test3;
COL2 COL3 COL4 COL5 ---------- ---------- ---------- ----------
201 301 401 501
202 302 402 502
203 303 403 503
SQL> rollback;
Rollback complete.
SQL> select * from test3;
no rows selected
SQL> SQL> SQL> SQL> commit;
Commit complete.
SQL> copy from tst/tst_at_dvlp -
> to tst/tst_at_dvlp -
> insert test3 -
> using select t1.col2, t1.col3, t2.col4, t2.col5 -
> from test1 t1 -
> , test2 t2 -
> where t1.col1 = t2.col1
Array fetch/bind size is 15. (arraysize is 15) Will commit when done. (copycommit is 0) Maximum long size is 80. (long is 80)
select t1.col2, t1.col3, t2.col4, t2.col5 from test1 t1, test2 t2
It seems there was a bug related to this, but I am getting the same error message when I am running on 8.1.7, where the bug should have been fixed.
Thanks.
Djordje
> -----Original Message----- > From: Hillman, Alex [mailto:Alex.Hillman_at_usmint.treas.gov] > Sent: Friday, June 08, 2001 11:42 AM > To: Multiple recipients of list ORACLE-L > Subject: RE: Join in the SQL Plus COPY command > > > You can change your query to: > > select s.* from source_tab s, rowid_list_tab rTo REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Fri Jun 08 2001 - 15:12:50 CDT
> where s.rowid=r.rowid
> > Alex Hilllman > > -----Original Message----- > Sent: Thursday, June 07, 2001 8:46 PM > To: Multiple recipients of list ORACLE-L > > > Hi gurus, > > I need to copy a part of the table with a long field from one > database to > another. > > If I try to use: > > COPY FROM XXXXX/XXXXX_at_source
> TO YYYYY/YYYYY_at_target
> INSERT target_tab
> USING select *
> from source_tab
> where rowid in (select rowid
> from rowid_list_tab)
> > the command breaks as, as far as I can see, COPY command does > not allow a > subquery. > > I am on 8.0.4. > > If anybody has had a similar problem and has found a > workaround, or another > idea how to copy a part of a table with a long field would be > very much > appreciated ? This is reallllllly important as a project is > depending on > that. > > Thanks. > > Djordje > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Djordje Jankovic > INET: djankovic_at_corp.attcanada.ca > > Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California -- Public Internet access / Mailing Lists > -------------------------------------------------------------------- > To REMOVE yourself from this mailing list, send an E-Mail message > to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Hillman, Alex > INET: Alex.Hillman_at_usmint.treas.gov > > Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California -- Public Internet access / Mailing Lists > -------------------------------------------------------------------- > To REMOVE yourself from this mailing list, send an E-Mail message > to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). > -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Djordje Jankovic INET: djankovic_at_corp.attcanada.ca Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists --------------------------------------------------------------------
 |
 |