SQL> set arraysize 1
SQL> set copycommit 1000
SQL> copy from kgel/vinotamu@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
1) 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
2) Create a new table using 'CREATE TABLE' ie:
SQL> create table <TableName> as select * from
<RemoteTableName>@<linkName>
or
2) 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@V806;
INSTANCE_NAME
----------------
V806
SQL> copy from [EMAIL PROTECTED] -
> 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:
1. 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@V806;
INSTANCE_NAME
----------------
V806
SQL> create table copy806 as select * from copy1@V806;
Table created.
SQL> insert into copy806 select * from copy1@V806;
64 rows created.
References:
===========
[BUG:903258] ORA-1002 COPYING FROM A REMOTE DATABASE
Search Words:
=============
ORA-1002 SQL*Plus
.
Copyright (c) 1995,2000 Oracle Corporation. All Rights Reserved. Legal
Notices and Terms of Use.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
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).
