How to log error using UTL_CALL_STACK [message #668615] |
Wed, 07 March 2018 03:28 |
|
sss111ind
Messages: 636 Registered: April 2012 Location: India
|
Senior Member |
|
|
HI All,
CREATE SEQUENCE error_log_seq;
CREATE TABLE error_log_tbl (
error_log_id NUMBER,
package_name VARCHAR2(30),
procedure_name VARCHAR2(50),
error_message VARCHAR2(4000),
creation_date DATE
);
CREATE OR REPLACE PACKAGE test_pkg AS
PROCEDURE error_log_insert (
p_package_name VARCHAR2,
p_procedure_name VARCHAR2,
p_error_msg VARCHAR2,
p_error_dt_tm DATE
);
PROCEDURE log_test;
END test_pkg;
create or replace PACKAGE BODY test_pkg AS
PROCEDURE error_log_insert (
p_package_name VARCHAR2,
p_procedure_name VARCHAR2,
p_error_msg VARCHAR2,
p_error_dt_tm DATE
)
IS
BEGIN
INSERT INTO error_log_tbl VALUES (
error_log_seq.NEXTVAL,
p_package_name,
p_procedure_name,
p_error_msg,
p_error_dt_tm
);
COMMIT;
END error_log_insert;
PROCEDURE log_test IS
l_empno VARCHAR2(1);
BEGIN
SELECT
empno
INTO
l_empno
FROM
emp
WHERE
ename = 'KING';
EXCEPTION
WHEN OTHERS THEN
test_pkg.error_log_insert(
'test_pkg',
'log_test',
sqlcode
|| ' : '
|| sqlerrm
|| '. Backtrace = '
|| dbms_utility.format_error_backtrace,
systimestamp
);
END log_test;
END test_pkg;
How to use UTL_CALL_STACK instead of dbms_utility package in this case. Please help
Regards,
Nathan
|
|
|
|
|
Re: How to log error using UTL_CALL_STACK [message #668633 is a reply to message #668618] |
Wed, 07 March 2018 07:32 |
cookiemonster
Messages: 13922 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
Well I've never used utl_call_stack but a quick browse of the documentation shows it does a lot of things.
The question you need to answer first is - what information do you want that you can't get from dbms_utility?
If you can't answer that then you've got a solution in search of a problem.
|
|
|
|
|
Re: How to log error using UTL_CALL_STACK [message #668964 is a reply to message #668668] |
Mon, 26 March 2018 07:16 |
Bill B
Messages: 1971 Registered: December 2004
|
Senior Member |
|
|
While you can have your logging package (test_pkg) if the program aborts you will not have anything saved in your error_log_tbl unless you make your logging package using "PRAGMA AUTONOMOUS_TRANSACTION;"
Frankly logging routines are the only place where I found a good use for the PRAGMA
[Updated on: Mon, 26 March 2018 07:25] Report message to a moderator
|
|
|