Oracle-L: RE: DB Link Error

From: <Jared.Still_at_radisys.com>
Date: Fri, 09 Aug 2002 17:18:21 -0800
Message-ID: <F001.004B139B.20020809171821@fatcity.com>

SQL> set arraysize 1
SQL> set copycommit 1000
SQL> copy from kgel/vinotamu_at_nxtp -

> insert nxtp.temp_mgh -
> using -
> select * from rrs$.temp_mgh

Array fetch/bind size is 1. (arraysize is 1) Will commit after every 1000 array binds. (copycommit is 1000) Maximum long size is 80. (long is 80)

select * from rrs$.temp_mgh
*
Error in SELECT statement: ORA-01002: fetch out of sequence

Kevin,

This appears to be a well known problem.

Two entries from MetaLink:

  SQL*Plus Technical Forum
  From: Gorm Heilskov 12-Jun-01 19:58
  Subject: ORA-01002: fetch out of sequence for Copy statement

ORA-01002: fetch out of sequence for Copy statement

I receive an ORA-01002 when trying to use the copy statement on an 8.0.4 database on Netware from an 8.1.7 client. The copy statement runs fine on an 8.1.7 database on Windows 2000 using an 8.1.7 client.
It also works fine using an 8.0.5 client. What is preventing the copy statement from working?

From: Oracle, Anil Shenoy 15-Jun-01 07:32 Subject: Re : ORA-01002: fetch out of sequence for Copy statement

Hi,
A bug with no 644413 has been filed on this and has been fixed in 8.1.5 and 8.0.6.1. I cannot file a backport request as 8.0.4 is desupported.

However you can use the workaround as below

Create a Database link from the 8.1.x db to the 8.0.x db using 'CREATE DATABASE' ie: SQL> create database link <linkName> connect to <UserId> identified by
Create a new table using 'CREATE TABLE' ie: SQL> create table <TableName> as select * from <RemoteTableName>@<linkName> or
Insert data into an existing table using 'INSERT' ie: SQL> insert into <TableName> select * from <RemoteTableName>@<linkName>

  Regards,
  Anil
  Oracle Support Services

                  Bookmark
                                                          Fixed font 
                         Go to End


  Doc ID: 
         Note:110364.1
  Subject: 
         Workaround for ORA-1002 on COPY COMMAND from 8.Xto 8.X
  Type: 
         PROBLEM
  Status: 
         PUBLISHED

                                                                  Content 
Type: 
          TEXT/PLAIN
                                                                  Creation 
Date: 
          26-MAY-2000
                                                                  Last 
Revision Date: 
          22-JAN-2002

Problem Description:

You are using the COPY command to copy data from one 8.x database to another
8.x database. You receive an ORA-01002 error.

      ORA-01002: fetch out of sequence
          Cause: This may be caused by fetching from a 'select for update' 
cursor
                 after a commit.  A PL/SQL cursor loop implicitly does 
fetches
                 and may also cause this error.

You see there are several bugs on the issue but not all of them are included
in patchsets or have fixes.

In this example you are using COPY from 8.1.6 to 8.0.6:

Testcase:

SQL> select INSTANCE_NAME from v$instance; INSTANCE_NAME

V816

SQL> select INSTANCE_NAME from v$instance_at_V806; INSTANCE_NAME

V806

  SQL> copy from alla/alla_at_tiger5_V806.world -   > insert copy2 using select * from copy1;    select * from copy1
  *
  Error in SELECT statement: ORA-1002: fetch out of sequence

Solution Description:

Use the following Workaround:

Create a Database link from the 8.1.x db to the 8.0.x db using 'CREATE DATABASE'

SQL> create database link <linkName> connect to <UserId> identified by @

'Password' using 'SID';

2. Create a new table using 'CREATE TABLE'

SQL> create table <TableName> as select * from <RemoteTableName>@<linkName>

OR - 2. Insert data into an existing table using 'INSERT'

SQL> insert into <TableName> select * from <RemoteTableName>@<linkName>

Example:

SQL> select INSTANCE_NAME from v$instance_at_V806; INSTANCE_NAME

V806

SQL> create table copy806 as select * from copy1_at_V806; Table created.

SQL> insert into copy806 select * from copy1_at_V806; 64 rows created.

References:

[BUG:903258] ORA-1002 COPYING FROM A REMOTE DATABASE Search Words:

ORA-1002 SQL*Plus
.

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: 
  INET: Jared.Still_at_radisys.com

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

Received on Fri Aug 09 2002 - 20:18:21 CDT

RE: DB Link Error - More Info