Pl Sql Error Stack
Contents |
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the company Business pl sql call stack Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation
How To Find Which Line Error Was Raised In Oracle
Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 6.2 million programmers, just oracle call stack trace like you, helping each other. Join them; it only takes a minute: Sign up Oracle PL/SQL: how to get the stack trace, package name and procedure name up vote 16 down vote favorite 4 Sometimes the exception returns format_error_stack vs format_error_backtrace something like: "ORA-06502: PL/SQL: numeric or value error: character string buffer too small". It's not so readable since it doesn't report neither the table, the column and the value it tried to write. it would be useful to get the current procedure name at the moment the Exception happened or is catched. How can I obtain that? oracle exception-handling plsql stack-trace share|improve this question edited Aug 12 '11 at 2:50 APC 87.3k1384184 asked Aug 11
Utl_call_stack
'11 at 20:26 Revious 1,749135495 add a comment| 4 Answers 4 active oldest votes up vote 25 down vote accepted You probably want DBMS_UTILITY.FORMAT_ERROR_BACKTRACE function SQL> ed Wrote file afiedt.buf 1 create or replace procedure p1 2 is 3 begin 4 raise_application_error( -20001, 'Error 1', true ); 5* end; SQL> / Procedure created. SQL> create or replace procedure p2 2 as 3 begin 4 null; 5 p1; 6 end; 7 / Procedure created. SQL> begin 2 p2; 3 exception 4 when others then 5 dbms_output.put_line( dbms_utility.format_error_backtrace ); 6 end; 7 / ORA-06512: at "SCOTT.P1", line 4 ORA-06512: at "SCOTT.P2", line 5 ORA-06512: at line 2 PL/SQL procedure successfully completed. share|improve this answer edited Mar 19 '15 at 8:43 Krumia 5,79432544 answered Aug 11 '11 at 20:36 Justin Cave 160k14204250 add a comment| up vote 5 down vote Or you could use DBMS_DEBUG.PRINT_BACKTRACE share|improve this answer edited Mar 19 '15 at 8:41 Krumia 5,79432544 answered Aug 11 '11 at 20:38 mamboking 3,3161321 add a comment| up vote 5 down vote Or try DBMS_UTILITY.FORMAT_CALL_STACK share|improve this answer edited Mar 19 '15 at 8:43 Krumia 5,79432544 answered Oct 27 '14 at 13:30 xmedeko 1,99411836 add a comment| up vote 0 down vote I use the combination of DBMS_UTILITY.FORMAT_ERROR_STACK and DBMS_UTILITY.FORMAT_ERROR_BACKTRACE. (Improving the answer of Justin Cave) when others then Dbms_Output.put_line ( DBMS_UTILITY.FORMAT_ERROR_STACK() ); Dbms_Output.put_line ( DBMS_UTIL
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 2014 TECHNOLOGY: PL/SQL dbms_utility backtrace Sophisticated Call Stack Analysis By Steven Feuerstein UTL_CALL_STACK package in dbms_utility.format_error_backtrace 11g Oracle Database 12c gives developers better answers. Answer to Previous Challenge The PL/SQL Challenge question in last issue’s
Utl_call_stack Example
“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 choices were correct! This http://stackoverflow.com/questions/7032373/oracle-pl-sql-how-to-get-the-stack-trace-package-name-and-procedure-name 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 extremely helpful. There is, however, always room http://www.oracle.com/technetwork/issue-archive/2014/14-jan/o14plsql-2045346.html 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 .. 1000 5 LOOP 6 NULL; 7 END LOOP; 8 9 pkg1.proc2; 1
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, the http://awads.net/wp/2006/07/25/how-to-find-where-an-error-was-raised-in-plsql/ 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 http://www.toadworld.com/platforms/oracle/w/wiki/3591.dbms-utility-format-error-backtrace 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> CREATE OR pl sql 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 / Procedure created. pl sql error 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: at "HR.P3", line 5 PL/SQL procedur
SQL Server PRODUCTSDiscussion & Resources Benchmark Factory Code Tester for Oracle SharePlex SQL Navigator SQL Optimizer Spotlight Stat Toad Intelligence Central Toad Data Modeler Toad Data Point Toad Extension for Eclipse Toad for Hadoop Toad for IBM DB2 Toad for Oracle Toad for MySQL Toad for SQL Server Toad for SAP Solutions Toad MAC Edition Home » Platforms » Oracle » Oracle Wiki » DBMS_UTILITY.FORMAT_ERROR_BACKTRACE DBMS_UTILITY.FORMAT_ERROR_BACKTRACE Oracle Community Join Overview Forum Blog Wiki Members Contents Oracle Wiki Oracle - Wiki Agile Development / DevOps Amazon APEX Big Data SQL Data Replication Database Administration Database Tuning Docker Enterprise Content Management Systems (ECM) Enterprise Manager Exadata EXPLAIN PLAN Network Management NoSQL & Hadoop Oracle Applications Oracle Architecture Oracle Cloud Oracle Clusterware Oracle Development Oracle R Enterprise Oracle12c Peoplesoft PL/SQL Development Scripting Languages SQL Reference ANSI Reserved Words Built-in Packages DBMS_ADDM DBMS_ALERT DBMS_APPLICATION_INFO DBMS_APPLY_ADM DBMS_AQ DBMS_AQADM DBMS_AQELM DBMS_ASSERT DBMS_AUTO_TASK_ADMIN DBMS_CAPTURE_ADM DBMS_COMPARISON (OTOC197) DBMS_CUBE DBMS_CUBE_ADVISE DBMS_DB_VERSION DBMS_DDL DBMS_DEBUG DBMS_DEFER DBMS_DEFER_QUERY DBMS_DEFER_SYS DBMS_DESCRIBE DBMS_ERRLOG DBMS_EXPORT DBMS_FGA DBMS_FLASHBACK DBMS_IOT DBMS_JOB DBMS_LOB DBMS_LOCK DBMS_LOGMNR DBMS_LOGMNR_CDC_PUBLISH DBMS_LOGMNR_CDC_SUBSCRIBE DBMS_LOGMNR_D DBMS_LOGSTBY DBMS_METADATA DBMS_MVIEW DBMS_OBFUSCATION_TOOLKIT DBMS_OFFLINE_OG DBMS_OFFLINE_SNAPSHOT DBMS_OLAP DBMS_ORACLE_TRACE_AGENT DBMS_ORACLE_TRACE_USER DBMS_OUTLN DBMS_OUTPUT DBMS_PCLXUTIL DBMS_PIPE DBMS_PREPROCESSOR DBMS_PROFILER DBMS_RANDOM DBMS_RECTIFIER_DIFF DBMS_REDEFINITION DBMS_REFRESH DBMS_REPAIR DBMS_REPCAT DBMS_REPCAT_ADMIN DBMS_REPCAT_AUTH DBMS_REPCAT_INSTANTIATE DBMS_REPCAT_RGT DBMS_REPUTIL DBMS_RESOURCE_MANAGER DBMS_RESOURCE_MANAGER_PRIVS DBMS_RESULT_CACHE DBMS_RESUMABLE DBMS_RLS DBMS_ROWID DBMS_RULE DBMS_RULE_ADM DBMS_SESSION DBMS_SHARED_POOL DBMS_SNAPSHOT DBMS_SPACE DBMS_SPACE_ADMIN DBMS_SPM DBMS_SQL DBMS_STANDARD DBMS_STATS DBMS_STORAGE_MAP DBMS_STREAMS DBMS_STREAMS_ADM DBMS_STREAMS_ADVISOR_ADM DBMS_SYSTEM DBMS_TRACE DBMS_TRANSACTION DBMS_TRANSFORM DBMS_TTS DBMS_UTILITY Overview (PLS5G1) Programs (OTOC345) DBMS_UTILITY.ACTIVE_INSTANCES DBMS_UTILITY.ANALYZE_DATABASE DBMS_UTILITY.ANALYZE_PART_OBJECT DBMS_