-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathoperation_log.pkb
More file actions
183 lines (133 loc) · 5.23 KB
/
operation_log.pkb
File metadata and controls
183 lines (133 loc) · 5.23 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
create or replace package body operation_log as
type num_t is table of number;
parent_ids num_t := num_t();
procedure log_insert( -- {
p_txt varchar2,
p_is_exception varchar2 :='N',
p_back_trace varchar2 := null,
p_clob clob := null
) is
pragma autonomous_transaction;
v_back_trace varchar2(4000);
v_caller call_stack.who_am_i_r := call_stack.who_am_i(2);
v_parent_id number;
begin
if parent_ids.count > 0 then
v_parent_id := parent_ids(parent_ids.count);
end if;
insert into operation_log_table values (operation_log_seq.nextval, sysdate, p_txt,
v_caller.type_,
substr(v_caller.name_, 1, 30), -- TODO 2016-11-22: This substr is necessarey because of »0X459AF82320 40 ANONYMOUS BLOCK«
v_caller.pkg_name,
v_caller.line,
v_caller.owner,
p_is_exception, v_parent_id, p_back_trace,
p_clob
);
commit;
end log_insert; -- }
procedure log_(txt varchar2, is_exception boolean := false, clob_ clob := null) is -- {
v_is_exception varchar2(1) := 'N';
v_back_trace varchar2(4000);
begin
if is_exception then
v_is_exception := 'Y';
v_back_trace := dbms_utility.format_error_backtrace;
end if;
log_insert(txt, p_is_exception => v_is_exception, p_back_trace => v_back_trace, p_clob => clob_);
end log_; -- }
procedure indent(txt varchar2) is -- {
begin
log_insert(txt);
parent_ids.extend;
parent_ids(parent_ids.count) := operation_log_seq.currval;
end indent; -- }
procedure dedent(txt varchar2 := null) is -- {
begin
if parent_ids.count > 0 then
parent_ids.trim;
else
log_insert('Warning: dedent called but parent_ids.count = 0');
end if;
if txt is not null then
log_insert(txt);
end if;
end dedent; -- }
procedure exc(txt varchar2 := null) is -- {
begin
if sqlcode = c_ex_num then
dedent(txt);
else
log_(sqlerrm, is_exception => true);
dedent(txt);
end if;
raise_application_error(c_ex_num, sqlerrm);
end exc; -- }
procedure print_id_recursively(p_id number, p_level number := 0, p_curly_braces boolean := false) is -- {
v_first boolean := true;
v_tm varchar2(21);
v_txt varchar2(4000);
v_caller_type operation_log_table.caller_type %type;
v_caller_name operation_log_table.caller_name %type;
v_caller_pkg_name operation_log_table.caller_pkg_name %type;
v_caller_line operation_log_table.caller_line %type;
v_caller_owner operation_log_table.caller_owner %type;
v_cnt_children number;
c_txt_width constant number := 120;
c_caller_width constant number := 150;
v_clob varchar(8);
begin
select to_char(tm, 'yyyy-mm-dd hh24:mi:ss'), txt, caller_type, caller_name, caller_pkg_name, caller_line, caller_owner,case when clob_ is not null then ' -clob- ' else ' ' end
into v_tm ,v_txt,v_caller_type,v_caller_name,v_caller_pkg_name,v_caller_line,v_caller_owner,v_clob
from operation_log_table
where id = p_id;
select count(*) into v_cnt_children from operation_log_table where id_parent = p_id;
dbms_output.put( substr(rpad(
lpad(' ', p_level * 2) ||
replace(
replace(
replace(v_txt, chr( 10), ' '),
chr(123), ' '), -- opening curly brace
chr(125), ' '), -- closing curly brace
c_caller_width),
1, c_txt_width
) || ' ' ||
&tq84_prefix.string_op.sprintf('%8s %s %-30s %-30s %4d %-30s',
v_clob ,
v_tm ,
v_caller_name ,
v_caller_pkg_name ,
v_caller_line ,
v_caller_owner
));
if p_curly_braces and v_cnt_children > 0 then
dbms_output.put_line(' ' || chr(123));
else
dbms_output.put_line('');
end if;
for r in (select id from operation_log_table where id_parent = p_id order by id) loop
print_id_recursively(r.id, p_level + 1, p_curly_braces => p_curly_braces);
end loop;
if p_curly_braces then
if v_cnt_children > 0 then
dbms_output.put_line(lpad(' ', (p_level) * 2) || chr(125));
end if;
end if;
end print_id_recursively; -- }
procedure find_last_root_ids(p_count number := 20) is -- {
begin
for r in (
select id, tm from (
select id, tm, row_number() over (order by id desc) r
from operation_log_table
where id_parent is null
order by id desc
)
where r <= p_count
) loop
dbms_output.put_line(to_char(r.id, '9999999') || ': ' || to_char(r.tm, 'dd.mm.yyyy hh24:mi:ss'));
end loop;
end find_last_root_ids; -- }
end operation_log;
/
show errors