7 Replies Latest reply: Feb 28, 2011 12:33 PM by 843389 RSS

    PL SQL ( NO_DATA_FOUND exception)

    843389
      I have the following chunk of code, which could return no rows, one row or many rows. When it
      returns one or many rows the DBMS_OUTPUT.PUT_LINE prints out my values.

      When no data is found 'Hello' is not printed?

      1) Can somebody explain to me why this is happening?

      2) Suggest a way I can change the code to make the exception print when no data is found?


      SET SERVEROUTPUT ON;
      BEGIN
      FOR prec IN ( select * from xxx.part_maint where drop_partition = 'Y' )
      LOOP
      DBMS_OUTPUT.PUT_LINE (prec.SCHEMA_NAME || ' ' || prec.OBJECT_NAME);
      END LOOP;
      EXCEPTION
      WHEN NO_DATA_FOUND THEN
      DBMS_OUTPUT.PUT_LINE ('hello');
      END;
        • 1. Re: PL SQL ( NO_DATA_FOUND exception)
          SomeoneElse
          Cursor for loops don't throw the NO_DATA_FOUND exception.
          • 2. Re: PL SQL ( NO_DATA_FOUND exception)
            JustinCave
            If you code a loop, Oracle does not throw an exception if no data is returned (just as it doesn't throw a TOO_MANY_ROWS exception if more than one row is returned).

            If you do a SELECT ... INTO, Oracle will throw an error if 0 rows are returned (NO_DATA_FOUND) or if multiple rows are returned (TOO_MANY_ROWS). If you do any sort of loop, on the other hand, you have to write code if you want to specify that returning 0 rows is an error.

            Justin
            • 3. Re: PL SQL ( NO_DATA_FOUND exception)
              660376
              Here's one way to do it. I'm not sure what your data type is for drop_partition, adjust as needed:
              SET SERVEROUTPUT ON;
              DECLARE
                  v_drop_partition VARCHAR2(1) := NULL;
              BEGIN
                  SELECT drop_partition
                    INTO v_drop_partition
                    FROM xxx.part_maint
                   WHERE drop_partition = 'Y'
                  ;
                  FOR prec IN (SELECT * FROM xxx.part_maint WHERE drop_partition = 'Y' )
                  LOOP
                      DBMS_OUTPUT.PUT_LINE (prec.SCHEMA_NAME || ' ' || prec.OBJECT_NAME);
                  END LOOP;
              EXCEPTION
                  WHEN NO_DATA_FOUND THEN
                      DBMS_OUTPUT.PUT_LINE ('hello');
              END;
              • 4. Re: PL SQL ( NO_DATA_FOUND exception)
                Centinul
                Using that code you'll also get an error when there is more than one value, e.g. TOO_MANY_ROWS exception will be raised and the loop won't be executed.
                • 5. Re: PL SQL ( NO_DATA_FOUND exception)
                  William Robertson
                  Depends what you want to do, but maybe something like
                  declare
                     v_foundrows boolean := false;
                  begin
                     for r in ( select * from xxx.part_maint where drop_partition = 'Y' )
                     loop
                        dbms_output.put_line(r.schema_name || ' ' || r.object_name);
                        v_foundrows := true;
                     end loop;
                  
                     if not v_foundrows then
                        raise no_data_found;
                     end if; 
                  
                  exception
                     when no_data_found then
                        dbms_output.put_line('hello');
                  end;
                  • 6. Re: PL SQL ( NO_DATA_FOUND exception)
                    660376
                    You're right Centinul, I guess I tried to think and nothing happened ;).

                    Here's another way to do this:
                    SET SERVEROUTPUT ON;
                    DECLARE
                        v_number_of_records     NUMBER    := 0;
                    BEGIN
                        SELECT COUNT(*)
                          INTO v_number_of_records
                          FROM xxx.part_maint
                         WHERE drop_partition = 'Y'
                        ;
                        IF v_number_of_records = 0 THEN
                            RAISE NO_DATA_FOUND;
                        ELSE
                            FOR prec IN (SELECT * FROM xxx.part_maint WHERE drop_partition = 'Y' )
                            LOOP
                                DBMS_OUTPUT.PUT_LINE (prec.SCHEMA_NAME || ' ' || prec.OBJECT_NAME);
                            END LOOP;
                        END IF;
                    EXCEPTION
                        WHEN NO_DATA_FOUND THEN
                            DBMS_OUTPUT.PUT_LINE ('hello');
                    END;
                    /
                    or another way could be (less complex):
                    SET SERVEROUTPUT ON;
                    DECLARE
                        v_number_of_records     NUMBER    := 0;
                    BEGIN
                        SELECT COUNT(*)
                          INTO v_number_of_records
                          FROM xxx.part_maint
                         WHERE drop_partition = 'Y'
                        ;
                        IF v_number_of_records = 0 THEN
                            DBMS_OUTPUT.PUT_LINE ('hello');
                        ELSE
                            FOR prec IN (SELECT * FROM xxx.part_maint WHERE drop_partition = 'Y' )
                            LOOP
                                DBMS_OUTPUT.PUT_LINE (prec.SCHEMA_NAME || ' ' || prec.OBJECT_NAME);
                            END LOOP;
                        END IF;
                    END;
                    /
                    However, I like William Robertson's better than mine.

                    Edited by: Starlight Rider on Feb 28, 2011 1:15 PM
                    • 7. Re: PL SQL ( NO_DATA_FOUND exception)
                      843389
                      Thanks everybody for your expertise advice and patience with a PL SQL newbie. A few more
                      good answers like this and LUNCH is ON ME.