the perfect 12c error handler - Apex

65 Pages·2017·4.41 MB·English

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