Error Trace In Pl Sql
Contents |
January 2014 Oracle Magazine Online 2016 2015 2014 2013 2012 2011 2010 January 2014 March 2014 May 2014 July 2014 September 2014 November 2014 As Published In January/February pl sql stack trace 2014 TECHNOLOGY: PL/SQL Sophisticated Call Stack Analysis By Steven Feuerstein UTL_CALL_STACK
Pl Sql Error Message
package in Oracle Database 12c gives developers better answers. Answer to Previous Challenge The PL/SQL Challenge question pl sql error not a single-group group function in last issue’s “SQL in PL/SQL Enhancements” article focused on enhancements for executing SQL from PL/SQL in Oracle Database 12c. The quiz demonstrated the new DBMS_SQL .return_result procedure, and all three
Pl Sql Error Handling Best Practices
choices were correct! This third and last article on new PL/SQL features in Oracle Database 12c Release 1 focuses on the new UTL_CALL_STACK package. Call Stacks, Error Stacks, and Error Backtraces Prior to Oracle Database 12c, Oracle Database offered several DBMS_UTILITY functions to answer key questions programmers ask as they develop, debug, and maintain their code, and the functions have been pl sql error sql statement ignored extremely helpful. There is, however, always room for improvement, and that is why Oracle Database 12c added UTL_CALL_STACK. Before I dive into UTL_CALL_STACK, here’s a refresher on the three DBMS_UTILITY functions that are reimagined by the new UTL_CALL_STACK package. DBMS_UTILITY.FORMAT_CALL_STACK. Introduced in Oracle7, the DBMS_UTILITY.FORMAT_CALL_STACK built-in function returns a formatted string that shows the execution call stack: the sequence of invocations of procedures or functions that led to the point at which the function was called. In other words, this function answers the question, “How did I get here?” Listing 1 demonstrates the DBMS_UTILITY.FORMAT_CALL_STACK function and what the formatted string looks like. Code Listing 1: Demonstration of the DBMS_UTILITY.FORMAT_CALL_STACK function SQL> CREATE OR REPLACE PROCEDURE proc1 2 IS 3 BEGIN 4 DBMS_OUTPUT.put_line (DBMS_UTILITY.format_call_stack); 5 END; 6 / SQL> CREATE OR REPLACE PACKAGE pkg1 2 IS 3 PROCEDURE proc2; 4 END pkg1; 5 / SQL> CREATE OR REPLACE PACKAGE BODY pkg1 2 IS 3 PROCEDURE proc2 4 IS 5 BEGIN 6 proc1; 7 END; 8 END pkg1; 9 / SQL> CREATE OR REPLACE PROCEDURE proc3 2 IS 3 BEGIN 4 FOR indx IN 1
Social Links Printer Friendly About Search 8i | 9i | 10g | 11g | 12c | 13c | Misc | PL/SQL | SQL
Pl Sql Error No Data Found
| RAC | WebLogic | Linux Home » Articles » 12c
Pl Sql Error Handling When Others
» Here UTL_CALL_STACK : Get Detailed Information About the Currently Running Subprogram in Oracle Database 12c Release sql plus trace 1 (12.1) Oracle database 12c introduced the UTL_CALL_STACK package to allow programmatic access to the call stack and error stack, giving much greater flexibility for debugging and error handling http://www.oracle.com/technetwork/issue-archive/2014/14-jan/o14plsql-2045346.html of PL/SQL code. This is only a replacement for the existing functionality if you need the extra level of control. The existing functionality in the DBMS_UTILITY package is still available and has not been deprecated. Call Stack Error Stack Backtrace Call Stack The call stack allows you to identify exactly where you are in the currently running code, https://oracle-base.com/articles/12c/utl-call-stack-12cr1 which includes information about nesting of subprogram calls. In previous releases this information was displayed using the DBMS_UTILITY.FORMAT_CALL_STACK function, as shown below. -- Procedure to display the call stack. CREATE OR REPLACE PROCEDURE display_call_stack AS BEGIN DBMS_OUTPUT.put_line('***** Call Stack Start *****'); DBMS_OUTPUT.put_line(DBMS_UTILITY.format_call_stack); DBMS_OUTPUT.put_line('***** Call Stack End *****'); END; / -- Test package to show a nested call. CREATE OR REPLACE PACKAGE test_pkg AS PROCEDURE proc_1; PROCEDURE proc_2; PROCEDURE proc_3; END; / CREATE OR REPLACE PACKAGE BODY test_pkg AS PROCEDURE proc_1 AS BEGIN proc_2; END; PROCEDURE proc_2 AS BEGIN proc_3; END; PROCEDURE proc_3 AS BEGIN display_call_stack; END; END; / -- Run the test. SET SERVEROUTPUT ON EXEC test_pkg.proc_1; ***** Call Stack Start ***** ----- PL/SQL Call Stack ----- object line object handle number name 0xb6d4ac18 4 procedure TEST.DISPLAY_CALL_STACK 0xb6d14298 15 package body TEST.TEST_PKG 0xb6d14298 10 package body TEST.TEST_PKG 0xb6d14298 5 package body TEST.TEST_PKG 0xb99fe7c8 1 anonymous block ***** Call Stack End ***** PL/SQL procedure successfully completed. SQL> As you can see, the output from the DBMS_UTILITY.FORMAT_CALL_STACK function is rather ugly and we
is very important to find the line number on which the error had occurred. The question is how to find that line number. Before Oracle Database 10g Release 1, http://awads.net/wp/2006/07/25/how-to-find-where-an-error-was-raised-in-plsql/ the only way to know the line number is to let the exception go unhandled in your PL/SQL code. In Oracle Database 10g Release 1 and above, you can take advantage of the new function DBMS_UTILITY.FORMAT_ERROR_BACKTRACE. This new function returns a formatted string that displays a stack of programs and line numbers leading back to the line on which the error was originally raised. For example, prior to 10gR1: SQL> pl sql CREATE OR REPLACE PROCEDURE p1 2 IS 3 BEGIN 4 DBMS_OUTPUT.put_line ('in p1, raising error'); 5 RAISE VALUE_ERROR; 6 END; 7 / Procedure created. SQL> CREATE OR REPLACE PROCEDURE p2 2 IS 3 BEGIN 4 DBMS_OUTPUT.put_line ('in p2'); 5 DBMS_OUTPUT.put_line ('calling p1'); 6 p1; 7 END; 8 / Procedure created. SQL> CREATE OR REPLACE PROCEDURE p3 2 IS 3 BEGIN 4 DBMS_OUTPUT.put_line ('in p3, calling p2'); 5 p2; 6 END; 7 pl sql error / Procedure created. Notice the unhandled VALUE_ERROR exception raised in p1. Now, Let's call p3: SQL> BEGIN 2 DBMS_OUTPUT.put_line ('calling p3'); 3 p3; 4 END; 5 / BEGIN * ERROR at line 1: ORA-06502: PL/SQL: numeric or value error ORA-06512: at "HR.P1", line 5 ORA-06512: at "HR.P2", line 6 ORA-06512: at "HR.P3", line 5 ORA-06512: at line 3 As expected, by not handling the exception, the procedure returns an error and we are able to know where the exception was raised. In this example, the error ORA-06502: PL/SQL: numeric or value error was raised at "HR.P1", line 5. Starting with 10gR1, you can call the DBMS_UTILITY.FORMAT_ERROR_BACKTRACE function in your exception handler. Let's use this function in the exception section of procedure p3: SQL> CREATE OR REPLACE PROCEDURE p3 2 IS 3 BEGIN 4 DBMS_OUTPUT.put_line ('in p3, calling p2'); 5 p2; 6 EXCEPTION 7 WHEN OTHERS 8 THEN 9 DBMS_OUTPUT.put_line ('Error stack from p3:'); 10 DBMS_OUTPUT.put_line 11 (DBMS_UTILITY.format_error_backtrace); 12 END; 13 / Procedure created. Let's call p3: SQL> set serveroutput on SQL> BEGIN 2 DBMS_OUTPUT.put_line ('calling p3'); 3 p3; 4 END; 5 / calling p3 in p3, calling p2 in p2 calling p1 in p1, raising error Error stack from p3: ORA-06512: at "HR.P1", line 5 ORA-06512: at "HR.P2", line 6 ORA-06512: