Re: Help on error trapping

A copy of this was sent to "Tom Deseamus" <tdeseamus_at_digital-hire.com> (if that email address didn't require changing) On Fri, 7 Jan 2000 08:24:38 -0800, you wrote:

>I'm trying to develop an exception handler that I could use to check on
>actual error numbers and supply my own error messages. Here's the code, any
>suggestions?
>

don't use dbms_output unless you follow it by a RAISE, else the caller has no way to figure out an error occured. I wouls use

exception
  when too_many_rows then

        raise_application_error( -20001, 'Too Many Rows Error Occurred' );   when dup_val_on_index then
    raise_application_error( -20002, 'Duplicate Value on index' );   ...

that way, you get your error message and the caller is aware that an error occurred.

dbms_output is designed to be used mostly in sqlplus and svrmgrl for simple debug/informational messages...

>PROCEDURE EXEXCEPTIONHANDLER(DANUMBER NUMBER, DATEXT VARCHAR2) IS
>
>THENUMBER NUMBER;
>TEXTSTUFF VARCHAR2(5);
>THEERRORNUMBER NUMBER;
>
>BEGIN
> THENUMBER := THENUMBER / DANUMBER;
> TEXTSTUFF := DATEXT * 5;
>
> DBMS_OUTPUT.ENABLE;
>
>EXCEPTION
> WHEN TOO_MANY_ROWS THEN
> DBMS_OUTPUT.PUT_LINE('TOO MANY ROWS ERROR OCCURRED');
> --WHEN wrong_number_or_types_of_arguments THEN;
> --DBMS_OUTPUT.PUT_LINE('ARGUMENT CALL MISMATCH');
> --WHEN NUMERIC_OR_VALUE THEN
> --DBMS_OUTPUT.PUT_LINE('NUMBER ERROR');
> WHEN DUP_VAL_ON_INDEX THEN
> DBMS_OUTPUT.PUT_LINE('DUPLICATE VALUE ON INDEX');
> WHEN INVALID_NUMBER THEN
> DBMS_OUTPUT.PUT_LINE('INVALID NUMBER ASSIGNMENT');
> WHEN VALUE_ERROR THEN
> DBMS_OUTPUT.PUT_LINE('TRUNCATION OR CONVERSION ERROR');
> --WHEN SQLCODE := -6512 THEN ***********************************HERE!
> --DBMS_OUTPUT.PUT_LINE('MY OWN ERROR MESSAGE');
> when others THEN
> DBMS_OUTPUT.PUT_LINE('THE ERROR IS!: ' || TO_CHAR(SQLCODE));
>
>END;
>
>
>

-- 
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'...
Current article is "Part I of V, Autonomous Transactions" updated June 21'st
 
Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation