Oracle Error Messages Sqlerrm
Contents |
Server MySQL MariaDB PostgreSQL SQLite MS Office Excel Access Word Web Development HTML CSS Color Picker Languages C Language More ASCII Table Linux UNIX Java oracle sqlerrm Clipart Techie Humor Advertisement Oracle Basics Oracle Advanced Oracle Cursors Oracle oracle raise exception with message Exception Handling Named Programmer-Defined Exception Named System Exception WHEN OTHERS Clause SQLCODE SQLERRM Oracle Foreign Keys Oracle Loops/Conditionals oracle sqlcode list Oracle Transactions Oracle Triggers String/Char Functions Numeric/Math Functions Date/Time Functions Conversion Functions Analytic Functions Advanced Functions NEXT: Declare Cursor Oracle / PLSQL: SQLERRM Function This Oracle tutorial explains
Functions For Error Trapping Are Contained In Which Section Of A Pl/sql Block
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 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 sqlerrm line number 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 Share this page: Advertisement Back to top Home | About Us | Contact Us | Testimonials | Donate While using this site, you agree to have read and accepted our Terms of Service and Privacy Policy. We use advertisements to support this website and fund the development of new content. Copyright © 2003-2016 TechOnTheNet.com. All rights reserved.
is useful only in an exception handler. Outside a handler, SQLERRM with no argument always returns the normal, oracle sql codes list successful completion message. For internal exceptions, SQLERRM returns the message associated
Sqlerrm Invalid Identifier
with the Oracle error that occurred. The message begins with the Oracle error code. For user-defined
Sqlerrm Length
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 https://www.techonthenet.com/oracle/exceptions/sqlerrm.php 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 http://docs.oracle.com/cd/B19306_01/appdev.102/b14261/sqlerrm_function.htm 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 content navigation, but does not change the content in any way.
Error Handling" in Oracle Database PL/SQL Language Reference. See the end of this chapter for TimesTen-specific considerations. The following topics are https://docs.oracle.com/cd/E11882_01/timesten.112/e21639/exceptions.htm covered: Understanding exceptions Trapping exceptions Showing errors in ttIsql Differences in TimesTen: exception handing and error behavior Understanding exceptions This section provides an overview of exceptions in PL/SQL programming, covering https://livesql.oracle.com/apex/livesql/file/content_CSHKGPS5AHKCMUIAYYMTL1QYT.html the following topics: About exceptions Exception types About exceptions An exception is a PL/SQL error that is raised during program execution, either implicitly by TimesTen or explicitly by your oracle sql program. Handle an exception by trapping it with a handler or propagating it to the calling environment. For example, if your SELECT statement returns multiple rows, TimesTen returns an error (exception) at runtime. As the following example shows, you would see TimesTen error 8507, then the associated ORA error message. (ORA messages, originally defined for Oracle Database, are oracle error messages similarly implemented by TimesTen.) Command> DECLARE > v_lname VARCHAR2 (15); > BEGIN > SELECT last_name INTO v_lname > FROM employees > WHERE first_name = 'John'; > DBMS_OUTPUT.PUT_LINE ('Last name is :' || v_lname); > END; > / 8507: ORA-01422: exact fetch returns more than requested number of rows 8507: ORA-06512: at line 4 The command failed. You can handle such exceptions in your PL/SQL block so that your program completes successfully. For example: Command> DECLARE > v_lname VARCHAR2 (15); > BEGIN > SELECT last_name INTO v_lname > FROM employees > WHERE first_name = 'John'; > DBMS_OUTPUT.PUT_LINE ('Last name is :' || v_lname); > EXCEPTION > WHEN TOO_MANY_ROWS THEN > DBMS_OUTPUT.PUT_LINE (' Your SELECT statement retrieved multiple > rows. Consider using a cursor.'); > END; > / Your SELECT statement retrieved multiple rows. Consider using a cursor. PL/SQL procedure successfully completed. Exception types There are three types of exceptions: Predefined exceptions are error conditions that are defined by PL/SQL. Non-predefined exceptions include any standard TimesTen errors. User-defined exceptions are exceptions specific to your application. I
returns the current error message (if no argument is passed to it) or the system error message associated with the specified error code. DBMS_UTILITY.FORMAT_ERROR_STACK also returns the error message (or stack, if there is a stack of errors) and avoids truncation issues that may occur with SQLERRM. Category PL/SQL General Contributor Steven Feuerstein (Oracle) Created Wednesday January 27, 2016 Statement 1 SQLERRM with No ArgumentsBEGIN RAISE TOO_MANY_ROWS; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.put_line (SQLERRM); END; / Statement 2 SQLERRM as Lookup FunctionBEGIN DBMS_OUTPUT.put_line (SQLERRM (-1422)); END; / Statement 3 Even though some other parts of Oracle Database store error codes as unsigned integers (LOG ERRORS, SAVE EXCEPTIONS).That's Right: Oracle Errors are NegativeBEGIN DBMS_OUTPUT.put_line (SQLERRM (1422)); END; / Statement 4 And show the length of the string (useful when examining truncation issues in last step of script).Call Both Error Message FunctionsCREATE OR REPLACE PROCEDURE show_errors IS BEGIN DBMS_OUTPUT.put_line ('-------SQLERRM-------------'); DBMS_OUTPUT.put_line (SQLERRM); DBMS_OUTPUT.put_line ('-------FORMAT_ERROR_STACK--'); DBMS_OUTPUT.put_line (DBMS_UTILITY.format_error_stack); DBMS_OUTPUT.put_line (' '); END; Statement 5 CREATE OR REPLACE PROCEDURE proc1 IS BEGIN RAISE NO_DATA_FOUND; END; Statement 6 CREATE OR REPLACE PACKAGE pkg1 IS PROCEDURE proc2; END pkg1; Statement 7 CREATE OR REPLACE PACKAGE BODY pkg1 IS PROCEDURE proc2 IS BEGIN proc1; EXCEPTION WHEN OTHERS THEN RAISE DUP_VAL_ON_INDEX; END; END pkg1; Statement 8 The third argument of raise_application_error determines whether or not the stack of errors is kept or discarded. TRUE = Keep.Raise Application Error - and Keep the StackCREATE OR REPLACE PROCEDURE proc3 IS BEGIN FOR indx IN 1 .. 1000 LOOP NULL; END LOOP; pkg1.proc2; EXCEPTION WHEN OTHERS THEN raise_application_error (-20000, 'TOP MOST ERROR MESSAGE', TRUE); END; Statement 9 BEGIN proc3; EXCEPTION WHEN OTHERS THEN show_errors; END; Statement 10 Now Discard Error StackCREATE OR REPLACE PROCEDURE proc3 IS BEGIN FOR indx IN 1 .. 1000 LOOP NULL; END LOOP; pkg1.proc2; EXCEPTION WHEN OTHERS THEN raise_application_error (-20000, 'TOP M