locked
fetch cursor to insert data to table RRS feed

  • Question

  • User-1075092149 posted

    my code as belows:

    ---------------------------------------

    LOOP

    fetch cursor

    into aaa,bbb,ccc,ddd,eee

    insert into table_A (id,name,address,dept,tel) values (aaa,bbb,ccc,ddd,eee);

    commit;

    exit when cursor%NOTFOUND;

    END LOOP;

    -----------------------------

    why when i run the sp and output list always has error message about "ora-01002: fetch out of sequence".

    how to fix it?

    Friday, August 3, 2012 12:02 AM

Answers

  • User-578610739 posted

    Hi Member,

    I think you missing something or declare cursor with reserve word "Cursor" . reaneme it and then execute

    or

    you check this example.

     CREATE OR REPLACE PROCEDURE my_proc IS
     
    var_empno emp.empno%type;
     
    var_ename emp.ename%type;
     
    var_sal emp.sal%type;
     
     
    //declaring a cursor//
     
    CURSOR EMP_CURSOR IS
     
    select empno, ename, sal from emp;
     
    BEGIN
     
     
    //opening a cursor//
     
    open EMP_CURSOR;
     
    LOOP
     
     
    //fetching records from a cursor//
     
    fetch EMP_CURSOR into var_empno, var_ename, var_sal;
     
     
    //testing exit conditions//
     
    EXIT when EMP_CURSOR%NOTFOUND;
     
    IF (var_sal > 1000) then
     
    DBMS_OUTPUT.put_line(var_empno || ' ' || var_ename || ' ' || var_sal);
     
    ELSE
     
    DBMS_OUTPUT.put_line(var_ename || ' sal is less then 1000');
     
    END IF;
     
    END LOOP;
     
     
    //closing the cursor//
     
    close EMP_CURSOR;
     
    DBMS_OUTPUT.put_line('DONE');
     
     
    END;
     
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, August 6, 2012 2:02 AM

All replies

  • User551462331 posted

    does this work?

    LOOP

    fetch cursor

    into aaa,bbb,ccc,ddd,eee

    exit when cursor%NOTFOUND;

    insert into table_A (id,name,address,dept,tel) values (aaa,bbb,ccc,ddd,eee);

    commit;

    exit when cursor%NOTFOUND;

    END LOOP;

    hope this helps...

    Friday, August 3, 2012 12:08 AM
  • User-1075092149 posted

    does this work?

    LOOP

    fetch cursor

    into aaa,bbb,ccc,ddd,eee

    exit when cursor%NOTFOUND;

    insert into table_A (id,name,address,dept,tel) values (aaa,bbb,ccc,ddd,eee);

    commit;

    exit when cursor%NOTFOUND;

    END LOOP;

    hope this helps...

    result is same, still has this error about "ORA-01002: fetch out of seqence" , but data is inserted into table successfully, why?

    Friday, August 3, 2012 12:16 AM
  • User551462331 posted

    try removing commit statement and put it after end loop

    hope this helps...

    Friday, August 3, 2012 12:27 AM
  • User-578610739 posted

    Hi Friend,

    refer this.

    ORA-01002: 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.

    https://forums.oracle.com/forums/thread.jspa?threadID=2217240

    Friday, August 3, 2012 2:04 AM
  • User-1075092149 posted

    i remove the word "commit" and place it outside loop ,it also cause this error

    Sunday, August 5, 2012 5:46 AM
  • User-578610739 posted

    Hi Member,

    I think you missing something or declare cursor with reserve word "Cursor" . reaneme it and then execute

    or

    you check this example.

     CREATE OR REPLACE PROCEDURE my_proc IS
     
    var_empno emp.empno%type;
     
    var_ename emp.ename%type;
     
    var_sal emp.sal%type;
     
     
    //declaring a cursor//
     
    CURSOR EMP_CURSOR IS
     
    select empno, ename, sal from emp;
     
    BEGIN
     
     
    //opening a cursor//
     
    open EMP_CURSOR;
     
    LOOP
     
     
    //fetching records from a cursor//
     
    fetch EMP_CURSOR into var_empno, var_ename, var_sal;
     
     
    //testing exit conditions//
     
    EXIT when EMP_CURSOR%NOTFOUND;
     
    IF (var_sal > 1000) then
     
    DBMS_OUTPUT.put_line(var_empno || ' ' || var_ename || ' ' || var_sal);
     
    ELSE
     
    DBMS_OUTPUT.put_line(var_ename || ' sal is less then 1000');
     
    END IF;
     
    END LOOP;
     
     
    //closing the cursor//
     
    close EMP_CURSOR;
     
    DBMS_OUTPUT.put_line('DONE');
     
     
    END;
     
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, August 6, 2012 2:02 AM