Examples of how to use Error Logging for SQL statements (Doc ID 1076042.1)
来源:互联网 发布:字符串 table js 编辑:程序博客网 时间:2024/04/29 08:11
In this Document
Applies to:
Oracle Server - Enterprise Edition - Version 10.2.0.1 to 11.2.0.4 [Release 10.2 to 11.2]
Information in this document applies to any platform.
Goal
Give examples of the Error Logging feature for SQL introduced in 10.2
Simple case involving one table
create user test identified by test;
grant dba to test;
alter user test default tablespace users;
connect test/test;
-- CREATE THE TABLE FOR THE TEST
create table test (col1 varchar2(1) primary key, col2 varchar2(20));
-- Table created
-- PRODUCE AN ERROR
declare i number;
begin
i := 0;
while i <= 100 loop
insert into test values (to_char(i),'test');
i := i+1;
end loop;
end;
/
-- ERROR at line 1:
-- ORA-12899: value too large for column "SYS"."TEST"."COL1" (actual: 2, maximum:1)
-- ORA-06512: at line 5
-- SHOW THAT NO ROWS WERE INSERTED
select count(*) from test;
-- COUNT(*)
-- ----------
-- 0
-- ENABLE ERROR LOGGING FOR THE TABLE
begin
dbms_errlog.create_error_log('TEST') ;
end;
/
-- PL/SQL procedure successfully completed.
-- RE-EXECUTE THE PROCESS THAT PRODUCED THE EARLIER ERRORS AND LOG THE ERRORS
declare i number;
begin
i := 0;
while i <= 100 loop
insert into test values (to_char(i),'test')
LOG ERRORS INTO ERR$_TEST REJECT LIMIT UNLIMITED;
i := i+1;
end loop;
end;
/
-- PL/SQL procedure successfully completed.
-- SHOW THE RESULTS
select count(*) from test;
-- COUNT(*)
-- ----------
-- 10
select count(*) from err$_test;
-- COUNT(*)
-- ----------
-- 91
-- COL1 values 0-9 succeed ... values 10-100 fail
-- CLEAN UP FROM THE TEST
DROP TABLE TEST PURGE;
DROP TABLE ERR$_TEST PURGE;
CONNECT / AS SYSDBA
DROP USER TEST CASCADE;
CASE STUDY #2
Test a multi table insert with only one of the two tables using error logging but without using multitable insert statements
create user test identified by test;
grant dba to test;
alter user test default tablespace users;
connect test/test;
-- CREATE THE TABLES FOR THE TEST
create table test (col1 varchar2(1) primary key, col2 varchar2(20));
create table test2 (col1 varchar2(3) primary key, col2 varchar2(20));
-- PRODUCE AN ERROR
declare i number;
begin
i := 0;
while i <= 100 loop
insert into test values (to_char(i),'test');
insert into test2 values (to_char(i),'test');
i := i+1;
end loop;
end;
/
-- ERROR at line 1:
-- ORA-12899: value too large for column "SYS"."TEST"."COL1" (actual: 2, maximum:1)
-- ORA-06512: at line 5
-- SHOW THAT NO ROWS WERE INSERTED
select count(*) from test;
-- COUNT(*)
-- ----------
-- 0
select count(*) from test2;
-- COUNT(*)
-- ----------
-- 0
-- ENABLE ERROR LOGGING FOR THE FIRST TABLE
begin
dbms_errlog.create_error_log('TEST') ;
end;
/
-- RE-EXECUTE THE PROCESS THAT PRODUCED THE EARLIER ERRORS AND LOG THE ERRORS
declare i number;
begin
i := 0;
while i <= 100 loop
insert into test values (to_char(i),'test')
LOG ERRORS INTO ERR$_TEST REJECT LIMIT UNLIMITED;
insert into test2 values (to_char(i),'test');
i := i+1;
end loop;
end;
/
-- PL/SQL procedure successfully completed.
-- SHOW THE RESULTS
select count(*) from test;
-- COUNT(*)
-- ----------
-- 10
select count(*) from err$_test;
-- COUNT(*)
-- ----------
-- 91
-- COL1 values 0-9 succeed ... values 10-100 fail
select count(*) from test2;
-- COUNT(*)
-- ----------
-- 101
-- CLEAN UP FROM THE TEST
DROP TABLE TEST PURGE;
DROP TABLE ERR$_TEST PURGE;
CONNECT / AS SYSDBA
DROP USER TEST CASCADE;
CASE STUDY #3
Test a multi table insert with only one of the two tables using error logging using multitable insert statements
create user test identified by test;
grant dba to test;
alter user test default tablespace users;
connect test/test;
-- CREATE THE TABLES FOR THE TEST
create table test (col1 varchar2(1) primary key, col2 varchar2(20));
create table test2 (col1 varchar2(3) primary key, col2 varchar2(20));
create table test3 (col1 number);
-- LOAD THE TEST3 TABLE WITH VALUES
declare i number;
begin
i := 0;
while i <= 100 loop
insert into test3 values(i);
i := i+1;
end loop;
end;
/
-- PL/SQL procedure successfully completed.
commit;
-- Commit complete.
insert all
into test values (to_char(col1),'test')
into test2 values (to_char(col1),'test')
select col1 from test3;
-- ERROR at line 2:
-- ORA-12899: value too large for column "TEST"."TEST"."COL1" (actual: 2, maximum:1)
-- SHOW THAT NO ROWS WERE INSERTED
select count(*) from test;
-- COUNT(*)
-- ----------
-- 0
select count(*) from test2;
-- COUNT(*)
-- ----------
-- 0
-- ENABLE ERROR LOGGING FOR THE FIRST TABLE
begin
dbms_errlog.create_error_log('TEST') ;
end;
/
-- RE-EXECUTE THE PROCESS THAT PRODUCED THE EARLIER ERRORS AND LOG THE ERRORS
insert all
into test values (to_char(col1),'test') log errors into err$_test reject limit unlimited
into test2 values (to_char(col1),'test')
select col1 from test3;
-- 111 rows created.
-- SHOW THE RESULTS
select count(*) from test;
-- COUNT(*)
-- ----------
-- 10
select count(*) from err$_test;
-- COUNT(*)
-- ----------
-- 91
-- COL1 values 0-9 succeed ... values 10-100 fail
select count(*) from test2;
-- COUNT(*)
-- ----------
-- 101
-- CLEAN UP FROM THE TEST
DROP TABLE TEST PURGE;
DROP TABLE ERR$_TEST PURGE;
DROP TABLE TEST2 PURGE;
DROP TABLE TEST3 PURGE;
CONNECT / AS SYSDBA
DROP USER TEST CASCADE;
CASE STUDY #4
Simple case involving one table but with manually created error logging table
create user test identified by test;
grant dba to test;
alter user test default tablespace users;
connect test/test;
-- CREATE THE TABLE FOR THE TEST
create table test (col1 varchar2(1) primary key, col2 varchar2(20));
-- Table created
-- PRODUCE AN ERROR
declare i number;
begin
i := 0;
while i <= 100 loop
insert into test values (to_char(i),'test');
i := i+1;
end loop;
end;
/
-- ERROR at line 1:
-- ORA-12899: value too large for column "SYS"."TEST"."COL1" (actual: 2, maximum:1)
-- ORA-06512: at line 5
-- SHOW THAT NO ROWS WERE INSERTED
select count(*) from test;
-- COUNT(*)
-- ----------
-- 0
-- MANUALLY CREATE THE ERROR LOGGING TABLE
CREATE TABLE ERR$_TEST
( "ORA_ERR_NUMBER$" NUMBER,
"ORA_ERR_MESG$" VARCHAR2(2000),
"ORA_ERR_ROWID$" UROWID (4000),
"ORA_ERR_OPTYP$" VARCHAR2(2),
"ORA_ERR_TAG$" VARCHAR2(2000),
"COL1" VARCHAR2(4000),
"COL2" VARCHAR2(4000)
) SEGMENT CREATION IMMEDIATE;
-- PL/SQL procedure successfully completed.
-- RE-EXECUTE THE PROCESS THAT PRODUCED THE EARLIER ERRORS AND LOG THE ERRORS
declare i number;
begin
i := 0;
while i <= 100 loop
insert into test values (to_char(i),'test')
LOG ERRORS INTO ERR$_TEST REJECT LIMIT UNLIMITED;
i := i+1;
end loop;
end;
/
-- PL/SQL procedure successfully completed.
-- SHOW THE RESULTS
select count(*) from test;
-- COUNT(*)
-- ----------
-- 10
select count(*) from err$_test;
-- COUNT(*)
-- ----------
-- 91
-- COL1 values 0-9 succeed ... values 10-100 fail
- Examples of how to use Error Logging for SQL statements (Doc ID 1076042.1)
- How to Collect and Display System Statistics (CPU and IO) for CBO use (Doc ID 149560.1)
- the examples :how to use LP_SOLVE.EXE
- How To Use Google Logging Library (glog)
- How To Use Google Logging Library (glog)
- How To Use Google Logging Library (glog)
- How to use CPeriodic for a steady stream of ticks
- How to use java logging with logging.properties
- How to deinstall OCM (Doc ID 761313.1)
- How to Identify Resource Intensive SQL ("TOP SQL") (Doc ID 232443.1)
- Linux: How to Configure the DNS Server for 11gR2 SCAN (Doc ID 1107295.1)
- How to Validate Network and Name Resolution Setup for the Clusterware and RAC (Doc ID 1054902.1)
- How to change hostname for E-Business Suite Release 12 on single node (Doc ID 1277556.1)
- How To Collect 10046 Trace (SQL_TRACE) Diagnostics for Performance Issues (Doc ID 376442.1)
- How To Find Where The Memory Is Growing For A Process (Doc ID 822527.1)
- How To: Gather Statistics for the Cost Based Optimizer (Doc ID 1226841.1)
- How to determine the correct setting for JOB_QUEUE_PROCESSES (Doc ID 578831.1)
- How To Configure Client Failover For Dataguard Connections Using Database Services (Doc ID 1429223.1
- 程序调试手记—解决Stack Overflow问题
- 屏蔽键盘的某些按键的消息
- 解决ubuntu12.04安装fsl出现的“依赖,不安装”问题
- 堆和栈的区别
- MFC中处理消息的几个函数之间的区别
- Examples of how to use Error Logging for SQL statements (Doc ID 1076042.1)
- 用MultiByteToWideChar和WideCharToMultiByte进行短字符和宽字符之间的转换
- VC解析XML--使用CMarkup类解析XML
- WinCE读写ini文件和xml文件的方法
- 在C/C++中用strtok()实现Split函数的功能
- VC下加载JPG/GIF/PNG图片的两种方法
- CxImage的简单用法
- SkinSB皮肤滚动条库的使用
- 从位图中创建一个区域窗口