Oracle Pl Sql 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 powerful and flexible exception architecture. Of course, there is always room for dbms_utility.format_error_backtrace example in oracle improvement, and in Oracle Database 10g, exception handling takes a big step forward
How To Find Which Line Error Was Raised In Oracle
with the introduction of the DBMS_UTILITY.FORMAT_ERROR_BACKTRACE function. This article explores the problem that this function solves and how best to what are the methods there in save exceptions in oracle 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 that raised oracle error stack trace 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 in any of
$$plsql_line
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 exception section to the proc3 procedure and
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 Stack Overflow the company pl sql call stack Business Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs dbms_utility.format_call_stack example Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 6.2 million programmers,
Format_error_stack Vs Format_error_backtrace
just like you, helping each other. Join them; it only takes 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 http://www.oracle.com/technetwork/testcontent/o25plsql-093886.html 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 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 http://stackoverflow.com/questions/1589252/is-there-a-way-to-get-the-line-number-where-an-exception-was-thrown 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 23k1495145 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,36612540 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,95372863 answered Oct 19 '09 at 15:11 Henry Gao 2,87111315 add a comment| up vote 1 down vote The answers have mentioned both, $$PLSQL_LINE & DBMS_UTILITY.FORMAT_ERROR_BACKTRACE. But I would like to add a bit about the difference between them: Predefined Inquiry Directives $$PLSQL_LINE & $$PLSQL_UNIT PLSQL_LINE predefined inquiry directive is a PLS_INTEGER literal value indicating the line number reference to $$PLSQL_LINE in the current program unit. From its definition, PLSQL_LINE is not suitable for exceptions logging because it will provide the line number of the exception, r
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 displays only the error details http://www.orafaq.com/forum/t/119924 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. http://www.praetoriate.com/t_high_perform_line_numbers.htm 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 [message #325173 is a reply to message #325159] Thu, 05 in oracle June 2008 04:26 Michel Cadot Messages: 63912Registered: 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 #325173] Thu, 05 June 2008 04:38 rajatratewal Messages: 507Registered: March 2008 Location: INDIA Senior Member Yes oracle pl sql 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: 63912Registered: 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 Report message to a moderator Re: How to get Error Line Number in PL/SQL in Exception Block [message #325192 is a reply to message #325185] Thu, 05 June 2008 0
position number. SQL*Plus skips over blank lines when compiling code, so you’ll need to determine the line of code to which the line number refers. This is done by using the list command, as shown in Figure 8.4. Figure 8.4Using the list command to find a line of code. If you like, you can also use the list command to display a range of lines. For example, list 10 15 displays lines 10 through 15 of your source code. If you specify just one line number, list will only display that line. For instance, list 10 displays only line 10 of your code. Using the list command without specifying a line number instructs SQL*Plus to display the entire contents of the buffer. When The Line Number Is Wrong Oracle reports the line number on which an error is detected. It’s not uncommon for the reported line number to be incorrect, because you’ve done something else incorrectly in your code that has no effect until Oracle tries to compile the line number specified in the output of the show errors command. Most of the time, incorrect line numbers are the result of variable and type declaration problems, or as a result of incorrect references to objects or variables. Consider again the code for the Calculate_Student_Grades() procedure, presented in Listing 8.4. Listing 8.4 The Calculate_Student_Grades() procedure. CREATE OR REPLACE PROCEDURE Calculate_Student_Grades IS CURSOR Active_Students_cur IS SELECT ssn FROM STUDENTS WHERE graduation_date IS NOT NULL; Active_Student_rec Active_Students_cur%ROWTYPE; vCurrentSSN STUDENTS.ssn%ROWTYPE; nNewGPA STUDENTS.gpa%TYPE; BEGIN FOR Active_Student_rec IN Active_Student_cur LOOP nNewGPA := Calculate_GPA (vSSN => vCurrentSSN); END LOOP; END Calculate_Student_Grades; / Attempting to compile this code generates three errors. The line and position numbers of these errors are shown in Table 8.1. Table 8.1 Compile errors for the Calculate_Student_Grades() procedure. Line Number Position Error 9 31 PLS-00310: with %ROWTYPE attribute, ‘STUDENTS.SSN’ must name a table, cursor or cursor-variable 10 40 PLS-00302: component