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

    how to trap the exception in cursors

    cubeguy
    cubeguy Sep 27, 2010 10:46 AM
      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
      • 2116 Views
      • Tags:
        • 1. Re: how to trap the exception in cursors
          Kanish
          Kanish Sep 27, 2010 10:49 AM (in response to cubeguy)
          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
            Tubby Sep 27, 2010 10:50 AM (in response to cubeguy)
            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
              Karthick2003 Sep 27, 2010 11:03 AM (in response to cubeguy)
              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
                cubeguy Sep 27, 2010 11:09 AM (in response to Kanish)
                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
                  Kanish Sep 27, 2010 11:17 AM (in response to cubeguy)
                  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
                    cubeguy Sep 27, 2010 11:20 AM (in response to Karthick2003)
                    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
                      Karthick2003 Sep 27, 2010 11:22 AM (in response to cubeguy)
                      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.