DBA Data[Home] [Help]

PACKAGE BODY: APPS.FND_DEBUG_UTIL

Source


1 package body FND_DEBUG_UTIL as
2 /* $Header: AFCPDBUB.pls 120.2 2005/08/19 19:48:36 tkamiya ship $ */
3 
4   --
5   -- PUBLIC VARIABLES
6   --
7 
8   -- Exceptions
9 
10   -- Exception Pragmas
11 
12   --
13   -- PRIVATE ROUTINES
14   --
15   --
16   -- Name
17   --   get_db_version
18   -- Purpose
19   --   get the current database version
20   --
21   --   return string which will contain the db version
22   --
23   function get_db_version
24     return varchar2 is
25 
26     dbversion varchar2(64);
27   begin
28     begin
29       select version
30         into dbversion
31         from v$instance;
32 
33       exception
34         when others then
35            null;
36     end;
37     return dbversion;
38   end;
39 
40   --
41   -- Name
42   --   construct_trace_file_name
43   -- Purpose
44   --   constructs the trace file name
45   --   depending on the oracle service id,
46   --   process id, platform and dbversion
47   --   the actual trace file name being used
48   --   to dump the trace
49   --
50   -- returns a string containing the trace file name
51   --
52   function construct_trace_file_name (sid       in varchar2,
53                                       spid      in varchar2,
54                                       platform  in varchar2,
55                                       dbver     in varchar2) return varchar2 is
56 
57     fname       varchar2(256);
58     upplatform  varchar2(64);
59     t_ident     varchar2(80);
60 
61   begin
62 
63     select DECODE(value, NULL, NULL, '_' || value)
64       into t_ident
65       from v$parameter
66      where name='tracefile_identifier';
67 
68     fname := sid || '_ora_' || spid || t_ident || '.trc';
69 
70     if (dbver is not null and
71         substr(dbver, 1, 5) = '8.1.7'
72         and platform is not null) then
73 
74       upplatform := upper(platform);
75       if (instr(upplatform, 'LINUX') >= 1) then
76        fname := 'ora_' || spid || t_ident || '.trc';
77       elsif (instr(upplatform, 'WIN_NT') >= 1) then
78        fname := 'ora0' || spid || '.trc';
79       elsif (instr(upplatform, 'HP') >= 1) then
80        fname := 'ora_' || spid || '_' || sid || t_ident || '.trc';
81       elsif (instr(upplatform, 'AIX') >= 1) then
82        fname := 'ora_' || spid || '_' || sid || t_ident || '.trc';
83       else
84        fname := 'ora_' || spid || t_ident || '.trc';
85       end if;
86     end if;
87 
88     return fname;
89   end;
90 
91   --
92   -- PUBLIC FUNCTIONS
93   --
94 
95   --
96   -- Name
97   --   get_trace_file_name
98   -- Purpose
99   --   get the trace file name
100   --   for the current database session
101   --   including the directory path ex ($ORACLE_HOME/log/udump/ora_8085.trc)
102   --
103   --   return string which will contain the trace file name
104   --
105   function get_trace_file_name return varchar2 is
106 
107     CURSOR CURTFNAME is
108       select name, value
109         from v$parameter
110         where name='db_name' or name like 'user_dump%';
111 
112     CURSOR CURPID (aud_sid number) is
113       select p.spid
114         from v$session s, v$process p
115         where s.audsid = aud_sid and s.paddr=p.addr;
116 
117     db_name           varchar2(64);
118     path              varchar2(512);
119     name              varchar2(64);
120     trace_file_name   varchar2(512);
121     spid              varchar2(12);
122     node              varchar2(64);
123     platform          varchar2(64);
124     dbver             varchar2(32);
125     audsid            number;
126     dir_sep	          varchar2(2) := '/';
127 
128   begin
129 
130     select userenv('SESSIONID') into audsid from dual;
131 
132     for file_rec in CURTFNAME loop
133       name := file_rec.name;
134 
135       if (lower(name) = 'db_name') then
136         db_name := file_rec.value;
137       else
138         path := file_rec.value;
139       end if;
140     end loop;
141 
142     for pid_rec in CURPID(audsid) loop
143       spid := pid_rec.spid;
144     end loop;
145 
146 
147     -- get platform
148     select dbms_utility.port_string into platform from dual;
149     -- dbms_output.put_line('Platform : ' || platform);
150 
151     -- get db version
152     dbver := get_db_version;
153 
154     trace_file_name := construct_trace_file_name(db_name, spid, platform, dbver);
155     -- dbms_output.put_line('Trace File Name : ' || trace_file_name);
156 
157 
158     if(substr(path,1,1) = '?') then
159       path := '$ORACLE_HOME' || substr(path,2);
160     end if;
161 
162     select DECODE(instr(upper(platform), 'WIN_NT') , 0, '/', '\')
163       into dir_sep
164       from dual;
165 
166     trace_file_name := path || dir_sep || trace_file_name;
167 
168     return trace_file_name;
169 
170   end;
171 
172   --
173   -- Name
174   --   get_trace_file_node
175   -- Purpose
176   --   get the node m/c name on which trace file will be generated
177   --   for the current session
178   --
179   --   return string which will contain the node name
180   --
181   function get_trace_file_node return varchar2 is
182 
183     node_name     varchar2(64);
184 
185     begin
186 
187       select host_name
188         into node_name
189         from v$instance;
190 
191     return node_name;
192 
193       exception
194          when others then
195            null;
196   end;
197 
198   --
199   -- Name:
200   --   STOP_PLSQL_PROFILER
201   -- Description:
202   --   This procedure will stop PLSQL profiler by calling FND_TRACE
203   --   and submit concurrent request to generate profiler output.
204   --   It updates the request_id in fnd_debug_rule_executions table as
205   --   log_request_id.
206 
207   procedure stop_plsql_profiler is
208       sql_str  varchar2(500);
209       req_id   number;
210   begin
211       -- Stop plsql profiler by calling fnd_trace.
212       -- using dynamic sql to avoid compile time dependency.
213       sql_str := 'begin fnd_trace.stop_trace(fnd_trace.plsql_prof); end;';
214 
215       execute immediate sql_str;
216 
217       -- submit request for pl/sql profiler output.
218       sql_str := 'begin :1 := fnd_trace.submit_profiler_report; end; ';
219 
223       update fnd_debug_rule_executions
220       execute immediate sql_str using out req_id;
221 
222       -- update fnd_debug_rule_executions table with request_id
224          set log_request_id = req_id
225        where transaction_id = fnd_log.g_transaction_context_id
226          and debug_option_name = 'PLSQL_PROFILER';
227 
228       exception
229          when others then
230             fnd_message.set_name ('FND', 'SQL-Generic error');
231             fnd_message.set_token ('ERRNO', sqlcode, FALSE);
232             fnd_message.set_token ('REASON', sqlerrm, FALSE);
233             fnd_message.set_token ('ROUTINE',
234 				   'FND_DEBUG_UTIL.STOP_PLSQL_PROFILER', FALSE);
235             if (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
236             fnd_log.message(FND_LOG.LEVEL_EXCEPTION,
237                           'fnd.plsql.FND_DEBUG_UTIL.STOP_PLSQL_PROFILER.others',
238                             FALSE);
239             end if;
240 
241   end;
242 
243   --
244   -- Name : Enable_logging
245   -- Description:
246   --    Enable Logging with a given log level.
247   --
248   procedure enable_logging( log_level IN number) is
249   begin
250      -- set the logging level based on current runtime level.
251      -- we will allow from EXCEPTION to STATEMENT but not from
252      -- STATEMENT to EXCEPTION, etc.
253      if ( FND_LOG.G_CURRENT_RUNTIME_LEVEL > log_level ) then
254 
255          fnd_profile.put('AFLOG_ENABLED', 'Y');
256          fnd_profile.put('AFLOG_MODULE', '%');
257          fnd_profile.put('AFLOG_LEVEL', to_char(log_level));
258          fnd_profile.put('AFLOG_FILENAME', '');
259          fnd_log_repository.init;
260 
261      end if;
262 
263      -- ignore all exceptions
264      exception
265          when others then
266             fnd_message.set_name ('FND', 'SQL-Generic error');
267             fnd_message.set_token ('ERRNO', sqlcode, FALSE);
268             fnd_message.set_token ('REASON', sqlerrm, FALSE);
269             fnd_message.set_token ('ROUTINE',
270                                    'FND_DEBUG_UTIL.enable_logging', FALSE);
271             if (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
272             fnd_log.message(FND_LOG.LEVEL_EXCEPTION,
273                           'fnd.plsql.FND_DEBUG_UTIL.ENABLE_LOGGING.others',                            FALSE);
274             end if;
275   end;
276 
277 end FND_DEBUG_UTIL;