Error Line Number In 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 always room for improvement, and in Oracle Database 10g, exception show line number in pl sql developer handling takes a big step forward with the introduction of the DBMS_UTILITY.FORMAT_ERROR_BACKTRACE function. This article pl sql exception line number explores the problem that this function solves and how best to use it. Who Raised That Exception? When an exception is raised, pl sql error message 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 pl sql error stack 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 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
Pl Sql Error Not A Single-group Group Function
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 exception section to the proc3 procedure and then display the error information (the simplest form of error logging). Here is the second version of proc3 : CREATE OR REPLACE PROCEDURE proc3 IS BEGIN DBMS_OUTPUT.put_line ('calling proc2'); proc2; EXCEPTION WHEN OTHERS THEN my_putline ( DBMS_UTILITY.FORMAT_ERROR_STACK); END; / Notice that I call DBMS_UTILITY.FORMAT_ERROR_STACK , because
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,
Pl Sql Error Handling Best Practices
the only way to know the line number is to let the exception go pl sql error sql statement ignored unhandled in your PL/SQL code. In Oracle Database 10g Release 1 and above, you can take advantage of the new function pl sql error no data found DBMS_UTILITY.FORMAT_ERROR_BACKTRACE. This 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> http://www.oracle.com/technetwork/testcontent/o25plsql-093886.html 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; 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 http://awads.net/wp/2006/07/25/how-to-find-where-an-error-was-raised-in-plsql/ / 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_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-
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and http://stackoverflow.com/questions/1589252/is-there-a-way-to-get-the-line-number-where-an-exception-was-thrown policies of this site About Us Learn more about Stack Overflow the company Business Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users http://oracle.ittoolbox.com/groups/technical-functional/oracle-db-l/line-number-at-runtime-in-plsql-1670070 Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them; it only takes pl sql a minute: Sign up Is there a way to get the line number where an exception was thrown? up vote 9 down vote favorite 3 Im working on a pl-sql script, in which I have about 10 TO_CHAR conversions. One of them is throwing an ORA-06502: PL/SQL: numeric or value error: character string buffer too small exception. Currently, im logging the pl sql error message with this piece of code EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.put_line('Exception message is '||SQLERRM(sqlcode)); ROLLBACK; I'd like to add (mostly for debugging purposes) the line where the exception is thrown, in order to receive a message in the form of ORA-06502: PL/SQL: numeric or value error: character string buffer too small (at line x) Is there an easy way to do this? logging plsql share|improve this question asked Oct 19 '09 at 15:10 Tom 22.9k1494145 add a comment| 4 Answers 4 active oldest votes up vote 8 down vote accepted You need 10g to use DBMS_OUTPUT.put_line('Error in '|| $$plsql_unit || ' at ' || $$plsql_line); also look into using DBMS_UTILITY.format_error_backtrace there is an article in Oracle Magazine from april '05 by Steven Feuerstein: http://www.oracle.com/technetwork/issue-archive/2005/05-mar/o25plsql-093886.html Cheers, niels share|improve this answer edited Nov 7 '13 at 13:32 Malice 2,35612539 answered Oct 19 '09 at 15:25 Niels Castle 6,8622048 add a comment| up vote 2 down vote you need 10g or above. Check DBMS_UTILITY.FORMAT_ERROR_BACKTRACE. http://www.oracle.com/technetwork/issue-archive/2005/05-mar/o25plsql-093886.html share|improve this answer edited Nov 7 '13 at 13:32 Saturnix 3,94362863 answered Oct 19 '09 at 15:11 Henry Gao 2,8511131
Technology and Trends Enterprise Architecture and EAI ERP Hardware IT Management and Strategy Java Knowledge Management Linux Networking Oracle PeopleSoft Project and Portfolio Management SAP SCM Security Siebel Storage UNIX Visual Basic Web Design and Development Windows < Back CHOOSE A DISCUSSION GROUP Research Directory TOPICS Database Hardware Networking SAP Security Web Design MEMBERS Paul_Pedant DACREE MarkDeVries MacProTX Inside-ERP VoIP_News Inside-CRM maxwellarnold I_am_the_dragon PCMag Michael Meyers-Jouan TerryCurran Chris_Day Andrew.S.Baker JoeTorre bracke Locutus Ramnath.Awate Dennis Stevenson Craig Borysowich DukeGanote Richard mircea_luca AbhaiTripathi Nikki Klein knowscognosdoi Clinton Jones blrvenkat Iqbalyk bluesguyAZ59 COMPANIES Pivotal CRM GeoTrust Avaya Trend Micro Incorporated View All Topics View All Members View All Companies Toolbox for IT Topics Oracle Groups Ask a New Question Oracle Database This group is where peers share technical expertise, solve problems, and discuss issues related to the use of Oracle Databases, including Oracle Grid. Home | Invite Peers | More Oracle Groups Your account is ready. You're now being signed in. Solve problems - It's Free Create your account in seconds E-mail address is taken If this is your account,sign in here Email address Username Between 5 and 30 characters. No spaces please The Profile Name is already in use Password Notify me of new activity in this group: Real Time Daily Never Keep me informed of the latest: White Papers Newsletter Jobs By clicking "Join Now", you agree to Toolbox for Technology terms of use, and have read and understand our privacy policy. Line Number at runtime in Pl/Sql..? Ramkumar Singh asked Oct 25, 2007 | Replies (10) Hi All, I have writtern a procedure and i have also writtern an exception handler part. Now i wanted to know on which line no it is giving an error. So there is any way thru which i can get the line number at run time..? Thanks Ram Join this group Popular White Paper On This Topic Beginner's Guide to ERP 10Replies Best Answer 0 Mark this reply as the best answer?(Choose carefully, this can't be changed) Yes | No Saving... Unknown User replied Oct 25, 2007 hi if u have errors in the procedure. then type show errors in the sql pr ompt. then u can find the errors in that procedure. ----- Origina l Message ---- Top Best Answer 0 Mark this reply as the best answer?(Choose carefully, this can