Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2004
    Location
    USA
    Posts
    13

    Unanswered: Exception handling in Oracle 8

    Hi,

    How do I bypass raising an exception by Oracle when it cannot find data for a SELECT statement? I need to continue processing after the SELECT even though it didn't fetch any rows. It is not going to the SQL%NOTFOUND check block after the SELECT statement, rather, it goes to the exception block and breaking out of the program.

    I know that I could have a seperate exception handling block for the SELECT and keep the process continue with the outer block. Since I have multiple SELECT checks to do, is there any other way to accomplish this?

    Thanks in advance for suggestions.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    EXCEPTION
    WHEN OTHERS THEN RETURN;
    END;

  3. #3
    Join Date
    Jan 2004
    Location
    North Haven, CT
    Posts
    110
    Or you can define explicit cursors for the select statement and use
    cursor_name%NOTFOUND. No exception will be raised when no data found in this way. Otherwise you have to use multiple begin-exception-end blocks for individual selects.

  4. #4
    Join Date
    Jan 2004
    Location
    USA
    Posts
    13
    That will still exit from my procedure. I want to have a single procedure where I need to try multiple SELECT's one after another and I want to continue with the processing even when any of the SELECT returns no rows.

    Originally posted by anacedent
    EXCEPTION
    WHEN OTHERS THEN RETURN;
    END;

  5. #5
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    1 declare
    2 v_timestamp varchar2(32);
    3 begin
    4 dbms_output.put_line('Before');
    5 begin
    6 select timestamp into v_timestamp from sysstats where 1 = 2;
    7 EXCEPTION
    8 WHEN no_data_found THEN null;
    9 end;
    10 dbms_output.put_line('After ');
    11 begin
    12 select timestamp into v_timestamp from sysstats where 1 = 2;
    13 EXCEPTION
    14 WHEN no_data_found THEN null;
    15 end;
    16 select to_char(sysdate,'YYYY-MON-DD:HH24:MI') into v_timestamp from dual;
    17 dbms_output.put_line('Now is - ' || v_timestamp);
    18 EXCEPTION
    19 WHEN OTHERS THEN NULL;
    20* END;
    bcm@MWH.ARES.MWH.COM> /
    Before
    After
    Now is - 2004-JAN-28:13:27

    PL/SQL procedure successfully completed.

  6. #6
    Join Date
    Jan 2004
    Location
    USA
    Posts
    13
    Thank you guys for your valuable suggestions. I tried both the methods and both are working. But I still wish I could check the SQLCODE for a not found case immedialtely after the SELECT without worrying about the exceptions like what I used to do in Informix 4GL.

  7. #7
    Join Date
    May 2008
    Posts
    3
    difference between userdefined exception and raise_applivation_error in oracle

Posting Permissions

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