SQL*PLUS Copy Command Limitation?
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 18

Thread: SQL*PLUS Copy Command Limitation?

  1. #1
    Join Date
    Dec 2005
    Posts
    9

    SQL*PLUS Copy Command Limitation?

    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!

  2. #2
    Join Date
    May 2003
    Location
    Pretoria, Rep of South Africa
    Posts
    191
    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.
    Able was I ere I saw Elba

  3. #3
    Join Date
    Dec 2005
    Posts
    9
    yes i have check the error message. does'nt ring a bell! Still does not give a clear definition or cause of the problem.

  4. #4
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    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:
    Code:
    copy to edrtest/edr123@ora9i
    Login to the 9i database with a 9i client and use:
    Code:
    copy from scott/tiger@ORA8
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  5. #5
    Join Date
    Dec 2005
    Posts
    9
    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. :(

  6. #6
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Quote Originally Posted by 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. :(
    Does the table have a long col?

    Tamil

  7. #7
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    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?
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  8. #8
    Join Date
    May 2003
    Location
    Pretoria, Rep of South Africa
    Posts
    191
    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)
    Able was I ere I saw Elba

  9. #9
    Join Date
    Dec 2005
    Posts
    9
    Quote Originally Posted by 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?

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

  10. #10
    Join Date
    Dec 2005
    Posts
    9
    Quote Originally Posted by tamilselvan
    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?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width