Need help with basic exception handling

wilconow · #1 (**permalink**) 10-17-03, 12:35

Need help with basic exception handling
--------------------------------------------------------------------------------
Hello,
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.

Code:

 
CREATE OR REPLACE PROCEDURE PREV_EXPIRATION  -- email 06 .. this will be called by a cron job.
        
        IS 
        
        v_email coordinator.email%TYPE;
        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);

BEGIN
         
                 
        SELECT s.site_id, s.site_name, c.email, 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';
                        
                        EXCEPTION
                                 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:
www.xxx.org/contxxxs/field.html

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
HVService/xxxInc@xxx.org
800/645-1992 8:30 a.m. - 5:00 p.m. EST, Monday through Friday

Thank you for previewing COMPASS/ESL Internet Version!
'
||f_crlf||f_crlf||
 
'-------------------
This e-mail is automatically generated.  DO NOT REPLY TO THIS E-MAIL.'

, --end email message


v_recipient => 'schneidj@xxx.org');  --MAKE SURE TO ADD TWO CORRECT RECEIPIENTS !!
        
        
        --END IF;

END;
/

<< Prev Topic | Next Topic >>

wilconow · #2 (**permalink**) 10-17-03, 13:41

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

andrewst · #3 (**permalink**) 10-20-03, 08:51

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.:

EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE_APPLICATION_ERROR( -20001, 'Site 1297 information not found' );
END;

Internet Services

Web Development

Digital Marketing

Consumer Tech