Error Line Number Pl Sql
Contents |
TECHNOLOGY: PL/SQL Tracing Lines By Steven Feuerstein Find and report your errors—by line number—in Oracle Database 10g. PL/SQL offers a powerful and flexible exception architecture. Of course, there is show line number in pl sql developer always room for improvement, and in Oracle Database 10g, exception handling takes a
Pl Sql Exception Line Number
big step forward with the introduction of the DBMS_UTILITY.FORMAT_ERROR_BACKTRACE function. This article explores the problem that this function solves pl sql error message and how best to use it. Who Raised That Exception? When an exception is raised, one of the most important pieces of information a programmer would like to uncover is the line
Pl Sql Error Stack
of code that raised the exception. Prior to Oracle Database 10g, one could obtain this information only by allowing the exception to go unhandled. Let's revisit the error-handling behavior available to programmers in Oracle9i Database. Consider this simple chain of program calls in Listing 1: procedure proc3 calls proc2 calls proc1 , at which point proc1 raises the NO_DATA_FOUND exception. Notice that there pl sql error not a single-group group function is no error handling in any of the procedures; it is most significantly lacking in the top-level proc3 procedure. If I run proc3 in SQL*Plus, I will see the following results: ERROR at line 1: ORA-01403: no data found ORA-06512: at "SCOTT.PROC1", line 4 ORA-06512: at "SCOTT.PROC2", line 6 ORA-06512: at "SCOTT.PROC3", line 4 ORA-06512: at line 3 Code Listing 1: A stack of procedures CREATE OR REPLACE PROCEDURE proc1 IS BEGIN DBMS_OUTPUT.put_line ('running proc1'); RAISE NO_DATA_FOUND; END; / CREATE OR REPLACE PROCEDURE proc2 IS l_str VARCHAR2(30) := 'calling proc1'; BEGIN DBMS_OUTPUT.put_line (l_str); proc1; END; / CREATE OR REPLACE PROCEDURE proc3 IS BEGIN DBMS_OUTPUT.put_line ('calling proc2'); proc2; END; / This is the error trace dump of an unhandled exception, and it shows that the error was raised on line 4 of proc1. On the one hand, we should be very pleased with this behavior. Now that we have the line number, we can zoom right in on the problem code and fix it. On the other hand, we got this information by letting the exception go unhandled. In many applications, however, we work to avoid unhandled exceptions. Let's
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 only way to know the line number
Pl Sql Error Handling Best Practices
is to let the exception go unhandled in your PL/SQL code. In Oracle Database 10g Release
Pl Sql Error Sql Statement Ignored
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 pl sql error no data found programs and line numbers leading back to the line on which the error was originally raised. For example, prior to 10gR1: SQL> CREATE OR REPLACE PROCEDURE p1 2 IS 3 BEGIN 4 DBMS_OUTPUT.put_line ('in p1, raising error'); 5 RAISE http://www.oracle.com/technetwork/testcontent/o25plsql-093886.html 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. 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 http://awads.net/wp/2006/07/25/how-to-find-where-an-error-was-raised-in-plsql/ / 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 procedure successfully completed. The procedure p3 successfully completed and returned the execution stack at the point where the exception was raised. The first line of the stack is where the exception was raised. In this example, it was at "HR.P1", line 5. In the above e
than it can show what exception was raised.We will never know in which http://kaamka.blogspot.com/2011/05/error-line-number-in-plsql-procedure.html line exception was thrown.Best approach will be to use https://www.techonthenet.com/oracle/errors/ora06512.php combination of SQLERRM and DBMS_UTILITY.FORMAT_ERROR_BACKTRACE()Example : If we are calling many functions in single PL/SQL block then we it will give us the exact trace of exception with line number.ORA-06512: at "ABC.XYZ_PROC", line 7056 example --> DECLARE vr_sqlerrm pl sql VARCHAR2(1000):=null; BEGIN ----some code here EXCEPTION WHEN OTHERS THEN vr_sqlerrm := SUBSTR( SQLERRM||DBMS_UTILITY.FORMAT_ERROR_BACKTRACE(),0,999); raise_application_error(-20003,EXCEPTION is --> 'vr_sqlerrm); END; Posted by amardeep at 11:47 PM Labels: DBMS_UTILITY.FORMAT_ERROR_BACKTRACE, exact line number, tracking exceptions in oracle 10g No comments: Post a pl sql error Comment Newer Post Older Post Home Subscribe to: Post Comments (Atom) Followers Total Pageviews About Me amardeep View my complete profile Blog Archive ► 2016 (8) ► September (8) ► 2014 (1) ► July (1) ► 2013 (1) ► August (1) ► 2012 (1) ► May (1) ▼ 2011 (10) ▼ May (10) calculate size of folder in linux Regular Expression for no special character Regular expression in PL/SQL TATA PHOTON + on Ubuntu 10.04 (Lucid Lynx) enable profiler in JDeveloper run jvm in 64 bit mode Java GC (garbage collection) logs analysis Java Thread dump analysis Print bar code in xslt Error line number in PL/SQL procedure. ► 2009 (24) ► September (6) ► July (1) ► June (3) ► May (1) ► April (4) ► March (1) ► February (1) ► January (7) Simple template. Powered by Blogger.
MySQL MariaDB PostgreSQL SQLite MS Office Excel Access Word Web Development HTML CSS Color Picker Languages C Language More ASCII Table Linux UNIX Java Clipart Techie Humor Advertisement Oracle Basics ALIASES AND AND & OR BETWEEN COMPARISON OPERATORS DELETE DISTINCT EXISTS FROM GROUP BY HAVING IN INSERT INSERT ALL INTERSECT IS NOT NULL IS NULL JOIN LIKE MINUS NOT OR ORDER BY PIVOT REGEXP_LIKE SELECT SUBQUERY TRUNCATE UNION UNION ALL UPDATE WHERE Oracle Advanced Oracle Cursors Oracle Exception Handling Oracle Foreign Keys Oracle Loops/Conditionals Oracle Transactions Oracle Triggers String/Char Functions Numeric/Math Functions Date/Time Functions Conversion Functions Analytic Functions Advanced Functions Oracle / PLSQL: ORA-06512 Learn the cause and how to resolve the ORA-06512 error message in Oracle. Description When you encounter an ORA-06512 error, the following error message will appear: ORA-06512: at line