Home » SQL & PL/SQL » SQL & PL/SQL » How to log error using UTL_CALL_STACK (Oracle 12c)
How to log error using UTL_CALL_STACK [message #668615] Wed, 07 March 2018 03:28 Go to next message
sss111ind
Messages: 582
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 #668617 is a reply to message #668615] Wed, 07 March 2018 03:45 Go to previous messageGo to next message
cookiemonster
Messages: 13133
Registered: September 2008
Location: Rainy Manchester
Senior Member
What exactly do you want utl_call_stack to do?
Have you read the documentation on it?
Re: How to log error using UTL_CALL_STACK [message #668618 is a reply to message #668617] Wed, 07 March 2018 04:16 Go to previous messageGo to next message
sss111ind
Messages: 582
Registered: April 2012
Location: India
Senior Member


Yes we come to know that it shows the details of error stack . So we thought to use it in error logging purpose. Is it possible to use this package here or any other option is there.
Re: How to log error using UTL_CALL_STACK [message #668633 is a reply to message #668618] Wed, 07 March 2018 07:32 Go to previous messageGo to next message
cookiemonster
Messages: 13133
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 #668634 is a reply to message #668615] Wed, 07 March 2018 07:35 Go to previous messageGo to next message
BlackSwan
Messages: 26027
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/wiki/WHEN_OTHERS
Re: How to log error using UTL_CALL_STACK [message #668668 is a reply to message #668633] Thu, 08 March 2018 07:03 Go to previous messageGo to next message
sss111ind
Messages: 582
Registered: April 2012
Location: India
Senior Member

Thank you CookieMonster for pointing out the difference, I think the purpose of two packages are different. And dbms_utility.format_error_backtrace it gives every information to the user and not necessary to use the utl_call_stack. I forgot to raise in when_others block .

[Updated on: Thu, 08 March 2018 07:04]

Report message to a moderator

Re: How to log error using UTL_CALL_STACK [message #668964 is a reply to message #668668] Mon, 26 March 2018 07:16 Go to previous message
Bill B
Messages: 1738
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

Previous Topic: Merge 2 or more rows into 1
Next Topic: oracle Trigger for E-mail notifications !!
Goto Forum:
  


Current Time: Tue Jun 19 00:07:40 CDT 2018