The perfect 12c PLSQL error handler www . syntegris.de Sven-Uwe Weller Syntegris CEO, CTO "Design and Development" main Oracle technologies: APEX, SQL, PLSQL, JET Oracle Certified Professional, Oracle Certified Expert, Oracle Ace very active OTN Member Mail: [email protected] Twitter: @SvenWOracle Blog: svenweller.wordpress.com www.syntegris.de the perfect 12c/18c error handler TOPICS Callstack optimization Errorstack levels Error Backtrace trace and error logs UTL_CALL_STACK © SYNTEGRIS INFORMATION SOLUTIONS GMBH the perfect error handler ERROR HANDLER THINK What do we expect from the perfect error handler? How does a typical (11g) error handler look like? How does 12c/18c change the game? © SYNTEGRIS INFORMATION SOLUTIONS GMBH the perfect error handler ERROR HANDLER Questions about PLSQL? Ask this friendly fellow Steven Feuerstein "Handle exceptions at outermost layer to make sure that you present a non-scary feedback to your users, but be ready to add exception handlers in sub-programs and blocks in which you need to log critical application state for later debugging." © SYNTEGRIS INFORMATION SOLUTIONS GMBH the perfect error handler ERROR HANDLER What do we expect from the perfect error handler? ✦ Complete information - What: Error stack - Where: Line, Module, Error backtrace, Callstack - Additional Details: Parameters, Current Object ✦ No unwanted information ✦ No negative performance impact - unless error happens ✦ Robust to use, easy to add to code © SYNTEGRIS INFORMATION SOLUTIONS GMBH the perfect error handler ERROR HANDLER How does a typical (11g) error handler look like? begin order_api.create_order(12345); Exception Section exception when others then + logger.log_error Logging Framework (p_scope=>’Create Order’); raise; end; © SYNTEGRIS INFORMATION SOLUTIONS GMBH the perfect error handler ERROR HANDLER How does 12c change the game? UTL_CALL_STACK Other 12c error related enhancements? SQL: VALIDATE_CONVERSION DBMS_PLSQL_CODE_COVERAGE Improved Debugger PRAGMA DEPRECATE © SYNTEGRIS INFORMATION SOLUTIONS GMBH PLSQL BASICS capture error messages SQLERRM dbms_utility.format_error_stack dbms_utility.format_call_stack dbms_utility.format_error_backtrace utl_call_stack © SYNTEGRIS INFORMATION SOLUTIONS GMBH PLSQL BASICS capture error messages - EXAMPLE declare val number; function divide(a in number, b in number) return number is v_result number; begin v_result := a/b; return v_result; end divide; begin val := divide(10,0); end; / ORA-01476: divisor is equal to zero
 No handler ORA-06512: at line 7
 ORA-06512: at line 11 https://docs.oracle.com/en/database/oracle/oracle-database/18/errmg/ORA-04930.html#GUID-E38AFF74-04C8-4ACE-8BB1-16204D904026 ORA-06512: at stringline string Cause: Backtrace message as the stack is unwound by unhandled exceptions. Action: Fix the problem causing the exception or write an exception handler for this condition. Or you may need to contact your application administrator or DBA. © SYNTEGRIS INFORMATION SOLUTIONS GMBH