· 6 years ago · Nov 01, 2019, 04:18 PM
1 plog.debug / plog.d / plog.dbg
2 plog.info / plog.i / plog.inf
3 plog.warning/ plog.w / plog.wrn
4 plog.error / plog.e / plog.err
5
6---
7
8create table melog_data (
9 log_id number not null,
10 session_id number not null,
11 timestamp_insert timestamp not null,
12 timestamp_insert_utc timestamp generated always as (
13 sys_extract_utc(timestamp_insert)
14 ) virtual,
15 modul varchar2(255 char) not null,
16 modul_detail varchar2(255 char),
17 log_level number(1, 0) not null,
18 log_level_text varchar2(5 char) generated always as (
19 case log_level
20 when 1 then 'Fatal'
21 when 2 then 'Error'
22 when 3 then 'Info'
23 when 4 then 'Debug'
24 end
25 ) virtual,
26 text varchar2(4000 char) not null,
27 text_detail clob,
28 schema_name varchar2(30 char) not null,
29 objekt_typ varchar2(30 char) not null,
30 objekt_name varchar2(30 char) not null,
31 sub_objekt_typ varchar2(30 char),
32 sub_objekt_name varchar2(30 char),
33 username varchar2(30 char) not null,
34 anzahl_zeilen number(12, 0),
35 constraint pk_melog_data_log_id primary key ( log_id )
36)
37partition by range (timestamp_insert)
38interval(numtoyminterval(1, 'month'))
39(
40 partition melog_data_p_init values less than (to_date('01012019', 'ddmmyyyy'))
41);
42--------------------------------------------------------------------------------
43comment on table melog_data is
44 '..';
45comment on column melog_data.anzahl_zeilen is
46 '..';
47--------------------------------------------------------------------------------
48create bitmap index idx_melog_data_modul on melog_data (modul) local;
49create bitmap index idx_melog_data_log_level on melog_data (log_level) local;
50create bitmap index idx_melog_data_schema_name on melog_data (schema_name) local;
51create bitmap index idx_melog_data_username on melog_data (username) local;
52create index idx_melog_data_session_id on melog_data (session_id) local;
53create index idx_melog_data_timestamp_ins on melog_data (timestamp_insert) local;
54
55
56create or replace package melog authid definer
57-- API zum Protokollieren von Nachrichten jeglicher Art in die allgemein in
58-- MERDW zugängliche Tabelle MELOG_DATA.
59is
60
61 -- Schreibt einen Datensatz mit LOG_LEVEL=1 in die Tabelle MELOG_DATA.
62 procedure fatal (
63 i_text in melog_data.text%type,
64 i_modul in melog_data.modul%type,
65 i_schema_name in melog_data.schema_name%type,
66 i_objekt_typ in melog_data.objekt_typ%type,
67 i_objekt_name in melog_data.objekt_name%type,
68 i_sub_objekt_typ in melog_data.sub_objekt_typ%type default null,
69 i_sub_objekt_name in melog_data.sub_objekt_name%type default null
70 );
71
72 -- Schreibt einen Datensatz mit LOG_LEVEL=1 in die Tabelle MELOG_DATA.
73 procedure fatal (
74 i_melog_data in melog_data%rowtype,
75 i_text in melog_data.text%type,
76 i_anzahl_zeilen in melog_data.anzahl_zeilen%type default null
77 );
78
79 -- Schreibt einen Datensatz mit LOG_LEVEL=2 in die Tabelle MELOG_DATA.
80 procedure error (
81 i_text in melog_data.text%type,
82 i_modul in melog_data.modul%type,
83 i_schema_name in melog_data.schema_name%type,
84 i_objekt_typ in melog_data.objekt_typ%type,
85 i_objekt_name in melog_data.objekt_name%type,
86 i_sub_objekt_typ in melog_data.sub_objekt_typ%type default null,
87 i_sub_objekt_name in melog_data.sub_objekt_name%type default null
88 );
89
90 -- Schreibt einen Datensatz mit LOG_LEVEL=2 in die Tabelle MELOG_DATA.
91 procedure error (
92 i_melog_data in melog_data%rowtype,
93 i_text in melog_data.text%type,
94 i_anzahl_zeilen in melog_data.anzahl_zeilen%type default null
95 );
96
97 -- Schreibt einen Datensatz mit LOG_LEVEL=3 in die Tabelle MELOG_DATA.
98 procedure info (
99 i_text in melog_data.text%type,
100 i_modul in melog_data.modul%type,
101 i_schema_name in melog_data.schema_name%type,
102 i_objekt_typ in melog_data.objekt_typ%type,
103 i_objekt_name in melog_data.objekt_name%type,
104 i_sub_objekt_typ in melog_data.sub_objekt_typ%type default null,
105 i_sub_objekt_name in melog_data.sub_objekt_name%type default null
106 );
107
108 -- Schreibt einen Datensatz mit LOG_LEVEL=3 in die Tabelle MELOG_DATA.
109 procedure info (
110 i_melog_data in melog_data%rowtype,
111 i_text in melog_data.text%type,
112 i_anzahl_zeilen in melog_data.anzahl_zeilen%type default null
113 );
114
115 -- Schreibt einen Datensatz mit LOG_LEVEL=4 in die Tabelle MELOG_DATA.
116 procedure debug (
117 i_text in melog_data.text%type,
118 i_modul in melog_data.modul%type,
119 i_schema_name in melog_data.schema_name%type,
120 i_objekt_typ in melog_data.objekt_typ%type,
121 i_objekt_name in melog_data.objekt_name%type,
122 i_sub_objekt_typ in melog_data.sub_objekt_typ%type default null,
123 i_sub_objekt_name in melog_data.sub_objekt_name%type default null
124 );
125
126 -- Schreibt einen Datensatz mit LOG_LEVEL=4 in die Tabelle MELOG_DATA.
127 procedure debug (
128 i_melog_data in melog_data%rowtype,
129 i_text in melog_data.text%type,
130 i_anzahl_zeilen in melog_data.anzahl_zeilen%type default null
131 );
132
133 -- Schreibt einen Datensatz mit LOG_LEVEL=4 in die Tabelle MELOG_DATA.
134 procedure debug (
135 i_text in melog_data.text%type
136 );
137
138 -- Schreibt einen Datensatz mit LOG_LEVEL=3 (=LOG_LEVEL_TEXT "Info") und
139 -- Standardtext "BEGINN" in die Tabelle MELOG_DATA.
140 procedure beginn (
141 i_modul in melog_data.modul%type,
142 i_schema_name in melog_data.schema_name%type,
143 i_objekt_typ in melog_data.objekt_typ%type,
144 i_objekt_name in melog_data.objekt_name%type,
145 i_sub_objekt_typ in melog_data.sub_objekt_typ%type default null,
146 i_sub_objekt_name in melog_data.sub_objekt_name%type default null
147 );
148
149 -- Schreibt einen Datensatz mit LOG_LEVEL 3 (=LOG_LEVEL_TEXT "Info") und
150 -- Standardtext "ENDE" in die Tabelle MELOG_DATA.
151 procedure ende (
152 i_modul in melog_data.modul%type,
153 i_schema_name in melog_data.schema_name%type,
154 i_objekt_typ in melog_data.objekt_typ%type,
155 i_objekt_name in melog_data.objekt_name%type,
156 i_sub_objekt_typ in melog_data.sub_objekt_typ%type default null,
157 i_sub_objekt_name in melog_data.sub_objekt_name%type default null
158 );
159
160end melog;
161/
162
163
164create or replace package body melog as
165--------------------------------------------------------------------------------
166--------------------------------------------------------------------------------
167-- funktionen um package stateless (zur alternative konstanten) zu halten...
168--------------------------------------------------------------------------------
169 function get_fatal_log_level return simple_integer deterministic
170 is
171 c_fatal_log_level constant simple_integer := 1;
172 begin
173 return c_fatal_log_level;
174 end get_fatal_log_level;
175--------------------------------------------------------------------------------
176 function get_error_log_level return simple_integer deterministic
177 is
178 c_error_log_level constant simple_integer := 2;
179 begin
180 return c_error_log_level;
181 end get_error_log_level;
182--------------------------------------------------------------------------------
183 function get_info_log_level return simple_integer deterministic
184 is
185 c_info_log_level constant simple_integer := 3;
186 begin
187 return c_info_log_level;
188 end get_info_log_level;
189--------------------------------------------------------------------------------
190 function get_debug_log_level return simple_integer deterministic
191 is
192 c_debug_log_level constant simple_integer := 4;
193 begin
194 return c_debug_log_level;
195 end get_debug_log_level;
196--------------------------------------------------------------------------------
197--------------------------------------------------------------------------------
198 function get_melog_info_link (
199 i_session_id in number,
200 i_modul in varchar2
201 ) return varchar2 deterministic
202 is
203-- c_ir_static_id constant utl.vc2_m := '36231';
204 c_apex_url_rdw_p constant utl.vc2_m := 'http://lvasp-apex1.metzler.com:8080/ords/';
205 c_apex_url_rdw_c constant utl.vc2_m := 'http://lvast-apex2.metzler.com:8802/ords/';
206 c_apex_url_rdw_f constant utl.vc2_m := 'http://lvast-apex2.metzler.com:7777/ords/';
207 c_env constant utl.vc2_xs not null := upper(sys_context('me_ctx', 'alias_short') );
208 l_session_id integer := i_session_id;
209 l_modul melog_data.modul%type not null := i_modul;
210 begin
211 -- in plaintext mails werden leerzeichen als ende eines links interpretiert
212 l_modul:=replace(l_modul,' ','+');
213 return
214 case c_env
215 when 'P' then c_apex_url_rdw_p
216 when 'C' then c_apex_url_rdw_c
217 when 'F' then c_apex_url_rdw_f
218 else c_apex_url_rdw_f
219 end
220 ||'f?p=362:31:'||chr(38)||'APP_SESSION.::::'
221 --||'f?p=362:31:'||v('APP_SESSION')||'::::'
222 ||'IR_SESSION_ID,IRC_MODUL:'
223 -- scheint auch ohne static id gehen, vermutlich weil nur 1 IR auf seite!?
224 --||'IR['||c_ir_static_id||']_SESSION_ID,IR['||c_ir_static_id||']C_MODUL:'
225 ||l_session_id||','||l_modul;
226 end get_melog_info_link;
227-------------------------------------------------------------------------------
228 procedure set_not_null_values (
229 i_melog_data in out nocopy melog_data%rowtype
230 ) is
231 c_app_user constant string(30 char) := 'APP_USER';
232 c_sessionid constant string(30 char) := 'sessionid';
233 c_me_ctx constant string(30 char) := 'me_ctx';
234 c_os_user constant string(30 char) := 'os_user';
235 c_userenv constant string(30 char) := 'userenv';
236 begin
237 i_melog_data.log_id := coalesce(i_melog_data.log_id,melog_data_seq.nextval);
238 i_melog_data.session_id := coalesce(i_melog_data.session_id,
239 userenv(c_sessionid));
240 i_melog_data.timestamp_insert := systimestamp;
241 i_melog_data.username := coalesce(i_melog_data.username,
242$if env.is_rdw or env.is_mifg $then
243 v(c_app_user), -- apex
244$end
245 sys_context(c_me_ctx,c_os_user),-- metzler
246 sys_context(c_userenv,c_os_user)-- oracle
247 );
248 end set_not_null_values;
249--------------------------------------------------------------------------------
250 procedure insert_row (
251 i_melog in melog_data%rowtype
252 ) is
253 pragma autonomous_transaction;
254 l_objekt_typ melog_data.objekt_typ%type;
255 l_sub_objekt_typ melog_data.sub_objekt_typ%type;
256 begin
257 -- "shortcuts"
258 l_objekt_typ:=case upper(i_melog.objekt_typ)
259 when '$P' then 'PACKAGE'
260 when '$T' then 'TYPE'
261 when '$F' then 'FUNCTION'
262 when '$A' then 'ANONYMOUS BLOCK'
263 else upper(i_melog.objekt_typ)
264 end;
265 l_sub_objekt_typ:=case upper(i_melog.objekt_typ)
266 when '$P' then 'PROCEDURE'
267 when '$F' then 'FUNCTION'
268 else upper(i_melog.sub_objekt_typ)
269 end;
270
271
272 insert into melog_data (
273 log_id,
274 session_id,
275 timestamp_insert,
276 modul,
277 modul_detail,
278 log_level,
279 log_level_text,
280 text,
281 text_detail,
282 schema_name,
283 objekt_typ,
284 objekt_name,
285 sub_objekt_typ,
286 sub_objekt_name,
287 username,
288 anzahl_zeilen
289 ) values (
290 i_melog.log_id,
291 i_melog.session_id,
292 i_melog.timestamp_insert,
293 i_melog.modul,
294 i_melog.modul_detail,
295 i_melog.log_level,default,
296 i_melog.text,
297 i_melog.text_detail,
298 upper(i_melog.schema_name),
299 l_objekt_typ,
300 upper(i_melog.objekt_name),
301 l_sub_objekt_typ,
302 upper(i_melog.sub_objekt_name),
303 upper(i_melog.username),
304 i_melog.anzahl_zeilen
305 );
306 commit;
307 if i_melog.log_level = get_fatal_log_level and not env.is_test
308 then
309 mail.send (
310 i_from => '$MEGIT-AMDR@metzler.com',
311 i_to => '$MEGIT-AMDR@metzler.com',
312 i_subject => '-- Schwerer (FATAL) Fehler -- (MELOG)',
313 i_message => 'Es ist ein schwerer Fehler aufgetreten. '||
314 'Die LOG_ID lautet: '||i_melog.log_id||chr(10)||chr(10)
315$if env.is_rdw $then
316 ||'Bitte umgehend analysieren:'||chr(10)||chr(10)
317 ||'<'||
318 get_melog_info_link(i_melog.session_id, i_melog.modul)
319 ||'>'
320$end
321 ,
322 i_cc => null,
323 i_name => 'DWH-FATAL-LOGERROR',
324 i_plain_text_mode => true
325 );
326 end if;
327 end insert_row;
328--------------------------------------------------------------------------------
329--------------------------------------------------------------------------------
330 procedure fatal (
331 i_text in melog_data.text%type,
332 i_modul in melog_data.modul%type,
333 i_schema_name in melog_data.schema_name%type,
334 i_objekt_typ in melog_data.objekt_typ%type,
335 i_objekt_name in melog_data.objekt_name%type,
336 i_sub_objekt_typ in melog_data.sub_objekt_typ%type,
337 i_sub_objekt_name in melog_data.sub_objekt_name%type
338 ) is
339 l_text melog_data.text%type not null := i_text;
340 l_modul melog_data.modul%type not null := i_modul;
341 l_schema_name melog_data.schema_name%type not null := i_schema_name;
342 l_objekt_typ melog_data.objekt_typ%type not null := i_objekt_typ;
343 l_objekt_name melog_data.objekt_name%type not null := i_objekt_name;
344 l_sub_objekt_typ melog_data.sub_objekt_typ%type null := i_sub_objekt_typ;
345 l_sub_objekt_name melog_data.sub_objekt_name%type null := i_sub_objekt_name;
346 l_melog melog_data%rowtype;
347 begin
348 l_melog.log_level := get_fatal_log_level;
349 l_melog.text := l_text;
350 l_melog.modul := l_modul;
351 l_melog.schema_name := l_schema_name;
352 l_melog.objekt_typ := l_objekt_typ;
353 l_melog.objekt_name := l_objekt_name;
354 l_melog.sub_objekt_typ := l_sub_objekt_typ;
355 l_melog.sub_objekt_name := l_sub_objekt_name;
356 set_not_null_values(l_melog);
357 insert_row(l_melog);
358 end fatal;
359--------------------------------------------------------------------------------
360 procedure fatal (
361 i_melog_data in melog_data%rowtype,
362 i_text in melog_data.text%type,
363 i_anzahl_zeilen in melog_data.anzahl_zeilen%type default null
364 ) is
365 l_melog_data melog_data%rowtype := i_melog_data;
366 l_text melog_data.text%type not null := i_text;
367 l_anzahl_zeilen melog_data.anzahl_zeilen%type := i_anzahl_zeilen;
368 begin
369 l_melog_data.log_level := get_fatal_log_level;
370 l_melog_data.text := l_text;
371 l_melog_data.anzahl_zeilen := l_anzahl_zeilen;
372 set_not_null_values(l_melog_data);
373 insert_row(l_melog_data);
374 end fatal;
375--------------------------------------------------------------------------------
376 procedure error (
377 i_text in melog_data.text%type,
378 i_modul in melog_data.modul%type,
379 i_schema_name in melog_data.schema_name%type,
380 i_objekt_typ in melog_data.objekt_typ%type,
381 i_objekt_name in melog_data.objekt_name%type,
382 i_sub_objekt_typ in melog_data.sub_objekt_typ%type,
383 i_sub_objekt_name in melog_data.sub_objekt_name%type
384 ) is
385 l_text melog_data.text%type not null := i_text;
386 l_modul melog_data.modul%type not null := i_modul;
387 l_schema_name melog_data.schema_name%type not null := i_schema_name;
388 l_objekt_typ melog_data.objekt_typ%type not null := i_objekt_typ;
389 l_objekt_name melog_data.objekt_name%type not null := i_objekt_name;
390 l_sub_objekt_typ melog_data.sub_objekt_typ%type null := i_sub_objekt_typ;
391 l_sub_objekt_name melog_data.sub_objekt_name%type null := i_sub_objekt_name;
392 l_melog melog_data%rowtype;
393 begin
394 l_melog.log_level := get_error_log_level;
395 l_melog.text := l_text;
396 l_melog.modul := l_modul;
397 l_melog.schema_name := l_schema_name;
398 l_melog.objekt_typ := l_objekt_typ;
399 l_melog.objekt_name := l_objekt_name;
400 l_melog.sub_objekt_typ := l_sub_objekt_typ;
401 l_melog.sub_objekt_name := l_sub_objekt_name;
402 set_not_null_values(l_melog);
403 insert_row(l_melog);
404 end error;
405--------------------------------------------------------------------------------
406 procedure error (
407 i_melog_data in melog_data%rowtype,
408 i_text in melog_data.text%type,
409 i_anzahl_zeilen in melog_data.anzahl_zeilen%type default null
410 ) is
411 l_melog_data melog_data%rowtype := i_melog_data;
412 l_text melog_data.text%type not null := i_text;
413 l_anzahl_zeilen melog_data.anzahl_zeilen%type := i_anzahl_zeilen;
414 begin
415 l_melog_data.log_level := get_error_log_level;
416 l_melog_data.text := l_text;
417 l_melog_data.anzahl_zeilen := l_anzahl_zeilen;
418 set_not_null_values(l_melog_data);
419 insert_row(l_melog_data);
420 end error;
421--------------------------------------------------------------------------------
422 procedure info (
423 i_text in melog_data.text%type,
424 i_modul in melog_data.modul%type,
425 i_schema_name in melog_data.schema_name%type,
426 i_objekt_typ in melog_data.objekt_typ%type,
427 i_objekt_name in melog_data.objekt_name%type,
428 i_sub_objekt_typ in melog_data.sub_objekt_typ%type,
429 i_sub_objekt_name in melog_data.sub_objekt_name%type
430 ) is
431 l_text melog_data.text%type not null := i_text;
432 l_modul melog_data.modul%type not null := i_modul;
433 l_schema_name melog_data.schema_name%type not null := i_schema_name;
434 l_objekt_typ melog_data.objekt_typ%type not null := i_objekt_typ;
435 l_objekt_name melog_data.objekt_name%type not null := i_objekt_name;
436 l_sub_objekt_typ melog_data.sub_objekt_typ%type null := i_sub_objekt_typ;
437 l_sub_objekt_name melog_data.sub_objekt_name%type null := i_sub_objekt_name;
438 l_melog melog_data%rowtype;
439 begin
440 l_melog.log_level := get_info_log_level;
441 l_melog.text := l_text;
442 l_melog.modul := l_modul;
443 l_melog.schema_name := l_schema_name;
444 l_melog.objekt_typ := l_objekt_typ;
445 l_melog.objekt_name := l_objekt_name;
446 l_melog.sub_objekt_typ := l_sub_objekt_typ;
447 l_melog.sub_objekt_name := l_sub_objekt_name;
448 set_not_null_values(l_melog);
449 insert_row(l_melog);
450 end info;
451--------------------------------------------------------------------------------
452 procedure info (
453 i_melog_data in melog_data%rowtype,
454 i_text in melog_data.text%type,
455 i_anzahl_zeilen in melog_data.anzahl_zeilen%type default null
456 ) is
457 l_melog_data melog_data%rowtype := i_melog_data;
458 l_text melog_data.text%type not null := i_text;
459 l_anzahl_zeilen melog_data.anzahl_zeilen%type := i_anzahl_zeilen;
460 begin
461 l_melog_data.log_level := get_info_log_level;
462 l_melog_data.text := l_text;
463 l_melog_data.anzahl_zeilen := l_anzahl_zeilen;
464 set_not_null_values(l_melog_data);
465 insert_row(l_melog_data);
466 end info;
467--------------------------------------------------------------------------------
468 procedure debug (
469 i_text in melog_data.text%type,
470 i_modul in melog_data.modul%type,
471 i_schema_name in melog_data.schema_name%type,
472 i_objekt_typ in melog_data.objekt_typ%type,
473 i_objekt_name in melog_data.objekt_name%type,
474 i_sub_objekt_typ in melog_data.sub_objekt_typ%type,
475 i_sub_objekt_name in melog_data.sub_objekt_name%type
476 ) is
477 l_text melog_data.text%type not null := i_text;
478 l_modul melog_data.modul%type not null := i_modul;
479 l_schema_name melog_data.schema_name%type not null := i_schema_name;
480 l_objekt_typ melog_data.objekt_typ%type not null := i_objekt_typ;
481 l_objekt_name melog_data.objekt_name%type not null := i_objekt_name;
482 l_sub_objekt_typ melog_data.sub_objekt_typ%type null := i_sub_objekt_typ;
483 l_sub_objekt_name melog_data.sub_objekt_name%type null := i_sub_objekt_name;
484 l_melog melog_data%rowtype;
485 begin
486 l_melog.log_level := get_debug_log_level;
487 l_melog.text := l_text;
488 l_melog.modul := l_modul;
489 l_melog.schema_name := l_schema_name;
490 l_melog.objekt_typ := l_objekt_typ;
491 l_melog.objekt_name := l_objekt_name;
492 l_melog.sub_objekt_typ := l_sub_objekt_typ;
493 l_melog.sub_objekt_name := l_sub_objekt_name;
494 set_not_null_values(l_melog);
495 insert_row(l_melog);
496 end debug;
497--------------------------------------------------------------------------------
498 procedure debug (
499 i_melog_data in melog_data%rowtype,
500 i_text in melog_data.text%type,
501 i_anzahl_zeilen in melog_data.anzahl_zeilen%type
502 ) is
503 l_melog_data melog_data%rowtype := i_melog_data;
504 l_text melog_data.text%type not null := i_text;
505 l_anzahl_zeilen melog_data.anzahl_zeilen%type := i_anzahl_zeilen;
506 begin
507 l_melog_data.log_level := get_debug_log_level;
508 l_melog_data.text := l_text;
509 l_melog_data.anzahl_zeilen := l_anzahl_zeilen;
510 set_not_null_values(l_melog_data);
511 insert_row(l_melog_data);
512 end debug;
513--------------------------------------------------------------------------------
514 procedure debug (
515 i_text in melog_data.text%type
516 ) is
517 c_modul constant melog_data.modul%type := 'N.A.';
518 c_schema_name constant melog_data.schema_name%type not null := 'A_DWHSHARE';
519 c_objekt_typ constant melog_data.objekt_typ%type := 'PACKAGE';
520 c_objekt_name constant melog_data.objekt_name%type := 'MELOG';
521 c_sub_objekt_typ constant melog_data.sub_objekt_typ%type := 'PROCEDURE';
522 c_sub_objekt_name constant melog_data.sub_objekt_name%type := 'DEBUG';
523 l_text melog_data.text%type not null := i_text;
524 l_melog melog_data%rowtype;
525 begin
526 l_melog.log_level := get_debug_log_level;
527 l_melog.modul := c_modul;
528 l_melog.schema_name := c_schema_name;
529 l_melog.objekt_typ := c_objekt_typ;
530 l_melog.objekt_name := c_objekt_name;
531 l_melog.sub_objekt_typ := c_sub_objekt_typ;
532 l_melog.sub_objekt_name := c_sub_objekt_name;
533 l_melog.text := l_text;
534 set_not_null_values(l_melog);
535 insert_row(l_melog);
536 end debug;
537--------------------------------------------------------------------------------
538 procedure beginn (
539 i_modul in melog_data.modul%type,
540 i_schema_name in melog_data.schema_name%type,
541 i_objekt_typ in melog_data.objekt_typ%type,
542 i_objekt_name in melog_data.objekt_name%type,
543 i_sub_objekt_typ in melog_data.sub_objekt_typ%type,
544 i_sub_objekt_name in melog_data.sub_objekt_name%type
545 ) is
546 c_text constant melog_data.text%type := 'BEGINN';
547 l_modul melog_data.modul%type not null := i_modul;
548 l_schema_name melog_data.schema_name%type not null := i_schema_name;
549 l_objekt_typ melog_data.objekt_typ%type not null := i_objekt_typ;
550 l_objekt_name melog_data.objekt_name%type not null := i_objekt_name;
551 l_sub_objekt_typ melog_data.sub_objekt_typ%type null := i_sub_objekt_typ;
552 l_sub_objekt_name melog_data.sub_objekt_name%type null := i_sub_objekt_name;
553 l_melog melog_data%rowtype;
554 begin
555 l_melog.modul := l_modul;
556 l_melog.schema_name := l_schema_name;
557 l_melog.objekt_typ := l_objekt_typ;
558 l_melog.objekt_name := l_objekt_name;
559 l_melog.sub_objekt_typ := l_sub_objekt_typ;
560 l_melog.sub_objekt_name := l_sub_objekt_name;
561 set_not_null_values(l_melog);
562 info(l_melog,c_text);
563 end beginn;
564--------------------------------------------------------------------------------
565 procedure ende (
566 i_modul in melog_data.modul%type,
567 i_schema_name in melog_data.schema_name%type,
568 i_objekt_typ in melog_data.objekt_typ%type,
569 i_objekt_name in melog_data.objekt_name%type,
570 i_sub_objekt_typ in melog_data.sub_objekt_typ%type,
571 i_sub_objekt_name in melog_data.sub_objekt_name%type
572 ) is
573 c_text constant melog_data.text%type := 'ENDE';
574 l_modul melog_data.modul%type not null := i_modul;
575 l_schema_name melog_data.schema_name%type not null := i_schema_name;
576 l_objekt_typ melog_data.objekt_typ%type not null := i_objekt_typ;
577 l_objekt_name melog_data.objekt_name%type not null := i_objekt_name;
578 l_sub_objekt_typ melog_data.sub_objekt_typ%type null := i_sub_objekt_typ;
579 l_sub_objekt_name melog_data.sub_objekt_name%type null := i_sub_objekt_name;
580 l_melog melog_data%rowtype;
581 begin
582 l_melog.modul := l_modul;
583 l_melog.schema_name := l_schema_name;
584 l_melog.objekt_typ := l_objekt_typ;
585 l_melog.objekt_name := l_objekt_name;
586 l_melog.sub_objekt_typ := l_sub_objekt_typ;
587 l_melog.sub_objekt_name := l_sub_objekt_name;
588 set_not_null_values(l_melog);
589 info(l_melog,c_text);
590 end ende;
591--------------------------------------------------------------------------------
592end melog;
593/
594
595create or replace package mail authid definer
596-- Package zum Versenden von E-Mails.
597is
598
599 -- Sendet eine E-Mail.
600 -- @Der Absender der E-Mail.
601 -- @Der oder die Empfänger der E-Mail. Mehrere Empfänger sind durch ein
602 -- Komma zu trenen.
603 -- @Der Betreff der E-Mail.
604 -- @Der eigentliche Text der E-Mail.
605 -- @Optionale Empfänger in CC. Mehrere Empfänger sind durch ein
606 -- Komma zu trenen.
607 -- @Optional ein aussagekräftiger Absendername der anstatt der E-Mail Adresse
608 -- des Absenders angezeigt wird.
609 -- @Optional kann die Ausgabe in einem reinen Textformat erzwungen werden.
610 procedure send (
611 i_from in varchar2,
612 i_to in varchar2,
613 i_subject in varchar2,
614 i_message in varchar2,
615 i_cc in varchar2 default null,
616 i_name in varchar2 default null,
617 i_plain_text_mode in boolean default false
618 );
619
620end mail;
621/
622
623
624
625create or replace package body mail is
626--------------------------------------------------------------------------------
627--------------------------------------------------------------------------------
628 c_hst constant utl.vc2_s := 'relay.metzler.com';
629 c_prt constant positive := 25;
630 c_pkb constant utl.vc2_s := 'PACKAGE BODY';
631 c_prc constant utl.vc2_s := 'PROCEDURE';
632 c_com constant utl.vc2_xs := ',';
633 c_sem constant utl.vc2_xs := ';';
634 c_eol constant utl.vc2_s := sys.utl_tcp.crlf;
635 c_fmt constant utl.vc2_s := 'dd-mon-yyyy hh24:mi:ss';
636 c_dat constant utl.vc2_s := 'Date: ';
637 c_to constant utl.vc2_s := 'To: ';
638 c_cc constant utl.vc2_s := 'CC: ';
639 c_frm constant utl.vc2_s := 'From: ';
640 c_sub constant utl.vc2_s := 'Subject: ';
641 c_rep constant utl.vc2_s := 'Reply-to: ';
642 c_miv constant utl.vc2_s := 'MIME-Version: 1.0';
643 c_bry constant utl.vc2_s := '----=*#abc1234321cba#*=';
644--------------------------------------------------------------------------------
645 function core (
646 i_from in varchar2,
647 i_to in varchar2,
648 i_subject in varchar2,
649 i_message in varchar2,
650 i_cc in varchar2,
651 i_name in varchar2,
652 i_plain_text_mode in boolean )
653 return utl_smtp.connection deterministic
654 is
655 c_name constant utl.vc2_s := 'CORE';
656 l_con utl_smtp.connection;
657 ---
658 procedure add_rcpts(i_rcpts in varchar2) is
659 l_rcpts vc2_tt;--apex_t_varchar2;
660 begin
661
662 if i_rcpts is not null then
663-- l_rcpts := apex_string.split(i_rcpts,c_com);
664 l_rcpts := utl.split(i_rcpts,c_com);
665 <<each_recipient>>
666 for i in 1..l_rcpts.count loop
667 sys.utl_smtp.rcpt(l_con, trim(l_rcpts(i)) );
668 end loop each_recipient;
669 end if;
670
671 end add_rcpts;
672 ---
673 procedure add_cc is
674 begin
675 if i_cc is not null then
676 sys.utl_smtp.write_data(l_con,
677 c_cc || replace(i_cc, c_com, c_sem) || c_eol);
678 end if;
679 end add_cc;
680 ---
681 procedure add_sender is
682 c_lt constant utl.vc2_xs:='<';
683 c_gt constant utl.vc2_xs:='>';
684 c_dq constant utl.vc2_xs:='"';
685 c_sp constant utl.vc2_xs:=' ';
686 l_from_txt utl.vc2_l;
687 begin
688 if i_name is null then
689 sys.utl_smtp.write_data(l_con, c_frm || i_from || c_eol);
690 else
691 l_from_txt:=c_frm ||c_dq||i_name||c_dq||c_sp||c_lt||i_from||c_gt;
692 sys.utl_smtp.write_raw_data(l_con, sys.utl_raw.cast_to_raw(l_from_txt));
693 sys.utl_smtp.write_data(l_con, c_eol);
694 end if;
695 end add_sender;
696 ---
697 procedure set_mail_header is
698 begin
699 sys.utl_smtp.write_data(l_con, c_dat || to_char(sysdate, c_fmt) || c_eol);
700 sys.utl_smtp.write_data(l_con, c_to || i_to || c_eol);
701 add_cc;
702 add_sender;
703 sys.utl_smtp.write_raw_data(l_con,
704 sys.utl_raw.cast_to_raw(c_sub || i_subject));
705 sys.utl_smtp.write_data(l_con, c_eol);
706 sys.utl_smtp.write_data(l_con, c_rep || i_from || c_eol);
707 if not i_plain_text_mode then
708 sys.utl_smtp.write_data(l_con, c_miv || c_eol);
709 sys.utl_smtp.write_data(l_con, 'Content-Type: multipart/alternative; boundary="' || c_bry || '"' || c_eol);
710 end if;
711 sys.utl_smtp.write_data(l_con, c_eol);
712 end set_mail_header;
713 ---
714 procedure set_mail_body is
715 begin
716 if i_plain_text_mode then
717 sys.utl_smtp.write_raw_data(l_con, sys.utl_raw.cast_to_raw(i_message));
718 sys.utl_smtp.write_data(l_con, c_eol);
719 sys.utl_smtp.write_data(l_con, c_eol);
720 else
721 sys.utl_smtp.write_data(l_con, '--' || c_bry || c_eol);
722 sys.utl_smtp.write_data(l_con, 'Content-Type: text/html; charset="iso-8859-1"' || c_eol || c_eol);
723 -- Umlautproblem!
724 --sys.utl_smtp.write_data(l_con, i_message);
725 sys.utl_smtp.write_raw_data(l_con, sys.utl_raw.cast_to_raw(i_message));
726 sys.utl_smtp.write_data(l_con, c_eol || c_eol);
727 sys.utl_smtp.write_data(l_con, '--' || c_bry || '--' || c_eol);
728 end if;
729 end set_mail_body;
730 ---
731 begin
732 -- handshake
733 l_con := sys.utl_smtp.open_connection(c_hst, c_prt);
734 sys.utl_smtp.helo(l_con, c_hst);
735 sys.utl_smtp.mail(l_con, i_from);
736 add_rcpts(i_to);
737 add_rcpts(i_cc);
738 -- open mail
739 sys.utl_smtp.open_data(l_con);
740 -- header
741 set_mail_header;
742 -- body
743 set_mail_body;
744
745 return l_con;
746
747 end core;
748--------------------------------------------------------------------------------
749--------------------------------------------------------------------------------
750 procedure send (
751 i_from in varchar2,
752 i_to in varchar2,
753 i_subject in varchar2,
754 i_message in varchar2,
755 i_cc in varchar2,
756 i_name in varchar2,
757 i_plain_text_mode in boolean )
758 is
759 c_name constant utl.vc2_s := 'SEND';
760 c_itxt constant utl.vc2_m := 'Mail erfolgreich gesendet!';
761 l_from utl.vc2_l not null := i_from;
762 l_to utl.vc2_l not null := i_to;
763 l_subj utl.vc2_l not null := i_subject;
764 l_msg utl.vc2_xl not null := i_message;
765 l_cc utl.vc2_l null := i_cc;
766 l_name utl.vc2_l null := i_name;
767-- l_log melog_t := melog_t(sys_guid, c_pkb, $$plsql_unit, c_prc, c_name);
768 l_con utl_smtp.connection;
769 begin
770 l_con := core(trim(l_from), trim(l_to), trim(l_subj), trim(l_msg),
771 trim(l_cc), trim(l_name), i_plain_text_mode);
772 -- close mail
773 sys.utl_smtp.close_data(l_con);
774 sys.utl_smtp.quit(l_con);
775-- l_log.info(i_text => c_itxt);
776 end send;
777--------------------------------------------------------------------------------
778-- Offene Dinge (SCM)
779--------------------------------------------------------------------------------
780---- subject length? ----
781--RFC 2822, section 2.1.1 "Line Length Limits" would seem TO indicate that NO SINGLE LINE IN an email Can exceed 998 chars AND should NOT exceed 78 chars...
782--HTTP://www.faqs.org/rfcs/rfc2822.HTML
783------------------------
784end mail;
785/
786
787
788create or replace type melog_t authid current_user
789-- TYPE zum einfacheren Protokollieren von Nachrichten jeglicher Art in die
790-- allgemein in MERDW zugängliche Tabelle MELOG_DATA via package MELOG.
791is object (
792--------------------------------------------------------------------------------
793 modul varchar2(255 char),
794 modul_detail varchar2(255 char),
795 text_detail clob,
796 schema_name varchar2(30 char),
797 objekt_typ varchar2(30 char),
798 objekt_name varchar2(30 char),
799 sub_objekt_typ varchar2(30 char),
800 sub_objekt_name varchar2(30 char),
801 anzahl_zeilen varchar2(30 char),
802--------------------------------------------------------------------------------
803 constructor function melog_t (
804 self in out nocopy melog_t,
805 i_modul in varchar2,
806 i_schema_name in varchar2,
807 i_objekt_typ in varchar2,
808 i_objekt_name in varchar2
809 ) return self as result,
810--------------------------------------------------------------------------------
811 constructor function melog_t (
812 self in out nocopy melog_t,
813 i_modul in varchar2,
814 i_schema_name in varchar2,
815 i_objekt_typ in varchar2,
816 i_objekt_name in varchar2,
817 i_sub_objekt_typ in varchar2,
818 i_sub_objekt_name in varchar2
819 ) return self as result,
820--------------------------------------------------------------------------------
821 member procedure fatal (self in out nocopy melog_t, i_text in varchar2),
822 member procedure error (self in out nocopy melog_t, i_text in varchar2),
823 member procedure info (self in out nocopy melog_t, i_text in varchar2),
824 member procedure debug (self in out nocopy melog_t, i_text in varchar2)
825);
826/
827
828
829create or replace type body melog_t is
830--------------------------------------------------------------------------------
831--------------------------------------------------------------------------------
832 constructor function melog_t (
833 self in out nocopy melog_t,
834 i_modul in varchar2,
835 i_schema_name in varchar2,
836 i_objekt_typ in varchar2,
837 i_objekt_name in varchar2
838 ) return self as result
839 is
840 l_modul melog_data.modul%type not null := i_modul;
841 l_schema_name melog_data.schema_name%type not null := i_schema_name;
842 l_objekt_typ melog_data.objekt_typ%type not null := i_objekt_typ;
843 l_objekt_name melog_data.objekt_name%type not null := i_objekt_name;
844 begin
845 self.modul := l_modul;
846 self.schema_name := l_schema_name;
847 self.objekt_typ := l_objekt_typ;
848 self.objekt_name := l_objekt_name;
849 return;
850 end;
851--------------------------------------------------------------------------------
852 constructor function melog_t (
853 self in out nocopy melog_t,
854 i_modul in varchar2,
855 i_schema_name in varchar2,
856 i_objekt_typ in varchar2,
857 i_objekt_name in varchar2,
858 i_sub_objekt_typ in varchar2,
859 i_sub_objekt_name in varchar2
860 ) return self as result
861 is
862 l_modul melog_data.modul%type not null := i_modul;
863 l_schema_name melog_data.schema_name%type not null := i_schema_name;
864 l_objekt_typ melog_data.objekt_typ%type not null := i_objekt_typ;
865 l_objekt_name melog_data.objekt_name%type not null := i_objekt_name;
866 l_sub_objekt_typ melog_data.sub_objekt_typ%type not null := i_sub_objekt_typ;
867 l_sub_objekt_name melog_data.sub_objekt_name%type not null := i_sub_objekt_name;
868 begin
869 self.modul := l_modul;
870 self.schema_name := l_schema_name;
871 self.objekt_typ := l_objekt_typ;
872 self.objekt_name := l_objekt_name;
873 self.sub_objekt_typ := l_sub_objekt_typ;
874 self.sub_objekt_name := l_sub_objekt_name;
875 return;
876 end;
877--------------------------------------------------------------------------------
878--------------------------------------------------------------------------------
879 member procedure fatal (self in out nocopy melog_t, i_text in varchar2)
880 is
881 l_melog_data melog_data%rowtype;
882 l_text melog_data.text%type not null := i_text;
883 begin
884 l_melog_data.schema_name := self.schema_name;
885 l_melog_data.modul := self.modul;
886 l_melog_data.modul_detail := self.modul_detail;
887 l_melog_data.text_detail := self.text_detail;
888 l_melog_data.objekt_typ := self.objekt_typ;
889 l_melog_data.objekt_name := self.objekt_name;
890 l_melog_data.sub_objekt_typ := self.sub_objekt_typ;
891 l_melog_data.sub_objekt_name := self.sub_objekt_name;
892 melog.fatal(l_melog_data, l_text, self.anzahl_zeilen);
893 self.text_detail := null;
894 self.anzahl_zeilen := null;
895 end;
896--------------------------------------------------------------------------------
897 member procedure error (self in out nocopy melog_t, i_text in varchar2)
898 is
899 l_melog_data melog_data%rowtype;
900 l_text melog_data.text%type not null := i_text;
901 begin
902 l_melog_data.schema_name := self.schema_name;
903 l_melog_data.modul := self.modul;
904 l_melog_data.modul_detail := self.modul_detail;
905 l_melog_data.text_detail := self.text_detail;
906 l_melog_data.objekt_typ := self.objekt_typ;
907 l_melog_data.objekt_name := self.objekt_name;
908 l_melog_data.sub_objekt_typ := self.sub_objekt_typ;
909 l_melog_data.sub_objekt_name := self.sub_objekt_name;
910 melog.error(l_melog_data, l_text, self.anzahl_zeilen);
911 self.text_detail := null;
912 self.anzahl_zeilen := null;
913 end;
914--------------------------------------------------------------------------------
915 member procedure info (self in out nocopy melog_t, i_text in varchar2)
916 is
917 l_melog_data melog_data%rowtype;
918 l_text melog_data.text%type not null := i_text;
919 begin
920 l_melog_data.schema_name := self.schema_name;
921 l_melog_data.modul := self.modul;
922 l_melog_data.modul_detail := self.modul_detail;
923 l_melog_data.text_detail := self.text_detail;
924 l_melog_data.objekt_typ := self.objekt_typ;
925 l_melog_data.objekt_name := self.objekt_name;
926 l_melog_data.sub_objekt_typ := self.sub_objekt_typ;
927 l_melog_data.sub_objekt_name := self.sub_objekt_name;
928 melog.info(l_melog_data, l_text, self.anzahl_zeilen);
929 self.text_detail := null;
930 self.anzahl_zeilen := null;
931 end;
932--------------------------------------------------------------------------------
933 member procedure debug (self in out nocopy melog_t, i_text in varchar2)
934 is
935 l_melog_data melog_data%rowtype;
936 l_text melog_data.text%type not null := i_text;
937 begin
938 l_melog_data.schema_name := self.schema_name;
939 l_melog_data.modul := self.modul;
940 l_melog_data.modul_detail := self.modul_detail;
941 l_melog_data.text_detail := self.text_detail;
942 l_melog_data.objekt_typ := self.objekt_typ;
943 l_melog_data.objekt_name := self.objekt_name;
944 l_melog_data.sub_objekt_typ := self.sub_objekt_typ;
945 l_melog_data.sub_objekt_name := self.sub_objekt_name;
946 melog.debug(l_melog_data, l_text, self.anzahl_zeilen);
947 self.text_detail := null;
948 self.anzahl_zeilen := null;
949 end;
950--------------------------------------------------------------------------------
951end;
952/
953
954
955create or replace view melog_5m_v as
956 select
957 *
958 from
959 melog_data
960 where
961 timestamp_insert >= ( sysdate - ( 1 / 24 / 12 ) );
962/
963--------------------------------------------------------------------------------
964comment on table melog_5m_v is
965 '...';
966
967
968comments via declar für table, views etc. setzen