7 Replies Latest reply on Sep 27, 2010 11:22 AM by Karthick2003

    how to trap the exception in cursors

    cubeguy
      Hi

      How to trap the exception NO DATA FOUND/other exceptions with the cursor
      DECLARE
      CURSOR c1 IS SELECT * FROM EMP WHERE empno = 1234;
      BEGIN
      FOR i IN c1 LOOP
      DBMS_OUTPUT.PUT_LINE(i.ename);
      END LOOP;
      END;
      so 1234 is not in my table, how to trap this.could some one help me please

      Edited by: user4587979 on Sep 27, 2010 3:46 AM
        • 1. Re: how to trap the exception in cursors
          Kanish
          No possible for interinsic cursor,
          DECLARE
          CURSOR c1 IS SELECT * FROM EMP WHERE empno = 1234;
          cnt number := 0;
          BEGIN
          
          For i in c1 Loop
          cnt := cnt + 1;
          end Loop;
          If cnt > 1 Then
          FOR i IN c1 LOOP
          DBMS_OUTPUT.PUT_LINE(i.ename);
          END LOOP;
          Else
          dbms_output.put_line (' No data found');
          END;
          kanish
          1 person found this helpful
          • 2. Re: how to trap the exception in cursors
            Tubby
            user4587979 wrote:
            Hi

            How to trap the exception NO DATA FOUND/other exceptions with the cursor
            DECLARE
            CURSOR c1 IS SELECT * FROM EMP WHERE empno = 1234;
            BEGIN
            FOR i IN c1 LOOP
            DBMS_OUTPUT.PUT_LINE(i.ename);
            END LOOP;
            END;
            so 1234 is not in my table, how to trap this.could some one help me please

            Edited by: user4587979 on Sep 27, 2010 3:46 AM
            You don't trap NO_DATA_FOUND in a cursor loop, as for others ... you trap and handle the ones you expect.

            NO_DATA_FOUND isn't a condition associated with the processing of a cursor loop.

            You have other options though, for example ...
            declare
               l_processed_something boolean default false;
            begin
               for x in cursor
               loop
                  l_processed_something   := true;
                  <more processing>
               end loop;
            end;
            /
            • 3. Re: how to trap the exception in cursors
              Karthick2003
              May be this
              declare
                CURSOR c1 IS SELECT * FROM EMP WHERE empno = 1234;
                lemp c1%rowtype ;
              begin
                open c1;
                loop
                  fetch c1 into lemp;
                  exit when c1%notfound;
                  DBMS_OUTPUT.PUT_LINE(lemp.ename);
                end loop;
                if c1%rowcount = 0 then
                  raise no_data_found;
                end if;
                close c1;
              end;
              • 4. Re: how to trap the exception in cursors
                cubeguy
                Hi Kanish,

                i've modified some of the your code
                 DECLARE
                 CURSOR c1 IS SELECT * FROM EMP WHERE empno = 1234;
                 A EXCEPTION;
                 cnt number :=0;
                 BEGIN
                 FOR i IN c1 LOOP
                 cnt := cnt + 1;
                 END LOOP;
                 IF cnt > 1 THEN
                 FOR i IN c1 LOOP
                 DBMS_OUTPUT.PUT_LINE(i.ename);
                 END LOOP;
                 ELSE
                 RAISE A;
                 end if;
                 EXCEPTION
                  WHEN a THEN
                  DBMS_OUTPUT.PUT_LINE('noo employee');
                 END;
                Is any thing wrong with this
                is this gives any trouble?
                • 5. Re: how to trap the exception in cursors
                  Kanish
                  No problem it should execute correctly. i had tested also.

                  kanish
                  1 person found this helpful
                  • 6. Re: how to trap the exception in cursors
                    cubeguy
                    Thanks Karthik can it be done with for loop- i tried to do that, but exception is not raising
                     DECLARE
                       CURSOR c1 IS SELECT * FROM EMP WHERE empno = 1234;
                      BEGIN
                        FOR i IN c1 LOOP
                        IF c1%rowcount = 0 THEN
                         RAISE NO_DATA_FOUND;
                     ELSE
                      DBMS_OUTPUT.PUT_LINE(i.ename);
                     end if;
                     end loop;
                     EXCEPTION
                     WHEN NO_DATA_FOUND THEN
                     DBMS_OUTPUT.PUT_LINE('No Employee');
                     END;
                    • 7. Re: how to trap the exception in cursors
                      Karthick2003
                      NO you cant. If there is no record you will not go inside the FOR loop at all.

                      You need to open the cursor explicitly.