Cursor FOR loop vs. open/fetch/loop
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Cursor FOR loop vs. open/fetch/loop

Hybrid View

  1. #1
    Join Date
    Jan 2001
    Posts
    63
    I would like to replace the following open/fetch/close statements with a cursor FOR loop, but I need to check for this particular DB2 error on every fetch, and I don't know how to do that error checking inside the FOR loop. Any help is appreciated.
    *****************************************
    cursor c1 is select ..........
    e_DB2_error EXCEPTION;
    PRAGMA EXCEPTION_INIT (e_DB2_error, -9100);
    begin
    open c1 cursor;
    begin
    fetch c1 into .....local variables;
    exception
    WHEN e_DB2_error THEN
    etc. etc.
    end;
    while c1%found
    loop
    /* process rows... */
    /* fetch the next row of the cursor */
    begin
    fetch c1 into .....local variables;
    exception
    WHEN e_DB2_error THEN
    etc. etc.
    end;
    end loop;
    close c1 cursor;
    end;
    ************************************

  2. #2
    Join Date
    Jul 2000
    Posts
    296
    Use PL/SQL block with exception handler for your -9100 error inside the loop:
    DECLARE
    CURSOR C1 IS ...
    EXCEPTION ...
    PRAGMA ...
    BEGIN
    FOR r1 IN c1
    LOOP
    BEGIN
    ...
    EXCEPTION
    WHEN e_DB2_error THEN
    ...
    END;
    END LOOP;
    END;

  3. #3
    Join Date
    Jan 2001
    Posts
    63
    I am surprised to see the LOOP within the FOR.... I thought FOR was a looping construct already.

  4. #4
    Join Date
    Aug 2000
    Posts
    462
    create or replace procedure myproc as
    cursor c1 is select a from b;
    e_DB2_error EXCEPTION;
    PRAGMA EXCEPTION_INIT (e_DB2_error, -9100);
    x varchar2(100);
    begin
    open c1;
    begin
    fetch c1 into x;
    exception
    WHEN e_DB2_error THEN
    dbms_output.put_line('edb2error before loop');
    end;
    while c1%found loop
    begin
    fetch c1 into x;
    exception
    WHEN e_DB2_error THEN
    dbms_output.put_line('db2error in loop');
    end;
    end loop;
    close c1;
    end;
    /

  5. #5
    Join Date
    Jul 2000
    Posts
    296
    You need the LOOP in FOR loops

    Syntax for CURSOR FOR LOOP is:

    FOR record_index IN cursor_name
    LOOP

    END LOOP;

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