/
logger.pkb
executable file
·1720 lines (1515 loc) · 51.6 KB
/
logger.pkb
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
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967
968
969
970
971
972
973
974
975
976
977
978
979
980
981
982
983
984
985
986
987
988
989
990
991
992
993
994
995
996
997
998
999
1000
create or replace package body logger
as
-- Note: The license is defined in the package specification of the logger package
--
-- _______________________________________________________________________________
--
--
-- Definitions of conditional compilation variables:
-- $$NO_OP : When true, completely disables all logger DML. Also used to
-- : generate the logger_no_op.sql code path
--
-- $$RAC_LT_11_2 : Set in logger_configure to handle the fact that RAC doesn't
-- : support global app contexts until 11.2
--
-- $$FLASHBACK_ENABLED : Set in logger_configure to determine whether or not we can grab the scn from dbms_flashback.
-- : Primarily used in the trigger on logger_logs.
--
-- $$APEX : Set in logger_configure. True if we can query a local synonym to wwv_flow_data to snapshot
-- : the APEX session state.
--
-- $$LOGGER_DEBUG : Only to be used during development of logger
-- : Primarily used for dbms_output.put_line calls
-- TYPES
type ts_array is table of timestamp index by varchar2(100);
-- VARIABLES
g_Log_Id Number;
g_proc_start_times ts_array;
g_running_timers pls_integer := 0;
-- CONSTANTS
gc_line_feed varchar2(1) := chr(10);
gc_date_format varchar2(255) := 'DD-MON-YYYY HH24:MI:SS';
gc_timestamp_format varchar2(255) := gc_date_format || ':FF';
gc_timestamp_tz_format varchar2(255) := gc_timestamp_format || ' TZR';
gc_ctx_attr_level varchar2(5) := 'level';
gc_ctx_attr_include_call_stack varchar2(18) := 'include_call_stack';
-- PRIVATE
/**
* Returns the display/print friendly parameter information
* Private
*
* @author Martin D'Souza
* @created 20-Jan-2013
*
* @param p_parms Array of parameters (can be null)
* @return Clob of param information
*/
function get_param_clob(p_params in logger.tab_param)
return clob
as
l_return clob;
l_no_vars constant varchar2(255) := 'No params defined';
begin
-- Generate line feed delimited list
if p_params.count > 0 then
for x in p_params.first..p_params.last loop
l_return := l_return || p_params(x).name || ': ' || p_params(x).val;
if x != p_params.last then
l_return := l_return || gc_line_feed;
end if;
end loop;
end if; -- p_params.count > 0
if l_return is null then
l_return := l_no_vars;
end if;
return l_return;
end get_param_clob;
/**
* Validates assertion. Will raise an application error if assertion is false
* Private
*
* @author Martin D'Souza
* @created 29-Mar-2013
*
* @param p_condition Boolean condition to validate
* @param p_message Message to include in application error if p_condition fails
*/
procedure assert(
p_condition in boolean,
p_message in varchar2)
as
begin
if not p_condition or p_condition is null then
raise_application_error(-20000, p_message);
end if;
end assert;
/**
* Sets the global context
*
* @author Tyler Muth
* @created ???
*
* @param p_attribute Attribute for context to set
* @param p_value Value
* @param p_client_id Optional client_id. If specified will only set the attribute/value for specific client_id (not global)
*/
procedure save_global_context(
p_attribute in varchar2,
p_value in varchar2,
p_client_id in varchar2 default null)
is
pragma autonomous_transaction;
begin
$IF $$NO_OP $THEN
null;
$ELSE
dbms_session.set_context(
namespace => g_context_name,
attribute => p_attribute,
value => p_value,
client_id => p_client_id);
$END
commit; -- MD: moved commit to outside of the NO_OP check since commit or rollback must occur in this procedure
end save_global_context;
/**
* Will return the extra column appended with the display friendly parameters
*
* @author Martin D'Souza
* @created 1-May-2013
*
* @param p_extra Current "Extra" field
* @param p_params Parameters. If null, then no changes to the Extra column
*/
function set_extra_with_params(
p_extra in logger_logs.extra%type,
p_params in tab_param
)
return logger_logs.extra%type
as
l_extra logger_logs.extra%type;
begin
$IF $$NO_OP $THEN
return null;
$ELSE
if p_params.count = 0 then
return p_extra;
else
l_extra := p_extra || gc_line_feed || gc_line_feed || '*** Parameters ***' || gc_line_feed || gc_line_feed || get_param_clob(p_params => p_params);
end if;
return l_extra;
$END
end set_extra_with_params;
-- PUBLIC
function admin_security_check
return boolean
is
l_protect_admin_procs varchar2(50) := get_pref('PROTECT_ADMIN_PROCS');
l_return boolean default false;
begin
if get_pref('PROTECT_ADMIN_PROCS') = 'TRUE' then
if get_pref('INSTALL_SCHEMA') = sys_context('USERENV','SESSION_USER') then
l_return := true;
else
l_return := false;
raise_application_error (-20000, 'You are not authorized to call this procedure.');
end if;
else
l_return := true;
end if;
return l_return;
end admin_security_check;
procedure null_global_contexts
is
pragma autonomous_transaction;
begin
$IF $$NO_OP $THEN
null;
$ELSE
$IF $$RAC_LT_11_2 $THEN
null;
$ELSE
dbms_session.set_context(
namespace => g_context_name,
attribute => gc_ctx_attr_level,
value => null);
dbms_session.set_context(
namespace => g_context_name,
attribute => gc_ctx_attr_include_call_stack,
value => null);
$END
$END
commit;
end null_global_contexts;
/**
* Converts string names to text value
*
* Changes
* - 2.1.0: Start to use global variables and correct numbers
*
* @author Tyler Muth
* @created ???
*
* @param p_level String representation of level
* @return level number. -1 if not found
*/
function convert_level_char_to_num(
p_level in varchar2)
return number
is
l_level number;
begin
case p_level
when 'OFF' then l_level := 0;
when 'PERMANENT' then l_level := g_permanent;
when 'ERROR' then l_level := g_error;
when 'WARNING' then l_level := g_warning;
when 'INFORMATION' then l_level := g_information;
when 'DEBUG' then l_level := g_debug;
when 'TIMING' then l_level := g_timing;
when 'SYS_CONTEXT' then l_level := g_sys_context;
when 'APEX' then l_level := g_apex;
else l_level := -1;
end case;
return l_level;
end convert_level_char_to_num;
function get_level_number
return number
$IF $$RAC_LT_11_2 $THEN
$IF not dbms_db_version.ver_le_10_2 $THEN
result_cache relies_on (logger_prefs, logger_prefs_by_client_id)
$END
$END
is
l_level number;
l_level_char varchar2(50);
l_scope varchar2(30) := 'get_level_number';
begin
$IF $$NO_OP $THEN
return 0;
$ELSE
$IF $$LOGGER_DEBUG $THEN
dbms_output.put_line(l_scope || ': select logger_level');
$END
-- If enabled then first try to get the levle from it. If not go to the original code below
select logger_level
into l_level_char
from (
select logger_level, row_number () over (order by rank) rn
from (
-- Client specific logger levels trump system level logger level
select logger_level, 1 rank
from logger_prefs_by_client_id
where client_id = sys_context('userenv','client_identifier')
union
-- System level configuration
select pref_value logger_level, 2 rank
from logger_prefs
where pref_name = 'LEVEL'
)
)
where rn = 1;
l_level := convert_level_char_to_num(l_level_char);
return l_level;
$END
end get_level_number;
/**
* Determines if the statement can be stored in LOGGER_LOGS
*
* @author Tyler Muth
* @created ???
*
* @param p_level Level (number)
* @return True of statement can be logged to LOGGER_LOGS
*/
function ok_to_log(p_level in number)
return boolean
$IF $$RAC_LT_11_2 $THEN
$IF not dbms_db_version.ver_le_10_2 $THEN
$IF $$NO_OP is null or NOT $$NO_OP $THEN
result_cache relies_on (logger_prefs, logger_prefs_by_client_id)
$END
$END
$END
is
l_level number;
l_level_char varchar2(50);
begin
$IF $$NO_OP $THEN
return false;
$ELSE
$IF $$RAC_LT_11_2 $THEN
l_level := get_level_number;
$ELSE
l_level := sys_context(g_context_name,gc_ctx_attr_level);
if l_level is null then
l_level := get_level_number;
save_global_context(gc_ctx_attr_level,l_level);
end if;
$END
if l_level >= p_level then
return true;
else
return false;
end if;
$END
end ok_to_log;
function include_call_stack
return boolean
$IF $$RAC_LT_11_2 $THEN
$IF not dbms_db_version.ver_le_10_2 $THEN
$IF $$NO_OP is null or NOT $$NO_OP $THEN
result_cache relies_on (logger_prefs, logger_prefs_by_client_id)
$END
$END
$END
is
l_call_stack_pref varchar2(50);
begin
$IF $$NO_OP $THEN
return false;
$ELSE
$IF $$RAC_LT_11_2 $THEN
l_call_stack_pref := get_pref('INCLUDE_CALL_STACK');
$ELSE
l_call_stack_pref := sys_context(g_context_name,gc_ctx_attr_include_call_stack);
if l_call_stack_pref is null then
l_call_stack_pref := get_pref('INCLUDE_CALL_STACK');
save_global_context(gc_ctx_attr_include_call_stack,l_call_stack_pref);
end if;
$END
if l_call_stack_pref = 'TRUE' then
return true;
else
return false;
end if;
$END
end include_call_stack;
function date_text_format_base (
p_date_start in date,
p_date_stop in date)
return varchar2
as
x varchar2(20);
begin
x :=
case
when p_date_stop-p_date_start < 1/1440
then round(24*60*60*(p_date_stop-p_date_start)) || ' seconds'
when p_date_stop-p_date_start < 1/24
then round(24*60*(p_date_stop-p_date_start)) || ' minutes'
when p_date_stop-p_date_start < 1
then round(24*(p_date_stop-p_date_start)) || ' hours'
when p_date_stop-p_date_start < 14
then trunc(p_date_stop-p_date_start) || ' days'
when p_date_stop-p_date_start < 60
then trunc((p_date_stop-p_date_start)/7) || ' weeks'
when p_date_stop-p_date_start < 365
then round(months_between(p_date_stop,p_date_start)) || ' months'
else round(months_between(p_date_stop,p_date_start)/12,1) || ' years'
end;
x:= regexp_replace(x,'(^1 [[:alnum:]]{4,10})s','\1');
x:= x || ' ago';
return substr(x,1,20);
end date_text_format_base;
function date_text_format (p_date in date)
return varchar2
as
begin
return date_text_format_base(
p_date_start => p_date ,
p_date_stop => sysdate);
end date_text_format;
function get_character_codes(
p_string in varchar2,
p_show_common_codes in boolean default true)
return varchar2
is
l_string varchar2(32767);
l_dump varchar2(32767);
l_return varchar2(32767);
begin
-- replace tabs with ^
l_string := replace(p_string,chr(9),'^');
-- replace all other control characters such as carriage return / line feeds with ~
l_string := regexp_replace(l_string,'[[:cntrl:]]','~',1,0,'m');
select dump(p_string) into l_dump from dual;
l_dump := regexp_replace(l_dump,'(^.+?:)(.*)','\2',1,0); -- get everything after the :
l_dump := ','||l_dump||','; -- leading and trailing commas
l_dump := replace(l_dump,',',',,'); -- double the commas. this is for the regex.
l_dump := regexp_replace(l_dump,'(,)([[:digit:]]{1})(,)','\1 \2\3',1,0); -- lpad all single digit numbers out to 3
l_dump := regexp_replace(l_dump,'(,)([[:digit:]]{2})(,)','\1 \2\3',1,0); -- lpad all double digit numbers out to 3
l_dump := ltrim(replace(l_dump,',,',','),','); -- remove the double commas
l_dump := lpad(' ',(5-instr(l_dump,',')),' ')||l_dump;
-- replace every individual character with 2 spaces, itself and a comma so it lines up with the dump output
l_string := ' '||regexp_replace(l_string,'(.){1}',' \1,',1,0);
l_return := rtrim(l_dump,',') || chr(10) || rtrim(l_string,',');
if p_show_common_codes then
l_return := 'Common Codes: 13=Line Feed, 10=Carriage Return, 32=Space, 9=Tab'||chr(10) ||l_return;
end if;
return l_return;
end get_character_codes;
procedure get_debug_info(
p_callstack in clob,
o_unit out varchar2,
o_lineno out varchar2 )
as
--
l_callstack varchar2(3000) := p_callstack;
begin
l_callstack := substr( l_callstack, instr( l_callstack, chr(10), 1, 5 )+1 );
l_callstack := substr( l_callstack, 1, instr( l_callstack, chr(10), 1, 1 )-1 );
l_callstack := trim( substr( l_callstack, instr( l_callstack, ' ' ) ) );
o_lineno := substr( l_callstack, 1, instr( l_callstack, ' ' )-1 );
o_unit := trim(substr( l_callstack, instr( l_callstack, ' ', -1, 1 ) ));
end get_debug_info;
/**
* Main procedure that will store log data into logger_logs table
*
* @author Tyler Muth
* @created ???
*
* Modifications
* - 2.1.0: If text is > 4000 characters, it will be moved to the EXTRA column
*
* @param p_text
* @param p_log_level
* @param p_scope
* @param p_extra
* @param p_callstack
* @param p_params
*
*/
procedure log_internal(
p_text in varchar2,
p_log_level in number,
p_scope in varchar2,
p_extra in clob default null,
p_callstack in varchar2 default null,
p_params in tab_param default logger.gc_empty_tab_param)
is
l_proc_name varchar2(100);
l_lineno varchar2(100);
l_text varchar2(32767);
l_callstack varchar2(3000);
l_extra logger_logs.extra%type;
l_id logger_logs.id%type;
begin
$IF $$NO_OP $THEN
null;
$ELSE
l_text := p_text;
if p_callstack is not null and include_call_stack then
get_debug_info(
p_callstack => p_callstack,
o_unit => l_proc_name,
o_lineno => l_lineno);
l_callstack := regexp_replace(p_callstack,'^.*$','',1,4,'m');
l_callstack := regexp_replace(l_callstack,'^.*$','',1,1,'m');
l_callstack := ltrim(replace(l_callstack,chr(10)||chr(10),chr(10)),chr(10));
end if;
l_extra := set_extra_with_params(p_extra => p_extra, p_params => p_params);
ins_logger_logs(
p_unit_name => upper(l_proc_name) ,
p_scope => p_scope ,
p_logger_level =>p_log_level,
p_extra => l_extra,
p_text =>l_text,
p_call_stack =>l_callstack,
p_line_no => l_lineno,
po_id => l_id);
-- commit;
$END
end log_internal;
procedure snapshot_apex_items(
p_log_id in number)
is
l_app_session number;
l_app_id number;
begin
$IF $$NO_OP $THEN
null;
$ELSE
$IF $$APEX $THEN
l_app_session := v('APP_SESSION');
l_app_id := v('APP_ID');
for c1 in (
select item_name
from apex_application_items
where application_id = l_app_id)
loop
insert into logger_logs_apex_items(log_id,app_session,item_name,item_value)
values (p_log_id,l_app_session,c1.item_name,v(c1.item_name));
end loop; --c1
for c1 in (
select item_name
from apex_application_page_items
where application_id = l_app_id)
loop
insert into logger_logs_apex_items(log_id,app_session,item_name,item_value)
values (p_log_id,l_app_session,c1.item_name,v(c1.item_name));
end loop; --c1
$END
null;
$END
end snapshot_apex_items;
procedure log_error(
p_text in varchar2 default null,
p_scope in varchar2 default null,
p_extra in clob default null,
p_params in tab_param default logger.gc_empty_tab_param)
is
l_proc_name varchar2(100);
l_lineno varchar2(100);
l_text varchar2(4000);
l_call_stack varchar2(4000);
l_extra clob;
begin
$IF $$NO_OP $THEN
null;
$ELSE
if ok_to_log(logger.g_error) then
-- get_debug_info( l_proc_name, l_lineno );
get_debug_info(
p_callstack => dbms_utility.format_call_stack,
o_unit => l_proc_name,
o_lineno => l_lineno);
l_call_stack := dbms_utility.format_error_stack() ||chr(10)||dbms_utility.format_error_backtrace;
if p_text is not null then
l_text := p_text ||' '|| chr(10)||chr(10);
end if;
l_text := l_text || dbms_utility.format_error_stack();
l_extra := set_extra_with_params(p_extra => p_extra, p_params => p_params);
ins_logger_logs(
p_unit_name => upper(l_proc_name) ,
p_scope => p_scope ,
p_logger_level =>logger.g_error,
p_extra => l_extra,
p_text =>l_text,
p_call_stack =>l_call_stack,
p_line_no => l_lineno,
po_id => g_log_id);
end if;
$END
end log_error;
procedure log_permanent(
p_text in varchar2,
p_scope in varchar2 default null,
p_extra in clob default null,
p_params in tab_param default logger.gc_empty_tab_param)
is
begin
$IF $$NO_OP $THEN
null;
$ELSE
if ok_to_log(logger.g_permanent) then
log_internal(
p_text => p_text,
p_log_level => logger.g_permanent,
p_scope => p_scope,
p_extra => p_extra,
p_callstack => dbms_utility.format_call_stack,
p_params => p_params
);
end if;
$END
end log_permanent;
procedure log_warning(
p_text in varchar2,
p_scope in varchar2 default null,
p_extra in clob default null,
p_params in tab_param default logger.gc_empty_tab_param)
is
begin
$IF $$NO_OP $THEN
null;
$ELSE
if ok_to_log(logger.g_warning) then
log_internal(
p_text => p_text,
p_log_level => logger.g_warning,
p_scope => p_scope,
p_extra => p_extra,
p_callstack => dbms_utility.format_call_stack,
p_params => p_params);
end if;
$END
end log_warning;
procedure log_information(
p_text in varchar2,
p_scope in varchar2 default null,
p_extra in clob default null,
p_params in tab_param default logger.gc_empty_tab_param)
is
begin
$IF $$NO_OP $THEN
null;
$ELSE
if ok_to_log(logger.g_information) then
log_internal(
p_text => p_text,
p_log_level => logger.g_information,
p_scope => p_scope,
p_extra => p_extra,
p_callstack => dbms_utility.format_call_stack,
p_params => p_params);
end if;
$END
end log_information;
procedure log(
p_text in varchar2,
p_scope in varchar2 default null,
p_extra in clob default null,
p_params in tab_param default logger.gc_empty_tab_param)
is
begin
$IF $$NO_OP $THEN
null;
$ELSE
if ok_to_log(logger.g_debug) then
log_internal(
p_text => p_text,
p_log_level => logger.g_debug,
p_scope => p_scope,
p_extra => p_extra,
p_callstack => dbms_utility.format_call_stack,
p_params => p_params);
end if;
$END
end log;
function get_sys_context(
p_detail_level in varchar2 default 'USER', -- ALL, NLS, USER, INSTANCE
p_vertical in boolean default false,
p_show_null in boolean default false) -- vertical name value pairs or comma sep list.
return clob
is
l_ctx clob;
l_detail_level varchar2(20) := upper(p_detail_level);
procedure append_ctx(p_name in varchar2)
is
r_pad number := 30;
l_crlf varchar2(10) := chr(13)||chr(10);
invalid_userenv_parm exception;
pragma exception_init(invalid_userenv_parm, -2003);
begin
if p_show_null or sys_context('USERENV',p_name) is not null then
if p_vertical then
l_ctx := l_ctx || rpad(p_name,r_pad,' ')||': '||sys_context('USERENV',p_name)||l_crlf;
else
l_ctx := l_ctx || p_name||': '||sys_context('USERENV',p_name)||', ';
end if;
end if;
exception
when invalid_userenv_parm then
--log_warning('Invalid SYS_CONTEXT Parameter: '||p_name);
null;
end append_ctx;
begin
if l_detail_level in ('ALL','NLS','INSTANCE') then
append_ctx('NLS_CALENDAR');
append_ctx('NLS_CURRENCY');
append_ctx('NLS_DATE_FORMAT');
append_ctx('NLS_DATE_LANGUAGE');
append_ctx('NLS_SORT');
append_ctx('NLS_TERRITORY');
append_ctx('LANG');
append_ctx('LANGUAGE');
end if;
if l_detail_level in ('ALL','USER') then
append_ctx('CURRENT_SCHEMA');
append_ctx('SESSION_USER');
append_ctx('OS_USER');
append_ctx('CLIENT_IDENTIFIER');
append_ctx('CLIENT_INFO');
append_ctx('IP_ADDRESS');
append_ctx('HOST');
append_ctx('TERMINAL');
end if;
if l_detail_level in ('ALL','USER') then
append_ctx('AUTHENTICATED_IDENTITY');
append_ctx('AUTHENTICATION_DATA');
append_ctx('AUTHENTICATION_METHOD');
append_ctx('ENTERPRISE_IDENTITY');
append_ctx('POLICY_INVOKER');
append_ctx('PROXY_ENTERPRISE_IDENTITY');
append_ctx('PROXY_GLOBAL_UID');
append_ctx('PROXY_USER');
append_ctx('PROXY_USERID');
append_ctx('IDENTIFICATION_TYPE');
append_ctx('ISDBA');
end if;
if l_detail_level in ('ALL','INSTANCE') then
append_ctx('DB_DOMAIN');
append_ctx('DB_NAME');
append_ctx('DB_UNIQUE_NAME');
append_ctx('INSTANCE');
append_ctx('INSTANCE_NAME');
append_ctx('SERVER_HOST');
append_ctx('SERVICE_NAME');
end if;
if l_detail_level in ('ALL') then
append_ctx('ACTION');
append_ctx('AUDITED_CURSORID');
append_ctx('BG_JOB_ID');
append_ctx('CURRENT_BIND');
append_ctx('CURRENT_SCHEMAID');
append_ctx('CURRENT_SQL');
append_ctx('CURRENT_SQLn');
append_ctx('CURRENT_SQL_LENGTH');
append_ctx('ENTRYID');
append_ctx('FG_JOB_ID');
append_ctx('GLOBAL_CONTEXT_MEMORY');
append_ctx('GLOBAL_UID');
append_ctx('MODULE');
append_ctx('NETWORK_PROTOCOL');
append_ctx('SESSION_USERID');
append_ctx('SESSIONID');
append_ctx('SID');
append_ctx('STATEMENTID');
end if;
return rtrim(l_ctx,', ');
end get_sys_context;
function get_cgi_env(
p_show_null in boolean default false)
return clob
is
l_cgienv clob;
procedure append_cgi_env(
p_name in varchar2,
p_val in varchar2)
is
r_pad number := 30;
l_crlf varchar2(10) := chr(13)||chr(10);
--invalid_userenv_parm exception;
--pragma exception_init(invalid_userenv_parm, -2003);
begin
if p_show_null or p_val is not null then
l_cgienv := l_cgienv || rpad(p_name,r_pad,' ')||': '||p_val||l_crlf;
end if;
--exception when invalid_userenv_parm then
--log_warning('Invalid SYS_CONTEXT Parameter: '||p_name);
null;
end append_cgi_env;
begin
$IF $$NO_OP $THEN
return null;
$ELSE
for i in 1..owa.num_cgi_vars loop
append_cgi_env(
p_name => owa.cgi_var_name(i),
p_val => owa.cgi_var_val(i));
end loop;
return l_cgienv;
$END
end get_cgi_env;
procedure log_userenv(
p_detail_level in varchar2 default 'USER',-- ALL, NLS, USER, INSTANCE,
p_show_null in boolean default false,
p_scope in varchar2 default null)
is
l_extra clob;
begin
$IF $$NO_OP $THEN
null;
$ELSE
if ok_to_log(logger.g_debug) then
l_extra := get_sys_context(
p_detail_level => p_detail_level,
p_vertical => true,
p_show_null => p_show_null);
log_internal(
p_text => 'USERENV values stored in the EXTRA column',
p_log_level => logger.g_sys_context,
p_scope => p_scope,
p_extra => l_extra);
end if;
$END
end log_userenv;
procedure log_cgi_env(
p_show_null in boolean default false,
p_scope in varchar2 default null)
is
l_extra clob;
begin
$IF $$NO_OP $THEN
null;
$ELSE
if ok_to_log(logger.g_debug) then
l_extra := get_cgi_env(p_show_null => p_show_null);
log_internal(
p_text => 'CGI ENV values stored in the EXTRA column',
p_log_level => logger.g_sys_context,
p_scope => p_scope,
p_extra => l_extra);
end if;
$END
end log_cgi_env;
procedure log_character_codes(
p_text in varchar2,
p_scope in varchar2 default null,
p_show_common_codes in boolean default true)
is
l_error varchar2(4000);
l_dump clob;
begin
$IF $$NO_OP $THEN
null;
$ELSE
if ok_to_log(logger.g_debug) then
l_dump := get_character_codes(p_text,p_show_common_codes);
log_internal(
p_text => 'GET_CHARACTER_CODES output stored in the EXTRA column',
p_log_level => logger.g_debug,
p_scope => p_scope,
p_extra => l_dump);
end if;
$END
end log_character_codes;
procedure log_apex_items(
p_text in varchar2 default 'Log APEX Items',
p_scope in varchar2 default null)
is
l_error varchar2(4000);
pragma autonomous_transaction;
begin
$IF $$NO_OP $THEN
null;
$ELSE
if ok_to_log(logger.g_debug) then
$IF $$APEX $THEN
log_internal(
p_text => p_text,
p_log_level => logger.g_apex,
p_scope => p_scope);
snapshot_apex_items(p_log_id => g_log_id);
$ELSE
l_error := 'Error! Logger is not configured for APEX yet. '||
'Please check the CONFIGURATION section at https://logger.samplecode.oracle.com ';
log_internal(
p_text => l_error,
p_log_level => logger.g_apex,
p_scope => p_scope);
$END
end if;
$END
commit;
end log_apex_items;
PROCEDURE time_start(
p_unit IN VARCHAR2,
p_log_in_table IN boolean default true)
is
l_proc_name varchar2(100);
l_text varchar2(4000);
l_pad varchar2(100);
begin
$IF $$NO_OP $THEN
null;
$ELSE
if ok_to_log(logger.g_debug) then
g_running_timers := g_running_timers + 1;
if g_running_timers > 1 then
l_pad := lpad(' ',g_running_timers,'>')||' ';
end if;
g_proc_start_times(p_unit) := systimestamp;
l_text := l_pad||'START: '||p_unit;
if p_log_in_table then
ins_logger_logs(
p_unit_name => p_unit ,
p_logger_level => g_timing,
p_text =>l_text,
po_id => g_log_id);
end if;
end if;
$END
end time_start;
procedure time_stop(
p_unit IN VARCHAR2,
p_scope in varchar2 default null)
is
l_time_string varchar2(50);
l_text varchar2(4000);
l_pad varchar2(100);
begin
$IF $$NO_OP $THEN
null;
$ELSE
if ok_to_log(logger.g_debug) then
if g_proc_start_times.exists(p_unit) then
if g_running_timers > 1 then
l_pad := lpad(' ',g_running_timers,'>')||' ';
end if;
--l_time_string := rtrim(regexp_replace(systimestamp-(g_proc_start_times(p_unit)),'.+?[[:space:]](.*)','\1',1,0),0);
l_time_string := time_stop(
p_unit => p_unit,
p_log_in_table => false);