Exception Handling during Oracle Storage

 

1. Problem Source
In Oracle, you can use dbms_output.put_line to print the prompt information, but it is easy for the buffer to overflow.
You can use dbms_output.enable (1000000); To set the buffer size.
However, if there is a size, it may overflow again (the program is too bad to write, the error keeps, sorry ).
So I want to write the exception information to a table.
2. Create a table
This is easy
Create Table werrorlog
(
Procedure_name varchar2 (50) not null
, Err_msg varchar2 (255) not null
, Sys_err_code varchar2 (10) Not null
, Sys_err_msg varchar2 (1000) not null
, Create_time date not null
);
Comment on table werrorlog is 'Log table, used to record Stored Procedure error ';
Comment on column werrorlog. procedure_name is 'process name, wrong stored procedure or function ';
Comment on column werrorlog. err_msg is 'custom error information ';
Comment on column werrorlog. sys_err_code is 'oracle system error Code ';
Comment on column werrorlog. sys_err_msg is 'oracle system error information ';
Comment on column werrorlog. create_time is 'error occurred time ';
3. Stored Procedure
Create or replace procedure prc_err_log
(
I _procedure_name varchar2
, I _err_msg varchar2
)
-- Log writing process, Albert song
-- Note that no commit or rollback operation is performed in this process.
-- Usage
-- Exception
-- When others
-- Then
-- Rollbak;
-- Prc_err_log ('prc _ err_log ', 'Log writing table error ');
-- Commit;
As
V_sqlcode varchar (10 );
V_sqlerrm varchar (1000 );
Begin
V_sqlcode: = sqlcode;
V_sqlerrm: = sqlerrm;
Insert into werrorlog values (I _procedure_name, I _err_msg, v_sqlcode, v_sqlerrm, sysdate );
Exception
When others
Then
V_sqlcode: = sqlcode;
V_sqlerrm: = sqlerrm;
Insert into werrorlog values ('prc _ err_log ', 'Write log table error', v_sqlcode, v_sqlerrm, sysdate );
End;
4. Use
Create or replace procedure prc_test
As
V_data varchar2 (255 );
Begin
Insert into werrorlog values ('prc, 'error', 'test', 'test', sysdate );
Select err_msg into v_data from werrorlog where err_msg = 'no err MSG ';
Exception
When others then
Rollback;
Prc_err_log ('prc _ test', 'test prc_err_log ');
Commit;

End;
5. Test
Exec prc_test;
Select * From werrorlog;
6. Description
Why cannot I commit in prc_err_log?
The purpose is to be used in such a place.
Create or replace procedure prc_test_transaction
As
V_in varchar2 (255 );
Begin
Insert into testsql values ('11', '55 ');
If 1 = 1 then
Begin
Select Code into v_in from testsql where code = '201312 ';
Exception
When others
Then
Prc_err_log ('prc _ test_transaction', 'records with code 12323 not found in the testsql table ');

End;
End if;
...
Commit;
 
Exception
When others
Then
Rollback;
Prc_err_log ('prc _ err_log ', 'unknown error ');
Commit;
End;
In this case, if the commit at the first prc_err_log, the executed operation is submitted.

Postscript:
My only goal is to record the running process of the program in detail. It is best to know which program has encountered an exception. Now you can record some custom variables in err_msg to track the program status.
Soon after I first learned Oracle, I thought there should be a better method, but I did not find it and I did not create it myself.
Dbms_output has a new_line. I wonder if it can prevent Buffer Overflow?