Named System Exceptions
What is a named system exception?
Named system exceptions are exceptions that have been given names by PL/SQL. They are named in the STANDARD package in PL/SQL and do not need to be defined by the programmer.
Oracle has a standard set of exceptions already named as follows:
|
Oracle Exception Name |
Oracle Error |
Explanation |
|
DUP_VAL_ON_INDEX |
ORA-00001 |
You tried to execute an INSERT or UPDATE statement that has created a duplicate value in a field restricted by a unique index. |
|
TIMEOUT_ON_RESOURCE |
ORA-00051 |
You were waiting for a resource and you timed out. |
|
TRANSACTION_BACKED_OUT |
ORA-00061 |
The remote portion of a transaction has rolled back. |
|
INVALID_CURSOR |
ORA-01001 |
You tried to reference a cursor that does not yet exist. This may have happened because you've executed a FETCH cursor or CLOSE cursor before OPENing the cursor. |
|
NOT_LOGGED_ON |
ORA-01012 |
You tried to execute a call to Oracle before logging in. |
|
LOGIN_DENIED |
ORA-01017 |
You tried to log into Oracle with an invalid username/password combination. |
|
NO_DATA_FOUND |
ORA-01403 |
You tried one of the following:
|
|
TOO_MANY_ROWS |
ORA-01422 |
You tried to execute a SELECT INTO statement and more than one row was returned. |
|
ZERO_DIVIDE |
ORA-01476 |
You tried to divide a number by zero. |
|
INVALID_NUMBER |
ORA-01722 |
You tried to execute an SQL statement that tried to convert a string to a number, but it was unsuccessful. |
|
STORAGE_ERROR |
ORA-06500 |
You ran out of memory or memory was corrupted. |
|
PROGRAM_ERROR |
ORA-06501 |
This is a generic "Contact Oracle support" message because an internal problem was encountered. |
|
VALUE_ERROR |
ORA-06502 |
You tried to perform an operation and there was a error on a conversion, truncation, or invalid constraining of numeric or character data. |
|
CURSOR_ALREADY_OPEN |
ORA-06511 |
You tried to open a cursor that is already open. |
The syntax for the Named System Exception in a procedure is:
CREATE [OR REPLACE] PROCEDURE procedure_name
[ (parameter [,parameter]) ]
IS
[declaration_section]
BEGIN
executable_section
EXCEPTION
WHEN exception_name1 THEN
[statements]
WHEN exception_name2 THEN
[statements]
WHEN exception_name_n THEN
[statements]
WHEN OTHERS THEN
[statements]
END [procedure_name];
The syntax for the Named System Exception in a function is:
CREATE [OR REPLACE] FUNCTION function_name
[ (parameter [,parameter]) ]
RETURN return_datatype
IS | AS
[declaration_section]
BEGIN
executable_section
EXCEPTION
WHEN exception_name1 THEN
[statements]
WHEN exception_name2 THEN
[statements]
WHEN exception_name_n THEN
[statements]
WHEN OTHERS THEN
[statements]
END [function_name];
Here is an example of a procedure that uses a Named System Exception:
CREATE OR REPLACE PROCEDURE add_new_supplier
(supplier_id_in IN NUMBER, supplier_name_in IN VARCHAR2)
IS
BEGIN
INSERT INTO suppliers (supplier_id, supplier_name )
VALUES ( supplier_id_in, supplier_name_in );
EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
raise_application_error (-20001,'You have tried to insert a duplicate supplier_id.');
WHEN OTHERS THEN
raise_application_error (-20002,'An error has occurred inserting a supplier.');
END;
In this example, we are trapping the Named System Exception called DUP_VAL_ON_INDEX. We are also using the WHEN OTHERS clause to trap all remaining exceptions.
Wed, 17 Feb 2010 17:39:32 +0800
Greeting. The place of the father in the modern suburban family is a very small one, particularly if he plays golf. Help me! Help to find sites on the: Cosco date stamps. I found only this - <a href="http://www.socrates.majoranaorvieto.org/Members/DateStamp">staples date stamp</a>. If you receive to learn a internal exchange about yourself, long won a protocol that gets that date, date stamp. The items are violated of cumbersome right advanced activity, date stamp. THX :o, Laszlo from Thailand.
Wed, 17 Feb 2010 17:58:00 +0800
Good afternoon. I have the worst memory ever so no matter who comes up to me - they're just, like, 'I can't believe you don't remember me!" I'm like, 'Oh Dad I'm sorry!' Help me! Looking for sites on: Adjustable bed maxwell replace part. I found only this - <a href="http://www.jillstuart.com/Members/AdjustableBed">adjustable bed stores</a>. Incorrectly much as a come anus of being set, it did airway precision wall with other design to some people while pumps were placing because the putty fabrics were drawn, adjustable bed. Premier mattress bed: alternatively successive percentages and media, adjustable bed. THX :-), Marisol from Jordan.
Tue, 20 Apr 2021 11:43:36 +0800
Thank you for sharing this useful article information.I am really impressed with the article you provided. Aspiradora miele
Mon, 16 May 2022 19:35:54 +0800
These are some important tips for using Named System Exceptions. I think it is quite easy to use them with the specified mode of techniques. It will be really nice to have a referral like this to stay updated about the subject.view it now
Thu, 21 Jul 2022 21:33:23 +0800
You made some respectable points there. I looked on the internet for the issue and found most people will go along with with your website. LED補光燈
Wed, 24 Aug 2022 19:37:37 +0800
Aw, this was a really nice post. In thought I would like to put in writing like this additionally ?taking time and precise effort to make an excellent article?however what can I say?I procrastinate alot and on no account appear to get something done. skin care
Wed, 24 Aug 2022 23:45:11 +0800
Positive site, where did u come up with the information on this posting?I've read a several articles in your website now, and I enjoy your style. Thanks a million and please maintain the effective work. 안마야