Oracle Get Sql Error Message
Contents |
Server MySQL MariaDB PostgreSQL SQLite MS Office Excel Access Word Web Development HTML CSS oracle raise exception with message Color Picker Languages C Language More ASCII Table Linux UNIX
Functions For Error Trapping Are Contained In Which Section Of A Pl/sql Block
Java Clipart Techie Humor Advertisement Oracle Basics Oracle Advanced Oracle Cursors Oracle Exception Handling oracle sqlcode list Named Programmer-Defined Exception Named System Exception WHEN OTHERS Clause SQLCODE SQLERRM Oracle Foreign Keys Oracle Loops/Conditionals Oracle Transactions Oracle Triggers String/Char Functions Numeric/Math
Pl Sql Sqlcode
Functions Date/Time 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 oracle sql codes list with the most recently 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_
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
Sqlerrm Line Number
is recommended over SQLERRM, except when using the FORALL statement with its
Pl Sql Exception Handling Examples
SAVE EXCEPTIONS clause. For more information, see Retrieving the Error Code and Error Message. Syntax sqlerrm_function ::= oracle sqlcode values 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 https://www.techonthenet.com/oracle/exceptions/sqlerrm.php Oracle 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 https://docs.oracle.com/cd/B28359_01/appdev.111/b28370/sqlerrm_function.htm 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 e
its error-number argument. If the argument is omitted, it returns the error message associated with the current value of SQLCODE. SQLERRM with https://docs.oracle.com/cd/B12037_01/appdev.101/b10807/13_elems050.htm no argument is useful only in an exception handler. Outside a http://docs.oracle.com/cd/B19306_01/appdev.102/b14261/sqlerrm_function.htm handler, SQLERRM with no argument always returns the message normal, successful completion. 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 oracle sql 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 Description of the illustration sqlerrm_function.gif Keyword and Parameter Description error_number A valid Oracle error number. For a list of Oracle errors oracle get sql (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 following message: ORA-0000: normal, successful completion Passing a positive number to SQLERRM always returns the message User-Defined Exception unless you pass +100, in which case SQLERRM returns the following message: ORA-01403: no data found You cannot use SQLERRM directly in a SQL statement. Assign the value of SQLERRM to a local variable first: my_sqlerrm := SQLERRM; ... INSERT INTO errors VALUES (my_sqlerrm, ...); 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. Example The following example retrieves the error message associated with an unhandled exception, and stores it in an audit table. The SUBSTR function truncates the message if it is too long to fit in the
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 on this page enhances