--简单的例子 BEGIN INSERT INTO esap_exception_log (rid, exception_id, exception_source, exception_rank, exception_param) SELECT s_esap_exception_log.NEXTVAL, p_excep_id, p_excep_source, p_excep_rank, p_excep_param FROM DUAL; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.put_line ( 'ERR:insert_exception_log:' || TO_CHAR (SQLCODE) || ' ' || SQLERRM ); RAISE;
END ;
输出吗?我希望插到一个表中。我得不到sqlerrm sqlcode比如: --简单的例子 BEGIN INSERT INTO esap_exception_log (rid, exception_id, exception_source, exception_rank, exception_param) SELECT s_esap_exception_log.NEXTVAL, p_excep_id, p_excep_source, p_excep_rank, p_excep_param FROM DUAL; EXCEPTION WHEN OTHERS THEN insert into test(sqlerrm,sqlcode) --test为自建表,用与存放错误的 values(sqlerrm,sqlcode); END ;
commit 也是没有用的。它提示 :此处不允许这样
BEGIN INSERT INTO esap_exception_log (rid, exception_id, exception_source, exception_rank, exception_param) SELECT s_esap_exception_log.NEXTVAL, p_excep_id, p_excep_source, p_excep_rank, p_excep_param FROM DUAL; EXCEPTION WHEN OTHERS THEN v_sqlerrm := sqlerrm; n_sqlcode := sqlcode; insert into test(sqlerrm,sqlcode) --test为自建表,用与存放错误的 values(v_sqlerrm ,n_sqlcode); END ;
BEGIN INSERT INTO esap_exception_log (rid, exception_id, exception_source, exception_rank, exception_param) SELECT s_esap_exception_log.NEXTVAL, p_excep_id, p_excep_source, p_excep_rank, p_excep_param FROM DUAL; EXCEPTION WHEN OTHERS THEN insert into test(sqlerrm,sqlcode) --test为自建表,用与存放错误的 values(sqlerrm,sqlcode); END ;--lz你的可以执行么 esap_exception_log 这个表你有么 我只是给你举了一个例子我给你发的短消息有点问题 我以为没有异常,就没有sqlcode 呢 declarebegin dbms_output.put_line (sqlcode || sqlerrm); end; --输出 0ORA-0000: normal, successful completion
你测试一下 我这里没有test表 不过应该没问题了declare exception_name exception; begin raise exception_name; exception when others then dbms_output.put_line ('exception'); dbms_output.put_line (sqlcode || sqlerrm); insert into test(sqlerrm,sqlcode) --test为自建表,用与存放错误的 values(sqlerrm,sqlcode); end;--output exception 1User-Defined Exception
EXCEPTION
WHEN OTHERS --异常时把SQLCODE,SQLERRM output
THEN
DBMS_OUTPUT.put_line ( 'ERR:insert_exception_log:'
|| TO_CHAR (SQLCODE)
|| ' '
|| SQLERRM
);
RAISE;
ROLLBACK;
BEGIN
INSERT INTO esap_exception_log
(rid, exception_id, exception_source, exception_rank,
exception_param)
SELECT s_esap_exception_log.NEXTVAL, p_excep_id, p_excep_source,
p_excep_rank, p_excep_param
FROM DUAL; EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ( 'ERR:insert_exception_log:'
|| TO_CHAR (SQLCODE)
|| ' '
|| SQLERRM
);
RAISE;
END ;
--简单的例子
BEGIN
INSERT INTO esap_exception_log
(rid, exception_id, exception_source, exception_rank,
exception_param)
SELECT s_esap_exception_log.NEXTVAL, p_excep_id, p_excep_source,
p_excep_rank, p_excep_param
FROM DUAL; EXCEPTION
WHEN OTHERS
THEN
insert into test(sqlerrm,sqlcode) --test为自建表,用与存放错误的
values(sqlerrm,sqlcode);
END ;
INSERT INTO esap_exception_log
(rid, exception_id, exception_source, exception_rank,
exception_param)
SELECT s_esap_exception_log.NEXTVAL, p_excep_id, p_excep_source,
p_excep_rank, p_excep_param
FROM DUAL; EXCEPTION
WHEN OTHERS
THEN
v_sqlerrm := sqlerrm;
n_sqlcode := sqlcode;
insert into test(sqlerrm,sqlcode) --test为自建表,用与存放错误的
values(v_sqlerrm ,n_sqlcode);
END ;
INSERT INTO esap_exception_log
(rid, exception_id, exception_source, exception_rank,
exception_param)
SELECT s_esap_exception_log.NEXTVAL, p_excep_id, p_excep_source,
p_excep_rank, p_excep_param
FROM DUAL; EXCEPTION
WHEN OTHERS
THEN
insert into test(sqlerrm,sqlcode) --test为自建表,用与存放错误的
values(sqlerrm,sqlcode);
END ;--lz你的可以执行么
esap_exception_log
这个表你有么
我只是给你举了一个例子我给你发的短消息有点问题
我以为没有异常,就没有sqlcode 呢
declarebegin
dbms_output.put_line (sqlcode || sqlerrm);
end;
--输出
0ORA-0000: normal, successful completion
我这里没有test表
不过应该没问题了declare
exception_name exception;
begin
raise exception_name;
exception when others
then
dbms_output.put_line ('exception');
dbms_output.put_line (sqlcode || sqlerrm);
insert into test(sqlerrm,sqlcode) --test为自建表,用与存放错误的
values(sqlerrm,sqlcode);
end;--output
exception
1User-Defined Exception