Results 1 to 7 of 7
  1. #1
    Join Date
    Dec 2012
    Posts
    4

    Post Unanswered: Fetch out of Sequence error isn't returned when doing a single fetch

    Hi all,

    This is my first post in this forum. I welcome any and every suggestion, in case my post disobeys any guidelines laid. Here is where I seek help from forum members:

    As per my understanding, if we fetch all the rows from a sys_refcursor into a collection, within the pl/sql block, then it doesn't return any row to the calling program. Instead a fetch out of sequence error is encountered. This worked well. However, when I use a single fetch statement(in case the sys_refcursor contains a single row), I do not encounter "Fetch Out Of Sequence" error. It would be really helpful if anyone could please explain this behaviour.

    Below is a sample code snippet that I ran in PL/SQL Developer:

    CREATE OR REPLACE PROCEDURE test_cur_1(in_dept_id VARCHAR2,
    out_cursor OUT SYS_REFCURSOR) IS
    temp_count NUMBER;
    temp_rec dept%ROWTYPE;

    BEGIN
    --This piece of code runs into a "Fetch Out Of Sequence" error, when trying to open up the sys_refcursor.
    OPEN out_cursor FOR
    SELECT *
    FROM dept
    WHERE dept_id = in_dept_id;--dept_id is the primary key

    LOOP
    EXIT WHEN out_cursor%NOTFOUND;
    FETCH out_cursor
    INTO temp_rec;
    dbms_output.put_line('department name: '||temp_rec.dept_name);
    END LOOP;

    EXCEPTION
    WHEN OTHERS THEN
    OPEN out_cursor FOR
    SELECT SQLCODE||'->'||SQLERRM FROM dual;
    END;
    --------------------------------------------------------------------------------

    CREATE OR REPLACE PROCEDURE test_cur_2(in_dept_id VARCHAR2,
    out_cursor OUT SYS_REFCURSOR) IS
    temp_count NUMBER;
    temp_rec dept%ROWTYPE;

    BEGIN
    --This piece of code does not run into a "Fetch Out Of Sequence" error. Instead the output sys_refcursor opens up for no rows to display.
    OPEN out_cursor FOR
    SELECT *
    FROM dept
    WHERE dept_id = in_dept_id;--dept_id is the primary key

    FETCH out_cursor
    INTO temp_rec;

    dbms_output.put_line('department name: '||temp_rec.dept_name);

    EXCEPTION
    WHEN OTHERS THEN
    OPEN out_cursor FOR
    SELECT SQLCODE||'->'||SQLERRM FROM dual;
    END;

    This is just a sample code wherein I overwrite the old values after printing them. Also, I am using PL/SQL developer as the UI tool and I simply execute the procedure in a test window.


    Appreciate all your time!

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >EXCEPTION
    >WHEN OTHERS THEN
    >OPEN out_cursor FOR
    >SELECT SQLCODE||'->'||SQLERRM FROM dual;
    >END;


    silly, worthless & useless code that does nothing but ignores any error that occurs.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Dec 2012
    Posts
    4
    I agree. However, this is not a real time problem. This is just a sample code, wherein the above exception handler is meant for debugging my code. It would be really helpful, if anyone could help me with the explanation for the above code's behaviour.
    Last edited by sneha_86; 12-20-12 at 01:39.

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    I don't have your tables; could you create a test case (CREATE TABLE and several INSERT INTO statements), please?

    I ran your code in Scott's schema, but couldn't reproduce what you say.

    First example (the one that is supposed to run into an error) (note that, generally speaking, you should first FETCH and then EXIT, not vice versa):
    Code:
    SQL> create or replace procedure prc_1 (par_deptno in number,
      2                                     out_cursor out sys_refcursor
      3                                    )
      4  is
      5    dept_rec dept%rowtype;
      6  begin
      7    open out_cursor for
      8      select * from dept
      9      where deptno = par_deptno;
     10
     11    loop
     12      fetch out_cursor into dept_rec;
     13      exit when out_cursor%notfound;
     14
     15      dbms_output.put_Line('Department name: ' || dept_rec.dname);
     16    end loop;
     17    close out_cursor;
     18  end;
     19  /
    
    Procedure created.
    
    SQL> declare
      2    l_oc sys_refcursor;
      3  begin
      4    prc_1(20, l_oc);
      5  end;
      6  /
    Department name: RESEARCH
    
    PL/SQL procedure successfully completed.
    
    SQL>
    The second procedure:
    Code:
    SQL> create or replace procedure prc_2 (par_deptno in number,
      2                                     out_cursor out sys_refcursor
      3                                    )
      4  is
      5    dept_rec dept%rowtype;
      6  begin
      7    open out_cursor for
      8      select * from dept
      9      where deptno = par_deptno;
     10
     11    fetch out_cursor into dept_rec;
     12
     13    dbms_output.put_Line('Department name: ' || dept_rec.dname);
     14    close out_cursor;
     15  end;
     16  /
    
    Procedure created.
    
    SQL> declare
      2    l_oc sys_refcursor;
      3  begin
      4    prc_2(20, l_oc);
      5  end;
      6  /
    Department name: RESEARCH
    
    PL/SQL procedure successfully completed.
    
    SQL>
    As you can see, no problem in either of these procedures. So - test case, please?

  5. #5
    Join Date
    Dec 2012
    Posts
    4
    First example (the one that is supposed to run into an error) (note that, generally speaking, you should first FETCH and then EXIT, not vice versa):
    This was not known to me. i thought its good both ways. Thank you for correcting me there.

    Actually I am using PL/SQL as the UI tool for coding, wherein I can simply execute it by a test window. Logic that''s not clear to me is: when we fetch all data from a sys_refcursor in a loop or by bulk collection, I run into "fetch out of bound exception" when trying to view the cursor in the test window, which is as expected. But when my cursor contains a single row and i do a simple fetch without loop, i do not run into any error, which I should, when opening the cursor. Hope that brings in some clarity in my question. please let me know if there is anything else that you would want to know.

    Thank you again:-)


    I just figured out, that this is happening only when I test the above procedures in a test window of PL/SQL developer, but not when I execute it in a Begin-End block.
    Last edited by sneha_86; 12-20-12 at 05:36.

  6. #6
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Does it mean that everything is OK now?

    In the future: maybe you should also switch to SQL*Plus and copy/paste your session so that everyone could see what you did and how Oracle responded. GUI tools are nice, but forum discussions prefer command line tools.

  7. #7
    Join Date
    Dec 2012
    Posts
    4
    yes it is ok now.

    In the future: maybe you should also switch to SQL*Plus and copy/paste your session so that everyone could see what you did and how Oracle responded. GUI tools are nice, but forum discussions prefer command line tools.
    I will surely keep this in mind for future posts. Thank You once again!

Posting Permissions

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