SQL> BEGIN 2 EXECUTE IMMEDIATE 'garbage'; 3 EXCEPTION 4 WHEN OTHERS THEN 5 DBMS_OUTPUT.PUT_LINE( SQLERRM ); 6 RAISE; 7 END; 8 / ORA-00900: invalid SQL statement BEGIN * ERROR at line Instead of calling and parsing the backtrace function in each exception section, I can call the bt.info function and report on the specifics of the error. This makes it difficult to detect the error location especially with big program units, unless you wrap every statement with exception handler as Jeffrey answer's stated. SQL> BEGIN 2 will_error(); 3 EXCEPTION 4 WHEN OTHERS THEN 5 DBMS_OUTPUT.PUT_LINE( SQLERRM ); 6 DBMS_OUTPUT.PUT_LINE( DBMS_UTILITY.FORMAT_ERROR_BACKTRACE ); 7 END; 8 / ORA-06501: PL/SQL: program error ORA-06512: at "SCOTT.WILL_ERROR", line 3 this page
Handling errors Errors will occur, and when they do, it is important that we know about them, and get as much details from them as possible. l_depth LOOP DBMS_OUTPUT.put_line( RPAD(i, 10) || RPAD(TO_CHAR(UTL_CALL_STACK.backtrace_line(i),'99'), 10) || UTL_CALL_STACK.backtrace_unit(i) ); END LOOP; DBMS_OUTPUT.put_line('***** Backtrace End *****'); END; / -- Run the test. In stored procedures, explicit transaction handling and exception swallowing are both very dangerous practices, since they prevent Oracle from providing "statement level consistency". For the other, non-predefined, system-errors, a name can be linked by using the pragma “EXCEPTION_INIT”. http://www.oracle.com/technetwork/testcontent/o25plsql-093886.html
Although it is recommended to use proper a description for your errors, instead of recycling error codes. What I do then is add some step-names to the procedure like : declare v_step varchar2(10); begin v_step = 'INIT'; -- do something v_step = 'CALCULATE'; -- do something v_step = ERROR_NUMBER : The error number associated with the current line in the error stack. There is a second log written in the anonymous block, so we end up with two records.
The very first step in my info function is to perform a set of INSTR calls to identify the starting and ending locations of the various elements of the string that The developer of the application might even like to display that critical information to the users so that they can immediately and accurately report the problem to the support staff. As stated earlier, Oracle has supplied the DBMS_UTILITY.FORMAT_ERROR_STACK function for years, but this is of no use in solving this problem, as the following example demonstrates. $$plsql_line But system errors could also occur from hardware failures, like the “ORA-12541: TNS: no listener”, when an ftp-server might be unreachable over the network.
With these locations established, I can now use SUBSTR to extract the desired portions and assign them to the fields in my record to be returned to the calling program, as How To Find Which Line Error Was Raised? They might, for example, take screen scrapes of their scheduling systems' output as application logs and be satisfied with the level of information demonstrated above. This is only a replacement for the existing functionality if you need the extra level of control. http://stackoverflow.com/questions/1589252/is-there-a-way-to-get-the-line-number-where-an-exception-was-thrown They can be given a number and a name.
As a side note, errors that occur in the declaration section are also handled in the calling block. Pl Sql Call Stack 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 p1: ORA-06512: END; Besides user defined errors, we could also raise one of the predefined errors. DYNAMIC_DEPTH : The number of subprograms on the call stack, starting at the current position in the call stack, to the initial call.
Home | Articles | Scripts | Blog | Certification | Misc | About About Tim Hall Copyright & Disclaimer oracle-developer.net Home Articles 11g New Features 10g New Features 9i New Features http://awads.net/wp/2006/07/25/how-to-find-where-an-error-was-raised-in-plsql/ 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 EXCEPTION 8 WHEN OTHERS 9 THEN 10 RAISE NO_DATA_FOUND; 11 Pl/sql Line Number 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", What Are The Methods There In Save Exceptions In Oracle The first line of the stack is where the exception was raised.
I write a lot of PL/SQL that is used by customers frontends, and I like to log my errors before raising them since I don't have control over the frontends handling this website The UTL_CALL_STACK package contains APIs to display the backtrace. l_depth LOOP DBMS_OUTPUT.put_line( RPAD(i, 10) || RPAD(UTL_CALL_STACK.lexical_depth(i), 10) || RPAD(TO_CHAR(UTL_CALL_STACK.unit_line(i),'99'), 10) || RPAD(NVL(UTL_CALL_STACK.owner(i),' '), 10) || RPAD(NVL(UTL_CALL_STACK.current_edition(i),' '), 10) || UTL_CALL_STACK.concatenate_subprogram(UTL_CALL_STACK.subprogram(i)) ); END LOOP; DBMS_OUTPUT.put_line('***** Call Stack End *****'); END; / Senior MemberAccount Moderator Quote:But my question is How to get the Error line number that is causing the exception to throw. Oracle Error Stack Trace
Join them; it only takes a minute: Sign up Is there a way to get the line number where an exception was thrown? Show Line Number In Pl Sql Developer Please type your message and try again. 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
Senior MemberAccount Moderator Of course, the first question should be why do you use sqlerrm? "When others then dbms_output.put_line(sqlerrm)"? WHEN network_error THEN ... The "ORA-06512" error is not included, but this is implied because it is a backtrace message. Dbms_utility.format_call_stack Example For this reason, I add following block to every public function or procedure: PROCEDURE test(p_param1 IN NUMBER ,p_param2 IN VARCHAR2) IS BEGIN ...
My requirement is this.I hope this clarifies. Words that are both anagrams and synonyms of each other Is it possible to have more than one AD server with FSMO roles installed on it? Members Search Help Register Login Home Home» SQL & PL/SQL» SQL & PL/SQL» How to get Error Line Number in PL/SQL in Exception Block (Oracle 9i,220.127.116.11.0,Windows XP) Show: Today's Messages :: see here As soon as you issue a RAISE of a particular exception or re-raise the current exception, you restart the stack that the backtrace function produces.
SQL> You now have programmatic control to interrogate and display the call stack if you need to. I built a utility to do this called the BT package. 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 For example, prior to 10gR1: SQL> 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.
The 22 predefined exceptions also have a name assigned, which allows for easier, and more readable exception handling. On the one hand, we should be very pleased with this behavior. x x) has a type, then is the type system inconsistent? Code Listing 2: proc3 rewritten with FORMAT_ERROR_BACKTRACE CREATE OR REPLACE PROCEDURE proc3 IS BEGIN DBMS_OUTPUT.put_line ('calling proc2'); proc2; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.put_line ('Error stack at top level:'); my_putline (DBMS_UTILITY.FORMAT_ERROR_BACKTRACE); END;
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. What is the possible impact of dirtyc0w a.k.a. "dirty cow" bug? SQL> BEGIN 2 EXECUTE IMMEDIATE 'garbage'; 3 EXCEPTION 4 WHEN OTHERS THEN 5 DBMS_OUTPUT.PUT_LINE( DBMS_UTILITY.FORMAT_ERROR_BACKTRACE ); 6 RAISE; 7 END; 8 / ORA-06512: at line 2 BEGIN * ERROR at line