oracle oracle中的错误/异常处理
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1906208/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
error/exception handling in oracle
提问by user223541
i want to develop a procedure for following scenario.
我想为以下场景开发一个程序。
I have one source, one target and one error table. Target and Error tables have all fields that are present in source tables. But the data type of all fields for error table are varchar. Error table don't have integrity, foreign key and other constraints. Error table also have two more fields: Error no and error message.
我有一个源，一个目标和一个错误表。目标表和错误表具有源表中存在的所有字段。但是错误表的所有字段的数据类型都是varchar。错误表没有完整性、外键等约束。错误表还有另外两个字段：错误号和错误消息。
Now when procedure is executed if there is error while inserting any record into target then that record shold be moved to error table. Also the data base error code and error message should be logged in the error tables fields as mentioned.
现在，当程序执行时，如果在将任何记录插入目标时出现错误，那么该记录将被移动到错误表中。此外，数据库错误代码和错误消息应记录在提到的错误表字段中。
How can i devlop such a procedure?
我怎样才能开发这样的程序？
Example of table schema:
表架构示例：
source table
src(id number
,name varchar2(20)
, ... )
target table
tgt(id number
,name varchar2(20) not null
, ... )
error table
err (id varchar2(255)
,name varchar2(255)
, ...
, errno varchar2(255)
, errmsg varchar2(255))
回答by David Aldridge
Have you looked at Oracle's own error logging functionality?
您是否查看过 Oracle 自己的错误日志记录功能？
http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/tables.htm#ADMIN10261
http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/tables.htm#ADMIN10261
回答by Erich Kitzmueller
Procedures to do that could look like this:
执行此操作的程序可能如下所示：
procedure ins_tgt(p_id in number, p_name in varchar2, ...) is
v_errno number; v_errmsg varchar2(2000);
begin
insert into tgt(id, name, ...) values (p_id, p_name, ...);
exception
when others then
/* copy sqlcode and sqlerrm into variables since they can't be used directly in a SQL statement */
v_errno := sqlcode;
v_errmsg := sqlerrm;
insert into err(id, name, errno, errmsg) values (p_id, p_name, v_errno, v_errmsg);
end;
procedure copy_src_tgt is
begin
for s in (select * from src) loop
ins_tgt(s.id, s.name, ...);
end loop;
end;
but it seems like a horrible inefficient way to copy data from one table to another...
但将数据从一个表复制到另一个表似乎是一种低效的可怕方式......
回答by kurosch
CREATE OR REPLACE PACKAGE BODY foo_dml IS
PROCEDURE log_err (
p_sqlcode IN NUMBER,
p_sqlerrm IN VARCHAR2,
p_src IN foo%ROWTYPE
) IS
-- inserts the input row to the err log
BEGIN
INSERT INTO err (
errno,
errmsg,
ID,
NAME,
...
) VALUES (
p_sqlcode,
p_sqlerrm,
p_src.id,
p_src.name,
...
);
END;
PROCEDURE copy_to_tgt (
p_src IN foo%ROWTYPE
) IS
-- copies the input row to the tgt table
BEGIN
INSERT INTO
tgt
VALUES
p_src;
EXCEPTION
WHEN OTHERS THEN
log_err( SQLCODE, SQLERRM, p_src );
END;
END;
/
