0

I'm trying to handle two different exceptions could trigger in an external table that reads a CSV.

The code works great, but I'm concerned I'm doing it wrong.

SET SERVEROUTPUT ON;
DECLARE
  records INT := 0;  
  external_table EXCEPTION;
  PRAGMA EXCEPTION_INIT(external_table, -29913);    
BEGIN
  SELECT COUNT(*) INTO records FROM ext_testing;

  EXCEPTION
    WHEN external_table THEN
      IF dbms_utility.format_error_stack LIKE '%KUP-04040%' THEN
        RAISE_APPLICATION_ERROR(-20000, 'Missing CSV file!');
      ELSIF dbms_utility.format_error_stack LIKE '%ORA-30653%' THEN
          raise_application_error(-20001, 'Invalid UTI report, check for missing fields!');
      END IF;      
    WHEN others THEN      
        dbms_output.put_line('An exception occurred');
        dbms_output.put_line(SQLCODE || '=>' || SQLERRM);        
END;
1

I would suggest two small modifications:

  • re-raise the exception in the WHEN OTHERS exception handler. Not raising the exception will make the calling application think that the procedure did succeed. Or better yet don't use WHEN OTHERS: there's no point in catching an exception if you do nothing but re-raise it. Let the natural exception bubble up.
  • add an ELSE clause in the first exception handler just to be sure that you don't silently ignore a new type of error that doesn't get catched by the two IF conditions.

Aside from that, I don't see anything wrong with using dbms_utility.format_error_stack in an exception clause (in fact you can't use it anywhere else).

EXCEPTION
   WHEN external_table THEN
      IF dbms_utility.format_error_stack LIKE '%KUP-04040%' THEN
         RAISE_APPLICATION_ERROR(-20000, 'Missing CSV file!');
      ELSIF dbms_utility.format_error_stack LIKE '%ORA-30653%' THEN
         raise_application_error(-20001, 'Invalid UTI report, check for missing fields!');
      ELSE
         raise_application_error(-20002, 
                                 'An exception occurred: ' || SQLERRM,
                                 TRUE);
      END IF;
   -- WHEN OTHERS THEN -- No point really
   --    raise_application_error(-20003, 
   --                            'An unknown exception occurred: ' || SQLERRM,
   --                            TRUE);
END;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.