Home > Pl Sql > Pl Sql Show Error Line

Pl Sql Show Error Line

Contents

Listing 3 shows an example of such an occurrence. SUBPROGRAM : Subprogram name associated with the current call. 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:'); Code Listing 3: Exception handling procedure as autonomous transaction with COMMIT CREATE OR REPLACE PROCEDURE record_error IS PRAGMA AUTONOMOUS_TRANSACTION; l_code PLS_INTEGER := SQLCODE; l_mesg VARCHAR2(32767) := SQLERRM; BEGIN INSERT INTO error_log this content

The SQLERRM function returns the error message associated with the most recently raised error exception. Just e-mail: and include the URL for the page. SQL> There is very little you can do with the backtrace, other than reordering it. In this case, it is necessary to parse the backtrace string and retrieve just the top-most entry. http://www.oracle.com/technetwork/testcontent/o25plsql-093886.html

How To Find Which Line Error Was Raised In Oracle

In some cases, exceptions in nested calls result in different errors being produced by the error handler of the calling routine. This code is useful when you need to look up generic information about what might cause such a problem. CONCATENATE_SUBPROGRAM : Returns the UNIT.SUBPROGRAM.LOCAL_SUBPROGRAM form of the subprogram name. I then re-raise the same exception using the RAISE statement.

On the other hand, we got this information by letting the exception go unhandled. CREATE OR REPLACE PROCEDURE display_call_stack AS l_depth PLS_INTEGER; BEGIN l_depth := UTL_CALL_STACK.dynamic_depth; DBMS_OUTPUT.put_line('***** Call Stack Start *****'); DBMS_OUTPUT.put_line('Depth Lexical Line Owner Edition Name'); DBMS_OUTPUT.put_line('. 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", $$plsql_line This way you have (and can log) that critical line number, even if the exception is re-raised further up in the stack.

This process of “hiding” the way you implement and populate your log will make it easier and more productive to log errors. Pl/sql Line Number Suppose my error log table looks like this: CREATE TABLE error_log ( ERROR_CODE INTEGER , error_message VARCHAR2 (4000) , backtrace CLOB , callstack CLOB , created_on DATE , created_by VARCHAR2 (30) A win-win situation! What does the SQLERRM Function do?

CREATE OR REPLACE PROCEDURE display_call_stack AS l_depth PLS_INTEGER; BEGIN l_depth := UTL_CALL_STACK.dynamic_depth; DBMS_OUTPUT.put_line('***** Call Stack Start *****'); DBMS_OUTPUT.put_line('Depth Lexical Line Owner Edition Name'); DBMS_OUTPUT.put_line('. What Are The Methods There In Save Exceptions In Oracle Longest "De Bruijn phrase" in English Interviewee offered code samples from current employer -- should I accept? As a result, Oracle Database will raise the ORA-06502 error, which is predefined in PL/SQL as VALUE_ERROR. Conclusions PL/SQL provides a wide range of features to help you catch and diagnose errors as well as communicate application-specific errors to your users.

Pl/sql Line Number

He is the author of nine books on PL/SQL (all from O'Reilly Media, Inc.), including Oracle PL/SQL Best Practices and Oracle PL/SQL Programming . http://www.orafaq.com/forum/t/119924 This means that if you want to take advantage of DBMS_UTILITY.FORMAT_ERROR_BACKTRACE , take one of the following two approaches: Call the backtrace function in the exception section of the block in How To Find Which Line Error Was Raised In Oracle In other words, the exception section of a block can catch only exceptions raised in the executable section of the block. Oracle Error Stack Trace Impact of Multiple RAISEs An exception often occurs deep within the execution stack.

Of course, DBMS_OUTPUT.PUT_LINE will raise an exception if you pass it a string that is longer than 255 characters, so I will display the error message using my own enhanced version news 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. CREATE OR REPLACE PROCEDURE display_error_stack AS l_depth PLS_INTEGER; BEGIN l_depth := UTL_CALL_STACK.error_depth; DBMS_OUTPUT.put_line('***** Error Stack Start *****'); DBMS_OUTPUT.put_line('Depth Error Error'); DBMS_OUTPUT.put_line('. The trickier part of the exception section is deciding what to do after you have caught an exception. Pl Sql Call Stack

The best and simplest way to return this value is to use the TRUNC function. CREATE OR REPLACE PROCEDURE display_call_stack AS BEGIN DBMS_OUTPUT.put_line('***** Call Stack Start *****'); DBMS_OUTPUT.put_line(DBMS_UTILITY.format_call_stack); DBMS_OUTPUT.put_line('***** Call Stack End *****'); END; / -- Test package to show a nested call. Note See also the SQLCODE function. have a peek at these guys 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

DBMS_UTILITY.FORMAT_ERROR_BACKTRACE The execution call stack. Format_error_stack Vs Format_error_backtrace Disclaimer: This post was written with Oracle SQL Developer v3.1. Code Message'); DBMS_OUTPUT.put_line('--------- --------- --------------------'); FOR i IN REVERSE 1 ..

SET SERVEROUTPUT ON EXEC test_pkg.proc_1; ***** Call Stack Start ***** ----- PL/SQL Call Stack ----- object line object handle number name 0xb6d4ac18 4 procedure TEST.DISPLAY_CALL_STACK 0xb6d14298 15 package body TEST.TEST_PKG 0xb6d14298

up vote 9 down vote favorite 3 Im working on a pl-sql script, in which I have about 10 TO_CHAR conversions. Regards, Rajat Ratewal Report message to a moderator Re: How to get Error Line Number in PL/SQL in Exception Block [message #325220 is a reply to message #325195] more stack exchange communities company blog Stack Exchange Inbox Reputation and Badges sign up log in tour help Tour Start here for a quick overview of the site Help Center Detailed Dbms_utility.format_error_backtrace 11g For full explanations of both of these answers, visit plsqlchallenge.com, register or log in, and click the Closed/Taken tab in Play a Quiz.

When you record your error, you should include the information shown in Table 1, all obtainable through calls to functions supplied by Oracle Database. Raising Exceptions In most cases when an exception is raised in your application, Oracle Database will do the raising. The question is how to find that line number. http://iipseconline.com/pl-sql/pl-sql-error-line-number.html reply Scott Wesley posted 4 years ago I may have found my original issue (after upgrading today to 3.1.0.7) Nothing happens with "sho err", but "show err" or "show errors" does

If I ever need to change the structure of the error_log table, I will have to change all the INSERT statements to accommodate this change. My mission is to help you and your company be more efficient with our database tools. Welcome Tips and tricks for Oracle SQL Developer, Data Modeler, and SQLcl - that's what we do here. 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

Home | Articles | Scripts | Blog | Certification | Misc | About About Tim Hall Copyright & Disclaimer Advertisement About Us Contact Us Testimonials Donate Follow us Home Oracle / The next paragraphs describe generally the flow of execution in a block when an error occurs (see Figure 1). 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 The user (or the script that is being run) will not know that there was a problem.