11 Replies Latest reply: Aug 15, 2008 2:06 AM by SamB RSS

    oracle error 1002 and 3114

    452969
      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
          could you place your code ?

          looks like some issues with fetch sequence
          • 2. Re: oracle error 1002 and 3114
            452969
            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
              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
                "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
                  due to the code is too long... i simplified it to select * from table
                  • 6. Re: oracle error 1002 and 3114
                    SamB
                    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
                      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
                        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
                          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
                            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
                              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...