Exceptions in "Plsql" Oracle

I. Summary

In the development process of Plsql program, it is very important to monitor and deal with the program exception, including how to trigger the exception, when to deal with it, how to handle it, whether to centralize all the exceptions in the program, through the procedure or function of the common exception handling, If there is no perfect program processing mechanism, it is difficult to say that the program is a robust program, when the program encountered many types or a lot of data, the system without exception processing will inevitably lead to program error

When a certain exception is pre-contracted, it is necessary to handle the pre-awarded exception appropriately, whether to throw an exception or ignore it or another

Of course, the program does not pre-contract or some unknown exception encountered, so the exception handling module should also consider these non-foreseen programs

Plsql Abnormal data network spread a lot, are some similar, induction is very detailed, the data is wandering, also not very clear the source of the original, to the unknown great God salute

This article collated the information on the network, and re-typeset the next

• Classification of exceptions
  • System comes with exception
  • User-defined exception
• Exception throws
  • Plsql Engine Automatic Processing
  • Raise
  • Raise_application_error
• Handling of exceptions
  • User-defined exception handling
  • System comes with exception handling
  • Unknown exception handling
  • exception in the Declaration phase
• Exception propagation exception of Sqlcode and SQL ERRM
  • Exceptions propagate in the declaration block
  • The exception propagates in the execution block
• Oracle ERP Fnd_file Tools
  
  • Fnd_file.output
  • Fnd_file.log
• Attachment
  • Attachments-Oracle's own exception list
  • Accessories-Oracle Standard Error list

Ii. Overview

1. Classification of exceptions

1.1 System comes with exception

1.1.1 Concept: An Oracle error that is returned to a PL/SQL block during execution, or an error caused by an operation of PL/SQL code, such as a divisor of zero or a memory overflow condition

An internal exception is implicitly generated whenever PL/SQL violates the principles of Oracle or the principle of system dependencies.

Because each Oracle error has a number and exception is handled by name in PL/SQL, Oracle provides pre-defined internal exceptions

1.1.2 Example: An undefined exception is thrown automatically when the system encounters a predefined exception, such as No_data_found

1.2 User-defined exceptions

1.2.1 Concept: A developer display definition that passes information in a PL/SQL block to control error handling for an application

If you want to handle an unnamed inner exception, you must use the others exception handler or the pragma exception_init.

Pragma is controlled by the compiler, or is a comment for the compiler. pragma is processed at compile time, not at run time.

Exception_init tells the compiler to combine the exception name with the Oracle error code so that any inner exception can be referenced by name, and an appropriate exception handler can be written by name for the exception.

1.1.2 Example: Customizing an exception bxj_exception and throwing

2. Exception throws

2.1 Automatic processing via PLSQL engine

2.1.1 Concept: An exception is automatically thrown by the PL/SQL runtime engine when a database or PL/SQL error occurs at run time

2.1.2 Example: System self-directed throw exception, and processing

2.2 By raise

2.2.1 Concept: Exceptions can also be thrown through raise statements raise exception_name;
An explicit throw exception is a custom usage of the exception that the programmer handles the declaration, but raise is not limited to the exception that is declared, and it can throw any exception. For example, you want to detect a new runtime exception handler with a Timeout_on_resource error

2.2.2 Example: Throwing an exception by raise statement

2.3 by Raise_application_error

2.3.1 Concept: The Raise_application_error built-in function is used to throw an exception and give the exception an error number along with an error message.

The default error number for a custom exception is +1, and the default information is user_defined_exception.

The Raise_application_error function can be called in the execution and exception portions of the PL/SQL program block, explicitly throwing a named exception with a special error number.

Raise_application_error (Error_number,message[,true,false])

The range of error numbers is 20,000 to-20,999. The error message is a text string, up to 2048 bytes. True and false to indicate whether to add (TRUE) the heap (false) into the wrong heap (error stack) or overwrite (overwrite) error

2.3.2 Example: Throwing an exception by Raise_application_error statement

3. Handling of exceptions

3.1 User-defined exception handling, system self-exception handling, unknown exception handling

The following is a simple example of how to capture and handle three kinds of anomalies in a program

3.2 Exceptions in the Declaration phase

The following is a simple example of an exception in the declaration phase, which is generally rarely encountered during the package execution because it is audited during compilation

References fail unless the structure of the referenced table changes

4. Propagation of anomalies

4.1 If an exception occurs in the execution block

When an exception error is raised in the execution section, the following occurs:

If the current block sets the processing for the exception error, it executes and completes the execution of the block successfully, and then controls the transfer to the containing block.

If the processor is not defined on the current block exception error setting, the exception is propagated by throwing it in the containing block. Then perform the steps on the containing block

4.2 If an exception occurs in the declaration block

If an exception is raised in the declaration section, where an error occurs in the Declaration section, the error can affect other blocks

5. Sqlcode and SQL errm for exceptions

5.1 Concepts

Because the maximum length of the Oracle error message is 512 bytes, we can get the error message with the SQLERRM and SUBSTR functions together to get the complete wrong message, especially if it is more convenient when the when others exception handler.

• SQLCODE returns the Oracle error number encountered
• SQLERRM returns the Oracle error message encountered

5.2 Examples

Produce Sqlcode and SQLERRM

6. Oracle Fnd_file.output and Fnd_file.log

6.1 Fnd_file.output

Used to generate layout for the request output report, it is generally not possible to write a log statement here

6.2 Fnd_file.log

Used to store program log records for writing log, program exception handling records can be recorded through this API

7. Accessories

7.1 Attachments-Oracle's own exception list

• Access_into_null undefined Object
• If the corresponding when is not included in the Case_not_found case and no ELSE is set
• Collection_is_null collection Element not initialized
• Curser_already_open Cursor already open
• Dup_val_on_index duplicate values on a column corresponding to a unique index
• Invalid_cursor operation on an illegal cursor
• Invalid_number Embedded SQL Statements cannot convert characters to numbers
• No_data_found when using SELECT INTO does not return rows, or when applying an element that is not initialized by the index table
• Too_many_rows when performing a select INTO, the result set exceeds one row
• Zero_divide Divisor is 0
• Subscript_beyond_count element subscript exceeds the maximum value of a nested table or Varray
• Subscript_outside_limit the subscript is specified as a negative number when using a nested table or Varray
• When Value_error is assigned, the variable length is not sufficient to accommodate the actual data
• Login_denied PL/SQL application provides an incorrect user name or password when connecting to an Oracle database
• not_logged_on PL/SQL applications access data without a connection to the Oralce database
• Program_error PL/SQL Internal issues, you may need to reload the data Dictionary & Pl./sql System Package
• Rowtype_mismatch host cursor variable is incompatible with the return type of the PL/SQL cursor variable
• Self_is_null calling an object method on a NULL object when using an object type
• Storage_Error when running PL/SQL, out of memory space
• SYS_INVALID_ID Invalid ROWID string
• Timeout_on_resource Oracle timed out while waiting for resources

7.2 Attachments-Oracle Standard Error list
Oracle Standard Error

SOURCE Connection: Plsql_oracle EXCEPTION exception classification, handling, throwing, spreading

Exceptions in "Plsql" Oracle