4 Replies Latest reply: Mar 6, 2013 6:20 AM by Billy~Verreynne RSS

    Working With Exceptions

    Smile
      Hi
      I've the following block of code with exeption logic
      DECLARE   
        huge_quantity EXCEPTION;  
        
        CURSOR cur_emp is   
           SELECT e.ename ename, d.dname dname ,d.deptno deptno
             FROM emp e, dept d  
            WHERE e.deptno = d.deptno;
      
         up_limit CONSTANT NUMBER := 20;   
        message VARCHAR2(450); 
      
      BEGIN   
        FOR cur_rec in cur_emp 
         LOOP     
          
           IF cur_rec.deptno = up_limit THEN       
              message := 'The Employee ' || cur_rec.ename || ' has deptno = 20. 
                         Special discounts should be provided.Rest of the records are skipped.';
             RAISE huge_quantity;      
      
           ELSIF cur_rec.deptno <> up_limit THEN       
              message:= 'The number of unit is below the discount limit.';     
              
           END IF;      
             dbms_output.put_line (message);   
             
         END LOOP;  
      EXCEPTION    
        WHEN huge_quantity THEN     
           dbms_output.put_line ('Exception  '||message);  
         
      END;
      The block got executed successfully .
      But for the first record the IF condition got satisfied(Select Stmt) and exception is raised . The control will not go to the next record (As expected )

      But how to change the logic to get the output for all records , Means Exceptional Records and the normal records .

      Thanks
        • 1. Re: Working With Exceptions
          Paul  Horth
          Why would you expect it? The exception block is outside the loop.

          Try
          declare
            huge_quantity exception;
          
            cursor cur_emp is
              select e.ename  ename
                    ,d.dname  dname
                    ,d.deptno deptno
                from emp  e
                    ,dept d
               where e.deptno = d.deptno;
          
            up_limit constant number := 20;
            message varchar2(450);
          
          begin
            for cur_rec in cur_emp loop
              begin
                if cur_rec.deptno = up_limit then
                  message := 'The Employee ' || cur_rec.ename ||
                             ' has deptno = 20. 
                             Special discounts should be provided.Rest of the records are skipped.';
                  raise huge_quantity;
                
                elsif cur_rec.deptno up_limit then
                  message := 'The number of unit is below the discount limit.';
                
                end if;
                dbms_output.put_line(message);
              exception
                when huge_quantity then
                  dbms_output.put_line('Exception  ' || message);
              end loop;
            
            end;
          Not tested.

          I'm still a bit confused: your message 'Special discounts should be provided.Rest of the records are skipped.' seems to indicate
          you don't want to loop. (and that message won't get displayed anyway).

          BTW, lose the upper case keywords. Its a very silly 'standard'.

          Edited by: Paul Horth on Mar 6, 2013 11:37 AM
          • 2. Re: Working With Exceptions
            Karthick_Arp
            Just remove the exception then, Am i missing something?
            declare
              cursor cur_emp is
                select e.ename  ename
                      ,d.dname  dname
                      ,d.deptno deptno
                  from emp  e
                      ,dept d
                 where e.deptno = d.deptno;
             
              up_limit constant number := 20;
              message varchar2(450);
             
            begin
              for cur_rec in cur_emp loop
                  if cur_rec.deptno = up_limit then
                    message := 'Exception The Employee ' || cur_rec.ename || ' has deptno = 20.Special discounts should be provided.Rest of the records are skipped.';
                  elsif cur_rec.deptno <> up_limit then
                    message := 'The number of unit is below the discount limit.';
                  end if;
                  dbms_output.put_line(message);
              end loop;
            end;
            /
            Edited by: Karthick_Arp on Mar 6, 2013 3:38 AM
            Added example
            • 3. Re: Working With Exceptions
              Stew Ashton
              Building on Karthick's solution, you can do all the records and then raise an exception at the end if the upper limit is exceeded.
              set serveroutput on
              declare
                cursor cur_emp is select e.ename, d.dname, d.deptno
                from scott.emp e, scott.dept d
                where e.deptno = d.deptno;
              
                up_limit constant number := 20;
                message varchar2(450);
                is_limit_exceeded number := 0;
              
              begin
                for cur_rec in cur_emp loop
                  if cur_rec.deptno > up_limit then
                    is_limit_exceeded := is_limit_exceeded + 1;
                    message := 'Exception: the Employee ' || cur_rec.ename || ' has deptno exceeding the upper limit of '||up_limit||'.';
                  else
                    message := 'The department number '||cur_rec.deptno||' is not above the upper limit.';
                  end if;
                  dbms_output.put_line(message);
                end loop;
                if is_limit_exceeded > 0 then
                  raise_application_error(-20001, 'Upper limit exceeded for '||is_limit_exceeded||' records.');
                end if;
              end;
              /
              
              ORA-20001: Upper limit exceeded for 6 records.
              ORA-06512: à ligne 21
              
              The department number 10 is not above the upper limit.
              The department number 10 is not above the upper limit.
              The department number 10 is not above the upper limit.
              The department number 20 is not above the upper limit.
              The department number 20 is not above the upper limit.
              The department number 20 is not above the upper limit.
              The department number 20 is not above the upper limit.
              The department number 20 is not above the upper limit.
              Exception: the Employee WARD has deptno exceeding the upper limit of 20.
              Exception: the Employee TURNER has deptno exceeding the upper limit of 20.
              Exception: the Employee ALLEN has deptno exceeding the upper limit of 20.
              Exception: the Employee JAMES has deptno exceeding the upper limit of 20.
              Exception: the Employee BLAKE has deptno exceeding the upper limit of 20.
              Exception: the Employee MARTIN has deptno exceeding the upper limit of 20.
              • 4. Re: Working With Exceptions
                Billy~Verreynne
                Keep in mind that exceptions are not the right choice for conditional processing and conditional branching in any language.

                The primary use of an exception is to terminate the existing code block. Structured programming concepts say that a code block should not have multiple exits out of the code block. So a language that supports exceptions provide 2 basic logical exit points from a code block. One for success. One for failure. E.g.
                procedure/function  ....  // start of code block
                  .. code ..
                  assert( some-condition, else-raise-exception );
                  .. code ..
                  // successful processing exit from code block
                exception
                  // failed processing exit from code block
                end
                If you code nested code blocks and use exception handlers for these nested blocks, you are violating a basic structured programming principle.

                At times, there is no other choice, given how the call interface of another package works (e.g. UTL_HTTP). However, for your own code and your own exceptions, you should not violate this basic structured programming rule. If you do, code readability is an issue, code complexity increases, and maintenance of that code is automatically more difficult. There are some WTF code gems on the web that illustrate just how dangerous multiple exits in a single code block can be.

                This is a basic software engineering concept that applies to most (if not all) programming languages - PL/SQL included.