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