Learn in depth with example about creating user defined exception using Pragma Exception_INIT and Raise_application_error following handling of different types of system named and unnamed exception in details.
How to handle exception in oracle database,
How to handle user defined exception in oracle database,
What is Pragma in Exception Handling,
What is Pragma Exception_INIT in oracle,
Types of exception available in oracle,
How to use Raise_application_error in oracle,
How to use custom exception in oracle,
How to use pragma autonomous transaction,
SQLERRM, SQLCODE, Raise_application_error,
In Oracle PL/SQL, PRAGMA refers to a compiler directive or "hint" it is used to provide an instruction to the compiler. The 5 types of Pragma directives available in Oracle are listed below:
* PRAGMA AUTONOMOUS_TRANSACTION
* PRAGMA SERIALLY_REUSABLE
* PRAGMA RESTRICT_REFRENCES
* PRAGMA EXCEPTION_INIT
* PRAGMA INLINE
Details of System Exception:
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:
You executed a SELECT INTO statement and no rows were returned.
You referenced an uninitialized row in a table.
You read past the end of file with the UTL_FILE package.
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 a 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.