一、在plsql中,oracle自带的Exceptions如下:
Exception | Oracle Error | SQLCODE Value |
ACCESS_INTO_NULL | ORA-06530 | -6530 |
CASE_NOT_FOUND | ORA-06592 | -6592 |
COLLECTION_IS_NULL | ORA-06531 | -6531 |
CURSOR_ALREADY_OPEN | ORA-06511 | -6511 |
DUP_VAL_ON_INDEX | ORA-00001 | -1 |
INVALID_CURSOR | ORA-01001 | -1001 |
INVALID_NUMBER | ORA-01722 | -1722 |
LOGIN_DENIED | ORA-01017 | -1017 |
NO_DATA_FOUND | ORA-01403 | +100 |
NOT_LOGGED_ON | ORA-01012 | -1012 |
PROGRAM_ERROR | ORA-06501 | -6501 |
ROWTYPE_MISMATCH | ORA-06504 | -6504 |
SELF_IS_NULL | ORA-30625 | -30625 |
STORAGE_ERROR | ORA-06500 | -6500 |
SUBSCRIPT_BEYOND_COUNT | ORA-06533 | -6533 |
SUBSCRIPT_OUTSIDE_LIMIT | ORA-06532 | -6532 |
SYS_INVALID_ROWID | ORA-01410 | -1410 |
TIMEOUT_ON_RESOURCE | ORA-00051 | -51 |
TOO_MANY_ROWS | ORA-01422 | -1422 |
VALUE_ERROR | ORA-06502 | -6502 |
ZERO_DIVIDE | ORA-01476 | -1476 |
以上没有列出的oracle error可以通过如下方式截获
when others then
if sqlcode=[需要处理错误的oracle error code value] then
...
end if
二、自定义异常,示例如下:
--申明
DECLARE inventory_too_low EXCEPTION;
--抛出
IF order_rec.qty>inventory_rec.qty THEN
RAISE inventory_too_low;
END IF
--截获
EXCEPTION
WHEN inventory_too_low THEN
END;
此段摘自:http://techkb.it168.com/Articles/f/3/0/f303899469bf8c704d9fc6ca89efd443.htm
Oracle异常名称
错误号
说明
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.没有连接到ORACLE
LOGIN_DENIED
ORA-01017
You tried to log into Oracle with an invalid username/password combination.无效的用户名/口令
NO_DATA_FOUND
ORA-01403
SELECT INTO没有找到数据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
SELECT INTO 返回多行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
内部错误,需重新安装数据字典视图和pl/sql包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.
ROWTYPE_MISMATCH
ORA-6504
宿主游标变量与 PL/SQL变量有不兼容行类型
CURSOR_ALREADY_OPEN
ORA-06511
试图打开一个已打开的游标You tried to open a cursor that is already open.
ACCESS_INTO_NULL
ORA-6530
试图为null 对象的属性赋值
COLLECTION_IS_NULL
ORA-6531
试图给没有初始化的嵌套表变量或者Varry变量赋值
SUBSCRIPT_OUTSIDE_LIMIT
ORA-6532
对嵌套或varray索引使用了负数
SUBSCRIPT_BEYOND_COUNT
ORA-6533
对嵌套或varray索引的引用大于集合中元素的个数
本篇文章来源于 Linux公社网站(www.linuxidc.com) 原文链接:http://www.linuxidc.com/Linux/2010-12/30876.htm