如何查出违反唯一关键子的类似SQLERRM
有个过程 每天更新用户信息表
原本采用MERGE 来做的，可是有人嫌它慢，建议采用DELETE INSERT来做
我先 delete userinfo wehre logtime > trunc(sysdate)-15 ; commit;
insert into userinfo
select * from t_cache_userinfo a,t_cache_usertype b,t_cache_expuserinfo c
where a.ui_usertype=b.ut_id
and a.ui_username=c.ui_username
and c.ui_lasttime > trunc(sysdate)-15;
ORA-00001: 违反唯一约束条件
oracle 有 sql%count sqlcode sqlerrm 获得错误和游标信息
获得被违反了关键字哪行数据 最好是ROWID 可有呢?
The failure causes the whole insert to roll back, regardless of howmany rows were inserted successfully. Adding the DML error loggingclause allows us tocomplete the insert of the valid rows.
INSERT INTO dest
SELECT *
FROM source
LOG ERRORS INTO err$_dest ('INSERT') REJECT LIMIT UNLIMITED;
99998 rows created.
SQL>
The rows that failed during the insert are stored in theERR$_DEST table, along with the reason for the failure.
COLUMN ora_err_mesg$ FORMAT A70
SELECT ora_err_number$, ora_err_mesg$
FROM err$_dest
WHERE ora_err_tag$ = 'INSERT';
ORA_ERR_NUMBER$ ORA_ERR_MESG$
--------------- ---------------------------------------------------------
1400 ORA-01400: cannot insert NULL into ("TEST"."DEST"."CODE")
1400 ORA-01400: cannot insert NULL into ("TEST"."DEST"."CODE")
oracle10g新功能，log error的实验
当你通过DML语句对一个表进行DML操作时,如果一个违反某种约束的错误记录发生,则整个事务会中止并回滚,这样会严重影响数据的刷新和浪费系统的性能
,为了解决这种问题,oracle10g提供了一个错误记录日志表的功能,拿insert操作举例得用该功能可以将一个源记录集合中满足约束条件的记录插入目的表中,
而将违反约束的记录插入错误日志表,这样既充分利用了系统的资源,又可以延迟处理错误记录对于数据仓库是一个非常有用的方法.
具体使用该方法一般包括三个步骤:
1创建错误日志表,共有二种方法
法一:
利用DBMS_ERRLOG.create_error_log函数;
EXEC DBMS_ERRLOG.create_error_log(dml_table_name => 'A')
创建出来错误日志表名为err$_a,即创建的error表以err$_开头，加上表名,我们也可指定错误日志表名
DBMS_ERRLOG.create_error_log(dml_table_name => 'A',err_log_table_name => 'A_LOG');
法二:
手工创建错误日志表
但需要注意的是手工创建的错误日志表结构需要满足oracle规定,否则会报错.
2
执行带 error logging参数的DML语句,其insert语法结构如下其实我也没有找到update或者delete
相关语法
insert_into_clause { values_clause [ returning_clause ] | subquery }
[ error_logging_clause ]
3
查询错误日志表,和一般的select语句一样.
下边我拿insert操作做测试用例
--创建测试表
CREATE TABLE a AS
SELECT ROWNUM id,rownum+1 VALUE
FROM all_objects
WHERE rownum<3;
--创建唯一性索引
CREATE UNIQUE INDEX unq_a ON a(id);
--利用系统包来创建相应error log表
EXEC DBMS_ERRLOG.create_error_log(dml_table_name => 'A',err_log_table_name => 'A_LOG');
PL/SQL procedure successfully completed.
SQL> desc A_LOG
Name Null? Type
--------------------- -------- -----------------
ORA_ERR_NUMBER$ NUMBER --错误记录数
ORA_ERR_MESG$ VARCHAR2(2000) --错误信息
ORA_ERR_ROWID$ ROWID --错误记录的rowid(仅对update或delete)
ORA_ERR_OPTYP$ VARCHAR2(2) --操作类型(I-INSERT,U-UPDATE,D-DELETE)
ORA_ERR_TAG$ VARCHAR2(2000) --自定义标志说明
ID VARCHAR2(4000) --原表字段
VALUE VARCHAR2(4000) --原表字段
其中前五个字段是固定的,后边字段根据原表确定.
请注意:原表和对应error log表的字段类型是不完全匹配的,
其转换规则如下
:
NUMBER VARCHAR2(4000)
CHAR/VARCHAR2(n) VARCHAR2(4000)
NCHAR/NVARCHAR2(n) NVARCHAR2(4000)
DATE/TIMESTAMP VARCHAR2(4000)
RAW RAW(2000)
ROWID UROWID
LONG/LOB
插入数据,rownum<3的记录违反约束,ROWNUM=3的记录正常插入
SQL> INSERT INTO a
2 SELECT ROWNUM id, rownum+1 VALUE
3 FROM all_objects
4 WHERE rownum<4
5 LOG ERRORS INTO A_LOG REJECT LIMIT UNLIMITED
6 ;
1 ROWS created.
--
请注意：此处为UNLIMITED，即不限制错误记录的条数，默认为0，即有错误记录就停止整个事务并回滚，我们也可人为设置一个数目，
--假设为20，即插入时错误记录数超过20则停止整个事务并回滚