7 Replies Latest reply on Mar 25, 2019 9:30 AM by Cookiemonster76

    EXCEPTION WHEN NO_DATA_FOUND

    happy10319

      Hi,

      On 12c

      In my PL/SQL program I treat EXCEPTION WHEN NO_DATA_FOUND as follows and after that I like to do: dbms_output.put_line('xxx');.

      But it is not executed. Where is the end of EXCEPTION and how to continue after?

      =========my program===================

      DECLARE

      i number;

      CURSOR m_cur IS

      SELECT * from TABLE1

      UNION

      SELECT * from TABLE2;

      myrow m_cur%rowtype;

      BEGIN

      select id into i from table2 where id=284;

      DBMS_output.put_line('I IS : '||i);

       

      EXCEPTION WHEN NO_DATA_FOUND THEN

      BEGIN

      OPEN m_cur;

           LOOP

              FETCH m_cur INTO myrow;

              EXIT WHEN m_cur%NOTFOUND;

              i:=myrow.id;

      dbms_output.put_line('i : '||i);

           END LOOP;

           CLOSE m_cur;

      END;

      dbms_output.put_line('xxx');

      END;

      =============output========================

       

       

      Thanks.

       

      Construction:

      create  TABLE TABLE1 (id number, PROGRAM varchar2(25));

      create  TABLE TABLE2 (id number, PROGRAM varchar2(25));

      Insert into TABLE1 (ID,PROGRAM) values ('1681','prog1');

       

      Insert into TABLE1 (ID,PROGRAM) values ('1687','Z_prog1');

      Insert into TABLE2 (ID,PROGRAM) values ('284','prog1');

       

      Insert into TABLE2 (ID,PROGRAM) values ('285','prog1');

       

      Insert into TABLE2 (ID,PROGRAM) values ('286','Z_prog1');

       

      Insert into TABLE2 (ID,PROGRAM) values ('287','prog1');

       

      Insert into TABLE2 (ID,PROGRAM) values ('288','prog1');

        • 1. Re: EXCEPTION WHEN NO_DATA_FOUND
          mathguy

          The dbms_output.put_line('xxx');   line is executing just fine - when the error condition is actually encountered. You wanted to test it by selecting from TABLE2 where ID=284.  There is such an ID in TABLE2, so why would you expect the error to be thrown?

           

          Change that line to  ...  where ID = -284   (make it a negative number), you will see the entire nested block AND the PUT_LINE('xxx') instruction executed exactly as you thought they should be.

           

          A PL/SQL block has an optional declarations section, an execution section (starting at BEGIN), and an optional exception section (starting at EXCEPTION). Once your execution section throws an exception, control flow passes to the EXCEPTION section and it NEVER returns to the execution section. The instructions in the EXCEPTION section are executed, until the END statement is found at the end of the block.

          • 2. Re: EXCEPTION WHEN NO_DATA_FOUND
            Raj Nath

            I executed given code and could see working as expected. In case of exception, i could see the block to execute the code written in and also printing 'xxx'.

             

            Could please elaborate more on your expectation from the program.

            • 3. Re: EXCEPTION WHEN NO_DATA_FOUND
              happy10319

              Thanks.

              May be I did not explain well.

              How can I continue even if select returns a row? Even no exception. Yes I saw that when exception raise (id=999) it does what asked. But when no exception can't we continue and do something?

              Regards.

              • 4. Re: EXCEPTION WHEN NO_DATA_FOUND
                KayK

                Hi Happy,

                maybe you want something like this:

                 

                 

                DECLARE
                  search_id number;
                  count_id  number;
                  CURSOR m_cur IS
                  SELECT * from TABLE1
                  UNION
                  SELECT * from TABLE2;
                  myrow m_cur%rowtype;
                BEGIN
                  search_id := -284;
                  select count(*)
                    into count_id
                    from table2
                   where id = search_id;
                  --
                  if count_id > 0 then 
                    DBMS_output.put_line('ID found : ' || search_id || ', rows ' || count_id );
                  else
                  for myrow in m_cur
                    LOOP
                        dbms_output.put_line('myrow.id : '|| myrow.id);
                    END LOOP;
                  end if;
                EXCEPTION WHEN NO_DATA_FOUND THEN
                    dbms_output.put_line('exception no_data_found');
                END;
                /
                
                myrow.id : 284
                myrow.id : 285
                myrow.id : 286
                myrow.id : 287
                myrow.id : 288
                myrow.id : 1681
                myrow.id : 1687
                PL/SQL-Prozedur erfolgreich abgeschlossen.
                

                 

                The issue that id 284 is not present seems to be a possible event. Your program has to react on this. So it is not an exception.

                 

                regards

                Kay

                • 5. Re: EXCEPTION WHEN NO_DATA_FOUND
                  AndrewSayer

                  happy10319 wrote:

                   

                  Thanks.

                  May be I did not explain well.

                  How can I continue even if select returns a row? Even no exception. Yes I saw that when exception raise (id=999) it does what asked. But when no exception can't we continue and do something?

                  Regards.

                  Of course you can. As youve already demonstrated, anything within the begin part of that anonymous block is executed in order until an exception is raised, at which point it skips to the exception handling part of your block.

                  If you want things to happen on success then just code it as normal. If you only want that exception to matter for that particular SQL statement, then you would declare it as it‘s own pl/sql block.

                   

                  ie

                  begin

                  begin

                  select statement that raises error

                  exception when that error then

                  handle the error

                  end

                  this is still inside the begin part of the main outer pl/sql block so I this will execute unless an unhan error is thrown from the inner block (or if the handler reraises)

                  end

                   

                  BTW dbms_output is a terrible way of reporting anything, it relies on the client process asking what’s in the buffer and relies on it being enabled. The best way to give further information when an error occurs is to raise it with that information using raise_application_error

                  • 6. Re: EXCEPTION WHEN NO_DATA_FOUND
                    BluShadow

                    A couple of useful community documents for you to read:

                     

                    The first explains about how to structure your code and exception blocks and how execution processes through them...

                     

                    PL/SQL 101 : Exception Handling

                     

                    And the second is about using DBMS_OUTPUT as an output mechanism (or the fact that you shouldn't really)...

                     

                    PL/SQL 101 - DBMS_OUTPUT

                    • 7. Re: EXCEPTION WHEN NO_DATA_FOUND
                      Cookiemonster76

                      happy10319 wrote:

                       

                      Thanks.

                      May be I did not explain well.

                      How can I continue even if select returns a row? Even no exception. Yes I saw that when exception raise (id=999) it does what asked. But when no exception can't we continue and do something?

                      Regards.

                      The code always continues when there's no error.

                      As previously mentioned, the 'xxx' is in the exception handler so it'll never be executed if there's no error.

                      If you want that to appear regardless of whether there's an exception then you need to either:

                      1) put it in the code twice - so also put it immediately before the exception handler

                      2) nest the code, e.g.

                      DECLARE
                      
                        i number;
                        CURSOR m_cur IS
                        SELECT * from TABLE1
                        UNION
                        SELECT * from TABLE2;
                      
                        myrow m_cur%rowtype;
                      
                      BEGIN
                      
                        BEGIN
                         
                          select id into i from table2 where id=284;
                          DBMS_output.put_line('I IS : '||i);
                      
                        EXCEPTION WHEN NO_DATA_FOUND THEN
                      
                          OPEN m_cur;
                          LOOP
                      
                            FETCH m_cur INTO myrow;
                            EXIT WHEN m_cur%NOTFOUND;
                            i:=myrow.id;
                      
                            dbms_output.put_line('i : '||i);
                      
                          END LOOP;
                      
                          CLOSE m_cur;
                      
                        END;
                      
                        dbms_output.put_line('xxx');
                      
                      END;