[Home] [Help]
PACKAGE BODY: APPS.FND_TRACE_UTILS
Source
1 package body FND_TRACE_UTILS as
2 /* $Header: AFPMUTLB.pls 120.2 2005/11/03 14:55:41 rtikku noship $ */
3
4 G_CUTOFF_PCT NUMBER;
5 l_db_user varchar2(40);
6
7 procedure ol(p_str IN varchar2) is
8 PRAGMA AUTONOMOUS_TRANSACTION;
9
10 begin
11 null;
12 -- dbms_output.put_line(substr(p_str,1,250));
13 fnd_file.put_line(fnd_file.output,p_str);
14 -- fnd_file.put_line(fnd_file.output, replace(p_str, fnd_global.local_chr(0)));
15 end ol;
16
17 procedure dlog(p_str IN varchar2) is
18 PRAGMA AUTONOMOUS_TRANSACTION;
19
20 begin
21 -- dbms_output.put_line(substr(p_str,1,250));
22 FND_FILE.put_line(FND_FILE.log,p_str);
23 end dlog;
24
25 procedure PRINT_GRAND_SUMMARY(RELATED_RUN IN varchar2) is
26 l_sql_str varchar2(600);
27 l_tmp_str varchar2(200);
28 l_runid NUMBER;
29 l_ts varchar2(100);
30 l_run_total NUMBER;
31 l_grand_total NUMBER;
32 l_comment varchar2(200);
33 TYPE CurTyp IS REF CURSOR;
34 c_runs CurTyp;
35 begin
36
37 l_sql_str :='select trunc(sum(total_time/1000000000),2) '||
38 'from plsql_profiler_units ' ||
39 'where runid in ( select runid from plsql_profiler_runs ' ||
40 ' where related_run = :RELATED_RUN)';
41 EXECUTE IMMEDIATE l_sql_str into l_grand_total using RELATED_RUN;
42
43 ol('<table >');
44 ol('<tr><td class=OraTableTitle>');
45 ol('Grand Summary For Related Run : '||RELATED_RUN);
46 ol('</td></tr><tr><td>');
47 ol('<table class="OraTable">');
48 ol('<tr>');
49 ol('<td class="tshn" width=60 >Run ID</td>');
50 ol('<td class="tshc" width=125>Date</td>');
51 ol('<td class="tshn" width=125 >Total Time (s)</td>');
52 ol('<td class="tshn" width=125 >% Total </td>');
53 ol('<td class="tshc" width=250>Run Comment</td>');
54 ol('</tr>');
55
56 IF l_grand_total > 0 THEN
57 l_sql_str:='select runid,TO_CHAR(run_date,''DD-MON-RR HH24:MI:SS''),'||
58 '(select to_char(sum(total_time/1000000000),''999,999.00'') '||
59 ' from plsql_profiler_units u '||
60 ' where r.runid=u.runid), '||
61 'run_comment||'' '' ||run_comment1 ' ||
62 'from plsql_profiler_runs r '||
63 'where related_run=:related_run order by runid';
64 open c_runs for l_sql_str using related_run;
65 loop
66 fetch c_runs into l_runid,l_ts,l_run_total,l_comment;
67 EXIT WHEN c_runs%NOTFOUND;
68 -- l_tmp_str:='begin dbms_profiler.rollup_run(:RUN_ID); end;';
69 -- EXECUTE IMMEDIATE l_tmp_str USING l_runid;
70
71 ol('<tr class=tdc>');
72 ol('<td class=tdn> <a href="#R'||l_runid||'">'||l_runid||'</a></td>');
73 ol('<td >'||l_ts||'</td>');
74 ol('<td class=tdn >'||l_run_total||'</td>');
75 ol('<td class=tdn >');
76 ol(to_char((l_run_total*100)/(l_grand_total),'999.00'));
77 ol('</td>');
78 ol('<td>'||l_comment||'</td>');
79 ol('</tr>');
80 end loop;
81 close c_runs;
82 ol('<tr class=tdc>');
83 ol('<td colspan=2>Total</td>');
84 ol('<td class=tdn >'||l_grand_total||'</td>');
85 ol('<td class=tdn colspan=2></td>');
86 ol('</tr>');
87 ELSE -- if l_grand_total > 0
88 ol('<tr><td class=tdc colspan=5> Grand Total Time is 0.</td></tr>');
89 END IF;
90 ol('</table>');
91 ol('</td></tr></table>');
92 ol('<!-- ENDOFGRANDSUM -->');
93 ol('<br>');
94 ol('<br>');
95 ol('<br>');
96 end PRINT_GRAND_SUMMARY;
97
98
99 procedure PRINT_HEADER is
100 l_timestamp varchar2(40);
101 l_instance varchar2(40);
102 begin
103 -- l_timestamp:=to_char(sysdate,'dd-Mon-yy hh24:mi');
104 l_timestamp:=fnd_date.date_to_displaydate(sysdate);
105 select instance_name into l_instance
106 from v$instance;
107 -- ol('<tr><td>');
108 ol('<table width=100%><tr><td class=applicationName>');
109 ol('PL/SQL Profiler Report');
110 ol('</td><td>');
111 ol('<table>');
112 ol('<tr class=reportDataCell>');
113 ol('<td align=right>Instance : </td>');
114 ol('<td>'||l_instance||'</td></tr>');
115 ol('<tr class=reportDataCell>');
116 ol('<td align=right>Report Date : </td>');
117 ol('<td>'||l_timestamp||'</td></tr>');
118 ol('</table>');
119 ol('</td></tr>');
120 ol('</table>');
121 -- ol('</td></tr>');
122 ol('<br>');
123 ol('<br>');
124 end PRINT_HEADER;
125
126
127 procedure PRINT_UNIT(RUN_ID IN number,
128 U_NUMBER IN number,
129 U_TYPE IN varchar2,
130 U_OWNER IN varchar2,
131 U_NAME IN varchar2 ) is
132 l_sql_str varchar2(4000);
133 l_line_num varchar2(40);
134 l_line_min number;
135 l_line_max number;
136 l_occ varchar2(80);
137 l_exec varchar2(80);
138 l_text varchar2(4000);
139
140 TYPE CurTyp IS REF CURSOR;
141 c_units CurTyp;
142 begin
143
144 ol('<table>');
145 ol('<tr><td class=OraTableTitle>');
146 ol('<A NAME="U'||RUN_ID||'_'||U_NUMBER||'"></A>');
147 ol('Execution Details For Program Units : '||U_OWNER||'.'||U_NAME);
148 ol('</td></tr>');
149 ol('<tr><td>');
150 ol('<table class="OraTable" border=0>');
151 ol('<tr>');
152 ol('<td class="tshn" width=60 >Line #</td>');
153 ol('<td class="tshn" width=100 >Executions</td>');
154 ol('<td class="tshn" width=100 >Time (ms)</td>');
155 ol('<td class="tshc" width=600>Line Text </td>');
156 ol('</tr>');
157 -- l_sql_str:='select min(line#)-5,max(line#)+5 from plsql_profiler_data '||
158 -- 'where runid= :RUN_ID and unit_number = :U_NUMBER '||
159 -- 'and (total_occur > 0 or total_time > 0) ';
160 -- dlog(l_sql_str);
161
162 -- EXECUTE IMMEDIATE l_sql_str into l_line_min,l_line_max
163 -- using RUN_ID,U_NUMBER;
164
165 l_sql_str:='select p.line||decode(d.line#,null,null,'||
166 '''<a name="l''||d.line#||''">''), '||
167 'to_char(d.total_occur,''999,999,999''),'||
168 'to_char((d.total_time/1000000),''999,999.00'') ,ltrim(p.text) '||
169 'from plsql_profiler_data d, '||
170 'dba_source p '||
171 'where p.line = d.line#(+) '||
172 'and p.type in (''PACKAGE'',''PACKAGE BODY'',''PROCEDURE'',''FUNCTION'') '||
173 'and p.owner = :U_OWNER '||
174 'and p.name = upper(:u_NAME ) '||
175 -- 'and p.line between :l_line_min and :l_line_max '||
176 'and d.runid(+) = :RUN_ID '||
177 'and d.unit_number(+) = :U_NUMBER '||
178 'and p.type = :U_TYPE '||
179 'order by p.line ';
180 -- dlog(l_sql_str);
181 open c_units for l_sql_str
182 -- using U_OWNER,U_name,l_line_min,l_line_max,RUN_ID,U_NUMBER,U_TYPE;
183 using U_OWNER,U_name,RUN_ID,U_NUMBER,U_TYPE;
184 loop
185 fetch c_units
186 into l_line_num,l_occ,l_exec,l_text;
187 EXIT WHEN c_units%NOTFOUND;
188 ol('<tr class=tdn>');
189 ol('<td >'||l_line_num||'</td>');
190 ol('<td >'||l_occ||'</td>');
191 ol('<td >'||l_exec||'</td>');
192 ol('<td class=tdc>'||l_text||'</td>');
193 ol('</tr>');
194 end loop;
195 close c_units;
196 ol('</table> ');
197 ol('</td></tr></table> ');
198 -- ol('</td></tr>');
199 ol('<!-- ENDOFUNIT -->');
200 ol('<br>');
201 ol('<br>');
202
203 end PRINT_UNIT;
204
205
206 procedure PRINT_RUN(run_id IN number) is
207 l_tmp_str varchar2(100);
208 l_sql_str varchar2(4096);
209 l_buf varchar2(8192);
210 l_run_total number;
211 TYPE CurTyp IS REF CURSOR;
212 c_units CurTyp;
213 u_number number;
214 u_type varchar2(32);
215 u_owner varchar2(32);
216 u_name varchar2(32);
217 u_timestamp varchar2(32);
218 u_total_time varchar2(40);
219 u_percent number;
220 u_comm varchar2(400);
221
222 begin
223
224 -- ol('<tr><td>');
225
226
227 l_tmp_str:='select nvl(ROUND(sum(total_time)/1000000000,3),0) '||
228 'from plsql_profiler_units where runid= :RUN_ID';
229
230 EXECUTE IMMEDIATE l_tmp_str INTO l_run_total USING RUN_ID;
231
232
233
234 dlog('Run total is '||l_run_total);
235
236
237 ol('<table class="OraTable">');
238 ol('<tr>');
239 ol('<td class="tshn" width=60 >');
240 ol('<a name="R'||RUN_ID||'" </a>Run ID</td>');
241 ol('<td class="tshc" width=100>Date</td>');
242 ol('<td class="tshn" width=125 >Total Time (s)</td>');
243 ol('<td class="tshc" width=250>Run Comment</td>');
244 --ol('<td class="tshc" width=125>Description</td>');
245 ol('</tr>');
246
247 l_sql_str:=' SELECT ''<tr>''||
248 ''<td class="tdn" >''||runid||''</td>''||
249 ''<td class="tdc">''||TO_CHAR(run_date,''DD-MON-RR HH24:MI:SS'')||
250 ''</td>''|| ''<td class="tdn" >''||:l_run_total||''</td>''||
251 ''<td class="tdc">''||run_comment||'' '' ||run_comment1||''</td>''||
252 ''</tr>''
253 FROM plsql_profiler_runs
254 WHERE runid = :RUN_ID';
255
256 EXECUTE IMMEDIATE l_sql_str INTO l_buf USING l_run_total,RUN_ID;
257 ol(l_buf);
258 ol('</table> ');
259 IF l_run_total =0 THEN
260 ol('<table><tr><td colspan=4> <br></td></tr>');
261 ol('<tr><td colspan=4 class=OraTableTitle> ');
262 ol('Program Unit Summary/Details for this run not printed as Run Total is 0.');
263 ol('</td></tr></table>');
264 END IF;
265
266 -- ol('</td></tr>');
267 ol('<br>');
268
269
270 IF(l_run_total > 0) THEN
271 ol('<table >');
272 ol('<tr><td class=OraTableTitle>');
273 ol('Execution Summary By Program Units (consuming > '||G_CUTOFF_PCT);
274 ol('% of Total Time) For Run ID : '||RUN_ID);
275 ol('</td></tr>');
276 ol('<tr><td>');
277
278 ol('<table class="OraTable" border=0>');
279 ol('<tr>');
280 ol('<td class="tshn" width=50 >Unit #</td>');
281 ol('<td class="tshc" width=100>Type</td>');
282 ol('<td class="tshc" width=75>Owner</td>');
283 ol('<td class="tshc" width=250>Program Unit Name</td>');
284 ol('<td class="tshn" width=100 >Total Time(s)</td>');
285 ol('<td class="tshn" width=100 >% Total</td>');
286 ol('<td class="tshn" width=200 >Comment</td>');
287 ol('</tr>');
288
289
290 /* l_sql_str:='select unit_number,unit_type,unit_owner,unit_name, ' ||
291 ' unit_timestamp, '||
292 'to_char((total_time/1000000000),''999,999,999.00''), '||
293 'to_char(((total_time*100)/(1000000000*:l_run_total)),''999,999,999.00'') '||
294 ',decode(unit_owner,'''||l_db_user||''',
295 (select text from dba_source where type=unit_type and owner=unit_owner and name=unit_name and line=2),'' '')' ||
296 'from plsql_profiler_units '||
297 -- 'where runid = :RUN_ID and total_time > 1000000 '||
298 'where runid = :RUN_ID '||
299 'and (total_time*100)/(1000000000*:l_run_total) > :G_CUTOFF_PCT '||
300 ' order by total_time desc';
301 */
302 l_sql_str:='select unit_number,unit_type,unit_owner,unit_name, ' ||
303 ' unit_timestamp, '||
304 'trunc((total_time/1000000000),2), '||
305 'trunc(((total_time*100)/(1000000000*:l_run_total)),2) '||
306 ',decode(unit_owner,'''||l_db_user||''',
307 (select text from dba_source where type=unit_type and owner=unit_owner and name=unit_name and line=2),'' '')' ||
308 'from plsql_profiler_units '||
309 'where runid = :RUN_ID '||
310 'and (total_time*100)/(1000000000*:l_run_total) > :G_CUTOFF_PCT '||
311 ' order by total_time desc';
312 open c_units for l_sql_str
313 using l_run_total,RUN_ID,l_run_total,G_CUTOFF_PCT;
314 loop
315 fetch c_units
316 into u_number,u_type,u_owner,u_name,u_timestamp,u_total_time,u_percent,u_comm;
317 EXIT WHEN c_units%NOTFOUND;
318 ol('<tr class=tdc>');
319 ol('<td class=tdn>'||u_number||'</td>');
320 ol('<td>'||u_type||'</td>');
321 ol('<td>'||u_owner||'</td>');
322 IF u_owner=l_db_user THEN
323 ol('<td><a href="#U'||run_id||'_'||u_number||'">'||u_name||'</a></td>');
324 ELSE
325 ol('<td>'||u_name||'</td>');
326 END IF;
327 ol('<td class=tdn>'||u_total_time||'</td>');
328 ol('<td class=tdn>'||u_percent||'</td>');
329 ol('<td class=tdn>'||u_comm||'</td>');
330 ol('</tr>');
331 end loop;
332 close c_units;
333
334 ol('</table> ');
335 ol('</td></tr>');
336 ol('</table> ');
337
338 ol('<br>');
339
340 l_sql_str:='select unit_number,unit_type,unit_owner,unit_name ' ||
341 'from plsql_profiler_units '||
342 'where runid = :RUN_ID and total_time > 1000000 '||
343 'and unit_owner = '''||l_db_user||''' and unit_type IN '||
344 '(''PACKAGE'',''PACKAGE BODY'',''PROCEDURE'',''FUNCTION'') '||
345 'and (total_time*100)/(1000000000*:l_run_total) > :G_CUTOFF_PCT '||
346 ' order by total_time desc';
347
348 open c_units for l_sql_str
349 using RUN_ID,l_run_total,G_CUTOFF_PCT;
350 loop
351 fetch c_units
352 into u_number,u_type,u_owner,u_name;
353 EXIT WHEN c_units%NOTFOUND;
354 PRINT_UNIT(RUN_ID,u_number,u_type,u_owner,u_name);
355 end loop;
356 close c_units;
357
358
359 END IF;
360
361 ol('<!-- ENDOFRUN -->');
362 ol('<br>');
363 ol('<br>');
364
365 end PRINT_RUN;
366
367 procedure PLSQL_PROF_RPT( errbuf OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
368 retcode OUT NOCOPY /* file.sql.39 change */ NUMBER,
369 RUN_ID in NUMBER,
370 RELATED_RUN in NUMBER,
371 PURGE_DATA IN VARCHAR2 DEFAULT 'Y',
372 CUTOFF_PCT in NUMBER) IS
373
374 BEGIN
375 G_CUTOFF_PCT:=CUTOFF_PCT;
376
377 PLSQL_PROF_RPT(RUN_ID,RELATED_RUN,PURGE_DATA,CUTOFF_PCT);
378
379 EXCEPTION
380 WHEN NO_DATA_FOUND THEN
381 -- errbuf := 'ERROR:'||sqlerrm ;
382 -- retcode := '2';
383 FND_FILE.put_line(FND_FILE.log,errbuf);
384 FND_FILE.put_line(FND_FILE.log,'No Profiler Data was Found for the given Run');
385 WHEN OTHERS THEN
386 errbuf := 'Error:'||sqlerrm ;
387 retcode := '2';
388 FND_FILE.put_line(FND_FILE.log,'Error running PLSQL_PROF_RPT');
389 END PLSQL_PROF_RPT;
390
391 procedure PLSQL_PROF_RPT( RUN_ID in NUMBER,
392 RELATED_RUN in NUMBER,
393 PURGE_DATA IN VARCHAR2 DEFAULT 'Y',
394 CUTOFF_PCT in NUMBER) IS
395
396 l_tmp_str varchar2(100);
397 l_sql_str varchar2(4096);
398 l_buf varchar2(8192);
399 l_run_total number;
400 l_run_ok varchar2(1) :='N';
401 l_single_runid varchar2(1) :='Y';
402 l_run_count number :=1;
403 l_runid number;
404
405 TYPE CurTyp IS REF CURSOR;
406 c_runs CurTyp;
407
408 --cursor c_runs(rel_run number) is
409 -- select runid from plsql_profiler_runs1 where related_run=rel_run;
410
411 BEGIN
412 G_CUTOFF_PCT:=CUTOFF_PCT;
413
414 -- Check if the profiler package and the tables exist, if not log and exit.
415 BEGIN
416 select 'Y' into l_run_ok from all_objects
417 where owner='SYS' and object_type='PACKAGE' and object_name='DBMS_PROFILER';
418 select 'Y' into l_run_ok from all_objects
419 where owner='SYS' and object_type='PACKAGE BODY'
420 and object_name='DBMS_PROFILER';
421
422 select 'Y' into l_run_ok from all_tables
423 where table_name='PLSQL_PROFILER_RUNS' and rownum =1 and owner like '%';
424 select 'Y' into l_run_ok from all_tables
425 where table_name='PLSQL_PROFILER_UNITS' and rownum =1 and owner like '%';
426 select 'Y' into l_run_ok from all_tables
427 where table_name='PLSQL_PROFILER_DATA' and rownum =1 and owner like '%';
428 EXCEPTION
429 WHEN NO_DATA_FOUND THEN
430 dlog('Profiler Package and/or Profiler Tables could not be accessed.');
431 dlog(' ');
432 dlog(' ');
433 dlog(' ');
434 dlog('Please run the following scripts to install the PL/SQL Profiler objects and repeat the run.');
435 dlog(' ');
436 dlog(' ');
437 dlog('To install the PL/SQL Profiler package, run this as the SYS user:');
438 dlog(' $ORACLE_HOME/rdbms/admin/profload.sql ');
439 dlog(' ');
440 dlog('To install the PL/SQL Profiler tables, run this as the APPS user:');
441 dlog(' $ORACLE_HOME/rdbms/admin/proftab.sql ');
442 dlog(' ');
443 dlog(' ');
444 raise ;
445 END;
446
447 ol('<html><head><title>PL/SQL Profiler Output</title>');
448 ol('<style type="text/css">');
449 ol('h1 { font-family:Arial,Helvetica,Geneva,sans-serif;font-size:16pt }');
450 ol('h2 { font-family:Arial,Helvetica,Geneva,sans-serif;font-size:12pt }');
451 ol('h3 { font-family:Arial,Helvetica,Geneva,sans-serif;font-size:10pt }');
452 ol('pre { font-family:Courier New,Geneva;font-size:8pt }');
453 ol('HR { color: #CCCC99; height: 1px; }');
454
455 ol('.applicationBody { background-image: url(/OA_MEDIA/jtfulnon_med.gif); background-repeat: no-repeat; background-color: #FFFFFF }');
456 ol('.applicationName { font-family: Times New Roman, Times, serif; font-size: 18pt; font-weight: bold; color: #336699 }');
457
458 ol('.OraTableTitle {font-family:Arial,Helvetica,Geneva,sans-serif;font-size:13pt;background-color:#ffffff;color:#336699}');
459 ol('.OraTable {background-color:#999966}');
460
461 ol('.tdn { background-color: #f7f7e7; font-family: Arial, Helvetica, Geneva, sans-serif; font-size: 9pt; text-align:right }');
462 ol('.tdc { background-color: #f7f7e7; font-family: Arial, Helvetica, Geneva, sans-serif; font-size: 9pt; text-align:left }');
463 ol('.OraLinkText { background-color: #f7f7e7; font-family: Arial, Helvetica, Geneva, sans-serif; font-size: 9pt; color: #663300; }');
464 ol('.tableBigHeaderCell { background-color: #CCCC99; font-family: Arial, Helvetica, sans-serif; font-size: 12pt; font-weight: bold; color:#336699 }');
465 ol('.tableRowHeader { background-color: #FFFFCC; font-family: Arial, Helvetica, sans-serif; font-size: 10pt; font-weight: bold }');
466 ol('.tshc { background-color: #CCCC99; font-family: Arial, Helvetica, sans-serif; font-size: 10pt; font-weight: bold; color:#336699; text-align:left }');
467 ol('.tshn { background-color: #CCCC99; font-family: Arial, Helvetica, sans-serif; font-size: 10pt; font-weight: bold; color:#336699; text-align:right }');
468 ol('.tableSubHeaderCell { background-color: #CCCC99; font-family: Arial, Helvetica, sans-serif; font-size: 10pt; color: #336699 }');
469 ol('.tableTotal { font-family: Arial, Helvetica, sans-serif; font-size: 10pt; font-weight: bold; text-align: right }');
470 ol('.reportDataCell { background-color: #FFFFFF; font-size: 8pt }');
471 ol('.reportFootnote { background-color: #FFFFFF; font-family: Arial, Helvetica, sans-serif; font-size: 8pt; color: #336699 }');
472
473 ol('</style></head><body class="applicationBody">');
474
475 -- start outer holding table.
476 -- ol('<table width=800 cellpadding=0 cellspacing=0 border=0 > <tr><td>');
477
478 -- Print Report Header
479 PRINT_HEADER;
480
481 -- Check if data for the given run exists.
482 if RELATED_RUN is not null then
483 -- get how many runs we have, if multiple, print grand summary
484 -- and then iterate over individual runs, elsif single, print it
485 -- using the RUNID if provided else try to get the runid using RELATED_RUN.
486 -- If still not found, log message.
487 l_sql_str:='select count(*) from plsql_profiler_runs '||
488 'where related_run = :RELATED_RUN ';
489 EXECUTE IMMEDIATE l_sql_str INTO l_run_count using RELATED_RUN;
490
491 dlog(l_run_count||' Runs found for Related Run Id '||RELATED_RUN);
492 if l_run_count > 0 then
493 l_sql_str:='select runid from plsql_profiler_runs '||
494 'where related_run=:related_run order by runid';
495 open c_runs for l_sql_str using related_run;
496 loop
497 -- rollup all runs, so that grand summary can be printed
498 fetch c_runs into l_runid;
499 EXIT WHEN c_runs%NOTFOUND;
500 dlog('Rolling Up Run '||l_runid);
501 l_sql_str:='begin dbms_profiler.rollup_run(:RUN_ID); end;';
502 EXECUTE IMMEDIATE l_sql_str USING l_runid;
503 end loop;
504 close c_runs;
505 dlog('Printing Grand Summary');
506 print_grand_summary(RELATED_RUN);
507 -- then print each run
508 l_sql_str:='select runid from plsql_profiler_runs '||
509 'where related_run=:related_run order by runid';
510 open c_runs for l_sql_str using related_run;
511 loop
512 fetch c_runs into l_runid;
513 EXIT WHEN c_runs%NOTFOUND;
514 dlog('Processing Run Id '||l_runid);
515 print_run(l_runid);
516 end loop;
517 close c_runs;
518
519 elsif l_run_count =1 then
520 -- print single run, use RUNID if provided else get it
521 if RUN_ID is not null then
522 dlog('Rolling Up Run '||RUN_ID);
523 l_sql_str:='begin dbms_profiler.rollup_run(:RUN_ID); end;';
524 EXECUTE IMMEDIATE l_sql_str USING RUN_ID;
525 dlog('Printing Run Id '||RUN_ID);
526
527 print_run(RUN_ID);
528 else
529 -- get the runid based on related run and print it
530 l_runid:=-1;
531 l_sql_str:='select runid from plsql_profiler_runs '||
532 'where related_run=:RELATED_RUN';
533 EXECUTE IMMEDIATE l_sql_str INTO l_runid using RELATED_RUN;
534
535 if l_runid > -1 then
536 dlog('Rolling Up Run '||l_runid);
537 l_sql_str:='begin dbms_profiler.rollup_run(:RUN_ID); end;';
538 EXECUTE IMMEDIATE l_sql_str USING l_runid;
539 dlog('Printing Run Id '||l_runid);
540 print_run(l_runid);
541 else
542 dlog('Data for the given profiler run could not be found.');
543 end if; -- if l_runid > -1
544 end if; -- if RUNID is not null
545 else -- that means l_run_count = 0 and not run was found
546 dlog('No runs were found for the given RELATED RUN');
547 end if;
548 else -- RELATED_RUN was null, so we will use the supplied RUNID
549 if RUN_ID is not null then
550 dlog('Rolling Up Run '||RUN_ID);
551 l_sql_str:='begin dbms_profiler.rollup_run(:RUN_ID); end;';
552 EXECUTE IMMEDIATE l_sql_str USING RUN_ID;
553 dlog('Processing Run Id '||RUN_ID);
554 print_run(RUN_ID);
555 else
556 dlog('Data for Profiler Run Id : '||RUN_ID||' could not be found.');
557 end if; -- if RUNID is not null
558 end if; -- if RELATED_RUN is not null
559
560 -- IF PURGE_DATA flag is 'Y', then purge the profiler tables for this run
561
562 if ( (UPPER(PURGE_DATA) = 'Y') OR (UPPER(PURGE_DATA) = 'YES') ) then
563 if RELATED_RUN is not null then
564 begin
565 dlog('Purging Profiler Data for Related Run '||RELATED_RUN);
566
567 l_sql_str:='delete plsql_profiler_data where runid in '||
568 '(select runid from plsql_profiler_runs where related_run=:RELATED_RUN)';
569 EXECUTE IMMEDIATE l_sql_str USING RELATED_RUN;
570
571 l_sql_str:='delete plsql_profiler_units where runid in '||
572 '(select runid from plsql_profiler_runs where related_run=:RELATED_RUN)';
573 EXECUTE IMMEDIATE l_sql_str USING RELATED_RUN;
574
575 l_sql_str:='delete plsql_profiler_runs where related_run=:RELATED_RUN';
576 EXECUTE IMMEDIATE l_sql_str USING RELATED_RUN;
577
578 commit;
579 exception
580 when NO_DATA_FOUND then
581 null;
582 end;
583
584 elsif RUN_ID is not null then
585 begin
586 dlog('Purging Profiler Data for Run Id '||RUN_ID);
587
588 l_sql_str:='delete plsql_profiler_data where runid =:RUN_ID';
589 EXECUTE IMMEDIATE l_sql_str USING RUN_ID;
590
591 l_sql_str:='delete plsql_profiler_units where runid =:RUN_ID';
592 EXECUTE IMMEDIATE l_sql_str USING RUN_ID;
593
594 l_sql_str:='delete plsql_profiler_runs where runid=:RUN_ID';
595 EXECUTE IMMEDIATE l_sql_str USING RUN_ID;
596
597 commit;
598 exception
599 when NO_DATA_FOUND then
600 null;
601 end;
602 else
603 dlog('No Profiler Data found for purging');
604 end if; -- if RELATED_RUN is null
605 else
606 dlog('No Profiler Data Purged');
607 end if; -- if PURGE_DATA = 'Y'
608
609 ol('</body></html>');
610
611 END PLSQL_PROF_RPT;
612
613
614 BEGIN
615
616 select upper(user) into l_db_user from dual;
617
618 END FND_TRACE_UTILS;