如何将获取异常插入到定义表

显示全部楼层 · 2008-1-2 17:35:53

请问在开发存储过程中,如果出出异常,我如何将获取的异常插入到我定义的表中????????
代码如下:
begin
declare
i integer;
begin
.
.
.

end;
exception
WHEN OTHERS THEN
Rollback;
INSERT INTO day_synchronization_track (deal_time,deal_desc) VALUES (sysdate,SUBSTR(SQLERRM, 1, 200));
COMMIT;

end
---------------------------------------------------------------------------------
以上代码编译不过总是把以下语句错误:提示:Error: PL/SQL: ORA-00984: 列在此处不允许
INSERT INTO day_synchronization_track (deal_time,deal_desc) VALUES (sysdate,SUBSTR(SQLERRM, 1, 200));

千问 · 2008-1-2 17:35:53

如何将获取异常插入到定义表

请问在开发存储过程中,如果出现异常,我如何将获取的异常插入到我定义的表中????????
代码如下:
begin
declare
i integer;
begin
.
.
.

end;
exception
WHEN OTHERS THEN
Rollback;
INSERT INTO day_synchronization_track (deal_time,deal_desc) VALUES (sysdate,SUBSTR(SQLERRM, 1, 200));
COMMIT;

end
---------------------------------------------------------------------------------
以上代码编译不过总是把以下语句错误:提示:Error: PL/SQL: ORA-00984: 列在此处不允许
INSERT INTO day_synchronization_track (deal_time,deal_desc) VALUES (sysdate,SUBSTR(SQLERRM, 1, 200));

千问 · 2008-1-2 17:35:53

sqlerrm用动态语句
execute immediate 'INSERT INTO day_synchronization_track (deal_time,deal_desc) VALUES (sysdate,SUBSTR('''||SQLERRM||''', 1, 200))';

千问 · 2008-1-2 17:35:53

使用自治事务
pragma autonomous_transaction;

千问 · 2008-1-2 17:35:53

SQL> create table test02 (T_Time char(14),T_MARK varchar2(200));
Table created
SQL> select * from test;
A
B C
---- --------- --------------------
00015000 AAA
00028000 BBB
00032000 CCC
00049000 EEE
SQL> select a from test where c='DDD';
A
----
declare
o_Errmsgvarchar2(200);
v_a syc.test.a%type;
begin
begin
select a
into v_a
from test
wherec='DDD';
end;
exception
when others then
rollback;
dbms_output.put_line(substr(sqlerrm,1,200));
o_Errmsg := substr(sqlerrm,1,200);
insert into test02 (t_time,t_mark) values (to_char(sysdate,'yyyymmddhh24miss'),o_Errmsg);
commit;
end;
SQL> select * from test02;
T_TIME T_MARK
-------------- --------------------------------------------------------------------------------
20080925110203 ORA-01403: 未找到数据
SQL>
做了个测试是可以的

千问 · 2008-1-2 17:35:53

zxqsharp
------------------------
你在匿名块是可以通过,但在存储过程就不行
如:
create or replacePROCEDURE aaasssdddas
begin
declare
o_Errmsgvarchar2(200);

begin
insert into aabbcc(aaa,sss) values('******aaasssddd开始******',sysdate);
end;
exception
when others then
rollback;
dbms_output.put_line(substr(sqlerrm,1,200));
o_Errmsg := substr(sqlerrm,1,200);
INSERT INTO day_synchronization_track (deal_time,deal_desc) VALUES (sysdate,o_Errmsg);
COMMIT;
end;

千问 · 2008-1-2 17:35:53

zxqsharp
------------------------
你在匿名块是可以通过,但在存储过程就不行
如:
create or replacePROCEDURE aaasssdddas
begin
declare
o_Errmsgvarchar2(200);

begin
insert into aabbcc(aaa,sss) values('******aaasssddd开始******',sysdate);
end;
exception
when others then
rollback;
dbms_output.put_line(substr(sqlerrm,1,200));
o_Errmsg := substr(sqlerrm,1,200);
INSERT INTO day_synchronization_track (deal_time,deal_desc) VALUES (sysdate,o_Errmsg);
COMMIT;
end;

千问 · 2008-1-2 17:35:53

That you wrote codes contain many syntax errors, you'd better read oracle document first!
To see following:
create or replacePROCEDURE aaasssddd
as
o_Errmsgvarchar2(200);
begin
insert into aabbcc(aaa,sss) values('******aaasssddd开始******',sysdate);

exception
when others then
rollback;
dbms_output.put_line(substr(sqlerrm,1,200));
o_Errmsg := substr(sqlerrm,1,200);
INSERT INTO day_synchronization_track (deal_time,deal_desc) VALUES (sysdate,o_Errmsg);
COMMIT;
end;

千问 · 2008-1-2 17:35:53

直接用substr(sqlerrm,1,500)是不行的，但先定义个变量是可以的

千问 · 2008-1-2 17:35:53

原帖由 sxlcom 于 2008-9-25 11:23 发表
zxqsharp
------------------------
你在匿名块是可以通过,但在存储过程就不行
如:
create or replacePROCEDURE aaasssdddas
begin
declare
o_Errmsgvarchar2(200);

begin
insert into aabbcc(aaa,sss) values('******aaasssddd开始******',sysdate);
end;
exception
when others then
rollback;
dbms_output.put_line(substr(sqlerrm,1,200));
o_Errmsg := substr(sqlerrm,1,200);
INSERT INTO day_synchronization_track (deal_time,deal_desc) VALUES (sysdate,o_Errmsg);
COMMIT;
end;
在存储过程中用自治事务
create or replacePROCEDURE aaasssdddis
begin
declare
o_Errmsgvarchar2(200);
pragmaautonomous_transaction; --加上这句就可以了
begin
insert into aabbcc(aaa,sss) values('******aaasssddd开始******',sysdate);
end;
exception
when others then
rollback;
dbms_output.put_line(substr(sqlerrm,1,200));
o_Errmsg := substr(sqlerrm,1,200);
INSERT INTO day_synchronization_track (deal_time,deal_desc) VALUES (sysdate,o_Errmsg);
COMMIT;
end;