Oracle Plsql Error Stack
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 2014 TECHNOLOGY: PL/SQL Sophisticated Call Stack Analysis By Steven Feuerstein dbms_utility.format_error_backtrace example in oracle UTL_CALL_STACK package in Oracle Database 12c gives developers better answers. Answer to Previous
How To Find Which Line Error Was Raised In Oracle
Challenge The PL/SQL Challenge question in last issue’s “SQL in PL/SQL Enhancements” article focused on enhancements for executing SQL from PL/SQL pl sql call stack in Oracle Database 12c. The quiz demonstrated the new DBMS_SQL .return_result procedure, and all three choices were correct! This third and last article on new PL/SQL features in Oracle Database 12c Release 1 focuses on oracle call stack trace 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 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
Oracle Pl Sql Error Line Number
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; 10 END; 11 / SQL> BEGIN 2 proc3; 3 END; 4 / ——————— PL/SQL Call Stack ——————— object handle line number object name 000007FF7EA83240 4 procedure HR.PROC1 000007FF7E9CC3B0 6 package body HR.PKG1 000007FF7EA0A3B0 9 procedure HR.PROC3 000007FF7EA07C00 2 anonymous block This is very helpful information for tracing and erro
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 format_error_stack vs format_error_backtrace Learn more about Stack Overflow the company Business Learn more about hiring developers utl_call_stack or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack
Sqlerrm Line Number
Overflow Community Stack Overflow is a community of 6.2 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up Oracle PL/SQL: how to get the stack http://www.oracle.com/technetwork/issue-archive/2014/14-jan/o14plsql-2045346.html trace, package name and procedure name up vote 16 down vote favorite 4 Sometimes the exception returns 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 http://stackoverflow.com/questions/7032373/oracle-pl-sql-how-to-get-the-stack-trace-package-name-and-procedure-name 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 '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 sh
Social Links Printer Friendly About Search 8i | 9i | 10g | 11g | 12c | 13c | Misc | PL/SQL | https://oracle-base.com/articles/12c/utl-call-stack-12cr1 SQL | RAC | WebLogic | Linux Home » Articles » http://www.toadworld.com/platforms/oracle/w/wiki/3591.dbms-utility-format-error-backtrace 12c » Here UTL_CALL_STACK : Get Detailed Information About the Currently Running Subprogram in Oracle Database 12c Release 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 in oracle error handling 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 oracle plsql error currently running code, 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_STAC
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_UTILITY.ANALYZE_SCHEMA DBMS_UTILITY.CANONICALIZE DBMS_UTILITY.COMMA_TO_TABLE DBMS_UTILITY.COMPILE_SCHEMA DBMS_UTILITY.CREATE_ALTER_TYPE_ERR... DBMS_UTILITY.CURRENT_INSTANCE DBMS_UTILIT