9/26/2023 0 Comments Jnes error mapper unsupportedYou might store such information in a separate table. Write out debugging information in your exception handlers. ![]() Test your code with different combinations of bad data to see what potential errors arise. (You will learn how to do that later in this chapter.) If your database operations might cause particular ORA- errors, associate names with these errors so you can write handlers for them. Learn the names and causes of the predefined exceptions. Handle named exceptions whenever possible, instead of using WHEN OTHERS in exception handlers. You can avoid such problems by declaring individual variables with %TYPE qualifiers, and declaring records to hold query results with %ROWTYPE qualifiers. For example, perhaps a table you query will have columns added or deleted, or their types changed. Make your programs robust enough to work even if the database is not in the state you expect. Expect that at some time, your code will be passed incorrect or null parameters, that your queries will return no rows or more rows than you expect. Errors could also occur at other times, for example if a hardware failure with disk storage or memory causes a problem that has nothing to do with your code but your code still needs to take corrective action.Īdd error-checking code whenever you can predict that an error might occur if your code gets bad input data. Errors are especially likely during arithmetic calculations, string manipulation, and database operations. Guidelines for Avoiding and Handling PL/SQL Errors and Exceptionsīecause reliability is crucial for database programs, use both error checking and exception handling to ensure your program can handle all possibilities:Īdd exception handlers whenever there is any possibility of an error occurring. With some better error checking, we could have avoided the exception entirely, by substituting a null for the answer if the denominator was zero, as shown in the following example. The last example illustrates exception handling. WHEN OTHERS THEN - handles all other errorsĭBMS_OUTPUT.PUT_LINE('Some other kind of error occurred.') ĮND - exception handlers and block end here WHEN ZERO_DIVIDE THEN - handles 'division by zero' errorĭBMS_OUTPUT.PUT_LINE('Company must have had zero earnings.') Only one of the WHEN blocks is executed. Calculation might cause division-by-zero error.ĭBMS_OUTPUT.PUT_LINE('Price/earnings ratio = ' || pe_ratio) The optional OTHERS handler catches all exceptions that the block does not name specifically.Įxample 10-1 Runtime Error Handling DECLARE If the company has zero earnings, the division operation raises the predefined exception ZERO_DIVIDE, the execution of the block is interrupted, and control is transferred to the exception handlers. For information on managing errors when using BULK COLLECT, see "Handling FORALL Exceptions with the %BULK_EXCEPTIONS Attribute".Įxample 10-1 calculates a price-to-earnings ratio for a company. If there is no enclosing block, control returns to the host environment. After an exception handler runs, the current block stops executing and the enclosing block resumes with the next statement. To handle raised exceptions, you write separate routines called exception handlers. ![]() ![]() User-defined exceptions must be raised explicitly by RAISE statements, which can also raise predefined exceptions. Internal exceptions are raised implicitly (automatically) by the run-time system. That is, normal execution stops and control transfers to the exception-handling part of your PL/SQL block or subprogram. When an error occurs, an exception is raised. Unlike internal exceptions, user-defined exceptions must be given names. For example, you might define an exception named insufficient_funds to flag overdrawn bank accounts. You can define exceptions of your own in the declarative part of any PL/SQL block, subprogram, or package. The other internal exceptions can be given names. Some common internal exceptions have predefined names, such as ZERO_DIVIDE and STORAGE_ERROR. Examples of internally defined exceptions include division by zero and out of memory. Exceptions can be internally defined (by the runtime system) or user defined. In PL/SQL, an error condition is called an exception. Overview of PL/SQL Runtime Error Handling
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |