Oracle Error Line Numbers
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 dbms_utility.format_error_backtrace example in oracle course, there is always room for improvement, and in Oracle Database 10g, how to find which line error was raised in oracle exception handling takes a big step forward with the introduction of the DBMS_UTILITY.FORMAT_ERROR_BACKTRACE function. This article explores the
Oracle Error Stack Trace
problem that this function solves and how best to use it. Who Raised That Exception? When an exception is raised, one of the most important pieces of information a programmer
What Are The Methods There In Save Exceptions In Oracle
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. Consider this simple chain of program calls in Listing 1: procedure proc3 calls proc2 calls proc1 , at pl/sql line number 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. 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 g
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
$$plsql_line
company Business Learn more about hiring developers or posting ads with us Stack Overflow dbms_utility.format_call_stack example Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 6.2 format_error_stack vs format_error_backtrace million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up How does line numbering work in an Oracle trigger? up vote 4 down vote favorite 1 I have a http://www.oracle.com/technetwork/testcontent/o25plsql-093886.html trigger that's throwing an error, and I am not sure how to know which line of PL/SQL code is throwing that error. My error is [Oracle]ORA-01403: no data found ORA-06512: at "MYSCHEMA.FOO_BI", line 9 My trigger is something like this: create or replace TRIGGER "MYSCHEMA"."FOO_BI" BEFORE INSERT ON FOO REFERENCING OLD AS OLD NEW AS NEW FOR EACH ROW DECLARE NUM1 NUMBER; NUM2 NUMBER; BEGIN -- some comment if :new.batch_num is null then http://stackoverflow.com/questions/2999482/how-does-line-numbering-work-in-an-oracle-trigger SELECT COUNT(*) INTO :NEW.BATCH_NUM FROM FOO WHERE CORP_ID = :NEW.CORP_ID; end if; if :new.batch_id is null or :new.batch_id = '' then :NEW.BATCH_ID := :NEW.CORP_ID || '-' || :NEW.BATCH_NUM; end if; /* etc... */ I found what looks like a similar question but the line numbering starts with the create or replace... and represents my error line as a comment, which I think must be bogus. How is the line numbering reported when an error is thrown in execution of a trigger? oracle triggers share|improve this question asked Jun 8 '10 at 16:54 Chris Farmer 12.8k2381134 3 A Nit: In Oracle the empty string is equivalent to null. So or :new.batch_id = '' is equivalent to or :new.batch_id = null which will never return true. The first part :new.batch_id is null is all you need. –Shannon Severance Jun 8 '10 at 17:22 @Shannon: thanks for the tip! I can't claim responsibility for all of this trigger, but I have to confess that part might have been mine. Maybe. –Chris Farmer Jun 8 '10 at 17:28 add a comment| 2 Answers 2 active oldest votes up vote 13 down vote accepted The line numbering (as reported in stack traces) starts with the DECLARE being line 1. So, if you do the following: CREATE OR REP
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 http://stackoverflow.com/questions/2802182/how-to-find-out-line-number-procedure-name-in-pl-sql-in-case-of-an-error Business Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow 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 an error up vote 1 down vote favorite 1 in oracle 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 11:23 bhagwat 613 add a comment| 3 Answers 3 active oldest votes up vote oracle error line 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 matter). share|improve this answer answered Mar 2 '15 at 21:55 Greg Burghardt 6,82931234 add a comment| up vote 0 down vote Posting your exception with your question would give