Before executing an INSERT, UPDATE, or DELETE statement, Oracle marks an implicit savepoint (unavailable to you). If
the statement fails, Oracle rolls back to the savepoint. Normally, just the failed SQL statement is rolled back, not
the whole transaction. However, if the statement raises an unhandled exception, the host environment determines what
is rolled back.
If you exit a stored subprogram with an unhandled exception, PL/SQL does not assign values to OUT parameters. Also,
PL/SQL does not roll back database work done by the subprogram.
At the level of the SQL*Plus prompt, every update/insert/delete has one implicit savepoint, and also the invocation
of any unnamed block. Below that, the unnamed block itself has 'sub' savepoints - one for each insert/update/delete
statement in it, and one for each subprogram unit. And so on down the line.
If an error occurs, and that error is handled at any level by the time we're back at the SQL*Plus prompt, we only rollback
to the immediate savepoint at the start of the update/insert/delete that errors. Otherwise we rollback to the top-level
'virtual' savepoint currently in existence, which is my offending unnamed block. That is, a handled error is handled and
so can be dealt with without rolling back all the way to the top. It is handled and the transaction proceeds.
Commits define the end of a transaction (and start of a new one) - rollbacks only define the end of a transaction if they
rollback to the last commit, rather than savepoint (whether explicit or implicit).
I came to my 'version' from the following by no means exhaustive tests:
CASE 1:
I created a table a with one column, a1 number, and at the sqlplus prompt inserted a row with a1 = 1.
I then ran that unnamed block I referred in an earlier post that, without an exception handler, does the following:
INSERT INTO a VALUES (2);
INSERT INTO a VALUES (3);
INSERT INTO a VALUES ('a');
As expected I get an unhandled error on the last line. When I do a select for everything in the table a,
I get the first row I inserted 'manually', the one with a1 = 1.
So there seems to have been an invisible savepoint set just before the unnamed block ran.
CASE 2:
Then I modified the unnamed block so it did two good inserts and then called a stored procedure that did two good inserts
and ended with one 'bad' - inserting a character into a number column. The stored
procedure also had no error trap.
When I run this one, as expected, error message. When I select everything from the table, it gets that single row with a1 = 1.
Again, the unnamed block seems to set an invisible savepoint. And everything in the stored procedure got rolled back.
CASE 3:
Then I reran everything, except the unnamed block had a generic when others then null; error trap, and the stored procedure
had a generic when others the null; error trap.
In this case as expected, no error message was generated, and when I selected * from the table, it had inserted all the rows
that were valid and only failed to insert the 'bad' rows.
CASE 4:
Then I deleted everything from the table a except the a1 = 1 and did a commit.
Then I reran everything just as in case3, except that: the stored procedure had NO error trap but the unnamed block that
calls it DOES. The result was exactly the same as in case3 - everything was stored
except 'bad' rows.
CASE 5:
Then I deleted everything from the table 1 except the a1 = 1 and did a commit.
Then I reran everything just as in case4, except that the stored procedure was the one with the error trap and unnamed
block the one without an error trap. The results were that everything was stored in the table except the 'bad' lines.
CASE 6:
Finally ran case where my unnamed block did some ok inserts, I called a proc that did some more ok updates, then I
called a proc that did some ok inserts and a bad insert; and there were no error traps in any proc or block. Everything
got rolled back.
Usenet source: Ken Quirici (c.d.o.server - 29-Oct-2004)
Basic
Exception Handling
With Error Basic Block
Structure Handling
CREATE OR REPLACE PROCEDURE <procedure_name> IS
BEGIN
NULL;
EXCEPTION
WHEN <named_exception> THEN
-- handle identified exception
WHEN <named_exception> THEN
-- handle identified exception
WHEN OTHERS THEN
-- handle any exceptions not previously handled
END;
/
CREATE OR REPLACE PROCEDURE
myproc IS
BEGIN
NULL;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
WHEN ZERO_DIVIDE THEN
NULL;
WHEN OTHERS THEN
NULL;
END;
/
WHEN OTHERS THEN with
SQLCODE and SQLERRM
Note: If not the only exception handler ... must be the last exception handler
BEGIN
NULL;
RAISE too_many_rows;
EXCEPTION
WHEN OTHERS THEN
ecode := SQLCODE;
dbms_output.put_line(thisproc || ' - ' || ecode);
END trap_errcode;
/
set serveroutput on
exec trap_errcode
Display Error With SQLERRM
CREATE OR REPLACE PROCEDURE trap_errmesg IS
emesg VARCHAR2(250);
BEGIN
NULL;
RAISE too_many_rows;
EXCEPTION
WHEN OTHERS THEN
emesg := SQLERRM;
dbms_output.put_line(emesg);
END trap_errmesg;
/
set serveroutput on
exec trap_errmesg
WHEN <name
exception> THEN & Named Exceptions
Note: A table of the named exceptions is at the bottom of this web page.
When Invalid Cursor Exception Demo
CREATE OR REPLACE PROCEDURE invcur_exception IS
CURSOR x_cur is
SELECT *
FROM all_tables;
x_rec x_cur%rowtype;
BEGIN
LOOP
-- note the cursor was not opened before the FETCH
FETCH x_cur INTO x_rec;
EXIT WHEN x_cur%notfound;
NULL;
END LOOP;
EXCEPTION
WHEN INVALID_CURSOR THEN
dbms_output.put_line('Whoops!');
WHEN OTHERS THEN
dbms_output.put_line('Some Other Problem');
END invcur_exception;
/
set serveroutput on
exec invcur_exception
Two Many Rows Exception Demo
CREATE OR REPLACE PROCEDURE tmr_exception IS
x all_tables.table_name%TYPE;
BEGIN
-- note the statement will try to fetch many values
SELECT table_name -- try to SELECT many things into 1 var
INTO x
FROM all_tables;
EXCEPTION
WHEN TOO_MANY_ROWS THEN
dbms_output.put_line('Too Many Rows');
WHEN OTHERS THEN
dbms_output.put_line('Some Other Problem');
END tmr_exception;
/
set serveroutput on
exec tmr_exception
Division By Zero Error Trapping Demo
CREATE OR REPLACE PROCEDURE dbz_exception (numin NUMBER)
IS
z NUMBER := 0;
x NUMBER;
BEGIN
x := numin / z;
EXCEPTION
WHEN ZERO_DIVIDE THEN
dbms_output.put_line('Division By Zero');
WHEN OTHERS THEN
dbms_output.put_line('Some Other Problem');
END dbz_exception;
/
set serveroutput on
exec dbz_exception(6)
Divide By Zero Error Pass In The Zero
CREATE OR REPLACE PROCEDURE zero_div (numin NUMBER) IS
z NUMBER := TO_NUMBER(TO_CHAR(SYSDATE, 'YYYY'));
x NUMBER;
BEGIN
x := z / numin;
dbms_output.put_line('Division By ' || TO_CHAR(numin));
EXCEPTION
WHEN ZERO_DIVIDE THEN
dbms_output.put_line('Division By Zero');
WHEN OTHERS THEN
dbms_output.put_line('Some Other Problem');
END zero_div;
/
CREATE OR REPLACE FUNCTION is_ssn (string_in VARCHAR2)
RETURN VARCHAR2
IS
-- validating ###-##-#### format
delim VARCHAR2(1);
part1 NUMBER(3,0);
part2 NUMBER(2,0);
part3 NUMBER(4,0);
too_long EXCEPTION;
too_short EXCEPTION;
delimiter EXCEPTION;
BEGIN
IF LENGTH(string_in) > 11 THEN
RAISE too_long;
ELSIF LENGTH(string_in) < 11 THEN
RAISE too_short;
END IF;
part1 := TO_NUMBER(SUBSTR(string_in,1,3),'999');
delim := SUBSTR(string_in,4,1);
IF delim <> '-' THEN
RAISE delimiter;
END IF;
part2 := TO_NUMBER(SUBSTR(string_in,5,2),'99');
delim := SUBSTR(string_in,7,1);
IF delim <> '-' THEN
RAISE delimiter;
END IF;
part3 := TO_NUMBER(SUBSTR(string_in,8,4),'9999');
RETURN 'TRUE';
EXCEPTION
WHEN too_long THEN
dbms_output.put_line('More Than 11
Characters');
RETURN 'FALSE';
WHEN too_short THEN
dbms_output.put_line('Less Than 11
Characters');
RETURN 'FALSE';
WHEN delimiter THEN
dbms_output.put_line('Incorrect Delimiter');
RETURN 'FALSE';
WHEN OTHERS THEN
dbms_output.put_line('Some Other Issue');
RETURN 'FALSE';
END is_ssn;
/
set serveroutput on
SELECT is_ssn('123-45-6789') FROM dual;
SELECT is_ssn('123-45-67890') FROM dual;
SELECT is_ssn('123-45-678') FROM dual;
SELECT is_ssn('123-45=67890') FROM dual;
Pragma Exception_Init
PRAGMA EXCEPTION_INIT
Demo
CREATE TABLE results (
sourceno NUMBER(10) NOT NULL,
testno NUMBER(3) NOT NULL,
locationid NUMBER(10) NOT NULL);
-- the basic procedure
CREATE OR REPLACE PROCEDURE PragmaExcInit IS
BEGIN
INSERT INTO results
(sourceno)
VALUES
('1');
COMMIT;
END PragmaExcInit;
/
exec pragmaexcinit
-- the same procedure with exception trapping
CREATE OR REPLACE PROCEDURE PragmaExcInit IS
BEGIN
INSERT INTO results
(sourceno)
VALUES
('1');
COMMIT;
EXCEPTION
WHEN FieldsLeftNull THEN
dbms_output.put_line('ERROR: Trapped Fields Left Null');
WHEN OTHERS THEN
dbms_output.put_line(SQLERRM);
END PragmaExcInit;
/
Demo Procedure With User Defined Exceptions And
RAISE
CREATE OR REPLACE PROCEDURE raise_demo (inval NUMBER) IS
evenno EXCEPTION;
oddno EXCEPTION;
BEGIN
IF MOD(inval, 2) = 1 THEN
RAISE oddno;
ELSE
RAISE evenno;
END IF;
EXCEPTION
WHEN evenno THEN
dbms_output.put_line(TO_CHAR(inval) || ' is even');
WHEN oddno THEN
dbms_output.put_line(TO_CHAR(inval) || ' is odd');
END raise_demo;
/
error_number -20000 to -20999
error_message VARCHAR2(2048)
TRUE add to error stack
FALSE replace error stack (the default)
CREATE OR REPLACE PROCEDURE raise_app_error (inval NUMBER) IS
evenno EXCEPTION;
oddno EXCEPTION;
BEGIN
IF MOD(inval, 2) = 1 THEN
RAISE oddno;
ELSE
RAISE evenno;
END IF;
EXCEPTION
WHEN evenno THEN
RAISE_APPLICATION_ERROR(-20001, 'Even Number Entered');
WHEN oddno THEN
RAISE_APPLICATION_ERROR(-20999, 'Odd Number Entered');
END raise_app_error;
/
exec raise_app_error;
Locator Variables
Locating Errors With Locator
Variables
The use of variables to identify the location with a code block where the error was raised
set serveroutput on
DECLARE
step VARCHAR2(2);
i NUMBER(1) := 5;
n NUMBER(2) := 10;
BEGIN
step := 'A';
n := n/i;
i := i-1;
step := 'B';
n := n/i;
i := i-2;
step := 'C';
n := n/i;
i := i-2;
step := 'D';
n := n/i;
i := i-2;
step := 'E';
n := n/i;
i := i-1;
EXCEPTION
WHEN ZERO_DIVIDE THEN
dbms_output.put_line('Failure at: ' || step);
END;
/
Declaration Exceptions
Declaration Exceptions
Declaration exceptions can not be trapped with an error handler
DECLARE
i NUMBER(3) := 1000;
BEGIN
NULL;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
/
CREATE OR REPLACE PROCEDURE demo(someval IN NUMBER) IS
i NUMBER(3) := someval;
BEGIN
i := i+0;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
/
exec demo(999);
exec demo(1000);
Exception Handling Demo
Incomplete Handling
CREATE TABLE test (
col INT);
ALTER TABLE test
ADD CONSTRAINT pk_test
PRIMARY KEY (col)
USING INDEX;
CREATE OR REPLACE PROCEDURE p IS
BEGIN
INSERT INTO test VALUES (1);
END p;
/
BEGIN
p;
p;
END;
/
-- no records inserted as expected
SELECT * FROM test;
BEGIN
p;
p;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
/
-- one record inserted
SELECT * FROM test;
System-Defined Event Trapping
Error Stack Trapping with System Events
Declaration exceptions can not be trapped with an error handler
set serveroutput on
CREATE OR REPLACE TRIGGER e_trigger
BEFORE delete
ON t
FOR i in 1 .. nvl(l_text.count,0)
LOOP
dbms_output.put_line(l_text(i) );
END LOOP;
dbms_output.put_line( 'error text: ' );
FOR i IN 1 .. ora_server_error_depth
LOOP
dbms_output.put_line(ora_server_error_msg(i) );
END LOOP;
dbms_output.put_line( '--------------------' );
END e_trigger;
/
Simple Error
Handling Procedure
Function To Identify The User Logged Onto Oracle
CREATE OR REPLACE FUNCTION getosuser RETURN VARCHAR2 IS
vOSUser user_users.username%TYPE;
BEGIN
SELECT osuser
INTO vOSUser
FROM sys.v_$session
WHERE sid = (
SELECT sid
FROM sys.v_$mystat
WHERE rownum = 1);
RETURN vOSUser;
EXCEPTION
WHEN OTHERS THEN
RETURN 'UNK';
END getosuser;
/
SELECT getosuser FROM dual;
The Table Holding The Output Of The Error Logging Procedure
CREATE OR REPLACE TRIGGER error_trig
AFTER SERVERERROR ON DATABASE
CALL error_trap
/
BEGIN
RAISE zero_divide;
END;
/
set linesize 141
col error_timestamp format a31
col database_name format a40
col error_message format a40
col logged_on_as format a20
col client_host format a20
col service_name format a20
SELECT error_timestamp, database_name, instance_number
FROM error_log;
SELECT error_timestamp, error_number, error_message
FROM error_log;
SELECT logged_on_as, client_host, service_name
FROM error_log;
ALTER TABLE error_stacks
ADD CONSTRAINT pk_error_stacks
PRIMARY KEY (module, seq_number, error_order)
USING INDEX
TABLESPACE indx_sml;
ALTER TABLE error_stacks
ADD CONSTRAINT fk_es_errors
FOREIGN KEY (module, seq_number)
REFERENCES errors (module, seq_number)
ON DELETE CASCADE;
CREATE SEQUENCE error_seq
START WITH 1
INCREMENT BY 1;
Error Handling Package Header
CREATE OR REPLACE PACKAGE ErrorPkg AS
/* Generic error handling package, using DBMS_UTILITY.FORMAT_ERROR_STACK and
DBMS_UTILITY.FORMAT_CALL_STACK. This package stores general error information in the errors table,
with detailed call stack and error stack information in the call_stacks and error_stacks tables, respectively.
Entry point for handling errors. HandleAll should be called from all exception handlers
where you want the error to be logged. p_Top should be TRUE only at the topmost level
of procedure nesting. It should be FALSE at other levels.
*/
PROCEDURE HandleAll(p_Top BOOLEAN);
/*
Prints the error and call stacks (using DBMS_OUTPUT) for the given module and sequence number.
*/
PROCEDURE PrintStacks(p_Module IN errors.module%TYPE,
p_SeqNum IN errors.seq_number%TYPE);
/*
Unwinds the call and error stacks, and stores them in the errors and call stacks tables.
Returns the sequence number under which the error is stored. If p_CommitFlag is TRUE,
then the inserts are committed. In order to use StoreStacks, an error must have been
handled. Thus HandleAll should have been called with p_Top = TRUE.
*/
PROCEDURE StoreStacks(p_Module IN errors.module%TYPE,
p_SeqNum OUT errors.seq_number%TYPE,
p_CommitFlag BOOLEAN DEFAULT FALSE);
BEGIN
IF p_Top THEN
v_Handled := FALSE;
ELSIF NOT v_Handled THEN
v_Handled := TRUE;
v_ErrorStack := DBMS_UTILITY.FORMAT_ERROR_STACK;
v_CallStack := DBMS_UTILITY.FORMAT_CALL_STACK;
END IF;
END HandleAll;
--===================================================
PROCEDURE PrintStacks(
p_Module IN errors.module%TYPE,
p_SeqNum IN errors.seq_number%TYPE)
IS
CURSOR c_CallCur IS
SELECT object_handle, line_num, object_name
FROM call_stacks
WHERE module = p_Module
AND seq_number = p_SeqNum
ORDER BY call_order;
CURSOR c_ErrorCur IS
SELECT facility, error_number, error_mesg
FROM error_stacks
WHERE module = p_Module
AND seq_number = p_SeqNum
ORDER BY error_order;
BEGIN
SELECT timestamp, error_mesg
INTO v_TimeStamp, v_ErrorMsg
FROM errors
WHERE module = p_Module
AND seq_number = p_SeqNum;
-- Output the call stack.
dbms_output.put('Complete Call Stack:');
dbms_output.put(' Object Handle Line Number Object Name');
dbms_output.put_line(' ------------- ----------- -----------');
FOR v_CallRec in c_CallCur
LOOP
dbms_output.put(RPAD(' ' || v_CallRec.object_handle, 15));
dbms_output.put(RPAD(' ' || TO_CHAR(v_CallRec.line_num), 13));
dbms_output.put_line(' ' || v_CallRec.object_name);
END LOOP;
-- Output the error stack.
dbms_output.put_line('Complete Error Stack:');
FOR v_ErrorRec in c_ErrorCur
LOOP
dbms_output.put(' ' || v_ErrorRec.facility || '-');
dbms_output.put(TO_CHAR(v_ErrorRec.error_number) || ': ');
dbms_output.put_line(v_ErrorRec.error_mesg);
END LOOP;
END PrintStacks;
--===================================================
PROCEDURE StoreStacks(p_Module IN errors.module%TYPE,
p_SeqNum OUT errors.seq_number%TYPE,
p_CommitFlag BOOLEAN DEFAULT FALSE)
IS
v_SeqNum NUMBER;
v_Index NUMBER;
v_Length NUMBER;
v_End NUMBER;
v_Call VARCHAR2(100);
v_CallOrder NUMBER := 1;
v_Error VARCHAR2(120);
v_ErrorOrder NUMBER := 1;
BEGIN
-- Get the error sequence number.
SELECT error_seq.nextval
INTO v_SeqNum
FROM dual;
p_SeqNum := v_SeqNum;
-- Insert first part of header info. into the errors table
INSERT INTO errors
(module, seq_number, error_stack, call_stack, timestamp)
VALUES
(p_Module, v_SeqNum, v_ErrorStack, v_CallStack, SYSDATE);
/*
Unwind the error stack to get each error out by scanning the
error stack string. Start with the index at the beginning of
the string
*;
v_Index := 1;
/*
Loop through the string, finding each newline
A newline
ends each error on the stack
*/
WHILE v_Index < LENGTH(v_ErrorStack) LOOP
-- v_End is the position of the newline.
v_End := INSTR(v_ErrorStack, v_NewLine, v_Index);
-- The error is between the current index and the newline
v_Error := SUBSTR(v_ErrorStack, v_Index, v_End - v_Index);
-- Skip over the current error, for the next iteration
v_Index := v_Index + LENGTH(v_Error) + 1;
/* An error looks like 'facility-number: mesg'. Get each
piece out for insertion. The facility is the first 3
characters of the error.
*/
v_Facility := SUBSTR(v_Error, 1, 3);
-- Remove the facility and the dash (always 4 characters)
v_Error := SUBSTR(v_Error, 5);
-- Next get the error number
v_ErrNum := TO_NUMBER(SUBSTR(v_Error, 1, INSTR(v_Error,
':') - 1));
-- Remove the error number, colon & space (always 7 chars)
v_Error := SUBSTR(v_Error, 8);
-- What's left is the error message
v_ErrMsg := v_Error;
/*
Insert the errors, and grab the first error number and
message while we're at it
*/
IF v_ErrorOrder = 1 THEN
v_FirstErrNum := v_ErrNum;
v_FirstErrMsg := v_Facility || '-' ||
TO_NUMBER(v_ErrNum)
|| ': ' || v_ErrMsg;
END IF;
v_ErrorOrder := v_ErrorOrder + 1;
END LOOP;
-- Update the errors table with the message and code
UPDATE errors
SET error_number = v_FirstErrNum,
error_mesg = v_FirstErrMsg
WHERE module = p_Module
AND seq_number = v_SeqNum;
/*
Unwind the call stack to get each call out by scanning the
call stack string. Start with the index after the first call
on the stack.
This will be after the first occurrence of
'name' and the newline.
*/
v_Index := INSTR(v_CallStack, 'name') + 5;
/* Loop through the string, finding each newline. A newline
ends each call on the stack.
*/
WHILE v_Index < LENGTH(v_CallStack) LOOP
-- v_End is the position of the newline
v_End := INSTR(v_CallStack, v_NewLine, v_Index);
-- The call is between the current index and the newline
v_Call := SUBSTR(v_CallStack, v_Index, v_End - v_Index);
-- Skip over the current call, for the next iteration
v_Index := v_Index + LENGTH(v_Call) + 1;
/*
Within a call, we have the object handle, then the line
number, then the object name, separated by spaces. Separate
them out for insertion.
-- Trim white space from the call first.
*/
v_Call := TRIM(v_Call);
-- First get the object handle
v_Handle := SUBSTR(v_Call, 1, INSTR(v_Call, ' '));
-- Remove the object handle,then the white space
v_Call := SUBSTR(v_Call, LENGTH(v_Handle) + 1);
v_Call := TRIM(v_Call);
-- Get the line number
v_LineNum := TO_NUMBER(SUBSTR(v_Call,1,INSTR(v_Call,' ')));
-- Remove the line number, and white space
v_Call := SUBSTR(v_Call, LENGTH(v_LineNum) + 1);
v_Call := TRIM(v_Call);
-- What is left is the object name
v_ObjectName := v_Call;
-- Insert all calls except the call for ErrorPkg
IF v_CallOrder > 1 THEN
INSERT INTO call_stacks
(module, seq_number, call_order, object_handle, line_num,
object_name)
VALUES
(p_Module, v_SeqNum, v_CallOrder, v_Handle, v_LineNum,
v_ObjectName);
END IF;
v_Callorder := v_CallOrder + 1;
END LOOP;
IF p_CommitFlag THEN
COMMIT;
END IF;
END StoreStacks;
END ErrorPkg;
/
Format Error Stack Demo Table And Trigger
CREATE TABLE ttt (f1 number);
CREATE OR REPLACE TRIGGER ttt_insert
BEFORE INSERT ON ttt
BEGIN
RAISE ZERO_DIVIDE;
END ttt_insert;
/
Error Producing Procedures (A, B, And C)
CREATE OR REPLACE PROCEDURE C AS
BEGIN
INSERT INTO ttt VALUES (7);
EXCEPTION
WHEN OTHERS THEN
ErrorPkg.HandleAll(FALSE);
RAISE;
END C;
/
--===================================================
CREATE OR REPLACE PROCEDURE B AS
BEGIN
C;
EXCEPTION
WHEN OTHERS THEN
ErrorPkg.HandleAll(FALSE);
RAISE;
END B;
/
--===================================================
CREATE OR REPLACE PROCEDURE A AS
v_ErrorSeq NUMBER;
BEGIN
B;
EXCEPTION
WHEN OTHERS THEN
ErrorPkg.HandleAll(TRUE);
ErrorPkg.StoreStacks('Scott', v_ErrorSeq, TRUE);
ErrorPkg.PrintStacks('Scott', v_ErrorSeq);
END A;
/
Run Format Error Stack Demo
exec a;
-- Examine the tables errors, call_stack, and error_stack
Predefined (Named) PL/SQL Exceptions
Exception Name
Error
Description
ACCESS_INTO_NULL
ORA-06530
Attempted to assign values to the attributes of an uninitialized (NULL) object
CASE_NOT_FOUND
ORA-06592
None of the choices in the WHEN clauses of a CASE statement is selected and there is no ELSE clause
COLLECTION_IS_NULL
ORA-06531
Attempt to apply collection methods other than EXISTS to an uninitialized (NULL) PL/SQL table or varray
CURSOR_ALREADY_OPEN
ORA-06511
Exactly what it seems to be. Tried to open a cursor that was already open
DUP_VAL_ON_INDEX
ORA-00001
An attempt to insert or update a record in violation of a primary key or unique constraint
INVALID_CURSOR
ORA-01001
The cursor is not open or not valid in the context in which it is being called
INVALID_NUMBER
ORA-01722
It isn't a number even though you are treating it like one to trying to turn it into one
LOGIN_DENIED
ORA-01017
Invalid name and/or password for the instance
NO_DATA_FOUND
ORA-01403
The SELECT statement returned no rows or referenced a deleted
element in a nested table or referenced an initialized element in an Index-By table
NOT_LOGGED_ON
ORA-01012
Database connection lost
PROGRAM_ERROR
ORA-06501
Internal PL/SQL error
ROWTYPE_MISMATCH
ORA-06504
The rowtype does not match the values being fetched, or assigned, to it
SELF_IS_NULL
ORA-30625
Program attempted to call a MEMBER method, but the instance of the object type has not been
intialized. The built-in parameter SELF points to the object, and is always the first parameterpassed to a MEMBER method
STORAGE_ERROR
ORA-06500
A hardware problem: Either RAM or disk drive
SUBSCRIPT_BEYOND_COUNT
ORA-06533
Reference to a nested table or varray index higher than the number of elements in the collection
SUBSCRIPT_OUTSIDE_LIMIT
ORA-06532
Reference to a nested table or varray index outside the declared range (such as -1)
SYS_INVALID_ROWID
ORA-01410
The conversion of a character string into a universal rowid
fails because the character string does not represent a valid rowid
TIMEOUT_ON_RESOURCE
ORA-00051
The activity took too long and timed out
TOO_MANY_ROWS
ORA-01422
The SQL INTO statement brought back more than one value or row (only one is allowed)
USERENV_COMMITSCN_ERROR
ORA-01725
Added for USERENV enhancement, bug 1622213
VALUE_ERROR
ORA-06502
An arithmetic, conversion, truncation, or size-constraint error.
Usually raised by trying to cram a 6 character string into a VARCHAR2(5) variable
ZERO_DIVIDE
ORA-01476
Not only would your math teacher not let you do it. Computer's
won't either. Who said you didn't learn anything useful in primary school
