Pl sql How to Continue Operation After Error Occurs in Package Body
This is an excerpt from the book Advanced PL/SQL: The Definitive Reference by Boobal Ganesan. The errors which occur during the program's execution after the compilation check is performed are called as the run-time errors or the exceptions. These errors are mostly data related and can occur in all the sections of the block like, DECLARE (CREATE section in case of a subprogram), BEGIN-END and even in the EXCEPTION section. When an exception is encountered, the control of the program unit is transferred to the current block's exception handling section. Within the exception section, the WHEN clause evaluates the exception which has been raised with the exception defined in its clause. When it matches, the statements inside that particular WHEN clause are executed. If these statements encounter into an Exception, the program control will look for an exception section in the enclosing block and if there are no exception handling sections found during this exception occurrence, the program unit will be unconditionally terminated from its execution skipping the rest of the statements from the program unit with an appropriate error code and an error message describing the exception occurred. The OTHERS handler in the last WHEN clause handles all types of exceptions when the exception occurred does not match with any of the exception handlers defined in the previously defined WHEN clauses in the current block. The below prototype defines the basic structure of an Exception section. DECLARE BEGIN <Executable_statements>; EXCEPTION WHEN <Exception_name1> THEN <Executable_statements> | <Subprogram_call> | <Anonymous_block>; WHEN <Exception_name2> THEN <Executable_statements> | <Subprogram_call> | <Anonymous_block>; .. WHEN OTHERS THEN <Executable_statements> | <Subprogram_call> | <Anonymous_block>; END; / % Note: The OTHERS handler must be always mentioned at the last out of all the WHEN clauses as it could override the other handlers. Oracle by default fails the program during the compilation if this happens. There are four categories of exceptions available in PL/SQL and they are ============================================ Runtime errors are errors that occur while code is executing. These errors can arise due to data problems or code problems. For instance, attempting to assign a 31-character string to a varchar2 (30) variable will cause a runtime error. Most approaches to dealing with runtime errors utilize the DBMS_Output package to isolate the location of an error so that a developer can correct the problem. This package provides an excellent debugging tool when used properly. The DBMS_Output package was first introduced with Oracle7 to allow output to the SQL buffer in SQL*Plus from PL/SQL blocks. The package was intended primarily as a debugging tool, and it has served that purpose admirably (although it is now being supplanted by step-through debuggers available in Oracle's Procedure Builder and several other third-party tools). In order to use the DBMS_Output package for debugging, you must issue the command in SQL*Plus. This command instructs SQL*Plus to collect the contents of the buffer after executing a PL/SQL block or stored PL/SQL object. The size of this buffer defaults to 2,000 characters. For practical purposes, this limit is far too low. Fortunately, you can use the command to specify the size of the buffer. In this command, n specifies the buffer size and can range from 2,000 characters to an upper limit of 1 million characters. A million characters is more than sufficient to debug any modularized block of code. Debugging with the DBMS_Output package involves mostly calls to the DBMS_Output.Put_Line() procedure. This procedure writes a line to the SQL buffer. When a block of PL/SQL code finishes executing, the contents of the SQL buffer are displayed. Listing 8.5 is an excerpt from the debugging version of the Build_SUID_Matrix package (the final version of the package can be found on the CD). Listing 8.5 An excerpt of debugging code from the Build_SUID_Matrix package. Each of the calls to the DBMS_Output.Put_Line() procedure indicates the progress of the procedure. A string of text is passed as the procedure's lone parameter. The runtime error must always occur after the last message that was delivered to the buffer. Isolating the error is now a simple matter of determining which statements occurred after the message. This is a complete book on PL/SQL with everything you need to know to write efficient and complex PL/SQL code. Oracle Training from Don Burleson Burleson is the American Team Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals. Feel free to ask questions on our Oracle forum. Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications. Errata? Oracle technology is changing and we strive to update our BC Oracle support information. If you find an error or have a suggestion for improving our content, we would appreciate your feedback. Just e-mail: Copyright © 1996 - 2020 All rights reserved by Burleson Oracle ® is the registered trademark of Oracle Corporation. Using the DBMS_Output Package
set serveroutput on set serveroutput on size n DBMS_Output.Put_Line ('Fetch ObjectSourceCode_cur');
FETCH ObjectSourceCode_cur INTO ObjectSourceCode_rec; EXIT WHEN ObjectSourceCode_cur%NOTFOUND;
-- -- Initialize variables. -- iStringLen := 0; iStringPos := 0;
-- -- Clean the line of code before processing it. -- DBMS_Output.Put_Line ('Call CleanLineOfSource'); vLine := CleanLineOfSource (ObjectSourceCode_rec.text); DBMS_Output.Put_Line (vLine);
-- -- If the line contains the string 'DELETE ', this might be a delete -- operation. -- DBMS_Output.Put_Line ('Check for DELETE '); iStringPos := instr (vLine, 'DELETE '); iStringLen := length (vLine);
-- -- Test the line to determine if the 'DELETE ' string is -- A) inside a comment -- B) part of an identifier -- DBMS_Output.Put_Line ('Is the string inside a comment?');
IF ((instr (vLine, '--') > 0) AND (instr (vLine, '--') < iStringPos)) THEN iStringPos := 0; END IF;
-- -- Is the string inside a comment? -- DBMS_Output.Put_Line ('Check for string following a */');
IF ((instr (vLine, '/*') > 0) AND (iStringPos > instr (vLine, '/*'))) THEN iStringPos := 0; END IF; -- -- Is the string inside a comment? -- DBMS_Output.Put_Line ('Check for */ without a preceding /*');
IF ((instr (vLine, '*/') > 0) AND (instr (vLine, '/*') = 0)) THEN iStringPos := 0; END IF;
-- -- Is the string inside an identifier? -- DBMS_Output.Put_Line ('Check for part of identifier'); IF ((instr (vLine, '_DELETE ') = (iStringPos - 1)) AND (instr (vLine, '_DELETE ') > 0)) THEN iStringPos := 0; END IF;
-- -- If the delete is beyond the first character of the line, -- either it is poorly written code or it is a comment. -- DBMS_Output.Put_Line ('The delete is past the first character');
IF (iStringPos > 1) THEN iStringPos := 0; END IF;
-- -- If the line has passed all the false positive tests, go ahead -- and display the table name. -- IF (iStringPos > 0) THEN vParsedString := substr (vLine, (iStringPos + 6)); END IF;
-- -- If the line has passed the false positive tests, check to see -- if it contains a 'FROM' clause. If so, remove the clause from -- the string. -- IF (iStringPos > 0) THEN iStringPos := instr (vParsedString, 'FROM ');
IF (iStringPos > 0) THEN vParsedString := substr (vParsedString, (iStringPos + 5)); END IF;
-- -- Remove the semicolon at the end of the line. -- DBMS_Output.Put_Line ('Replace ; at the end of the line'); vParsedString := replace (vParsedString, ';', '');
-- -- Call the UpdateMatrix procedure to perform the write to the -- SUID_MATRIX table. -- UpdateMatrix (vParsedString, vOwner, vObject, 'DELETE'); END IF;
Need to learn to program with PL/SQL? For complete notes on programming in PL/SQL, we recommend the book Advanced PL/SQL: The Definitive Reference by Boobal Ganesan.
The best on site " Oracle training classes" are just a phone call away! You can get personalized Oracle training by Donald Burleson, right at your shop!
Source: http://dba-oracle.com/t_adv_plsql_runtime_errors.htm
Post a Comment for "Pl sql How to Continue Operation After Error Occurs in Package Body"