Oracle Get Error Message From Code
Contents |
is useful only in an exception handler. Outside a handler, SQLERRM with no argument always returns the normal, oracle sqlerrm successful completion message. For internal exceptions, SQLERRM returns the message associated
Pl Sql Sqlcode
with the Oracle error that occurred. The message begins with the Oracle error code. For user-defined oracle sqlcode list 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 error code 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
Sqlerrm Line Number
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.
Churchill Run-time errors arise from design faults, coding mistakes, hardware failures, and many other sources. Although you cannot anticipate all possible errors, you can plan to handle certain kinds of errors meaningful to oracle sql codes list your PL/SQL program. With many programming languages, unless you disable error checking, a run-time functions for error trapping are contained in which section of a pl/sql block error such as stack overflow or division by zero stops normal processing and returns control to the operating system. With PL/SQL,
Oracle Raise Exception With Message
a mechanism called exception handling lets you "bulletproof" your program so that it can continue operating in the presence of errors. This chapter discusses the following topics: Overview of PL/SQL Error Handling Advantages of PL/SQL http://docs.oracle.com/cd/B19306_01/appdev.102/b14261/sqlerrm_function.htm Exceptions Predefined PL/SQL Exceptions Defining Your Own PL/SQL Exceptions How PL/SQL Exceptions Are Raised How PL/SQL Exceptions Propagate Reraising a PL/SQL Exception Handling Raised PL/SQL Exceptions Tips for Handling PL/SQL Errors Overview of PL/SQL Error Handling In PL/SQL, a warning or error condition is called an exception. Exceptions can be internally defined (by the run-time system) or user defined. Examples of internally defined exceptions include division by zero and out of https://docs.oracle.com/cd/A97630_01/appdev.920/a96624/07_errs.htm memory. Some common internal exceptions have predefined names, such as ZERO_DIVIDE and STORAGE_ERROR. The other internal exceptions can be given names. You can define exceptions of your own in the declarative part of any PL/SQL block, subprogram, or package. For example, you might define an exception named insufficient_funds to flag overdrawn bank accounts. Unlike internal exceptions, user-defined exceptions must be given names. When an error occurs, an exception is raised. That is, normal execution stops and control transfers to the exception-handling part of your PL/SQL block or subprogram. Internal exceptions are raised implicitly (automatically) by the run-time system. User-defined exceptions must be raised explicitly by RAISE statements, which can also raise predefined exceptions. To handle raised exceptions, you write separate routines called exception handlers. After an exception handler runs, the current block stops executing and the enclosing block resumes with the next statement. If there is no enclosing block, control returns to the host environment. In the example below, you calculate and store a price-to-earnings ratio for a company with ticker symbol XYZ. If the company has zero earnings, the predefined exception ZERO_DIVIDE is raised. This stops normal execution of the block and transfers control to the exception handlers. The optional OTHERS handler catches all exceptions that the block
Functions PSOUG Forum Oracle Blogs Search the Reference Library pages: FreeOracle MagazineSubscriptionsand Oracle White Papers Oracle Exception Handling Version 11.1 General NOTE: How Oracle Does Implicit Rollbacks Before executing an INSERT, UPDATE, or DELETE statement, Oracle marks an implicit savepoint http://psoug.org/reference/exception_handling.html (unavailable to you). If the statement fails, Oracle rolls back to the savepoint. Normally, just the failed SQL statement is rolled back, not the whole transaction. However, if the statement raises an unhandled exception, the host environment determines what is http://awads.net/wp/2006/08/01/little-known-way-to-get-the-error-message-in-plsql/ rolled back. If you exit a stored subprogram with an unhandled exception, PL/SQL does not assign values to OUT parameters. Also, PL/SQL does not roll back database work done by the subprogram. At the level of the SQL*Plus prompt, oracle sql every update/insert/delete has one implicit savepoint, and also the invocation of any unnamed block. Below that, the unnamed block itself has 'sub' savepoints - one foreach insert/update/delete statement in it, and one for each subprogram unit. And so on down the line. If an error occurs, and that error is handled at any level by the time we're back at the SQL*Plus prompt, we only rollback to the immediate savepoint at the start of the update/insert/delete that errors. Otherwise we oracle get error rollback to the top-level 'virtual' savepoint currently in existence, which is my offending unnamed block. That is, a handled error is handled and so can be dealt with without rolling back all the way to the top. It is handled and the transaction proceeds. Commits define the end of a transaction (and start of a new one) - rollbacks only define the end of a transaction if they rollback to the last commit, rather than savepoint (whether explicit or implicit). I came to my 'version' from the following by no means exhaustive tests: CASE 1: I created a table a with one column, a1 number, and at the sqlplus prompt inserted a row with a1 = 1. I then ran that unnamed block I referred in an earlier post that, without an exception handler, does the following: INSERT INTO a VALUES (2); INSERT INTO a VALUES (3); INSERT INTO a VALUES ('a'); As expected I get an unhandled error on the last line. When I do a select for everything in the table a, I get the first row I inserted 'manually', the one with a1 = 1. So there seems to have been an invisible savepoint set just before the unnamed block ran. CASE 2: Then I modified the unnamed block so it did two good inserts and then called a stored procedure that did two good inserts and ended with one 'bad' - inserting
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 / -7: non-ORACLE exception ORA-0