SQL*PLUS Copy Command Limitation?

[Bong]

Hi Guyz,

I am trying to execute a copy command in Ora 9i environment to copy data from Ora 8.0. I know that copy command does not need dblink(?). Ora 9i and Ora8.0 can't have direct DBlink anyway. My problem is this, when I execute the copy command i get fetch out of sequence error. What does it mean and how to resolve it? Help me Oracle Gurus out there.

SQL> copy from scott/tiger@ORA8 to edrtest/edr123@ora9i
> CREATE PROJ_JOB_TMP USING select * from proj_job;


Array fetch/bind size is 5. (arraysize is 5)
Will commit when done. (copycommit is 0)
Maximum long size is 80. (long is 80)

select * from proj_job
*
Error in SELECT statement: ORA-01002: fetch out of sequence

Thanks guyz!

[robertbalmer]

ORA-01002 fetch out of sequence

Cause: In a host language program, a FETCH call was issued out of sequence. A successful parse-and-execute call must be issued before a fetch. This can occur if an attempt was made to FETCH from an active set after all records have been fetched. 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.

Action: Parse and execute a SQL statement before attempting to fetch the data.

[Bong]

yes i have check the error message. does'nt ring a bell! Still does not give a clear definition or cause of the problem.

[slimdave]

When I've had errors like this with the COPY command in the past they've generally been solved by using a different version of the client ... so you could try a couple of things:

Login to the 8.0 database with an 8.0 client and use:

copy to edrtest/edr123@ora9i

Login to the 9i database with a 9i client and use:

copy from scott/tiger@ORA8

[Bong]

thanks slimdave. i am actually login to 9i database with a 9i client when I hit this error. I tried the other way that you suggested, still getting error. :(

[tamilselvan]

thanks slimdave. i am actually login to 9i database with a 9i client when I hit this error. I tried the other way that you suggested, still getting error. :(

Does the table have a long col?

Tamil

[slimdave]

There are a couple of bugs you might be hitting with client version less than 9.0.1.4.0 ... what version are you on?

[robertbalmer]

what about environment settings

sql>set array 1000
sql>
sql>set long 2000
SQL> copy from user/user@1234 to user/user@4321 create test1 using select
* from t1

Array fetch/bind size is 1000. (arraysize is 1000)
Will commit when done. (copycommit is 0)
Maximum long size is 2000. (long is 2000)

[Bong]

There are a couple of bugs you might be hitting with client version less than 9.0.1.4.0 ... what version are you on?

my 9i version is 9.2.0.2.0 and my Oracle 8 is 8.0.5.2.1

[Bong]

Does the table have a long col?

Tamil

Hi Tamil, my table don't have a long column. just using a simple test table. Will having a long column give this error?