System-Generated
Exceptions in PL/SQL
The following table provides details on system-generated
exceptions in PL/SQL, including their error codes and descriptions in a
structured format.
|
Exception Name
|
Error Code
|
Description
|
|
ACCESS_INTO_NULL
|
ORA-06530
|
1. Occurs when assigning values to an uninitialized
object. 2. Objects must be initialized before use. 3. Common in user-defined
types and object-oriented PL/SQL. 4. Can be avoided by using the constructor
method while declaring the object.
|
|
CASE_NOT_FOUND
|
ORA-06592
|
1. Raised when a CASE statement does not match any
condition.
2. Happens if there is no ELSE clause.
3. Causes the program to
terminate unexpectedly.
4. Always include an ELSE clause to prevent
this error.
|
|
COLLECTION_IS_NULL
|
ORA-06531
|
1. Occurs when using an uninitialized collection
(array, table, etc.). 2. Collection must be initialized before adding or
accessing elements. 3. Happens mostly with nested tables and VARRAYs. 4. Use EXTEND or ASSIGN before modifying a collection.
|
|
CURSOR_ALREADY_OPEN
|
ORA-06511
|
1. Occurs when trying to open a cursor that is already
open. 2. A cursor must be closed before reopening it. 3. Usually
happens in loops where cursors are not closed properly. 4. Always check
cursor status using %ISOPEN before opening it.
|
|
DUP_VAL_ON_INDEX
|
ORA-00001
|
1. Raised when inserting duplicate values in a unique
constraint column. 2. Ensures data integrity by preventing duplicate
values. 3. Happens in primary key or unique key constraints. 4. Use MERGE
or INSERT INTO ... ON DUPLICATE KEY UPDATE to handle duplicates.
|
|
INVALID_CURSOR
|
ORA-01001
|
1. Occurs when using an invalid cursor reference. 2. Trying to fetch from a closed or never-opened cursor causes this error. 3.
Always open the cursor before fetching from it. 4. Use %ISOPEN to
check the cursor status.
|
|
INVALID_NUMBER
|
ORA-01722
|
1. Raised when trying to convert a non-numeric string
into a number. 2. Happens when using TO_NUMBER() on invalid data. 3.
Ensure that input values are purely numeric. 4. Use REGEXP_LIKE() to
validate numeric data before conversion.
|
|
LOGIN_DENIED
|
ORA-01017
|
1. Raised when trying to log in with an incorrect
username or password. 2. Prevents unauthorized access to the database. 3.
Common in database connection authentication failures. 4. Ensure
credentials are correct before attempting login.
|
|
NO_DATA_FOUND
|
ORA-01403
|
1. Raised when a SELECT INTO query returns no rows. 2. Happens when searching for a non-existent record. 3. Always use EXCEPTION
WHEN NO_DATA_FOUND to handle this case. 4. Use cursors instead of SELECT
INTO if multiple records are expected.
|
|
NOT_LOGGED_ON
|
ORA-01012
|
1. Occurs when trying to execute a SQL operation without
an active session. 2. Common in disconnected sessions or expired login
sessions. 3. Ensure the database connection is active before executing
queries. 4. Can be prevented by re-establishing the
|
Comments
Post a Comment