How To Find Error Line Number In Oracle
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 always room
Dbms_utility.format_error_backtrace Example In Oracle
for improvement, and in Oracle Database 10g, exception handling takes a big how to find which line error was raised in oracle step forward with the introduction of the DBMS_UTILITY.FORMAT_ERROR_BACKTRACE function. This article explores the problem that this function solves and how what are the methods there in save exceptions in oracle 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 of code
Pl/sql Line Number
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 is no error handling
Oracle Error Stack Trace
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 see what happens when I add an e
CommunityOracle User Group CommunityTopliners CommunityOTN Speaker BureauJava CommunityError: You don't have JavaScript enabled. This tool uses JavaScript and much of it will not work correctly without it enabled. Please turn JavaScript back on and reload this $$plsql_line page. Please enter a title. You can not post a blank
Pl Sql Call Stack
message. Please type your message and try again. More discussions in PL/SQL and SQL All PlacesDatabaseDatabase Application DevelopmentPL/SQL dbms_utility.format_call_stack example and SQL This discussion is archived 3 Replies Latest reply on Feb 27, 2010 2:30 PM by Solomon Yakobson Get line number error muttleychess Feb 27, 2010 2:08 http://www.oracle.com/technetwork/testcontent/o25plsql-093886.html PM Hi Is there some way to get line number error when there is some exception ? I use oracle 9.2.02 thank you in advance I have the same question Show 0 Likes(0) 15002Views Tags: none (add) This content has been marked as final. Show 3 replies 1. Re: Get line number error sybrand_b Feb 27, 2010 2:12 https://community.oracle.com/thread/1037981 PM (in response to muttleychess) When you don't trap the exception you will get a line number, of the line in error. When you trap the exception you need to use dbms_utility.format_error_stack. Thid will not provide correct line numbers. This issue has been resolved in 10g or 11g, and 9iR2 is going completely out of support in July. ----------- Sybrand Bakker Senikor Oracle DBA Like Show 0 Likes(0) Actions 2. Re: Get line number error Solomon Yakobson Feb 27, 2010 2:17 PM (in response to muttleychess) Not in 9.2, AFAIK. DBMS_UTILITY.FORMAT_ERROR_BACKTRACE was introduced in 10g. SY. Like Show 0 Likes(0) Actions 3. Re: Get line number error Solomon Yakobson Feb 27, 2010 2:30 PM (in response to sybrand_b) sybrand_b wrote: When you trap the exception you need to use dbms_utility.format_error_stack. Thid will not provide correct line numbers. This issue has been resolved in 10g or 11g, and 9iR2 is going completely out of support in July.FORMAT_ERROR_STACK will not provide any line numbers: SQL> select * from v$version 2 / BANNER ----------------------------------------------------
E-mail to friend How to get Error Line Number in PL/SQL in Exception Block [message #325159] Thu, 05 June 2008 03:17 rajatratewal Messages: 507Registered: March 2008 Location: INDIA Senior Member When we use exception block in pl/sql and use SQLERRM it http://www.orafaq.com/forum/t/119924 displays only the error details but not the exact line number where error has occured. But if we don't use exception block line number is also displayed. How to get Error Line Number in pl/sql exception block. Is their any method to achieve this in 9i. 10g we have DBMS_UTILITY Backtrace procedure to achieve this. Any ideas?? Report message to a moderator Re: How to get Error Line Number in PL/SQL in Exception Block in oracle [message #325173 is a reply to message #325159] Thu, 05 June 2008 04:26 Michel Cadot Messages: 63888Registered: March 2007 Location: Nanterre, France, http://... Senior MemberAccount Moderator Of course, the first question should be why do you use sqlerrm? "When others then dbms_output.put_line(sqlerrm)"? Regards Michel [Updated on: Thu, 05 June 2008 04:30]Report message to a moderator Re: How to get Error Line Number in PL/SQL in Exception Block [message #325182 is a reply to message how to find #325173] Thu, 05 June 2008 04:38 rajatratewal Messages: 507Registered: March 2008 Location: INDIA Senior Member Yes you are right Michel I am using it with others clause. I can replace it with built in or custom exceptions like NO_DATA_FOUND etc. But my question is How to get the Error line number that is causing the exception to throw. If we run a pl/sql procedure without exception block then in Sql* plus we can see the line number where error has occured. If our procedure is small it is very easy to check the statement that is causing error.But if it is quite large then it is not possible to check every line in debug mode.because in b/w begin and end clause we can have number of statements and one exception block to handle all the exceptions. Is their any method available in oracle 9i by which i can trace the statement that has caused the exception to throw. Report message to a moderator Re: How to get Error Line Number in PL/SQL in Exception Block [message #325185 is a reply to message #325182] Thu, 05 June 2008 04:44 Michel Cadot Messages: 63888Registered: March 2007 Location: Nanterre, France, http://... Senior MemberAccount Moderator Quote:But my question is How to get the Error line number that is causing the exception to throw. Just remove "when others" Regards Michel Rep