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@dvlp -
> to tst/tst@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
*
Error in SELECT statement: ORA-01002: fetch out of sequence
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:[EMAIL PROTECTED]]
> 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 r
> 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@source
> TO YYYYY/YYYYY@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: [EMAIL PROTECTED]
>
> 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
>
> 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
