9 Replies Latest reply on Mar 19, 2019 11:06 AM by Cookiemonster76

    Case in For LOOP

    happy10319

      Hi,

      Running the following:

      DECLARE

      var varchar2(2);

      BEGIN

      for var in 1..5 loop

      CASE var

         WHEN 1 THEN dbms_output.put_line('A :     Excellent');

         WHEN 2 THEN dbms_output.put_line('B :     Good');

         WHEN 3 THEN dbms_output.put_line('C :     Satisfactory');

         WHEN 4 THEN dbms_output.put_line('D :     Minimal');

        

        

      END CASE;

      EXCEPTION

      WHEN CASE_NOT_FOUND THEN

      dbms_output.put_line('NOT SUCH CASE');

       

      END LOOP;

      END;

      I have:

      ORA-06550: line 13, column 1: PLS-00103: Encountered the symbol "EXCEPTION"

       

      Where am I wrong in my code?

      Thank you.

        • 1. Re: Case in For LOOP
          BEDE

          DECLARE

          var varchar2(2);

          BEGIN

          for var in 1..5 loop

          CASE var

             WHEN 1 THEN dbms_output.put_line('A :     Excellent');

             WHEN 2 THEN dbms_output.put_line('B :     Good');

             WHEN 3 THEN dbms_output.put_line('C :     Satisfactory');

             WHEN 4 THEN dbms_output.put_line('D :     Minimal');

             else dbms_output.put_line('NO SUCH CASE');

          end;

           

          END LOOP;

          END;

          • 2. Re: Case in For LOOP
            Mustafa KALAYCI

            you need to read about the exceptions! exception block is a part of begin-end structure. every "begin end" has its own exception block and exception block must be at the very end of the block. in your case your "end loop"is coming after the exception and you are violating block integrity.

            you need another "begin end" in you loop.

             

            DECLARE

            var varchar2(2);

            BEGIN

            for var in 1..5 loop

            begin

            CASE var

               WHEN 1 THEN dbms_output.put_line('A :     Excellent');

               WHEN 2 THEN dbms_output.put_line('B :     Good');

               WHEN 3 THEN dbms_output.put_line('C :     Satisfactory');

               WHEN 4 THEN dbms_output.put_line('D :     Minimal');

            END CASE;

            EXCEPTION

            WHEN CASE_NOT_FOUND THEN

            dbms_output.put_line('NOT SUCH CASE');

            end;

            END LOOP;

            END;

            • 3. Re: Case in For LOOP
              BEDE

              And a few more basic things you have missed:

              1. - when an exception is raised: if there is no else for that case, then no exception is raised, it's that just nothing of what is in the case branches is executed

              2. - you have not understood how the exception is to be placed in code.

              3. - I have never seen an Oracle named exception called CASE_NOT_FOUND (if you do find that, please indicate me the documentation). RTM on exceptions and see the Oracle named exceptions and how you can define your own exceptions

              Normally each pl/sql block can have an exception section and only one.

              For instance, take the sample below:

               

              declare

                v_num_stuff number;

                v_char_stuff varchar2(100);

              begin

                begin

                  select ... into v_num_stuff, v_char_stuff ...

                  from...

                  where..

              exception

                  when no_data_found then

                      dbms_output.put_line('Dreadful sorry, no data!');

                  when too_many_rows then

                      dbms_output.put_line('too many rows satisfy the condition');

              end;

                  v_num_stuff:=v_num+1;

                  dbms_output.put_line(v_num_stuff);

              end;

               

              That's a daft piece of code, but just intended to show how exceptions can be handled form the point of view of code structure.

              Generally it's good to have exception handler for whatever SQL statement that may raise an exception and not just one exception handler for a PL/SQL block containing some 10-20 SQL statements, each of which may possibly raise an exception. So you may give messages indicating more exactly what SQL statement failed and log failures in some log table.

               

              To study:

               

              https://docs.oracle.com/database/121/LNPLS/errors.htm#GUID-8C327B4A-71FA-4CFB-8BC9-4550A23734D6

               

               

              https://docs.oracle.com/database/121/LNPLS/case_statement.htm#LNPLS01304

               

              The above for the problems you have encountered until now. But, of course, there is much, much more... Generally, RTM first.

              • 5. Re: Case in For LOOP
                BluShadow

                BEDE wrote:

                 

                3. - I have never seen an Oracle named exception called CASE_NOT_FOUND (if you do find that, please indicate me the documentation). RTM on exceptions and see the Oracle named exceptions and how you can define your own exceptions

                <cut>

                https://docs.oracle.com/database/121/LNPLS/case_statement.htm#LNPLS01304

                 

                The above for the problems you have encountered until now. But, of course, there is much, much more... Generally, RTM first.

                 

                 

                It's in the very documentation you linked to for the CASE statement.  RTM. 

                 

                Example:

                 

                SQL> begin
                  2    case when true = false then
                  3          dbms_output.put_line('1');
                  4        when 1 = 0 then
                  5          dbms_output.put_line('2');
                  6    end case;
                  7  exception
                  8    when case_not_found then
                  9      dbms_output.put_line('3');
                10  end;
                11  /
                3

                PL/SQL procedure successfully completed.

                 

                Works for case statements in PL/SQL.

                 

                The only problem with the OP's code is he's put the exception inside the loop, rather than at the end of the code block, so it's not expecting the exception to be there before the end loop.

                 

                SQL> DECLARE
                  2    var varchar2(2);
                  3  BEGIN
                  4    for var in 1..5 loop
                  5      CASE var
                  6        WHEN 1 THEN dbms_output.put_line('A :    Excellent');
                  7        WHEN 2 THEN dbms_output.put_line('B :    Good');
                  8        WHEN 3 THEN dbms_output.put_line('C :    Satisfactory');
                  9        WHEN 4 THEN dbms_output.put_line('D :    Minimal');
                10      END CASE;
                11    END LOOP;
                12  EXCEPTION
                13    WHEN CASE_NOT_FOUND THEN
                14      dbms_output.put_line('NOT SUCH CASE');
                15  END;
                16  /
                A :    Excellent
                B :    Good
                C :    Satisfactory
                D :    Minimal
                NOT SUCH CASE

                PL/SQL procedure successfully completed.

                • 6. Re: Case in For LOOP
                  BEDE

                  Right about CASE_NOT_FOUND exception! Missed that. I haven't actually even thought that such an exception may be useful. This just compels one to use the else in order to avoid such an exception. Some kind of a safety net not to forget the else?

                  • 7. Re: Case in For LOOP
                    Cookiemonster76

                    BluShadow wrote:

                     

                     

                    The only problem with the OP's code is he's put the exception inside the loop, rather than at the end of the code block, so it's not expecting the exception to be there before the end loop.

                     

                    It's entirely possible that the exception should be inside the loop. In which case an extra BEGIN/END is needed as Mustafa showed in reply #2

                    • 8. Re: Case in For LOOP
                      BluShadow

                      Cookiemonster76 wrote:

                       

                      BluShadow wrote:

                       

                       

                      The only problem with the OP's code is he's put the exception inside the loop, rather than at the end of the code block, so it's not expecting the exception to be there before the end loop.

                       

                      It's entirely possible that the exception should be inside the loop. In which case an extra BEGIN/END is needed as Mustafa showed in reply #2

                       

                       

                      Possible... but no indication from the OP's original code that that was the case. ('scuse the pun)

                      • 9. Re: Case in For LOOP
                        Cookiemonster76

                        Apart from the fact that the exception handler was in the loop and is to catch errors from a bit of code in the loop in the case* that a particular value doesn't exist, which you might well do in a loop to let it continue to check all the values.

                         

                         

                        * almost unavoidable pun