Hello Good Evening!
I am trying to write a logger utility using UTL_CALL_STACK,
I tried Many popular Blogs, but I ended Up seeing solutions with UTL_CALL_STACK,
where the Stack is Displayed as DBMS_OUTPUT message,
However, I wanted to Save that Call_stack, error_stack,backtrace Information into a column of a Table for Logging and better readability
When Queried the Log Table will display
- Row with Call Stack Type as 'CALL' with log data + call Stack Info ( nestled in a Nested Column)
- Row with Call Stack Type as 'Error' with log data + Error Stack Info ( nestled in a Nested Column)
- Row with Call Stack Type as 'BACKTRACE' with log data + Back trace Stack Info ( nestled in a Nested Column)
Pointer 1,2,3 will be available only when the Log program logs a Error into the Log table, else it will be Empty for Log type ( INFO)
Please Find the Following Code Script
CREATE TYPE call_stack_t AS OBJECT
( stack_type VARCHAR2(10)
, stack_dynamic_depth NUMBER
, stack_unit_line NUMBER
, stack_lexical_depth NUMBER
, stack_current_edition NUMBER
, stack_concatenate_subprogram VARCHAR2(256)
, stack_message VARCHAR2(2000) -- same as LOG_MESSAGE
);
CREATE TYPE call_stack_tab IS TABLE OF call_stack_t;
CREATE TABLE LOGGER_TST
(
LOG_RUN_ID NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY ,
LOG_COMPONENT VARCHAR2(128 BYTE), -- Whether is it a DB Component, APEX Component
LOG_START_DATE DATE,
LOG_END_DATE DATE,
LOG_LEVEL NUMBER,
LOG_TYPE VARCHAR2(128 BYTE), -- INFO,WARNING,DEBUG,ERROR
LOG_STATUS VARCHAR2(128 BYTE), -- FAILURE, SUCCESS
PROGRAM_ITERATION NUMBER, -- NUMBER to find how many times the Program Ran until Success, More of a Metric Field
PROGRAM_TYPE VARCHAR2(128 BYTE), -- Package, Procedure,function
PROGRAM_NAME VARCHAR2(128 BYTE), -- Program name of the Program Type
PROGRAM_OWNER VARCHAR2(128 BYTE), -- Owner of the Program
SUBPROGRAM_TYPE VARCHAR2(128 BYTE), -- Procedure,function
SUBPROGRAM_NAME VARCHAR2(128 BYTE), -- Subprogram name of the SubProgram Type
LOG_STACK_TYPE VARCHAR2(10), -- Call, Error,backtrace
CALL_STACK call_stack_tab,
--ERROR_STACK
--BACKTRACE
LOG_MESSAGE VARCHAR2(4000 BYTE), -- log message
LOG_MESSAGE_OVERFLOW CLOB, -- Log message incase > 32K
LAST_MODIFIED_BY VARCHAR2(128 BYTE),
LAST_MODIFIED_DATE DATE,
CREATED_BY VARCHAR2(128 BYTE) DEFAULT 'RIM_API_USER',
CREATED_DATE DATE DEFAULT SYSDATE
)
NESTED TABLE call_stack STORE AS prog_call_stacks
The reason creating Table With a Nested table Column, is I want to save the UTL_CALL_STACK information into a readable Column, instead of one Variable nor a DBMS_OUTPUT message
with Call Stack info as following below.
***** Call Stack Start *****
Depth Lexical Line Owner Edition Name
. Depth Number
--------- --------- --------- --------- --------- --------------------
5 0 1 __anonymous_block
4 1 5 TEST TEST_PKG.PROC_1
3 1 10 TEST TEST_PKG.PROC_2
2 1 15 TEST TEST_PKG.PROC_3
1 0 13 TEST DISPLAY_CALL_STACK
***** Call Stack End *****
instead I would like to Store the above information as below or
Please Suggest a better way to Hold the Above Message so that when Queried the column will not have any alignment issue.
Table below is a Expected Output, but the data need not be True
| LOG_RUN_ID | LOG_COMPONENT | LOG_START_DATE | LOG_END_DATE | LOG_TYPE | LOG_STATUS | PROGRAM_ITERATION | PROGRAM_TYPE | PROGRAM_NAME | PROGRAM_OWNER | SUBPROGRAM_TYPE | SUBPROGRAM_NAME | LOG_STACK_TYPE | CALL_STACK | Error _stack | backtrace |
|---|
| 1 | DB | SYSDATE | SYSDATE | ERROR | FAILED | 1 | PACKAGE | LOGGER_TST_PKG | DB_USER | Procedure | Proc1 | CALL | Stack Type | Stack
depth | Stack Unit line | Lexical Depth | current Edition | subprg
concat | stack message |
|---|
| Call | 3 | 1 | 0 | | __anonymous_block | Some info |
| Same as Call Stack When Error | Same as Call Stack, when Error+ backtrace |
| 1 | DB | SYSDATE | SYSDATE | ERROR | FAILED | 1 | PACKAGE | LOGGER_TST_PKG | DB_USER | Procedure | Proc1 | CALL | Stack Type | Stack
depth | Stack Unit line | Lexical Depth | current Edition | subprg
concat | stack message |
|---|
| Call | 2 | 5 | 1 | | TEST_PKG.PROC1
| Some info |
| | |
My Question is
How Do I Insert into this table programatically using a PL/SQL Procedure?
I am getting an Error
[Error] PLS-00302 (48: 63): PLS-00302: component 'STACK_CONCATENATE_SUBPROGRAM' must be declared
When Executing the Below program
PROCEDURE insert_log_TST(p_in_logger_rec logger_tst%ROWTYPE)
IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO
grapl_logger_tst
( product_id
, log_component
, log_start_date
, log_end_date
, log_level
, log_type
, log_status
, program_iteration
, program_type
, program_name
, program_owner
, subprogram_type
, subprogram_name
, call_stack
(stack_type
,stack_dynamic_depth
,stack_unit_line
,stack_lexical_depth
,stack_current_edition
,stack_concatenate_subprogram
,stack_message)
, log_stack_type
)
VALUES( NVL(p_in_logger_rec.product_id,NULL)
,NVL(p_in_logger_rec.log_component,NULL)
,NVL(p_in_logger_rec.log_start_date,NULL)
,NVL(p_in_logger_rec.log_end_date,NULL)
,NVL(p_in_logger_rec.log_level,NULL)
,NVL(p_in_logger_rec.log_type,NULL)
,NVL(p_in_logger_rec.log_status,NULL)
,NVL(p_in_logger_rec.program_iteration,NULL)
,NVL(p_in_logger_rec.program_type,NULL)
,NVL(p_in_logger_rec.program_name,NULL)
,NVL(p_in_logger_rec.program_owner,NULL)
,NVL(p_in_logger_rec.subprogram_type,NULL)
,NVL(p_in_logger_rec.subprogram_name,NULL)
,call_stack_tab(call_stack_t( p_in_logger_rec.stack_type
,p_in_logger_rec.stack_dynamic_depth
,p_in_logger_rec.stack_unit_line
,p_in_logger_rec.stack_lexical_depth
,p_in_logger_rec.stack_current_edition
,p_in_logger_rec.stack_concatenate_subprogram
,'Call Stack Message'))
,NVL(p_in_logger_rec.log_stack_type,NULL)
);
COMMIT;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('>> Error: Logger Program '||SQLCODE ||'-'||SQLERRM);
RAISE;
END;
-- Procedure I will Call the above insert_log_TST program
PROCEDURE display_call_stack
AS
--Constants
gc_line_feed CONSTANT VARCHAR2(1):= CHR(10);
-- Carriage return and Line feed Constant
gc_cflf CONSTANT VARCHAR2(2):= CHR(13)||CHR(10);
l_depth PLS_INTEGER;
l_text_msg VARCHAR2(4000);
lv_stack_type VARCHAR2(10):='CALL';
lv_logger_rec grapl_logger%rowtype;
lv_subprogram utl_call_stack.unit_qualified_name;
BEGIN
l_depth := utl_call_stack.dynamic_depth;
lv_subprogram := utl_call_stack.subprogram(utl_call_stack.dynamic_depth);
FOR i IN 1 .. l_depth LOOP
-- I am not sure how do i get a List Like Below when Using Nested Table as a column
lv_logger_rec.stack_dynamic_depth := i;
lv_logger_rec.stack_type := lv_stack_type;
lv_logger_rec.stack_unit_line := TO_CHAR(utl_call_stack.unit_line(i),'99');
lv_logger_rec.stack_lexical_depth := utl_call_stack.lexical_depth(i);
lv_logger_rec.stack_current_edition := NVL(utl_call_stack.current_edition(i),' ');
lv_logger_rec.stack_concatenate_subprogram := utl_call_stack.concatenate_subprogram(utl_call_stack.subprogram(i);
lv_logger_rec.stack_message := 'Some Test Message';
);
INSERT_LOG_TST(lv_logger_rec);
END LOOP;
--DBMS_OUTPUT.put_line(lv_call_stack_rec.stack_message_overflow);
END;
/
I am stuck and not able to proceed further, need your directions and Pointers
Thanks