Oracle Exception Error Message Sqlerrm
Contents |
is useful only in an exception handler. Outside a handler, SQLERRM with no argument always oracle sqlcode list returns the normal, successful completion message. For internal exceptions, SQLERRM oracle raise exception with message returns the message associated with the Oracle error that occurred. The message begins with the Oracle sqlerrm line number 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 oracle sql codes list 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
Functions For Error Trapping Are Contained In Which Section Of A Pl/sql Block
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" "SQLCO
and you use the RESTRICT_REFERENCES pragma to assert its purity, you
Sqlerrm Invalid Identifier
cannot specify the constraints WNPS and RNPS. Note: DBMS_UTILTY.FORMAT_ERROR_STACK is sqlerrm in db2 recommended over SQLERRM, except when using the FORALL statement with its SAVE EXCEPTIONS clause. For sqlerrm length 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 http://docs.oracle.com/cd/B19306_01/appdev.102/b14261/sqlerrm_function.htm expression whose value is an Oracle Database error number. For a list of Oracle Database error numbers, see 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. https://docs.oracle.com/cd/B28359_01/appdev.111/b28370/sqlerrm_function.htm 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 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
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 http://psoug.org/reference/exception_handling.html Rollbacks Before executing an INSERT, UPDATE, or DELETE statement, Oracle marks an implicit savepoint (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 rolled back. If you exit a stored subprogram with an unhandled oracle sql 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, 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 oracle exception error 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 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 ('