This discussion is archived
11 Replies Latest reply: Aug 15, 2008 12:06 AM by SamB RSS

oracle error 1002 and 3114

452969 Newbie
Currently Being Moderated
my program is running smooth all the while, out of sudden it hit ora-1002 error subsequently hit ora-3114.

as i know the 1002 is cursor out of sequence, and 3114 is oracle not connect... what is the possible to link up this 2 error together?
  • 1. Re: oracle error 1002 and 3114
    NicloeiW Journeyer
    Currently Being Moderated
    could you place your code ?

    looks like some issues with fetch sequence
  • 2. Re: oracle error 1002 and 3114
    452969 Newbie
    Currently Being Moderated
    this is part of my code...

    sqlstmt.len = sprintf((char *) sqlstmt.arr,
    "SELECT * from table");

    EXEC SQL PREPARE edccnt3 FROM :sqlstmt;
    EXEC SQL DECLARE edccnt_c3 CURSOR FOR edccnt3;
    EXEC SQL OPEN edccnt_c3;
    EXEC SQL FETCH edccnt_c3 into :max_txn_time_year, :last_txn_time_year;
    if (max_txn_time_year > last_txn_time_year) {
         sqlstmt.len = sprintf((char *) sqlstmt.arr,     
    "SELECT * from table1 where xxx);                    
    } else {
    sqlstmt.len = sprintf((char *) sqlstmt.arr,     
    "SELECT * from table1 where xxx); }

              EXEC SQL CLOSE edccnt_c3;


    sprintf(buf_stg, "SQLSTMT=[%s].", (char *) sqlstmt.arr);
    log_msg(1, buf_stg);
    EXEC SQL PREPARE edccnt2 FROM :sqlstmt;
    EXEC SQL DECLARE edccnt_c2 CURSOR FOR edccnt2;
    EXEC SQL OPEN edccnt_c2;
    EXEC SQL FETCH edccnt_c2 into :min_txn_time_stg;
    if ((sqlca.sqlcode == ORA_SUCCESS ) && (cnt == 0)) {
    last_txn_time_stg[18] = 0x00;
    }
    else if (sqlca.sqlcode != ORA_SUCCESS ) {
    /*error happen here*/
    sprintf(buf_stg, "Failed with oracle error code %d", curr_eqptype, sqlca.sqlcode);
    log_msg(1, buf_stg);
    continue;
    }
  • 3. Re: oracle error 1002 and 3114
    NicloeiW Journeyer
    Currently Being Moderated
    here you are performing your Select 

    sqlstmt.len = sprintf((char *) sqlstmt.arr,
    "SELECT * from table");

    here you Execute The Cursor

    EXEC SQL PREPARE edccnt3 FROM :sqlstmt;
    EXEC SQL DECLARE edccnt_c3 CURSOR FOR edccnt3;
    EXEC SQL OPEN edccnt_c3;
    EXEC SQL FETCH edccnt_c3 into :max_txn_time_year, :last_txn_time_year;

    this looks Like pro c code, lets say i Read Like this
    you Select All Values From Table And When you Fetch you Fetch Only In 2
    variables max_txn_time_year ,last_txn_time_year

    If you want To do so, you need To Select 2 Columns In your Select statment,

    If my answer Is wrong my sincere apologizes As i am Not To much Into pro c
  • 4. Re: oracle error 1002 and 3114
    SamB Newbie
    Currently Being Moderated
    "1) Do not issue a fetch statement after the last row has been
    retrieved - there are no more rows to fetch."

    The problem is that you are trying to fetch data after the last row has been fetched.

    You need to check if there are any more rows left to fetch before you issue a fetch.

    In PLSQL this would be something like
    exit when cur%NOTFOUND;
    Message was edited by:
    SamB
  • 5. Re: oracle error 1002 and 3114
    452969 Newbie
    Currently Being Moderated
    due to the code is too long... i simplified it to select * from table
  • 6. Re: oracle error 1002 and 3114
    SamB Newbie
    Currently Being Moderated
    I'll post the full error description for your reference:

    01002. 00000 - "fetch out of sequence"
    *Cause:    This error means that a fetch has been attempted from a cursor
    which is no longer valid. Note that a PL/SQL cursor loop
    implicitly does fetches, and thus may also cause this error.
    There are a number of possible causes for this error, including:
    1) Fetching from a cursor after the last row has been retrieved
    and the ORA-1403 error returned.
    2) If the cursor has been opened with the FOR UPDATE clause,
    fetching after a COMMIT has been issued will return the error.
    3) Rebinding any placeholders in the SQL statement, then issuing
    a fetch before reexecuting the statement.
    *Action:   1) Do not issue a fetch statement after the last row has been retrieved - there are no more rows to fetch. 2) Do not issue a COMMIT inside a fetch loop for a cursor
    that has been opened FOR UPDATE.
    3) Reexecute the statement after rebinding, then attempt to
    fetch again.
  • 7. Re: oracle error 1002 and 3114
    NicloeiW Journeyer
    Currently Being Moderated
    cursoe c1 is select * from emp;

    rec_c1 c1%rowtype;

    open c1;
    fetch c1 into rec_c1;
    exit with c1%notfound;
      dbms_output.put_line(c1.col1||'_'||c1.col2||'_'|| and so on);

    close c1;
  • 8. Re: oracle error 1002 and 3114
    452969 Newbie
    Currently Being Moderated
    for the edccnt_c3 cursor, i just took the latest record then after that i close it.

    would there be a problem in between fetching edccnt_c3, re-assign the new statement to sqlstmt??

    beginning sqlstmt is tie with edccnt_c3, after i fetch i reassign new statement to sqlstmt for prepare the statement for edccnt_c2. but i close edccnt_c3 before open cursor edccnt_c2.
  • 9. Re: oracle error 1002 and 3114
    452969 Newbie
    Currently Being Moderated
    the new fetch is in different cursor - edccnt_c2.

    i close edccnt_c3 before fetching edccnt_c2
  • 10. Re: oracle error 1002 and 3114
    SamB Newbie
    Currently Being Moderated
    I'm sorry I don't know enough about Pro*C to answer your question.

    Do any of the "Causes" in my second post apply to your code?
  • 11. Re: oracle error 1002 and 3114
    452969 Newbie
    Currently Being Moderated
    the what abt the 3114?? would that be relate to 1002?
    how come after i get 1002 subsequent i will get 3114 error?

    please help...