Oracle Error Sqlcode Sqlerrm
Contents |
is useful only in an exception handler. Outside a handler, SQLERRM with no argument always sqlerrm sqlcode returns the normal, successful completion message. For internal exceptions, SQLERRM
Oracle Sqlcode List
returns the message associated with the Oracle error that occurred. The message begins with the Oracle oracle sql codes list 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 sqlcode values 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
Sqlcode In Db2
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"
Oracle error is no data found, in which case sqlerrm line number SQLCODE returns +100. For user-defined exceptions, SQLCODE returns +1, sqlerrcode or a value you assign if the exception is associated with an Oracle
Oracle Sqlcode 0
error number through pragma EXCEPTION_INIT. Syntax sqlcode function ::= Description of the illustration sqlcode_function.gif Usage Notes SQLCODE is only useful in an http://docs.oracle.com/cd/B19306_01/appdev.102/b14261/sqlerrm_function.htm exception handler. Outside a handler, SQLCODE always returns 0. SQLCODE is especially useful in the OTHERS exception handler, because it lets you identify which internal exception was raised. You cannot use SQLCODE directly in a SQL statement. Assign the value of SQLCODE to a local https://docs.oracle.com/cd/B19306_01/appdev.102/b14261/sqlcode_function.htm variable first. 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 SQLCODE. Examples Example 13-6 shows the use of SQLCODE and SQLERRM. Example 13-6 Using SQLCODE and SQLERRM DECLARE name employees.last_name%TYPE; v_code NUMBER; v_errm VARCHAR2(64); BEGIN SELECT last_name INTO name FROM employees WHERE employee_id = 1000; EXCEPTION WHEN OTHERS THEN v_code := SQLCODE; v_errm := SUBSTR(SQLERRM, 1 , 64); DBMS_OUTPUT.PUT_LINE('The error code is ' || v_code || '- ' || v_errm); END; / For examples, see the following: Example 10-11, "Displaying SQLCODE and SQLERRM" Related Topics "Exception Definition" "SQLERRM Function" "Retrieving the Error Code and Error Message: SQLCODE and SQLERRM" Scripting on this page enhances content navigation, but does not change the content in any way.
and you use the RESTRICT_REFERENCES pragma to assert its purity, you cannot https://docs.oracle.com/cd/B28359_01/appdev.111/b28370/sqlerrm_function.htm specify the constraints WNPS and RNPS. Note: DBMS_UTILTY.FORMAT_ERROR_STACK is recommended over SQLERRM, except when using the FORALL statement with its SAVE EXCEPTIONS clause. For 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 expression whose oracle sql 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. Outside an exception oracle error sqlcode 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-50000: Message 50000 not found; product=RDBMS;