Oracle Stored Procedure record exception

Oracle Stored Procedure record exception

For Oracle stored procedures, an exception can be thrown to the code or recorded in a table. If your system application has many nodes, such as our 40 nodes, if an error is thrown into the code, the error cannot be found at all. It is best to use a table to record the error. This function should be common.

-- Create an error log table create table PUB_PROC_ERR_LOG (LOG_ID NUMBER, MODULE_NAME VARCHAR2 (100), PROC_NAME VARCHAR2 (100), ERR_TIME DATE, SQL _CODE VARCHAR2 (50), SQL _ERRM VARCHAR2 (100 ), ERR_CONTENT VARCHAR2 (500); comment on column PUB_PROC_ERR_LOG.LOG_ID is 'Primary key'; comment on column comment is 'module name'; comment on column PUB_PROC_ERR_LOG.PROC_NAME is 'stored procedure name '; comment on column PUB_PROC_ERR_LOG.ERR_TIME is 'error time'; comment on column comment is 'sqlcode'; comment on column comment is 'sqlerrm '; comment on column PUB_PROC_ERR_LOG.ERR_CONTENT is' specific line of the error '; -- sequence of Table Primary keys create sequence Limit 1 maxvalue 9999999999999999999999999999 start with 21 increment by 1 cache 20; -- general record error stored procedure create or replace PROCEDURErecord_proc_err_log (module_name varchar2, proc_name varchar2, v_SQLCODE varchar2, v_SQLERRM varchar2, v_err_line varchar2) values; BEGINinsert into values (log_id, module_name, proc_name, err_time, SQL _code, values, err_content) values (values, module_name, proc_name, sysdate, v_SQLCODE, v_SQLERRM, v_err_line); commit; END record_proc_err_log; -- test create or replace procedure test_p1 isbeginexecute IMMEDIATE 'select from test'; predictionwhen others values ('module name ', 'test _ p1', SQLCODE, SQLERRM, substr (dbms_utility.format_error_backtrace, 1,400); end test_p1; SQL> col proc_name format a8; SQL> col err_time format a10; SQL> col SQL _code format a5; SQL> col SQL _ERRM format a22; SQL> col ERR_CONTENT format a42; SQL> select proc_name, err_time, SQL _code, SQL _ERRM, ERR_CONTENT from pub_proc_err_log; PROC_NAM ERR_TIME SQL _C SQL _ERRM ERR_CONTENT -------- ---------- ------------------------ starting test_p1 08-12-14-936 ORA-00936: Missing expression ORA-06512: In "LCAM_TEST.TEST_P1", line 3