--Create error Log table Create TablePub_proc_err_log (log_id Number, Module_nameVARCHAR2( -), Proc_nameVARCHAR2( -), Err_time Date,sql_codeVARCHAR2( -), Sql_errmVARCHAR2( -), Err_contentVARCHAR2( -)); Comment on columnPub_proc_err_log. log_id is 'PRIMARY Key'; Comment on columnPub_proc_err_log. Module_name is 'Module Name'; Comment on columnPub_proc_err_log. Proc_name is 'Stored Procedure name'; Comment on columnPub_proc_err_log. Err_time is 'Error Time'; Comment on columnPub_proc_err_log. Sql_code is 'SQLCODE'; Comment on columnPub_proc_err_log. Sql_errm is 'SQLERRM'; Comment on columnPub_proc_err_log. Err_content is 'the exact line of the error'; --sequence of table primary keys Createsequence Seq_record_proc_errminvalue1MaxValue9999999999999999999999999999Start with +Increment by 1Cache -; --Common logging error stored procedures CREATE OR REPLACE PROCEDURERecord_proc_err_log (module_namevarchar2, Proc_namevarchar2, V_sqlcodevarchar2, V_SQLERRMvarchar2, V_err_linevarchar2) isPRAGMA autonomous_transaction;BEGINInsert intoPub_proc_err_log (log_id,module_name,proc_name,err_time,sql_code,sql_errm,err_content)Values(seq_record_proc_err.nextval,module_name,proc_name,sysdate,v_sqlcode,v_sqlerrm,v_err_line);Commit;ENDRecord_proc_err_log;--to test Create or Replace procedureTest_p1 isbeginExecuteIMMEDIATE'Select from Test'; exception whenOthers ThenRecord_proc_err_log ('Module Name','TEST_P1', Sqlcode,sqlerrm,substr (Dbms_utility.format_error_backtrace,1, -));EndTest_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> SelectProc_name,err_time,sql_code,sql_errm,err_content fromPub_proc_err_log; Proc_nam err_time Sql_c sql_errm err_content-------- ---------- ----- ---------------------- ------------------------------------------Test_p1 ,-December- - -936ORA-00936: Missing expression ORA-06512: In the lcam_test. Test_p1 ", line3
Oracle Stored procedure Record exception