bigmarv.net

Oracle Exceptions

I can never remember what all the valid exceptions are when I'm writing PL/SQL code. (Or, as a friend of mine likes to call it, "PainfuL / SQueeL".) So, for my own benefit, and maybe yours, here's a list of some common predefined Oracle exceptions.

You'll want to use the OTHERS pseudo exception to catch any exceptions that aren't listed on this table (or any that you don't explicitly specify in your EXCEPTIONS section.) The WHEN OTHERS clause must always be the last one in your EXCEPTIONS section.

ACCESS_INTO_NULL Attempt to assign values to the attributes of a NULL object

COLLECTION_IS_NULL An attempt was made to apply collection methods other than EXISTS to a NULL PL/SQL table or varray.

CURSOR_ALREADY_OPEN You attempted to open a cursor that was already open.

DUP_VAL_ON_INDEX Unique constraint violation. This means you've used constraints or indexes to restrict entry of duplicate records, but then gone and tried to insert a duplicate record, at least as far as the record's keys are concerned.

INVALID_CURSOR This exception occurs when you try to
open an undeclared cursor

close a cursor that wasn't open

fetch from a cursor that wasn't open
or any of a bunch of other cursor blunders.

INVALID_NUMBER This exception occurs when you try to convert a string to a number, and the string doesn't contain a valid number.

LOGIN_DENIED You tried to use an invalid login and/or password when logging into Oracle.

NO_DATA_FOUND This exception is returned when your select statement returned zero rows. NOTE that an update statement will not throw this exception. Instead, query the sql%notfound and the sql%rowcount variables to determine the result of your update statements.

NOT_LOGGED_ON Occurs when you try to perform any database operation but you aren't logged into the database.

PROGRAM_ERROR This exception is thrown when the PL/SQL interpreter itself encounters an error while processing your code.

ROWTYPE_MISMATCH You'll get this exception when a host cursor variable and the PL/SQL cursor variable you're fetching into have incompatible types.

SELF_IS_NULL Occurs when an attempt to call a MEMBER method is made on a null instance.

STORAGE_ERROR Occurs when PL/SQL itself has run out of memory, it needs more than is available, or if there was some sort of corruption error.

SUBSCRIPT_BEYOND_COUNT Means you tried to index off the end of a varray or nexted table. The index you used was higher than the number of elements in the collection.

SUBSCRIPT_OUTSIDE_LIMIT Reference to a nested table or varray index outside the declared range -- such as an index of -1.

SYS_INVALID_ROW This occurs when the character string used to represent the rowID fails because the character string doesn't represent a valid row ID. Sounds scary.

TIMEOUT_ON_RESOURCE A time-out occurred while waiting on a resource. This usually means that there was some sort of unusual termination of an Oracle instance.

TOO_MANY_ROWS A SELECT ... INTO query matched more than one row. That is, a select that was supposed to return a single row returned more than one row.

TRANSACTION_BACKED_OUT Occurs when the remote part of a transaction is rolled back.

VALUE_ERROR This occurs when the value of a column or PL/SQL variable is messed up -- probably by some sort of truncation. This usually indicates some sort of conversion error.

ZERO_DIVIDE Occurs when you tried to divide by zero.

ACCESS_INTO_NULL	Attempt to assign values to the attributes of a NULL object
COLLECTION_IS_NULL	An attempt was made to apply collection methods other than EXISTS to a NULL PL/SQL table or varray.
CURSOR_ALREADY_OPEN	You attempted to open a cursor that was already open.
DUP_VAL_ON_INDEX	Unique constraint violation. This means you've used constraints or indexes to restrict entry of duplicate records, but then gone and tried to insert a duplicate record, at least as far as the record's keys are concerned.
INVALID_CURSOR	This exception occurs when you try to open an undeclared cursor close a cursor that wasn't open fetch from a cursor that wasn't open or any of a bunch of other cursor blunders.
INVALID_NUMBER	This exception occurs when you try to convert a string to a number, and the string doesn't contain a valid number.
LOGIN_DENIED	You tried to use an invalid login and/or password when logging into Oracle.
NO_DATA_FOUND	This exception is returned when your select statement returned zero rows. NOTE that an update statement will not throw this exception. Instead, query the `sql%notfound` and the `sql%rowcount` variables to determine the result of your update statements.
NOT_LOGGED_ON	Occurs when you try to perform any database operation but you aren't logged into the database.
PROGRAM_ERROR	This exception is thrown when the PL/SQL interpreter itself encounters an error while processing your code.
ROWTYPE_MISMATCH	You'll get this exception when a host cursor variable and the PL/SQL cursor variable you're fetching into have incompatible types.
SELF_IS_NULL	Occurs when an attempt to call a MEMBER method is made on a null instance.
STORAGE_ERROR	Occurs when PL/SQL itself has run out of memory, it needs more than is available, or if there was some sort of corruption error.
SUBSCRIPT_BEYOND_COUNT	Means you tried to index off the end of a varray or nexted table. The index you used was higher than the number of elements in the collection.
SUBSCRIPT_OUTSIDE_LIMIT	Reference to a nested table or varray index outside the declared range -- such as an index of -1.
SYS_INVALID_ROW	This occurs when the character string used to represent the rowID fails because the character string doesn't represent a valid row ID. Sounds scary.
TIMEOUT_ON_RESOURCE	A time-out occurred while waiting on a resource. This usually means that there was some sort of unusual termination of an Oracle instance.
TOO_MANY_ROWS	`A SELECT ... INTO` query matched more than one row. That is, a select that was supposed to return a single row returned more than one row.
TRANSACTION_BACKED_OUT	Occurs when the remote part of a transaction is rolled back.
VALUE_ERROR	This occurs when the value of a column or PL/SQL variable is messed up -- probably by some sort of truncation. This usually indicates some sort of conversion error.
ZERO_DIVIDE	Occurs when you tried to divide by zero.