Oracle Line Number Error
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 how to find which line error was raised in oracle powerful and flexible exception architecture. Of course, there is always room
Dbms_utility.format_error_backtrace Example In Oracle
for improvement, and in Oracle Database 10g, exception handling takes a big step forward with the what are the methods there in save exceptions in oracle introduction of the DBMS_UTILITY.FORMAT_ERROR_BACKTRACE function. This article explores the problem that this function solves and how best to use it. Who Raised That Exception? When an exception oracle error stack trace is raised, one of the most important pieces of information a programmer would like to uncover is the line 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.
Pl/sql Line Number
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 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.
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 $$plsql_line Stack Overflow the company Business Learn more about hiring developers or posting ads dbms_utility.format_call_stack example with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow
Oracle Call Stack Trace
is a community of 6.2 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up How to find out line number, procedure name in PL/SQL in case of http://www.oracle.com/technetwork/testcontent/o25plsql-093886.html an error up vote 1 down vote favorite 1 I am using a D2k 6i form and getting the error on form from stored database(oracle9i) procedure ORA-00001:Unique constraint(.) violated but i m not able to trace out from which procedure it is coming. can anybody help me regarding this oracle plsql unique-constraint ora-00001 share|improve this question edited Mar 2 '15 at 21:54 Greg Burghardt 6,82931234 asked May 10 '10 at http://stackoverflow.com/questions/2802182/how-to-find-out-line-number-procedure-name-in-pl-sql-in-case-of-an-error 11:23 bhagwat 613 add a comment| 3 Answers 3 active oldest votes up vote 1 down vote For posterity, here is the solution the OP found: ok in D2k forms there is an ON-ERROR trigger where you can use the function DBMS_ERROR_TEXT to get the procedure,package name line number of the statement from where the error is coming share|improve this answer answered May 11 '10 at 9:50 community wiki akf add a comment| up vote 1 down vote I've come across this pattern after much research, head banging and gnashing of teeth: CREATE OR REPLACE PACKAGE BODY my_schema.package_name IS PROCEDURE foo IS BEGIN -- Call stored procedures/functions that throw unhandled exceptions EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('ERROR! - ' || DBMS_UTILITY.FORMAT_ERROR_STACK || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE); END foo; END; The DBMS_UTILITY.FORMAT_ERROR_STACK function seems to give the error code and message, and DBMS_UTILITY.FORMAT_ERROR_BACKTRACE seems to give an honest to goodness stack trace, complete with line numbers and stored procedure names in Oracle 10g at least. I'm not sure if those functions are available in Oracle 9i. I couldn't find much information about this sort of thing even for Oracle 10g, so I thought I would at least post this answer since 9i is quite old (and so it 10g for that ma
is very important to find the line number on which the error had occurred. The question is how to find http://awads.net/wp/2006/07/25/how-to-find-where-an-error-was-raised-in-plsql/ that line number. Before Oracle Database 10g Release 1, the only way https://www.techonthenet.com/oracle/errors/ora06512.php 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 in oracle 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 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; oracle line number 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 / 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_UTILI
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 Error Message 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