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