Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2003
    Baltimore, MD

    Arrow Unanswered: Need help with basic exception handling

    Need help with basic exception handling
    I'm having a problem with exception handling.

    Below is code for a procedure I wrote. I have an exception of NO DATA FOUND - so when the sql query brings up nothing I want it to just produce a basic error message and terminate. When I comment out the exception code of NO DATA FOUND, it runs fine.

    However, when the exception code is in there, the procedure does not run (there is no email sent). This is when the query does find data. I don't understand why having the exception in there would cause the procedure not to run properly - especially when the error condition is not even met.

    Thanks in advance.
    Code is below.

    CREATE OR REPLACE PROCEDURE PREV_EXPIRATION  -- email 06 .. this will be called by a cron job.
            v_preview_expire_date VARCHAR(30);
            v_site_name site.site_name%TYPE;
            v_site_id site.site_id%TYPE;
            f_crlf char(2) := chr(13)||chr(10);        -- line breaks for the message body
            v_found char(1);
            SELECT s.site_id, s.site_name,, TO_CHAR(s.preview_expire_date,'Month fmDD, YYYY')
            INTO v_site_id, v_site_name, v_email, v_preview_expire_date
            FROM site s, coordinator c
            WHERE s.site_id = 1297
            AND c.site_id = 1297
            AND s.pre_exp_email_flag = '0';
                                     WHEN NO_DATA_FOUND THEN
                                         DBMS_OUTPUT.PUT_LINE ('No Data Found');
            ECOM_SEND_EMAIL (v_subject =>'COMPASS/ESL Internet Version - Preview Expiration Notice for '||v_site_name, 
    v_message => 'This e-mail is automatically generated.  DO NOT REPLY TO THIS E-MAIL.
    Your COMPASS/ESL Internet Version preview term for '||v_site_name||' has expired. At this time you are currently unable to access the program or administer tests.
    For questions about your COMPASS/ESL service, or to become a licensed COMPASS/ESL site, please contxxx your xxx Regional Representative. For a complete listing of xxx regional offices, visit:
    For other services and questions, contxxx xxx Customer Services:
    xxx, Inc, Hunt Valley Office
    Executive Plaza 1 - Suite 200
    11350 McCormick Road
    Hunt Valley, MD 21031-1080
    800/645-1992 8:30 a.m. - 5:00 p.m. EST, Monday through Friday
    Thank you for previewing COMPASS/ESL Internet Version!
    This e-mail is automatically generated.  DO NOT REPLY TO THIS E-MAIL.'
    , --end email message
    v_recipient => '');  --MAKE SURE TO ADD TWO CORRECT RECEIPIENTS !!
            --END IF;

    << Prev Topic | Next Topic >>

  2. #2
    Join Date
    Sep 2003
    Baltimore, MD

    Exclamation never mind - figured it out

    by putting the exception statement after all the executable code, it seems to take care of the problem.

  3. #3
    Join Date
    Sep 2002
    Provided Answers: 1

    Re: never mind - figured it out

    That is correct. But even better would be to remove the EXCEPTION section completely. At the moment, all it does is write "No data found" to the DBMS_OUTPUT buffer, which most users will never see and so will think it has been successful. With the EXCEPTION section removed the users will see ORA-01403: no data found and know there was a problem.

    Or you could handle it but raise a more informative message, e.g.:

    RAISE_APPLICATION_ERROR( -20001, 'Site 1297 information not found' );

Posting Permissions

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