PLSQL Error Handling Sample

accomplished by defining an EXCEPTION block right before the END of the procedure. You could specifically handle every type of Oracle predefined exceptions (or your own custom exception) using the WHEN <exception type> THEN clause. <exception type> = OTHERS serves as a catch basin for all excpetions.

TODO: review the list of Oracle predefined exceptions

FUNCTION get_actv_loan_typ_hist_id_fn(i_loan_type_code IN XXHLS.XXHLS_LOAN_TYPE_LIST.LOAN_TYPE_CODE%TYPE)
 
    RETURN XXHLS.XXHLS_LOAN_TYPE_HISTORY.LOAN_TYPE_HISTORY_ID%TYPE
   
    IS
   
        l_loan_type_history_id XXHLS.XXHLS_LOAN_TYPE_HISTORY.LOAN_TYPE_HISTORY_ID%TYPE DEFAULT NULL;
        
    BEGIN
       
      SELECT LOAN_TYPE_HISTORY_ID
      INTO l_loan_type_history_id
      FROM XXHLS.XXHLS_LOAN_TYPE_HISTORY
      WHERE LOAN_TYPE_CODE = i_loan_type_code
        AND SYSDATE BETWEEN
        EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE;
       
      RETURN l_loan_type_history_id;
       
      EXCEPTION
        -- case for future dated record, open ended record
     
        WHEN NO_DATA_FOUND THEN
       
          SELECT LOAN_TYPE_HISTORY_ID
          INTO l_loan_type_history_id
          FROM XXHLS.XXHLS_LOAN_TYPE_HISTORY
          WHERE LOAN_TYPE_CODE = i_loan_type_code
            AND EFFECTIVE_END_DATE IS NULL;
           
          RETURN l_loan_type_history_id;
        
    END get_actv_loan_typ_hist_id_fn;

it is also possible to handle errors within an exception block like:

...

      EXCEPTION
        -- case for future dated record, open ended record

        WHEN NO_DATA_FOUND THEN

          BEGIN
     
            SELECT LOAN_TYPE_HISTORY_ID
            INTO l_loan_type_history_id
            FROM XXHLS.XXHLS_LOAN_TYPE_HISTORY
            WHERE LOAN_TYPE_CODE = i_loan_type_code
              AND EFFECTIVE_END_DATE IS NULL;
           
            RETURN l_loan_type_history_id;

          EXCEPTION


            WHEN OTHERS THEN


              DBMS.OUTPUT(SQLERRM); 
...
    

Take note of the SQLERRM object @A@ this contains the error message from the exception caught.