DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: Exception Handling

Hybrid View

  1. #1
    Join Date
    Sep 2005
    Posts
    3

    Exception Handling

    Why is it that when I CATCH this EXCEPTION it ends my PROC, instead of continuing on to the next statement?

    BEGIN
    SELECT seats_held
    INTO l_fcc_held
    FROM flt_cabin_capacity
    WHERE cabin = c_cabin.cabin
    AND physical_flight_id = l_physical_flight_id
    AND actual_depart_date = l_departure_date;
    EXCEPTION
    WHEN OTHERS THEN
    IF SQLCODE = 100 THEN --NO_DATA_FOUND
    l_fcc_held := 0;
    END IF;
    END;

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    You're chosing to handle all exceptions, no matter what they are.

    Would this work for you:
    Code:
    select Nvl(sum(seats_held),0) ...
    That will always return a row, even if there are no rows that match the query ...
    Code:
    SQL> create table x (col1 number);
    
    Table created.
    
    SQL> select sum(col1) from x;
    
     SUM(COL1)
    ----------
    
    
    SQL> select Nvl(sum(col1),0) from x;
    
    NVL(SUM(COL1),0)
    ----------------
                   0
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  3. #3
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,492

    Cool


    Or, you could do this:
    Code:
    BEGIN
      BEGIN
        SELECT seats_held INTO l_fcc_held
          FROM flt_cabin_capacity
        WHERE cabin = c_cabin.cabin
            AND physical_flight_id = l_physical_flight_id
            AND actual_depart_date = l_departure_date;
      EXCEPTION
        WHEN NO_DATA_FOUND THEN
              l_fcc_held := 0;
       END;
      -- Procedure Continues here...
    END;
    /
    Or this:

    Code:
    BEGIN
      SELECT seats_held INTO l_fcc_held
        FROM flt_cabin_capacity
      WHERE cabin = c_cabin.cabin
          AND physical_flight_id = l_physical_flight_id
          AND actual_depart_date = l_departure_date;
      IF SQL%NOTFOUND
      THEN
          l_fcc_held := 0;
      END IF:
    -- Procedure Continues here...
    END;
    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

  4. #4
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Quote Originally Posted by LKBrwn_DBA

    Or this:

    Code:
    BEGIN
      SELECT seats_held INTO l_fcc_held
        FROM flt_cabin_capacity
      WHERE cabin = c_cabin.cabin
          AND physical_flight_id = l_physical_flight_id
          AND actual_depart_date = l_departure_date;
      IF SQL%NOTFOUND
      THEN
          l_fcc_held := 0;
      END IF:
    -- Procedure Continues here...
    END;
    No, that one won't do. That SQL%NOTFOUND part does not handle any exception, so NO_DATA_FOUND will be raised as unhandled exception by the server.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  5. #5
    Join Date
    Dec 2001
    Location
    UK
    Posts
    1,684
    Hi.

    I guess he meant this type of thing, but got lost in the moment:

    Code:
    PROCEDURE .......
      CURSOR c IS
        SELECT seats_held
          FROM flt_cabin_capacity
         WHERE cabin = c_cabin.cabin
           AND physical_flight_id = l_physical_flight_id
           AND actual_depart_date = l_departure_date;
    BEGIN
      OPEN c;
      FETCH c INTO l_fcc_held;
      IF c%NOTFOUND THEN
          l_fcc_held := 0;
      END IF:
      CLOSE c;
    -- Procedure Continues here...
    END;
    Cheers

    Tim...
    Tim...
    OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
    OCA PL/SQL Developer
    Oracle ACE Director
    My website: oracle-base.com
    My blog: oracle-base.com/blog

  6. #6
    Join Date
    Sep 2005
    Posts
    3
    Thanks Tim.

    Before reading your reply I ended up doing the following which is simliar to what you did

    c_GenericCursor REFCUR.T_CURSOR;

    BEGIN
    OPEN c_GenericCursor FOR
    SELECT seats_booked
    INTO l_fcc_booked
    FROM flt_cabin_capacity
    WHERE cabin = c_cabin.cabin
    AND physical_flight_id = l_physical_flight_id
    AND actual_depart_date = l_departure_date;
    IF c_GenericCursor%ROWCOUNT = 0 THEN
    l_fcc_booked :=0;
    END IF;
    END;

    The way I was doing it with the Begin-Statment-Exception-Statement-End should have worked, but since is was in a Loop-End Loop statement, the In-Line Exception Handling didn't work.

    Is there are RESUME NEXT statement in Orcale, where is if there is an exception, handle it, then Resume to the next statement instead of ending the proc? I could not find anything and this is my first time using Oracle.

  7. #7
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Quote Originally Posted by sloesch
    Is there are RESUME NEXT statement in Orcale, where is if there is an exception, handle it, then Resume to the next statement instead of ending the proc? I could not find anything and this is my first time using Oracle.
    you achieve this by nesting the BEGIN-END statements ...

    Code:
    create procedure ...
    Is
    Declare
       ...
    Begin
       ...
       Begin
          ...
       Exception
          ...
       End
       ...
    End;
    /
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  8. #8
    Join Date
    Sep 2005
    Posts
    3
    So slimdave if I understand you right, for in-line exception handling I should do a END with out the (;) when I am doing something like this:

    Create Procedure ...
    Is
    Declare
    ...
    Begin
    ...
    LOOP
    ...
    FOR ... IN ...
    LOOP
    Begin
    ...
    Exception
    WHEN NO_DATA_FOUND THEN
    ... :=0;
    End
    ...
    End Loop;
    ...
    END LOOP;
    ...
    COMMIT;
    Exception
    WHEN OTHERS THEN
    ...
    ROLLBACK;
    End;

    If my first Exception is TRUE handle it otherwise fall out and goto the Main Exception Raise Error and end PROC.
    Last edited by sloesch; 09-30-2005 at 09:08 AM.

  9. #9
    Join Date
    Jun 2005
    Location
    London, UK
    Posts
    159
    I would stick with the SELECT INTO personally, and handle NO_DATA_FOUND etc as suggested above. The explicit ref cursor approach is just more complication.

  10. #10
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    You need the semi-colon, but otherwise yes.

    Here's a doc reference: http://download-west.oracle.com/docs...ems4.htm#32792
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width