1 package body FND_TRACE as
2 /* $Header: AFPMTRCB.pls 120.4 2011/03/04 12:25:44 msaleem ship $ */
3
4 DBUSER VARCHAR2(30);
5 SESSION_ID_G NUMBER;
6 SERIAL#_G NUMBER;
7 SPID NUMBER;
8 MODULE_G VARCHAR2(80):='MOD_NA';
9
10 IDENT_STRING_G VARCHAR2(120) ;
11 IDENT_STRING_O VARCHAR2(120) ; -- external override
12 INVALID_IDENT VARCHAR2(1):='N'; -- identifier contains invalid chars
13
14
15 IDENT_FLAG VARCHAR2(1):='N';
16 TRC_ID NUMBER :=0;
17
18 PROF_FLAG VARCHAR2(1):='N';
19 PROF_RUNID NUMBER:=0;
20 PROF_REQID NUMBER:=-1;
21 db_versn NUMBER :=81; -- changes done for bug 11077620
22
23
24 procedure DLOG(MESG IN VARCHAR2) IS
25 BEGIN
26 -- dbms_output.put_line(mesg);
27 FND_FILE.put_line(FND_FILE.log,MESG);
28 END;
29
30 function GET_DB_VERSION return NUMBER IS
31 versn number:=8;
32 BEGIN
33 select substr(version,1,instr(version,'.')-1) into versn from v$instance;
34 return versn;
35 EXCEPTION
36 WHEN OTHERS THEN
37 return 8; -- Just in case, default it to 8
38 END;
39
40 procedure SET_IDENT_FOR_SESS(TRACE_TYPE in NUMBER) IS
41
42 IDENT_STRING_T varchar2(120); -- temporary local variable
43 l_pl_str varchar2(200);
44
45 BEGIN
46 -- Get the current identifier string
47 IF FND_GLOBAL.USER_NAME IS NOT NULL THEN
48 IDENT_STRING_T:=upper(FND_GLOBAL.USER_NAME);
49 -- change - to _
50 IDENT_STRING_T:=translate( IDENT_STRING_T,'-','_');
51 -- remove special chars, ie all chars other than alphanumeric and _
52 IDENT_STRING_T:=translate( IDENT_STRING_T,'x'||translate( IDENT_STRING_T,
53 '-_0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ',
54 '-'),'x');
55 -- Remove @ from ident_string_g, if if contains one
56 -- Next three lines have been commented as they are not needed anymore, it
57 -- has been taken care of by the earlier translate.
58 -- IF instr(IDENT_STRING_T,'@') > 0 THEN
59 -- IDENT_STRING_T:=substr(IDENT_STRING_T,1,instr(IDENT_STRING_T,'@')-1);
60 -- END IF;
61 -- Cap ident_string_g at 12 chars
62 IDENT_STRING_T:=substr(IDENT_STRING_T,1,12);
63 IF (FND_GLOBAL.CONC_REQUEST_ID > 0) then
64 IDENT_STRING_T:=IDENT_STRING_T||'_CR'||FND_GLOBAL.CONC_REQUEST_ID;
65 END IF;
66 ELSE
67 SELECT USER INTO DBUSER FROM DUAL;
68 IDENT_STRING_T:=substr(DBUSER,1,12);
69 END IF;
70
71 IF(nvl(IDENT_STRING_G,' ') <> IDENT_STRING_T) THEN
72 -- Reset the identifier string and flag.
73 IDENT_STRING_G:=IDENT_STRING_T;
74 IDENT_FLAG:='N';
75 END IF;
76
77 -- Set the invalid flag if the ident string contains a space
78 IF instr(IDENT_STRING_O,' ') > 0 THEN
79 INVALID_IDENT:='Y';
80 END IF;
81 IF instr(IDENT_STRING_G,' ') > 0 THEN
82 INVALID_IDENT:='Y';
83 END IF;
84
85
86 IF ( (IDENT_FLAG='N') AND (INVALID_IDENT='N')) THEN
87 IF (IDENT_STRING_O IS NOT NULL ) THEN -- external override
88 EXECUTE IMMEDIATE 'ALTER SESSION SET TRACEFILE_IDENTIFIER='''||IDENT_STRING_O||'''';
89 ELSE
90 EXECUTE IMMEDIATE 'ALTER SESSION SET TRACEFILE_IDENTIFIER='''||IDENT_STRING_G||'''';
91 END IF;
92 IDENT_FLAG:='Y';
93 END IF;
94
95 END;
96
97
98 procedure RESET_SESSION IS
99 BEGIN
100 execute immediate 'dbms_session.reset_package';
101 END;
102
103 function SUBMIT_PROFILER_REPORT RETURN NUMBER IS
104 BEGIN
105
106 RETURN SUBMIT_PROFILER_REPORT(PROF_RUNID,RELATED_RUNID,'Y') ;
107
108 EXCEPTION
109 WHEN OTHERS THEN
110 RETURN -1;
111 END SUBMIT_PROFILER_REPORT;
112
113 function SUBMIT_PROFILER_REPORT(PROF_RUNID IN NUMBER,
114 RELATED_RUNID IN NUMBER,
115 PURGE_DATA IN VARCHAR2) RETURN NUMBER IS
116 PRAGMA AUTONOMOUS_TRANSACTION;
117 PURGE_DATA_L VARCHAR2(5);
118 BEGIN
119
120 -- Default value for PURGE_DATA is 'Y', but if this profile option
121 -- is set, then we override the default value.
122
123 PURGE_DATA_L:=NVL(FND_PROFILE.VALUE('FND_PURGE_PROFILER_DATA'),'Y');
124
125 IF FND_GLOBAL.USER_NAME IS NOT NULL THEN
126 PROF_REQID := FND_REQUEST.SUBMIT_REQUEST(application => 'FND',
127 program => 'FNDPMPRPT',
128 argument1 => PROF_RUNID,
129 argument2 => RELATED_RUNID,
130 argument3 => PURGE_DATA_L);
131 COMMIT;
132 ELSE
133 PROF_REQID := -1;
134 END IF;
135 RETURN PROF_REQID;
136
137 EXCEPTION
138 WHEN OTHERS THEN
139 RETURN -1;
140 END SUBMIT_PROFILER_REPORT;
141
142
143 procedure START_TRACE(TRACE_TYPE in NUMBER) IS
144 l_trc_cmd varchar2(200);
145 l_pl_str varchar2(200);
146 err_code number;
147 curr_trace_level number:=0;
148
149 -- changes done for bug 11077620
150 err_flag varchar2(1) := 'N';
151 EVENT_SYNTAX_ERROR EXCEPTION;
152 PRAGMA EXCEPTION_INIT (EVENT_SYNTAX_ERROR, -49100);
153
154 PRAGMA AUTONOMOUS_TRANSACTION;
155
156 BEGIN
157 SET_IDENT_FOR_SESS(TRACE_TYPE);
158
159 IF(TRACE_TYPE < 20) THEN -- < 20 reserved for sql trace levels
160 curr_trace_level:=get_trace_level(TRACE_TYPE);
161 IF (TRACE_TYPE > curr_trace_level) THEN
162
163 -- changes done for bug 11077620
164 -- version >= 11gR1 add plan_stat=adaptive
165 IF (db_versn >= 111 ) then
166 begin
167 CASE TRACE_TYPE
168 WHEN 4 THEN l_trc_cmd:= 'alter session set events ''sql_trace bind=true, plan_stat=adaptive'''; EXECUTE IMMEDIATE l_trc_cmd;
169 WHEN 8 THEN l_trc_cmd:= 'alter session set events ''sql_trace wait=true, plan_stat=adaptive'''; EXECUTE IMMEDIATE l_trc_cmd;
170 WHEN 12 THEN l_trc_cmd:= 'alter session set events ''sql_trace wait=true, bind=true, plan_stat=adaptive'''; EXECUTE IMMEDIATE l_trc_cmd;
171 ELSE null; -- it should never fall through this branch.
172 END CASE;
173 exception
174 when EVENT_SYNTAX_ERROR then
175 err_flag := 'Y'; -- Fails for 11gR1 if patch is not applied
176 end;
177 end if;
178
179 IF (db_versn < 111 or (db_versn = 111 and err_flag = 'Y') ) then
180
181 l_trc_cmd:='alter session set events=''10046 trace name context forever, level '
182 ||trace_type||'''';
183 EXECUTE IMMEDIATE l_trc_cmd;
184 END IF;
185
186 END IF;
187 ELSIF (TRACE_TYPE=10941) THEN
188 IF PROF_FLAG = 'N' THEN -- Profiler has not been started yet.
189
190 SELECT nvl(S.MODULE,'MOD_NA')
191 INTO MODULE_G
192 FROM V$SESSION S
193 WHERE AUDSID=USERENV('SESSIONID');
194
195
196 IF (FND_GLOBAL.LOGIN_ID > 0) then
197 RELATED_RUNID:=FND_GLOBAL.LOGIN_ID;
198 ELSE
199 RELATED_RUNID:=SPID;
200 END IF;
201
202 DLOG('Related Run Id is '||RELATED_RUNID);
203
204 l_pl_str:='begin dbms_profiler.start_profiler(nvl(:IDENT_STRING_G,''PROFILER''),:MODULE_G,:PROF_RUNID); end;';
205 PROF_FLAG:='Y';
206 BEGIN
207 EXECUTE IMMEDIATE l_pl_str USING IN IDENT_STRING_G,
208 IN MODULE_G,
209 OUT PROF_RUNID;
210 dlog('Started the PL/SQL profiler for runid '||prof_runid);
211 -- Manually stamp the related run column
212 l_pl_str:='update plsql_profiler_runs set related_run=:RELATED_RUNID '||
213 'where runid=:PROF_RUNID';
214 EXECUTE IMMEDIATE l_pl_str USING IN RELATED_RUNID, IN PROF_RUNID;
215 commit;
216 -- If db is 10g, also enable plstimer
217 IF GET_DB_VERSION > 9 THEN
218 l_pl_str:='alter session set events=''10928 trace name context forever, level 1024''';
219 EXECUTE IMMEDIATE l_pl_str ;
220 END IF;
221 EXCEPTION
222 WHEN OTHERS THEN
223 PROF_FLAG:='E';
224 dlog('Exception occured while starting the PL/SQL profiler.');
225 END;
226
227 ELSIF (PROF_FLAG='Y') THEN
228 l_pl_str:='begin :err_code:=dbms_profiler.flush_data; end;';
229 dlog('Executing call to Flush Profiler');
230 EXECUTE IMMEDIATE l_pl_str USING OUT ERR_CODE;
231 IF ERR_CODE =2 THEN
232 DLOG('Error occured while writing to Profiler Tables. Error Ocde - 2');
233 ELSE
234 DLOG('Error occured while flushing profiler data. Error code returned by call - '||err_code);
235 END IF;
236 END IF;
237 ELSIF (TRACE_TYPE=10928) THEN
238 l_trc_cmd:='alter session set events=''10928 trace name context forever, level 1''';
239 EXECUTE IMMEDIATE l_trc_cmd;
240 ELSIF (TRACE_TYPE=10053) THEN
241 l_trc_cmd:='alter session set events=''10053 trace name context forever, level 1''';
242 EXECUTE IMMEDIATE l_trc_cmd;
243 ELSE
244 null;
245 -- log message , should never fall thru to this branch.
246 END IF;
247
248 END;
249
250 procedure START_TRACE(TRACE_TYPE in NUMBER,
251 SESSION_ID in NUMBER,
252 SERIAL# in NUMBER) IS
253 BEGIN
254
255 IF ((TRACE_TYPE=10053) OR (TRACE_TYPE=10046) OR (TRACE_TYPE=10928)) THEN
256 SET_IDENT_FOR_SESS(TRACE_TYPE);
257 END IF;
258
259
260 IF(TRACE_TYPE < 20) THEN -- < 20 reserved for sql trace levels
261
262 DBMS_SYSTEM.SET_EV(SESSION_ID,SERIAL#,10046,TRACE_TYPE,'');
263
264 ELSIF ((TRACE_TYPE=10053) OR (TRACE_TYPE=10928) OR (TRACE_TYPE=10941)) THEN
265 DBMS_SYSTEM.SET_EV(SESSION_ID_G,SERIAL#_G,TRACE_TYPE,1,'');
266 ELSE
267 null;
268 -- log message , should never fall thru to this branch.
269 END IF;
270
271 END;
272
273
274 procedure STOP_TRACE(TRACE_TYPE in NUMBER) IS
275 l_trc_cmd varchar2(200);
276 l_pl_str varchar2(200);
277 l_reqid number;
278 BEGIN
279
280 IF(TRACE_TYPE < 20) THEN
281 IDENT_FLAG:='N';
282 IDENT_STRING_G:=' ';
283 -- l_trc_cmd:='ALTER SESSION SET SQL_TRACE=FALSE';
284 l_trc_cmd:='alter session set events=''10046 trace name context off''';
285 EXECUTE IMMEDIATE l_trc_cmd;
286 ELSIF ( (TRACE_TYPE=10941) AND (PROF_FLAG='Y')) THEN
287 l_pl_str:='begin dbms_profiler.stop_profiler; end;';
288 BEGIN
289 EXECUTE IMMEDIATE l_pl_str ;
290 dlog('Stopped the PL/SQL profiler for runid '||prof_runid);
291 PROF_FLAG:='N';
292 -- If db version is 10g or greater, also switch off plstimer
293 IF GET_DB_VERSION > 9 THEN
294 l_pl_str:='alter session set events=''10928 trace name context off''';
295 EXECUTE IMMEDIATE l_pl_str ;
296 END IF;
297 EXCEPTION
298 WHEN OTHERS THEN
299 PROF_FLAG:='E';
300 dlog('Exception occured while stopping profiler');
301 END;
302 ELSIF (TRACE_TYPE=10928) THEN
303 l_trc_cmd:='alter session set events=''10928 trace name context off''';
304 ELSIF (TRACE_TYPE=10053) THEN
305 l_trc_cmd:='alter session set events=''10053 trace name context off''';
306 EXECUTE IMMEDIATE l_trc_cmd;
307 ELSE
308 l_trc_cmd:='select sysdate into l_trc_cmd from dual';
309 -- log message , should never fall thru to this branch.
310 END IF;
311 END;
312
313 procedure STOP_TRACE(TRACE_TYPE in NUMBER,
314 SESSION_ID in NUMBER,
315 SERIAL# in NUMBER) IS
316 BEGIN
317
318
319 IF(TRACE_TYPE < 20) THEN -- < 20 reserved for sql trace levels
320
321 DBMS_SYSTEM.SET_EV(SESSION_ID,SERIAL#,10046,0,'');
322 ELSIF ((TRACE_TYPE=10053) OR (TRACE_TYPE=10928)) THEN
323 DBMS_SYSTEM.SET_EV(SESSION_ID,SERIAL#,TRACE_TYPE,0,'');
324 ELSIF (TRACE_TYPE=10941) THEN
325 -- level 8 stands for "Store data in db and Stop"
326 DBMS_SYSTEM.SET_EV(SESSION_ID,SERIAL#,TRACE_TYPE,8,'');
327
328 END IF;
329
330 END;
331
332
333
334 procedure SET_TRACE_IDENTIFIER(IDENTIFIER_STRING in VARCHAR2) IS
335
336 BEGIN
337 IF (nvl(IDENT_STRING_G,' ') <> IDENTIFIER_STRING) THEN
338 IDENT_FLAG:='N'; -- new identifier has been set, reset flag
339 END IF;
340 IDENT_STRING_G := IDENTIFIER_STRING;
341 IDENT_STRING_O := IDENTIFIER_STRING; -- set the override identifier
342 END;
343
344 procedure SET_MAX_DUMP_FILE_SIZE(TRACEFILE_SIZE in NUMBER) IS
345 l_dump_cmd varchar2(100);
346 BEGIN
347 l_dump_cmd:='alter session set max_dump_file_size='||tracefile_size||' M';
348 EXECUTE IMMEDIATE l_dump_cmd;
349 END;
350
351
352
353 function GET_TRACE_IDENTIFIER RETURN VARCHAR2 IS
354 BEGIN
355
356 IF IDENT_STRING_G IS NOT NULL THEN
357 RETURN IDENT_STRING_G;
358 ELSE
359 RETURN 'NOT_SET'; -- actually should never fall thru to this.
360 END IF;
361
362 END;
363
364 function GET_TRACE_FILENAME RETURN VARCHAR2 IS
365 l_filename varchar2(256);
366 BEGIN
367 BEGIN
368 EXECUTE IMMEDIATE
369 'begin :l_filename:=fnd_debug_util.get_trace_file_name(); end;'
370 USING OUT l_filename;
371
372 EXCEPTION
373 WHEN OTHERS THEN
374 l_filename:='FND_DEBUG_UTILS_NOT_INSTALLED';
375 END;
376
377 RETURN l_filename;
378
379 END;
380
381 function IS_TRACE_ENABLED(TRACE_TYPE in NUMBER) RETURN BOOLEAN IS
382
383 EVENT_LEVEL NUMBER;
384 TRACE_ON BOOLEAN;
385 BEGIN
386
387 IF(TRACE_TYPE < 20) THEN
388 DBMS_SYSTEM.READ_EV(10046,EVENT_LEVEL);
389 ELSIF TRACE_TYPE=10941 THEN
390 IF PROF_FLAG='Y' THEN
391 EVENT_LEVEL:=1;
392 ELSE
393 EVENT_LEVEL:=0;
394 END IF;
395 ELSE
396 DBMS_SYSTEM.READ_EV(TRACE_TYPE,EVENT_LEVEL);
397 END IF;
398
399 IF EVENT_LEVEL > 0 THEN
400 TRACE_ON:= TRUE;
401 ELSE
402 TRACE_ON:= FALSE;
403 END IF;
404 RETURN TRACE_ON;
405 END;
406
407 function GET_TRACE_LEVEL(TRACE_TYPE in NUMBER) RETURN NUMBER IS
408
409 EVENT_LEVEL NUMBER:=0;
410 BEGIN
411
412 IF(TRACE_TYPE < 20) THEN
413 DBMS_SYSTEM.READ_EV(10046,EVENT_LEVEL);
414 ELSIF TRACE_TYPE=10941 THEN
415 IF PROF_FLAG='Y' THEN
416 EVENT_LEVEL:=1;
417 ELSE
418 EVENT_LEVEL:=0;
419 END IF;
420 ELSE
421 DBMS_SYSTEM.READ_EV(TRACE_TYPE,EVENT_LEVEL);
422 END IF;
423
424 RETURN EVENT_LEVEL;
425
426 END;
427
428 function GET_TRACE_ID (TRACE_TYPE in NUMBER) RETURN NUMBER IS
429 BEGIN
430 -- for sql trace types return spid
431 -- for plsql profiler return runid
432 -- for cbo trace return spid
433
434 IF((TRACE_TYPE < 20) OR (TRACE_TYPE=10053) OR (TRACE_TYPE=10928)) THEN
435 IF(SPID IS NOT NULL) THEN
436 RETURN SPID;
437 ELSE
438 RETURN 0;
439 END IF;
440 ELSIF TRACE_TYPE=10941 THEN
441 RETURN PROF_RUNID;
442 END IF;
443 END;
444
445 BEGIN -- Package Initialization section
446 -- Removed module from here as it gets reset for each form.
447
448 SELECT S.SID,S.SERIAL#,P.SPID
449 INTO SESSION_ID_G,SERIAL#_G,SPID
450 FROM V$SESSION S, V$PROCESS P
451 WHERE S.PADDR=P.ADDR
452 AND AUDSID=USERENV('SESSIONID');
453
454 -- changes done for bug 11077620
455 -- Check the database version.
456 -- version >= 11gR2 add plan_stat=adaptive
457 SELECT REPLACE(SUBSTR(version,1,instr(version,'.',1,2)-1),'.')
458 INTO db_versn
459 FROM v$instance;
460
461
462
463 EXECUTE IMMEDIATE 'alter session set max_dump_file_size=unlimited';
464
465 END FND_TRACE;
[Home] [Help]