Oracle Exception Get Error Message
Contents |
Server MySQL MariaDB PostgreSQL SQLite MS Office Excel Access Word Web Development HTML CSS Color Picker Languages C Language More ASCII Table oracle raise exception with message Linux UNIX Java Clipart Techie Humor Advertisement Oracle Basics Oracle
Oracle Sql Error Codes
Advanced Oracle Cursors Oracle Exception Handling Named Programmer-Defined Exception Named System Exception WHEN OTHERS Clause SQLCODE functions for error trapping are contained in which section of a pl/sql block SQLERRM Oracle Foreign Keys Oracle Loops/Conditionals Oracle Transactions Oracle Triggers String/Char Functions Numeric/Math Functions Date/Time Functions Conversion Functions Analytic Functions Advanced Functions NEXT: Declare Cursor Oracle /
Oracle Sqlcode List
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 raised error exception. This function should only be used within the Exception Handling section of your code. Syntax pl sql exception handling examples 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 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
TECHNOLOGY: PL/SQL Tracing Lines By Steven Feuerstein Find and report your errors—by line number—in Oracle Database 10g. PL/SQL offers a powerful and flexible exception architecture. Of course, there is always room for improvement,
Sqlerrm Line Number
and in Oracle Database 10g, exception handling takes a big step forward with the
Oracle Sql Codes List
introduction of the DBMS_UTILITY.FORMAT_ERROR_BACKTRACE function. This article explores the problem that this function solves and how best to use it. pl sql sqlcode Who Raised That Exception? When an exception is raised, one of the most important pieces of information a programmer would like to uncover is the line of code that raised the exception. Prior https://www.techonthenet.com/oracle/exceptions/sqlerrm.php to Oracle Database 10g, one could obtain this information only by allowing the exception to go unhandled. Let's revisit the error-handling behavior available to programmers in Oracle9i Database. Consider this simple chain of program calls in Listing 1: procedure proc3 calls proc2 calls proc1 , at which point proc1 raises the NO_DATA_FOUND exception. Notice that there is no error handling in any of the procedures; it is most http://www.oracle.com/technetwork/testcontent/o25plsql-093886.html significantly lacking in the top-level proc3 procedure. If I run proc3 in SQL*Plus, I will see the following results: ERROR at line 1: ORA-01403: no data found ORA-06512: at "SCOTT.PROC1", line 4 ORA-06512: at "SCOTT.PROC2", line 6 ORA-06512: at "SCOTT.PROC3", line 4 ORA-06512: at line 3 Code Listing 1: A stack of procedures CREATE OR REPLACE PROCEDURE proc1 IS BEGIN DBMS_OUTPUT.put_line ('running proc1'); RAISE NO_DATA_FOUND; END; / CREATE OR REPLACE PROCEDURE proc2 IS l_str VARCHAR2(30) := 'calling proc1'; BEGIN DBMS_OUTPUT.put_line (l_str); proc1; END; / CREATE OR REPLACE PROCEDURE proc3 IS BEGIN DBMS_OUTPUT.put_line ('calling proc2'); proc2; END; / This is the error trace dump of an unhandled exception, and it shows that the error was raised on line 4 of proc1. On the one hand, we should be very pleased with this behavior. Now that we have the line number, we can zoom right in on the problem code and fix it. On the other hand, we got this information by letting the exception go unhandled. In many applications, however, we work to avoid unhandled exceptions. Let's see what happens when I add an exception section to the proc3 procedure and then display the error information (the simplest form of
is useful only in an exception handler. Outside a handler, SQLERRM with no argument always returns the normal, http://docs.oracle.com/cd/B19306_01/appdev.102/b14261/sqlerrm_function.htm 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 http://awads.net/wp/2006/08/01/little-known-way-to-get-the-error-message-in-plsql/ 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 oracle sql 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 oracle exception get 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.
return the error message associated with the error code of the most recently raised exception in your PL/SQL block. The first function is SQLERRM. The second is the less known function DBMS_UTILITY.FORMAT_ERROR_STACK. Which one to use? To answer this question, let us first review the details of each function. SQLERRM Syntax: SQLERRM [(error_number)] It returns the error message associated with its error_number argument: SQL> BEGIN 2 DBMS_OUTPUT.put_line (SQLERRM (-1)); 3 END; 4 / ORA-00001: unique constraint (.) violated PL/SQL procedure successfully completed. If the argument is omitted (useful only in an exception handler), it returns the error message associated with the current value of SQLCODE (i.e. the error code of the most recently raised exception): SQL> BEGIN 2 RAISE NO_DATA_FOUND; 3 EXCEPTION 4 WHEN OTHERS 5 THEN 6 DBMS_OUTPUT.put_line (SQLERRM); 7 END; 8 / ORA-01403: no data found PL/SQL procedure successfully completed. Outside an exception handler, SQLERRM with no argument, or with argument equals to 0, always returns "ORA-0000: normal, successful completion": SQL> BEGIN 2 DBMS_OUTPUT.put_line (SQLERRM); 3 DBMS_OUTPUT.put_line (SQLERRM(0)); 4 END; 5 / ORA-0000: normal, successful completion ORA-0000: normal, successful completion PL/SQL procedure successfully completed. SQLERRM with an argument of 1 (the user-defined exception error number) returns "User-Defined Exception": SQL> BEGIN 2 DBMS_OUTPUT.put_line (SQLERRM(1)); 3 END; 4 / User-Defined Exception PL/SQL procedure successfully completed. SQLERRM with an invalid error code argument returns "ORA-NNNNN: Message NNNNN not found; product=RDBMS; facility=ORA" If the number is negative, and "-NNNNN: non-ORACLE exception" if the number is positive: SQL> BEGIN 2 DBMS_OUTPUT.put_line (SQLERRM (7)); 3 DBMS_OUTPUT.put_line (SQLERRM (-9)); 4 END; 5