Pl/sql Error Functions
Contents |
Server MySQL MariaDB PostgreSQL SQLite MS Office Excel Access Word Web Development HTML CSS Color Picker sqlerrm in pl/sql Languages C Language More ASCII Table Linux UNIX Java pl sql sqlcode Clipart Techie Humor Advertisement Oracle Basics Oracle Advanced Oracle Cursors Oracle Exception Handling Named Programmer-Defined functions for error trapping are contained in which section of a pl/sql block Exception Named System Exception WHEN OTHERS Clause SQLCODE SQLERRM Oracle Foreign Keys Oracle Loops/Conditionals Oracle Transactions Oracle Triggers String/Char Functions Numeric/Math Functions Date/Time
Oracle Sql Error Codes
Functions Conversion Functions Analytic Functions Advanced Functions NEXT: Declare Cursor Oracle / PLSQL: SQLERRM Function This Oracle tutorial explains how to use the Oracle/PLSQL SQLERRM function with syntax and examples. What does the SQLERRM Function do? The SQLERRM function returns the error message associated with the most recently oracle sql codes list raised error exception. This function should only be used within the Exception Handling section of your code. Syntax The syntax for the SQLERRM function in Oracle/PLSQL is: SQLERRM Parameters or Arguments There are no parameters or arguments for the SQLERRM function. Note See also the SQLCODE function. Example Since EXCEPTION HANDLING is usually written with the following syntax: EXCEPTION WHEN exception_name1 THEN [statements] WHEN exception_name2 THEN [statements] WHEN exception_name_n THEN [statements] WHEN OTHERS THEN [statements] END [procedure_name]; You could use the SQLERRM function to raise an error as follows: EXCEPTION WHEN OTHERS THEN raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM); END; Or you could log the error to a table using the SQLERRM function as follows: EXCEPTION WHEN OTHERS THEN err_code := SQLCODE; err_msg := SUBSTR(SQLERRM, 1, 200); INSERT INTO audit_table (error_number, error_message) VALUES (err_code, err_msg); END; NEXT: Declare Cursor S
errors. The latter are called exceptions. Note: The language of warning and error messages depends on the NLS_LANGUAGE parameter. For oracle sqlcode list information about this parameter, see Oracle Database Globalization Support Guide. Topics Compile-Time
Oracle Raise Exception With Message
Warnings Overview of Exception Handling Internally Defined Exceptions Predefined Exceptions User-Defined Exceptions Redeclared Predefined Exceptions Raising Exceptions Explicitly
Sqlerror
Exception Propagation Unhandled Exceptions Error Code and Error Message Retrieval Continuing Execution After Handling Exceptions Retrying Transactions After Handling Exceptions See Also: "Exception Handling in Triggers" "Handling FORALL Exceptions After https://www.techonthenet.com/oracle/exceptions/sqlerrm.php FORALL Statement Completes" Tip: If you have problems creating or running PL/SQL code, check the Oracle Database trace files. The USER_DUMP_DEST initialization parameter specifies the current location of the trace files. You can find the value of this parameter by issuing SHOW PARAMETER USER_DUMP_DEST. For more information about trace files, see Oracle Database Performance Tuning Guide. Compile-Time Warnings While compiling stored https://docs.oracle.com/cd/E11882_01/appdev.112/e25519/errors.htm PL/SQL units, the PL/SQL compiler generates warnings for conditions that are not serious enough to cause errors and prevent compilation—for example, using a deprecated PL/SQL feature. To see warnings (and errors) generated during compilation, either query the static data dictionary view *_ERRORS (described in Oracle Database Reference) or, in the SQL*Plus environment, use the command SHOW ERRORS. The message code of a PL/SQL warning has the form PLW-nnnnn. For the message codes of all PL/SQL warnings, see Oracle Database Error Messages. Table 11-1 summarizes the categories of warnings. Table 11-1 Compile-Time Warning Categories Category Description Example SEVERE Condition might cause unexpected action or wrong results. Aliasing problems with parameters PERFORMANCE Condition might cause performance problems. Passing a VARCHAR2 value to a NUMBER column in an INSERT statement INFORMATIONAL Condition does not affect performance or correctness, but you might want to change it to make the code more maintainable. Code that can never run By setting the compilation parameter PLSQL_WARNINGS, you can: Enable and disable all warnings, one or more categories of warnings, or specific warnings Treat specific warnings a
and you use the RESTRICT_REFERENCES pragma to assert its purity, you cannot specify the constraints WNPS and RNPS. Note: DBMS_UTILTY.FORMAT_ERROR_STACK https://docs.oracle.com/cd/B28359_01/appdev.111/b28370/sqlerrm_function.htm is recommended over SQLERRM, except when using the FORALL statement with its http://docs.oracle.com/cd/B19306_01/appdev.102/b14261/sqlerrm_function.htm SAVE EXCEPTIONS clause. For more information, see Retrieving the Error Code and Error Message. Syntax sqlerrm_function ::= Description of the illustration sqlerrm_function.gif Keyword and Parameter Descriptions error_number An expression whose value is an Oracle Database error number. For a list of Oracle Database error numbers, see Oracle oracle sql Database Error Messages. The default error number is the one associated with the current value of SQLCODE. Like SQLCODE, SQLERRM without error_number is useful only in an exception handler. Outside an exception handler, or if the value of error_number is zero, SQLERRM returns ORA-0000. If the value of error_number is +100, SQLERRM returns ORA-01403. If pl/sql error functions the value of error_number is a positive number other than +100, SQLERRM returns this message: -error_number: non-ORACLE exception If the value of error_number is a negative number whose absolute value is an Oracle Database error number, SQLERRM returns the error message associated with that error number. For example: SQL> BEGIN 2 DBMS_OUTPUT.PUT_LINE('SQLERRM(-6511): ' || TO_CHAR(SQLERRM(-6511))); 3 END; 4 / SQLERRM(-6511): ORA-06511: PL/SQL: cursor already open PL/SQL procedure successfully completed. SQL> If the value of error_number is a negative number whose absolute value is not an Oracle Database error number, SQLERRM returns this message: ORA-error_number: Message error_number not found; product=RDBMS; facility=ORA For example: SQL> BEGIN 2 DBMS_OUTPUT.PUT_LINE('SQLERRM(-50000): ' || TO_CHAR(SQLERRM(-50000))); 3 END; 4 / SQLERRM(-50000): ORA-50000: Message 50000 not found; product=RDBMS; facility=ORA PL/SQL procedure successfully completed. SQL> Examples Example 11-11, "Displaying SQLCODE and SQLERRM" Example 12-9, "Bulk Operation that Continues Despite Exceptions" Related Topics Block EXCEPTION_INIT Pragma RESTRICT_REFERENCES Pragma SQLCODE Function Retrieving the Error Code and Error Message See Also: Oracle Database Error Messages for a list of Oracle Database error m
is useful only in an exception handler. Outside a handler, SQLERRM with no argument always returns the normal, successful completion message. For internal exceptions, SQLERRM returns the message associated with the Oracle error that occurred. The message begins with the Oracle error code. For user-defined exceptions, SQLERRM returns the message user-defined exception, unless you used the pragma EXCEPTION_INIT to associate the exception with an Oracle error number, in which case SQLERRM returns the corresponding error message. For more information, see "Retrieving the Error Code and Error Message: SQLCODE and SQLERRM". Syntax sqlerrm function ::= Description of the illustration sqlerrm_function.gif Keyword and Parameter Description error_number A valid Oracle error number. For a list of Oracle errors (ones prefixed by ORA-), see Oracle Database Error Messages. Usage Notes SQLERRM is especially useful in the OTHERS exception handler, where it lets you identify which internal exception was raised. The error number passed to SQLERRM should be negative. Passing a zero to SQLERRM always returns the ORA-0000: normal, successful completion message. Passing a positive number to SQLERRM always returns the User-Defined Exception message unless you pass +100, in which case SQLERRM returns the ORA-01403: no data found message. You cannot use SQLERRM directly in a SQL statement. Assign the value of SQLERRM to a local variable first, as shown in Example 13-6. When using pragma RESTRICT_REFERENCES to assert the purity of a stored function, you cannot specify the constraints WNPS and RNPS if the function calls SQLERRM. Examples For examples, see the following: Example 10-11, "Displaying SQLCODE and SQLERRM" Example 13-6, "Using SQLCODE and SQLERRM" Related Topics "Exception Definition" "SQLCODE Function" Scripting o