[Home] [Help]
PACKAGE BODY: APPS.ETRM_PNAV
Source
1 package body etrm_pnav as
2 ----------------------------------------------------------------------------
3 -- Copyright A? 2001, 2014, Oracle and/or its affiliates. All rights reserved.
4 -- eTRM: Oracle Applications repository browser and dependency report
5 -- Author: Peter Goldthorp 9 October 2001
6 -----------------------------------------------------------------------------
7
8 -----------------------------------------------------------------------------
9 -- Private Procedures
10 -----------------------------------------------------------------------------
11
12 function get_object_id(c_name in dba_objects.object_name%type
13 , c_owner in dba_objects.owner%type
14 , c_type in dba_objects.object_type%type)
15 return dba_objects.object_id%type is
16
17 cursor cur_obj(c_name in dba_objects.object_name%type
18 , c_owner in dba_objects.owner%type
19 , c_type in dba_objects.object_type%type) is
20 select object_id
21 from dba_objects
22 where object_name = c_name
23 and object_type = c_type
24 and owner = c_owner;
25
26 v_return dba_objects.object_id%type := 0;
27
28 begin
29 for c_rec in cur_obj(c_name, c_owner, c_type) loop
30 v_return := c_rec.object_id;
31 end loop;
32 return v_return;
33 end get_object_id;
34
35
36 procedure draw_buttons
37 is
38 begin
39 uiutil.a_href_gen(c_link => 'etrm_search.search'
40 , c_display => 'Home');
41 -- uiutil.a_href_gen(c_link => 'https://login.oracle.com/pls/orasso/orasso.wwsso_app_admin.ls_logout?p_done_url=http://etrm.oracle.com/'
42 -- , c_display => 'Sign out');
43 uiutil.a_javascript_gen(c_link => 'etrm_search.help'
44 , c_display => 'Help');
45 end draw_buttons;
46
47 procedure draw_tabs
48 is
49 v_cookie owa_cookie.cookie;
50 v_fndnav_url varchar2(1024);
51 v_search_url varchar2(1024);
52 begin
53
54 v_cookie := owa_cookie.get('etrm_fndnav_context');
55 if (v_cookie.num_vals >0) THEN
56 v_fndnav_url := 'etrm_fndnav.ls_object?'||utl_url.escape(v_cookie.vals(1));
57 else
58 v_fndnav_url := 'etrm_fndnav.ls_apps';
59 end if;
60
61 v_cookie := owa_cookie.get('etrm_search_context');
62 if (v_cookie.num_vals >0) THEN
63 v_search_url := 'etrm_search.search?c_search='||utl_url.escape(v_cookie.vals(1));
64 else
65 v_search_url := 'etrm_search.search';
66 end if;
67
68
69
70 -- uiutil.render_tab(c_url => v_search_url
71 -- , c_title => 'Search'
72 -- , c_position => 'LEFT'
73 -- , c_state => 'ENABLED');
74
75 uiutil.render_tab(c_url => v_fndnav_url
76 , c_title => 'FND'
77 , c_position => 'LEFT'
78 , c_state => 'ENABLED');
79
80 uiutil.render_tab(c_url => 'etrm_pnav.ls_apps'
81 , c_title => 'DBA'
82 , c_position => 'RIGHT'
83 , c_state => 'SELECTED');
84 -- htp.p('<br>');
85 end draw_tabs;
86
87 procedure quick_list is
88
89 cursor cur_version is
90 select release_name
91 from fnd_product_groups
92 order by release_name;
93
94 v_version fnd_product_groups.release_name%type;
95 begin
96 v_version := '11i';
97 for v_rec in cur_version loop
98 v_version := v_rec.release_name;
99 end loop;
100
101 htp.p('<h1>'||v_version||' DBA Data</h1>');
102
103 end quick_list;
104
105 procedure draw_form(c_name in dba_objects.object_name%type := '%'
106 , c_owner in dba_objects.owner%type := '%'
107 , c_type in dba_objects.object_type%type := '%'
108 , c_status in dba_objects.status%type := '%')
109 is
110 begin
111
112 if (c_name = '%'
113 and c_owner = '%'
114 and c_type = '%'
115 and c_status = '%') then
116 htp.p('<p>Browse the online data dictionary of an Applications database</p>');
117 else
118 null;
119 -- htp.p('<p><a HREF="etrm_pnav.ls_object?c_name=*&c_type=*&c_owner=*&c_status=*"><img src="/images/repository.gif" border=0 alt="Home">DBA Navigator Home</a>');
120 end if;
121
122 htp.p('<FORM ACTION="etrm_pnav.ls_object" METHOD="POST">');
123 htp.p('<TABLE cellpadding="0" cellspacing="0" summary="">');
124
125 htp.p('<tr><td><label FOR="name"> Object Name <i>like</i>:</td> ');
126 htp.p('<td><INPUT TYPE="text" id="name" NAME="c_name" SIZE="30" maxlength="80" value="'
127 ||FND_CSS_PKG.Encode(c_name)
128 ||'"></label></td></tr>');
129
130 htp.p('<tr><td><label FOR="type"> Object Type:</td> ');
131 htp.p('<td><SELECT id="type" NAME="c_type" SIZE="1">');
132 htp.p(' <OPTION selected VALUE="'||FND_CSS_PKG.Encode(c_type)||'">'||FND_CSS_PKG.Encode(c_type));
133 if c_type = '%'
134 then null;
135 else
136 htp.p(' <OPTION VALUE="%">%');
137 end if;
138 htp.p(' <OPTION VALUE="CLUSTER">CLUSTER');
139 htp.p(' <OPTION VALUE="CONSUMER GROUP">CONSUMER GROUP');
140 htp.p(' <OPTION VALUE="CONTEXT">CONTEXT');
141 htp.p(' <OPTION VALUE="DIMENSION">DIMENSION');
142 htp.p(' <OPTION VALUE="DIRECTORY">DIRECTORY');
143 htp.p(' <OPTION VALUE="EVALUATION CONTEXT">EVALUATION CONTEXT');
144 htp.p(' <OPTION VALUE="FUNCTION">FUNCTION');
145 htp.p(' <OPTION VALUE="INDEX">INDEX');
146 htp.p(' <OPTION VALUE="INDEX PARTITION">INDEX PARTITION');
147 htp.p(' <OPTION VALUE="INDEX SUBPARTITION">INDEX SUBPARTITION');
148 htp.p(' <OPTION VALUE="INDEXTYPE">INDEXTYPE');
149 htp.p(' <OPTION VALUE="JAVA CLASS">JAVA CLASS');
150 htp.p(' <OPTION VALUE="JAVA DATA">JAVA DATA');
151 htp.p(' <OPTION VALUE="JAVA RESOURCE">JAVA RESOURCE');
152 htp.p(' <OPTION VALUE="JAVA SOURCE">JAVA SOURCE');
153 htp.p(' <OPTION VALUE="LIBRARY">LIBRARY');
154 htp.p(' <OPTION VALUE="LOB">LOB');
155 htp.p(' <OPTION VALUE="LOB PARTITION">LOB PARTITION');
156 htp.p(' <OPTION VALUE="LOB SUBPARTITION">LOB SUBPARTITION');
157 htp.p(' <OPTION VALUE="LOCATION">LOCATION');
158 htp.p(' <OPTION VALUE="MATERIALIZED VIEW">MATERIALIZED VIEW');
159 htp.p(' <OPTION VALUE="NEXT OBJECT">NEXT OBJECT');
160 htp.p(' <OPTION VALUE="OPERATOR">OPERATOR');
161 htp.p(' <OPTION VALUE="PACKAGE">PACKAGE');
162 htp.p(' <OPTION VALUE="PACKAGE BODY">PACKAGE BODY');
163 htp.p(' <OPTION VALUE="PROCEDURE">PROCEDURE');
164 htp.p(' <OPTION VALUE="QUEUE">QUEUE');
165 htp.p(' <OPTION VALUE="RESOURCE PLAN">RESOURCE PLAN');
166 htp.p(' <OPTION VALUE="RULE">RULE');
167 htp.p(' <OPTION VALUE="RULE SET">RULE SET');
168 htp.p(' <OPTION VALUE="SECURITY PROFILE">SECURITY PROFILE');
169 htp.p(' <OPTION VALUE="SEQUENCE">SEQUENCE');
170 htp.p(' <OPTION VALUE="SUBSCRIPTION">SUBSCRIPTION');
171 htp.p(' <OPTION VALUE="SYNONYM">SYNONYM');
172 htp.p(' <OPTION VALUE="TABLE">TABLE');
173 htp.p(' <OPTION VALUE="TABLE PARTITION">TABLE PARTITION');
174 htp.p(' <OPTION VALUE="TABLE SUBPARTITION">TABLE SUBPARTITION');
175 htp.p(' <OPTION VALUE="TRIGGER">TRIGGER');
176 htp.p(' <OPTION VALUE="TYPE">TYPE');
177 htp.p(' <OPTION VALUE="TYPE BODY">TYPE BODY');
178 htp.p(' <OPTION VALUE="UNDEFINED">UNDEFINED');
179 htp.p(' <OPTION VALUE="VIEW">VIEW');
180 htp.p(' <OPTION VALUE="XML SCHEMA">XML SCHEMA');
181 htp.p('</select>');
182 htp.p('</label></td></tr>');
183
184 htp.p('<tr><td><label for="owner"> Owner <i>like</i>:</td> ');
185 htp.p('<td><INPUT TYPE="text" id="owner" NAME="c_owner" SIZE=30 maxlength=80 value="'
186 ||FND_CSS_PKG.Encode(c_owner)
187 ||'"></label></td></tr>');
188
189 htp.p('<tr><td><label for="status"> Status:</td> ');
190 htp.p('<td><SELECT id="status" NAME="c_status" SIZE="1">');
191 htp.p(' <OPTION selected VALUE="'||FND_CSS_PKG.Encode(c_status)||'">'||FND_CSS_PKG.Encode(c_status));
192 if c_status = '%'
193 then null;
194 else
195 htp.p(' <OPTION VALUE="%">%');
196 end if;
197 htp.p(' <OPTION VALUE="VALID">VALID');
198 htp.p(' <OPTION VALUE="INVALID">INVALID');
199 htp.p('</select>');
200 htp.p('</label></td></tr>');
201
202 htp.p('</TABLE>');
203 htp.p('<INPUT TYPE="submit" VALUE="Submit">');
204 htp.p('</FORM>');
205
206 --htp.p('c_name='||c_name||' c_owner='||c_owner||' c_type='||c_type);
207 end draw_form;
208
209
210 FUNCTION get_object_name(n_object_id in dba_objects.object_id%type
211 , c_mode in varchar2 := 'BOTH')
212 return varchar2
213 is
214
215 cursor cur_raw(n_object_id in dba_objects.object_id%type) is
216 select name
217 from sys.obj$
218 where obj# = n_object_id;
219
220 cursor cur_cons(n_object_id in dba_objects.object_id%type) is
221 select name
222 from sys.con$
223 where con# = n_object_id;
224
225
226
227
228
229 cursor cur_object(n_object_id in dba_objects.object_id%type) is
230 select object_name
231 , object_type
232 , owner
233 from dba_objects
234 where object_id = n_object_id;
235
236 v_return_value varchar2(512);
237
238 begin
239 if (upper(c_mode) = 'RAW'
240 or upper(c_mode) = 'ITEM') then
241 for o_rec in cur_raw(n_object_id) loop
242 v_return_value := o_rec.name;
243 end loop;
244 elsif upper(c_mode) = 'CONS' then
245 for o_rec in cur_cons(n_object_id) loop
246 v_return_value := o_rec.name;
247 end loop;
248 else
249 for o_rec in cur_object(n_object_id) loop
250 if upper(c_mode) = 'USER' then
251 v_return_value := o_rec.owner;
252 elsif upper(c_mode) = 'TYPE' then
253 v_return_value := o_rec.object_type;
254 else
255 v_return_value := o_rec.owner||'.'||o_rec.object_name;
256 end if;
257 end loop;
258 end if;
259 return v_return_value;
260 end get_object_name;
261
262
263
264 PROCEDURE a_href_gen(n_object_id in dba_objects.object_id%type)
265 is
266 cursor cur_object(n_object_id in dba_objects.object_id%type) is
267 select object_name
268 , object_type
269 , owner
270 from dba_objects
271 where object_id = n_object_id;
272 begin
273 for o_rec in cur_object(n_object_id) loop
274 uiutil.a_href_gen(c_type => o_rec.object_type
275 , c_link => 'etrm_pnav.show_object?c_name='
276 ||replace(o_rec.object_name,'+','%2B')
277 ||'&c_owner='
278 ||o_rec.owner
279 ||'&c_type='
280 ||o_rec.object_type
281 , c_display => o_rec.object_name);
282 end loop;
283 end a_href_gen;
284
285
286 procedure ls_count(c_name in dba_objects.object_name%type := '%'
287 , c_owner in dba_objects.owner%type := '%'
288 , c_type in dba_objects.object_type%type := '%'
289 , c_status in dba_objects.status%type := '%') is
290
291 cursor cur_count(c_name in dba_objects.object_name%type
292 , c_owner in dba_objects.owner%type
293 , c_type in dba_objects.object_type%type
294 , c_status in dba_objects.status%type := '%') is
295 select owner, object_type, count(object_name) object_count
296 from dba_objects
297 where object_name like c_name ESCAPE '\'
298 and object_type like c_type
299 and owner like c_owner
300 and status like c_status
301 group by owner, object_type
302 order by owner, object_type;
303
304 v_name varchar2(80);
305 v_httpname varchar2(80);
306 v_owner dba_objects.owner%type;
307 v_type dba_objects.object_type%type;
308 v_counter number(8) := 0;
309
310
311 begin
312 uiutil.cabo1;
313 uiutil.cabo2a;
314 etrm_pnav.draw_buttons;
315 uiutil.cabo2b;
316 etrm_pnav.draw_tabs;
317 uiutil.cabo3;
318 -- htp.p('<h3>Objects</h3>');
319
320 v_owner := 'foRce_mE_to_diff';
321 v_name := replace(c_name, '*', '%'); -- http drops trailing %
322 v_name := replace(v_name, '%23', '#');
323 v_httpname := replace(c_name, '%', '*');
324 v_httpname := replace(v_httpname, '#', '%23');
325 htp.p('<dl>');
326 htp.p('<dt><A HREF="etrm_pnav.ls_object"><img src="/images/repository.gif" alt="Root"></a></dt>');
327 for a_rec in cur_count(v_name
328 , replace(c_owner, '*', '%')
329 , c_type
330 , replace(c_status, '*', '%')) loop
331
332 if a_rec.owner = v_owner then
333 null;
334 else
335 v_owner := a_rec.owner;
336 htp.p('<dt>');
337 uiutil.a_href_gen(c_type => 'SCHEMA'
338 , c_link => 'etrm_pnav.ls_object?c_name='
339 || v_httpname
340 ||'&c_owner='
341 ||a_rec.owner
342 ||'&c_status='
343 || replace(c_status, '%', '*')
344 , c_display => a_rec.owner);
345 htp.p('</dt>');
346 end if;
347 htp.p('<dd>');
348 uiutil.a_href_gen(c_type => a_rec.object_type
349 , c_link => 'etrm_pnav.ls_object?c_name='
350 || v_httpname
351 ||'&c_owner='
352 ||a_rec.owner
353 ||'&c_type='
354 ||a_rec.object_type
355 ||'&c_status='
356 ||replace(c_status, '%', '*')
357 , c_display => a_rec.object_type
358 ||' ('
359 ||a_rec.object_count
360 ||')');
361 htp.p('</dd>');
362 v_counter := v_counter + a_rec.object_count;
363 end loop;
364 htp.p('<dt>'||v_counter ||' objects</dt>');
365 htp.p('</dl>');
366
367 uiutil.cabo4;
368 etrm_pnav.quick_list;
369 uiutil.cabo5;
373
370 -- draw_form(c_name, c_owner, c_type, c_status);
371 uiutil.cabo6;
372 end ls_count;
374 procedure ls_object_type(c_name in dba_objects.object_name%type := '%'
375 , c_owner in dba_objects.owner%type := '%'
376 , c_type in dba_objects.object_type%type := '%'
377 , c_status in dba_objects.status%type := '%') is
378
379 cursor cur_object(c_name in dba_objects.object_name%type
380 , c_owner in dba_objects.owner%type
381 , c_type in dba_objects.object_type%type
382 , c_status in dba_objects.status%type := '%') is
383 select owner, object_name
384 from dba_objects
385 where object_name like c_name ESCAPE '\'
386 and object_type like c_type
387 and owner like c_owner
388 and status like c_status
389 order by owner, object_name;
390
391 v_name varchar2(80);
392 v_httpname varchar2(80);
393 v_owner dba_objects.owner%type;
394 v_type dba_objects.object_type%type;
395 v_counter number(8) := 0;
396
397
398
399 begin
400 uiutil.cabo1;
401 uiutil.cabo2a;
402 etrm_pnav.draw_buttons;
403 uiutil.cabo2b;
404 etrm_pnav.draw_tabs;
405 uiutil.cabo3;
406 htp.p('<h3>'||c_type||'</h3>');
407 v_owner := 'foRce_mE_to_diff';
408 v_name := replace(c_name, '*', '%'); -- http drops trailing %
409 v_name := replace(v_name, '%23', '#');
410 v_httpname := replace(c_name, '%', '*');
411 v_httpname := replace(v_httpname, '#', '%23');
412
413
414 htp.p('<dl>');
415 htp.p('<dt><A HREF="etrm_pnav.ls_object"><img src="/images/repository.gif" alt="Root"></a></dt>');
416 for a_rec in cur_object(v_name
417 , replace(c_owner, '*', '%')
418 , c_type
419 , replace(c_status, '*', '%')) loop
420
421 if a_rec.owner = v_owner then
422 null;
423 else
424 v_owner := a_rec.owner;
425 htp.p('<dt>');
426 uiutil.a_href_gen(c_type => 'SCHEMA'
427 , c_link => 'etrm_pnav.ls_object?c_name='
428 || v_httpname
429 ||'&c_owner='
430 ||a_rec.owner
431 ||'&c_status='
432 ||replace(c_status, '%', '*')
433 , c_display => a_rec.owner);
434 htp.p('</dt>');
435 end if;
436 htp.p('<dd>');
437 uiutil.a_javascript_gen(c_type => c_type
438 , c_link => 'etrm_pnav.show_object?c_name='
439 || replace(replace(a_rec.object_name, '#', '%23'),'+', '%2B')
440 ||'&c_owner='
441 ||a_rec.owner
442 ||'&c_type='
443 ||c_type
444 , c_display => a_rec.object_name);
445 htp.p('</dd>');
446 v_counter := v_counter + 1;
447 end loop;
448 htp.p('<dt>'||v_counter ||' objects</dt>');
449 htp.p('</dl>');
450
451 uiutil.cabo4;
452 etrm_pnav.quick_list;
453 uiutil.cabo5;
454 -- draw_form(c_name, c_owner, c_type, c_status);
455 uiutil.cabo6;
456 end ls_object_type;
457
458
459 function dba_object_info(c_name in dba_objects.object_name%type
460 , c_owner in dba_objects.owner%type
461 , c_type in dba_objects.object_type%type)
462 return dba_objects.object_id%type is
463
464 cursor cur_object(c_name in dba_objects.object_name%type
465 , c_owner in dba_objects.owner%type
466 , c_type in dba_objects.object_type%type) is
467 select OWNER
468 , OBJECT_NAME
469 , SUBOBJECT_NAME
470 , OBJECT_ID
471 , DATA_OBJECT_ID
472 , OBJECT_TYPE
473 , CREATED
474 , LAST_DDL_TIME
475 , TIMESTAMP
476 , STATUS
477 , TEMPORARY
478 , GENERATED
479 , SECONDARY
480 from dba_objects
481 where object_name = c_name
482 and object_type = c_type
483 and owner = c_owner;
484
485 cursor cur_table(c_name in dba_objects.object_name%type)
486 is
487 select a.application_short_name
488 , a.application_id
489 , t.table_id
490 from fnd_tables t
491 , fnd_application a
492 where table_name = c_name
493 and t.application_id = a.application_id
494 order by a.application_short_name;
495
496
497 cursor cur_view(c_name in dba_objects.object_name%type)
498 is
499 select a.application_short_name
500 , a.application_id
501 , t.view_id
502 from fnd_views t
503 , fnd_application a
504 where view_name = c_name
505 and t.application_id = a.application_id
506 order by a.application_short_name;
510
507
508 v_return_val dba_objects.object_id%type := 0;
509 v_first BOOLEAN;
511 begin
512 for o_rec in cur_object(c_name, c_owner, c_type) loop
513 htp.p('<h5>Object Details</h5>');
514
515 htp.p('<TABLE cellpadding="0" cellspacing="0"
516 summary="dba_objects information">');
517 htp.prn('<tr><th scope="row" align="left"><b> Object Name: </b></th> ');
518 htp.p('<td>'||o_rec.object_name||'</td></tr>');
519
520 htp.prn('<tr><th scope="row" align="left"><b> Object Type: </b></th> ');
521 htp.p('<td>'||o_rec.object_type||'</td></tr>');
522
523 htp.prn('<tr><th scope="row" align="left"><b> Owner: </b></th> ');
524 htp.p('<td>'||o_rec.owner||'</td></tr>');
525
526 v_first := TRUE;
527 if o_rec.object_type = 'TABLE' then
528
529 for t_rec in cur_table(o_rec.object_name) loop
530 if v_first = TRUE
531 then v_first := FALSE;
532 htp.prn('<tr><th scope="row" align="left"><b> FND Design Data: </b></th><td> ');
533 else
534 htp.p('<br>');
535 end if;
536 uiutil.a_href_gen(c_type => 'TABLE'
537 , c_link => 'etrm_fndnav.show_object?n_tabid='
538 ||t_rec.table_id
539 ||'&n_appid='
540 ||t_rec.application_id
541 ||'&c_type=TABLE'
542 , c_display => t_rec.application_short_name||'.'||c_name);
543 end loop;
544 elsif o_rec.object_type = 'VIEW' then
545
546 for t_rec in cur_view(o_rec.object_name) loop
547 if v_first = TRUE
548 then v_first := FALSE;
549 htp.prn('<tr><th scope="row" align="left"><b> FND Design Data: </b></th><td> ');
550 else
551 htp.p('<br>');
552 end if;
553 uiutil.a_href_gen(c_type => 'VIEW'
554 , c_link => 'etrm_fndnav.show_object?n_tabid='
555 ||t_rec.view_id
556 ||'&n_appid='
557 ||t_rec.application_id
558 ||'&c_type=VIEW'
559 , c_display => t_rec.application_short_name||'.'||c_name);
560 end loop;
561
562 end if;
563 if v_first = FALSE then
564 htp.p('</td><tr>');
565 end if;
566
567 htp.prn('<tr><th scope="row" align="left"><b> Subobject Name: </b></th> ');
568 htp.p('<td>'||o_rec.subobject_name||'</td></tr>');
569 /*
570 htp.prn('<tr><th scope="row" align="left"><b> Object ID: </b></th> ');
571 htp.p('<td>'||o_rec.object_id||'</td></tr>');
572
573 htp.prn('<tr><th scope="row" align="left"><b> Data Object ID: </b></th> ');
574 htp.p('<td>'||o_rec.data_object_id||'</td></tr>');
575
576 htp.prn('<tr><th scope="row" align="left"><b> Created: </b></th> ');
577 htp.p('<td>'||o_rec.created||'</td></tr>');
578
579 htp.prn('<tr><th scope="row" align="left"><b> Last DDL Time: </b></th> ');
580 htp.p('<td>'||o_rec.last_ddl_time||'</td></tr>');
581
582 htp.prn('<tr><th scope="row" align="left"><b> Timestamp: </b></th> ');
583 htp.p('<td>'||o_rec.timestamp||'</td></tr>');
584 */
585 htp.prn('<tr><th scope="row" align="left"><b> Status: </b></th> ');
586
587 if o_rec.status = 'INVALID' then
588 htp.p('<td>');
589 uiutil.a_href_gen(c_type => 'EXCEPTION'
590 , c_link => 'etrm_pnav.show_details?c_name='
591 ||c_name
592 ||'&c_owner='
593 ||c_owner
594 ||'&c_type='
595 ||c_type
596 ||'&c_detail_type=exception'
597 , c_display => o_rec.status);
598 htp.p('</td><tr>');
599 else
600 htp.p('<td>'||o_rec.status||'</td></tr>');
601 end if;
602 /*
603 htp.prn('<tr><th scope="row" align="left"><b> Temporary: </b></th> ');
604 htp.p('<td>'||o_rec.temporary||'</td></tr>');
605
606 htp.prn('<tr><th scope="row" align="left"><b> Generated: </b></th> ');
607 htp.p('<td>'||o_rec.generated||'</td></tr>');
608
609 htp.prn('<tr><th scope="row" align="left"><b> Secondary: </b></th> ');
610 htp.p('<td>'||o_rec.secondary||'</td></tr>');
611 */
612 htp.p('</table>');
613 v_return_val := o_rec.object_id;
614 end loop;
615 return v_return_val;
616 end dba_object_info;
617
618 procedure ls_table(n_object_id in dba_objects.object_id%type) is
619 -- fk details from sys.cdef$ because dba_constraints id slow
620
621
622 cursor cur_fk(n_object_id in dba_objects.object_id%type) is
623 select etrm_pnav.get_object_name(robj#, 'RAW') table_name
624 , robj# object_id
625 , con# cons_id
626 from sys.cdef$
627 where obj# = n_object_id
628 and robj# is not null
629 order by etrm_pnav.get_object_name(robj#);
630
631 cursor cur_fk2(n_object_id in dba_objects.object_id%type) is
632 select etrm_pnav.get_object_name(obj#, 'RAW') table_name
633 , obj# object_id
634 , con# cons_id
638
635 from sys.cdef$
636 where robj# = n_object_id
637 order by etrm_pnav.get_object_name(obj#);
639
640 cursor cur_list_cons_columns(cv_cons_name in dba_cons_columns.constraint_name%type
641 , c_owner in dba_objects.owner%type)
642 -- list the columns for a given constraint
643 is select cc.column_name
644 from dba_cons_columns cc
645 where cc.constraint_name = cv_cons_name
646 and cc.owner = c_owner
647 order by cc.position;
648
649
650 v_first boolean;
651 v_first2 boolean;
652 v_table_name dba_tables.table_name%type;
653
654 begin
655
656 v_first := TRUE;
657 for key_rec in cur_fk(n_object_id) loop
658 if v_first = TRUE then
659 v_first := FALSE;
660 htp.p('<h5>Foreign Keys</h5>');
661 htp.p('<table cellpadding="1" cellspacing="0" border="1"
662 summary="Foreign keys from/to this table">
663 <tr class="OraTableColumnHeader" >
664
665 <th class="OraTableColumnHeader" id="tab">Table</th>
666 <th class="OraTableColumnHeader" id="join"> <br></th>
667 <th class="OraTableColumnHeader" id="ftab">Foreign Table</th>
668 <th class="OraTableColumnHeader" id="col">Foreign Key Column</th></tr>');
669
670 end if;
671 htp.p('<td class="OraTableCellText" headers="tab">');
672 v_table_name := etrm_pnav.get_object_name(n_object_id, 'RAW');
673 htp.p(v_table_name);
674 htp.p('</td>');
675
676 htp.p('<td class="OraTableCellText" headers="join">'
677 ||'<img src="/images/relationship_end.gif" alt="'
678 ||etrm_pnav.get_object_name(key_rec.cons_id, 'CONS')
679 || '"></td>');
680 htp.p('<td class="OraTableCellText" headers="ftab">');
681 uiutil.a_href_gen(c_type => 'TABLE'
682 , c_link => 'etrm_pnav.show_object?c_name='
683 || etrm_pnav.get_object_name(key_rec.object_id, 'RAW')
684 ||'&c_owner='
685 || etrm_pnav.get_object_name(key_rec.object_id, 'USER')
686 ||'&c_type=TABLE'
687 , c_display => key_rec.table_name);
688
689 htp.p('</td>');
690
691 v_first2 := TRUE;
692 htp.p('<td class="OraTableCellText" headers="col">');
693 for key_col in cur_list_cons_columns(
694 cv_cons_name => etrm_pnav.get_object_name(key_rec.cons_id, 'CONS')
695 , c_owner=>etrm_pnav.get_object_name(key_rec.object_id, 'USER')) loop
696 if v_first2 = TRUE
697 then v_first2 := FALSE;
698 else
699 htp.p('<br>');
700 end if;
701 htp.p(v_table_name);
702 htp.p('<b>'||key_col.column_name||'</b>');
703 end loop;
704 htp.p('</td></tr>');
705
706 end loop;
707
708 for key_rec in cur_fk2(n_object_id) loop
709 if v_first = TRUE then
710 v_first := FALSE;
711 htp.p('<h5>Foreign Keys</h5>');
712 htp.p('<table cellpadding="1" cellspacing="0" border="1"
713 summary="Foreign keys from/to this table">
714 <tr class="OraTableColumnHeader" >
715
716 <th class="OraTableColumnHeader" id="tab">Table</th>
717 <th class="OraTableColumnHeader" id="join"> <br></th>
718 <th class="OraTableColumnHeader" id="ftab">Foreign Table</th>
719 <th class="OraTableColumnHeader" id="col">Foreign Key Column</th></tr>');
720 end if;
721
722
723
724 htp.p('<td class="OraTableCellText" headers="tab">');
725 v_table_name := key_rec.table_name;
726 uiutil.a_href_gen(c_type => 'TABLE'
727 , c_link => 'etrm_pnav.show_object?c_name='
728 || etrm_pnav.get_object_name(key_rec.object_id, 'ITEM')
729 ||'&c_owner='
730 || etrm_pnav.get_object_name(key_rec.object_id, 'USER')
731 ||'&c_type=TABLE'
732 , c_display => key_rec.table_name);
733
734 htp.p('</td>');
735 htp.p('<td class="OraTableCellText" headers="join">'
736 ||'<img src="/images/relationship_end.gif" alt="'
737 ||etrm_pnav.get_object_name(key_rec.cons_id, 'CONS')
738 || '"></td>');
739 htp.p('<td class="OraTableCellText" headers="ftab">');
740 htp.p(etrm_pnav.get_object_name(n_object_id, 'RAW'));
741 htp.p('</td>');
742 htp.p('</td>');
743
744 v_first2 := TRUE;
745 htp.p('<td class="OraTableCellText" headers="col">');
746 for key_col in cur_list_cons_columns(
747 cv_cons_name => etrm_pnav.get_object_name(key_rec.cons_id, 'CONS')
748 , c_owner=>etrm_pnav.get_object_name(key_rec.object_id, 'USER')) loop
749 if v_first2 = TRUE
750 then v_first2 := FALSE;
751 else
752 htp.p('<br>');
753 end if;
754 htp.p(v_table_name);
755 htp.p('<b>'||key_col.column_name||'</b>');
759 end loop;
756 end loop;
757 htp.p('</td></tr>');
758
760
761 if v_first
762 then null;
763 else
764 htp.p('</table>');
765 end if;
766 end ls_table;
767
768
769 procedure ls_table(c_name dba_tables.table_name%type
770 , c_owner dba_tab_columns.owner%type) is
771
772 CURSOR cur_table_dets(c_name in dba_tables.table_name%type
773 , c_owner in dba_objects.owner%type)
774 -- List table details
775 IS select tab.table_name
776 , tab.tablespace_name
777 , tab.pct_free
778 , tab.pct_used
779 , tab.temporary
780 , tab.duration
781 from dba_tables tab
782 where table_name = c_name
783 and owner = c_owner
784 order by tab.table_name;
785 --
786
787 CURSOR cur_comment(c_name in dba_tables.table_name%type
788 , c_owner in dba_objects.owner%type)
789 is select comments
790 from dba_tab_comments
791 where owner = c_owner
792 and table_name = c_name
793 and (table_type = 'TABLE'
794 OR table_type = 'VIEW');
795
796 cursor cur_index(cv_tab_name in dba_indexes.table_name%type
797 , c_owner in dba_objects.owner%type)
798 -- List Indexes
799 is select ind.index_name
800 , ind.index_type
801 , ind.uniqueness
802 , ind.tablespace_name
803 , ind.pct_increase
804 , ind.pct_free
805 , ind.owner
806 from dba_indexes ind
807 where ind.table_name = cv_tab_name
808 and ind.table_owner = c_owner
809 order by ind.uniqueness desc, ind.index_name;
810 --
811 cursor cur_index_col(cv_index_name in dba_ind_columns.index_name%type
812 , c_owner in dba_objects.owner%type)
813 -- List index columns
814 is select ic.column_name
815 , ic.column_length
816 from dba_ind_columns ic
817 where ic.index_name = cv_index_name
818 and ic.index_owner = c_owner
819 order by ic.column_position;
820
821 cursor cur_function_index(cv_index_name in dba_ind_columns.index_name%type
822 , c_owner in dba_objects.owner%type)
823 is SELECT COLUMN_EXPRESSION
824 FROM SYS.DBA_IND_EXPRESSIONS
825 where INDEX_OWNER = c_owner
826 and INDEX_NAME = cv_index_name
827 order by COLUMN_POSITION;
828
829
830 --
831 cursor cur_primary_key(cv_table_name in dba_constraints.table_name%type
832 , c_owner in dba_objects.owner%type)
833 -- List the primary key
834 is select uc.constraint_name
835 from dba_constraints uc
836 where uc.table_name = cv_table_name
837 and uc.owner = c_owner
838 and uc.constraint_type = 'P'
839 order by uc.constraint_name;
840 --
841 cursor cur_unique_keys(cv_table_name in dba_constraints.table_name%type
842 , c_owner in dba_objects.owner%type)
843 -- List the unique key constraints
844 is select uc.constraint_name
845 from dba_constraints uc
846 where uc.table_name = cv_table_name
847 and uc.constraint_type = 'U'
848 and uc.owner = c_owner
849 order by uc.constraint_name;
850 --
851
852
853 --
854 cursor cur_list_cons_columns(cv_cons_name in dba_cons_columns.constraint_name%type
855 , c_owner in dba_objects.owner%type)
856 -- list the columns for a given constraint
857 is select cc.column_name
858 from dba_cons_columns cc
859 where cc.constraint_name = cv_cons_name
860 and cc.owner = c_owner
861 order by cc.position;
862
863 --
864 -- Main Body
865 --
866
867 v_first BOOLEAN := TRUE;
868 v_first2 BOOLEAN := TRUE;
869 unexpected_error EXCEPTION;
870 loop_counter INTEGER;
871 v_datetime varchar2(100);
872 v_user varchar2(30);
873 v_pk_name dba_constraints.constraint_name%type;
874 --
875 BEGIN
876 --
877
878 for c_rec in cur_comment(c_name, c_owner) loop
879 uiutil.txt_prn(c_rec.comments);
880 end loop;
881
882 v_first := TRUE;
883 FOR tab_rec IN cur_table_dets(c_name, c_owner) LOOP
884
885 if tab_rec.temporary = 'Y' then
886 htp.p('<h5>Temporary Table Details</h5>');
887 htp.p('<p>'||c_owner||'.'||c_name||' is a global temporary table. The current session is able see data that it placed in the table but other sessions cannot. Data
888 in the table is temporary. It has a data duration of '||tab_rec.duration||'.
889 Data is removed at the end of this period.</p>');
890 end if;
891
892
893 htp.p('<h5>Storage Details</h5>');
894 htp.p('<TABLE cellpadding="0" cellspacing="3"
895 summary="dba_tables information">');
896
897 htp.p('<tr><th scope="row" align="left"><b>Tablespace: </b></th><td>'
898 || uiutil.el_image('TS')|| tab_rec.tablespace_name||'</td></tr>');
899 htp.p('<tr><th scope="row" align="left"><b>PCT Free: </b></th><td>'
903 htp.p('</table>');
900 ||tab_rec.pct_free||'</td></tr>');
901 htp.p('<tr><th scope="row" align="left"><b>PCT Used: </b></th><td>'
902 || tab_rec.pct_used||'</td></tr>');
904 end loop;
905 --
906
907
908
909 for key_rec in cur_primary_key(c_name, c_owner) loop
910 htp.p('<h5>Primary Key: '|| key_rec.constraint_name || '</h5>');
911 htp.p('<ol>');
912 v_pk_name := key_rec.constraint_name;
913 for key_col in cur_list_cons_columns(key_rec.constraint_name, c_owner) loop
914 htp.p('<li> '
915 || uiutil.el_image('COL')
916 ||key_col.column_name
917 ||'</li>');
918 end loop;
919 htp.p('</ol>');
920 end loop;
921 --
922 for key_rec in cur_unique_keys(c_name, c_owner) loop
923 htp.p('<h5>Unique Key: '|| key_rec.constraint_name || '</h5>');
924 htp.p('<ol>');
925 for key_col in cur_list_cons_columns(key_rec.constraint_name, c_owner) loop
926 htp.p('<li> '
927 || uiutil.el_image('COL')
928 ||key_col.column_name
929 ||'</li>');
930 end loop;
931 htp.p('</ol>');
932 end loop;
933 --
934 v_first := TRUE;
935 for ind_rec in cur_index(c_name, c_owner) loop
936 if v_first = TRUE then
937 v_first := FALSE;
938 htp.p('<h5>Indexes</h5>');
939 htp.p('<table cellpadding="1" cellspacing="0" border="1"
940 summary="Indexes in this table">
941 <tr class="OraTableColumnHeader" >
942 <th class="OraTableColumnHeader" id="name">Index</th>
943 <th class="OraTableColumnHeader" id="type">Type</th>
944 <th class="OraTableColumnHeader" id="uniq">Uniqueness</th>
945 <th class="OraTableColumnHeader" id="ts">Tablespace</th>
946 <th class="OraTableColumnHeader" id="col">Column</th></tr>');
947 end if;
948
949 htp.p('<tr class="OraTableCellText">');
950 htp.p('<td class="OraTableCellText" headers="name">');
951 htp.p(ind_rec.index_name);
952 htp.p('</td>');
953
954 htp.p('<td class="OraTableCellText" headers="type">');
955 htp.p(ind_rec.index_type);
956 htp.p('</td>');
957
958 htp.p('<td class="OraTableCellText" headers="uniq">');
959 htp.p(ind_rec.uniqueness);
960 htp.p('</td>');
961
962
963 htp.p('<td class="OraTableCellText" headers="ts">');
964 htp.p(uiutil.el_image('TS')||ind_rec.tablespace_name);
965 htp.p('</td>');
966
967
968
969 htp.p('<td class="OraTableCellText" headers="col">');
970
971 v_first2 := TRUE;
972 if ind_rec.index_type like 'FUNCTION-BASED%' then
973 for ind_col_rec in cur_function_index(ind_rec.index_name, ind_rec.owner) loop
974 IF v_first2 Then
975 v_first2 := FALSE;
976 else
977 htp.p('<br>');
978 end if;
979 htp.p(uiutil.el_image('COL')||ind_col_rec.column_expression);
980 end loop;
981 else
982 for ind_col_rec in cur_index_col(ind_rec.index_name, ind_rec.owner) loop
983 IF v_first2 Then
984 v_first2 := FALSE;
985 else
986 htp.p('<br>');
987 end if;
988 htp.p(uiutil.el_image('COL')||ind_col_rec.column_name);
989 end loop;
990 end if;
991 htp.p('</td></tr>');
992 end loop;
993
994 IF v_first Then
995 null;
996 else
997 htp.p('</table>');
998 end if;
999
1000
1001 --
1002 EXCEPTION
1003 WHEN unexpected_error THEN
1004 RAISE_APPLICATION_ERROR(-20003,
1005 'An unexpected error has occured in the script. This is an internal error - not normally issued'
1006 );
1007 END ls_table;
1008
1009 procedure ls_columns(c_name dba_tab_columns.table_name%type
1010 , c_owner dba_tab_columns.owner%type
1011 , c_query_text varchar2 := 'Y') is
1012
1013 cursor cur_columns(c_name dba_tab_columns.table_name%type
1014 , c_owner dba_tab_columns.owner%type)
1015 is select col.column_name
1016 , col.data_type
1017 , col.data_length
1018 , nvl(to_char(col.data_precision), '<br>') data_precision
1019 , nvl(decode(col.nullable, 'N', 'Yes'), '<br>') nullable
1020 from dba_tab_columns col
1021 where col.table_name = c_name
1022 and col.owner = c_owner
1023 order by col.column_id;
1024
1025
1026 cursor cur_col_comments(c_tname dba_tab_columns.table_name%type
1027 , c_cname dba_tab_columns.column_name%type
1028 , c_owner dba_tab_columns.owner%type)
1029 is select comments
1030 from dba_col_comments
1031 where owner = c_owner
1032 and table_name = c_tname
1033 and column_name = c_cname;
1034
1035 v_first boolean;
1036 v_first2 boolean;
1037
1038 begin
1042
1039 v_first := TRUE;
1040 for col_rec in cur_columns(c_name, c_owner) loop
1041 IF v_first Then
1043 htp.p('<h5> Columns</h5>');
1044 htp.p('<table cellpadding=1 cellspacing=0 border=1
1045 summary="Column details for this table">');
1046 htp.p('<TR>');
1047 htp.p('<TH class="OraTableColumnHeader" id="name">Name</TH>');
1048 htp.p('<TH class="OraTableColumnHeader" id="datatype"> Datatype</TH>');
1049 htp.p('<TH class="OraTableColumnHeader" id="length">Length</TH>');
1050 htp.p('<TH class="OraTableColumnHeader" id="null">Mandatory</TH>');
1051 htp.p('<TH class="OraTableColumnHeader" id="text">Comments</TH></TR>');
1052 v_first := FALSE;
1053 end if;
1054
1055 htp.prn('<TR> <TD Class="OraTableCellText" headers="name">');
1056 htp.prn(col_rec.column_name);
1057 htp.p('</TD>');
1058 htp.prn('<TD Class="OraTableCellText" headers="datatype">');
1059 htp.prn(col_rec.data_type);
1060 htp.p('</TD>');
1061 htp.prn('<TD Class="OraTableCellText" headers="length">');
1062 if col_rec.data_type = 'DATE'
1063 then htp.prn('<br>');
1064 elsif (col_rec.data_type = 'NUMBER'
1065 AND col_rec.data_precision = '<br>')
1066 then htp.prn('<br>');
1067 elsif (col_rec.data_type = 'NUMBER'
1068 AND col_rec.data_precision != '<br>')
1069 then htp.prn('('||col_rec.data_precision||')');
1070 elsif col_rec.data_length is null
1071 then htp.prn('<br>');
1072 else
1073 htp.prn('('||col_rec.data_length||')');
1074 end if;
1075 htp.p('</TD>');
1076
1077 htp.prn('<TD Class="OraTableCellText" headers="null">');
1078 htp.prn(col_rec.nullable);
1079 htp.p('</TD>');
1080 htp.prn('<TD Class="OraTableCellText" headers="text">');
1081 v_first2 := TRUE;
1082 for t_rec in cur_col_comments(c_tname => c_name
1083 , c_cname => col_rec.column_name
1084 , c_owner => c_owner) loop
1085 v_first2 := FALSE;
1086 if t_rec.comments like '%- Retrofitted%' then
1087 htp.prn('<br>');
1088 else
1089 htp.prn(t_rec.comments);
1090 end if;
1091 end loop;
1092 if v_first2 then
1093 htp.prn('<br>');
1094 end if;
1095 htp.p('</TD></TR>');
1096
1097
1098 end loop;
1099 IF v_first Then
1100 htp.p(FND_CSS_PKG.Encode(c_name)|| ' has no columns<p>');
1101 else
1102 htp.p('</table>');
1103
1104 -- sql to select from this table
1105 if c_query_text = 'Y' then
1106 htp.p('<h5>Query Text</h5>');
1107 htp.p('<p>Cut, paste (and edit) the following text to query this object:</p>');
1108 v_first := TRUE;
1109 for col_rec in cur_columns(c_name, c_owner) loop
1110 IF v_first Then
1111 htp.p('<pre>');
1112 htp.p('SELECT '||col_rec.column_name);
1113 v_first := FALSE;
1114 else
1115 htp.p(', '||col_rec.column_name);
1116 end if;
1117 end loop;
1118 htp.p('FROM '||FND_CSS_PKG.Encode(c_owner)||'.'||FND_CSS_PKG.Encode(c_name)||';');
1119 htp.p('</pre>');
1120 end if;
1121 end if;
1122
1123 end ls_columns;
1124
1125 procedure ls_mview(c_name in dba_objects.object_name%type
1126 , c_owner in dba_objects.owner%type) is
1127
1128 cursor cur_mview(c_name in dba_objects.object_name%type
1129 , c_owner in dba_objects.owner%type) is
1130 SELECT CONTAINER_NAME
1131 , UPDATABLE
1132 , REWRITE_ENABLED
1133 , REWRITE_CAPABILITY
1134 , REFRESH_MODE
1135 , REFRESH_METHOD
1136 , BUILD_MODE
1137 , FAST_REFRESHABLE
1138 FROM SYS.DBA_MVIEWS
1139 where OWNER = c_owner
1140 and MVIEW_NAME = c_name;
1141
1142 CURSOR cur_comment(c_name in dba_tables.table_name%type
1143 , c_owner in dba_objects.owner%type)
1144 is select comments
1145 from dba_tab_comments
1146 where owner = c_owner
1147 and table_name = c_name;
1148
1149
1150 cursor cur_dtree(c_name in dba_objects.object_name%type
1151 , c_owner in dba_objects.owner%type) is
1152 select LPAD(' ',5*(LEVEL-1)) || a.master tree_entry
1153 from SYS.SNAP_REFTIME$ a
1154 start with a.vname = c_name
1155 connect by prior a.master = a.vname;
1156
1157 vfirst boolean := TRUE;
1158
1159 begin
1160
1161 for t_rec in cur_mview(c_name=>c_name, c_owner =>c_owner) loop
1162 htp.p(
1163 ' <h5>M-View Details</h5>
1164 <TABLE cellpadding="1" cellspacing="0"
1165 summary="dba_types information">
1166 <tr><th scope="row" align="left"><b> Container: </b></th> <td>'
1167 ||t_rec.container_name||'</td></tr>
1168 <tr><th scope="row" align="left"><b> Updatable: </b></th> <td>'
1169 ||t_rec.updatable||'</td></tr>
1173 ||t_rec.rewrite_capability||'</td></tr>
1170 <tr><th scope="row" align="left"><b> Rewrite Enabled: </b></th> <td>'
1171 ||t_rec.rewrite_enabled||'</td></tr>
1172 <tr><th scope="row" align="left"><b> Rewrite Capability: </b></th> <td>'
1174 <tr><th scope="row" align="left"><b> Refresh Mode: </b></th> <td>'
1175 ||t_rec.refresh_mode||'</td></tr>
1176 <tr><th scope="row" align="left"><b> Refresh Method: </b></th> <td>'
1177 ||t_rec.refresh_method||'</td></tr>
1178 <tr><th scope="row" align="left"><b> Build Mode: </b></th> <td>'
1179 ||t_rec.build_mode||'</td></tr>
1180 <tr><th scope="row" align="left"><b> Fast Refreshable: </b></th> <td>'
1181 ||t_rec.fast_refreshable||'</td></tr>
1182 </table>');
1183 end loop;
1184
1185 for c_rec in cur_comment(c_name, c_owner) loop
1186 uiutil.txt_prn(c_rec.comments);
1187 end loop;
1188
1189 for d_rec in cur_dtree(c_name=>c_name, c_owner =>c_owner) loop
1190 if vfirst then
1191 vfirst := FALSE;
1192 htp.p('<h5>MV Log Dependencies:</h5>');
1193 htp.p('<pre>');
1194 end if;
1195 htp.p(d_rec.tree_entry);
1196 end loop;
1197 if vfirst then null;
1198 else htp.p('</pre>');
1199 end if;
1200
1201 end ls_mview;
1202
1203
1204 procedure ls_trigger(c_name in dba_objects.object_name%type
1205 , c_owner in dba_objects.owner%type) is
1206
1207 cursor cur_trigger(c_name in dba_objects.object_name%type
1208 , c_owner in dba_objects.owner%type) is
1209 SELECT TABLE_OWNER
1210 , BASE_OBJECT_TYPE
1211 , TABLE_NAME
1212 FROM SYS.DBA_TRIGGERS
1213 where OWNER = c_owner
1214 and TRIGGER_NAME = c_name;
1215
1216 begin
1217
1218 for t_rec in cur_trigger(c_name=>c_name, c_owner =>c_owner) loop
1219 htp.p(
1220 ' <h5>Trigger Details</h5>
1221 <TABLE cellpadding="1" cellspacing="0"
1222 summary="dba_types information">
1223 <tr><th scope="row" align="left"><b> Base Object: </b></th> <td>');
1224 uiutil.a_href_gen(c_type => t_rec.base_object_type
1225 , c_link => 'etrm_pnav.show_object?c_name='|| t_rec.table_name
1226 ||'&c_owner='|| t_rec.table_owner
1227 ||'&c_type='|| t_rec.base_object_type
1228 , c_display => t_rec.table_owner||'.'||t_rec.table_name);
1229
1230 htp.p('</td></tr>
1231 </table>');
1232 end loop;
1233 end ls_trigger;
1234
1235
1236 procedure ls_index(c_name in dba_objects.object_name%type
1237 , c_owner in dba_objects.owner%type) is
1238
1239 cursor cur_index(c_name in dba_objects.object_name%type
1240 , c_owner in dba_objects.owner%type) is
1241 SELECT TABLE_OWNER
1242 , TABLE_TYPE
1243 , TABLE_NAME
1244 FROM SYS.DBA_INDEXES
1245 where OWNER = c_owner
1246 and INDEX_NAME = c_name;
1247
1248 begin
1249
1250 for t_rec in cur_index(c_name=>c_name, c_owner =>c_owner) loop
1251 htp.p(
1252 ' <h5>Index Details</h5>
1253 <TABLE cellpadding="1" cellspacing="0"
1254 summary="dba_types information">
1255 <tr><th scope="row" align="left"><b> Base Object: </b></th> <td>');
1256 uiutil.a_href_gen(c_type => t_rec.table_type
1257 , c_link => 'etrm_pnav.show_object?c_name='|| t_rec.table_name
1258 ||'&c_owner='|| t_rec.table_owner
1259 ||'&c_type='|| t_rec.table_type
1260 , c_display => t_rec.table_owner||'.'||t_rec.table_name);
1261
1262 htp.p('</td></tr>
1263 </table>');
1264 end loop;
1265 end ls_index;
1266
1267
1268 procedure ls_synonym(c_name in dba_objects.object_name%type
1269 , c_owner in dba_objects.owner%type) is
1270
1271 cursor cur_synonym(c_name in dba_objects.object_name%type
1272 , c_owner in dba_objects.owner%type) is
1273 SELECT TABLE_OWNER
1274 , TABLE_NAME
1275 , DB_LINK
1276 FROM SYS.DBA_SYNONYMS
1277 where OWNER = c_owner
1278 and SYNONYM_NAME = c_name;
1279
1280 begin
1281
1282 for t_rec in cur_synonym(c_name=>c_name, c_owner =>c_owner) loop
1283 htp.p(
1284 ' <h5>Synonym Details</h5>
1285 <TABLE cellpadding="1" cellspacing="0"
1286 summary="dba_synonyms information">
1287 <tr><th scope="row" align="left"><b> Base Object: </b></th> <td>'
1288 ||t_rec.table_owner||'.'||t_rec.table_name);
1289 if t_rec.db_link is null
1290 then null;
1291 else
1292 htp.p('@'||t_rec.db_link);
1293 end if;
1294 htp.p('</td></tr>
1295 </table>');
1296 end loop;
1297 end ls_synonym;
1298
1299 procedure ls_queue(c_name in dba_objects.object_name%type
1300 , c_owner in dba_objects.owner%type) is
1301
1302 cursor cur_queue(c_name in dba_objects.object_name%type
1303 , c_owner in dba_objects.owner%type) is
1304 SELECT QUEUE_TABLE
1308 , ENQUEUE_ENABLED
1305 , QUEUE_TYPE
1306 , MAX_RETRIES
1307 , RETRY_DELAY
1309 , DEQUEUE_ENABLED
1310 , RETENTION
1311 , USER_COMMENT
1312 FROM SYS.DBA_QUEUES
1313 where OWNER = c_owner
1314 and NAME = c_name;
1315
1316 begin
1317
1318 for t_rec in cur_queue(c_name=>c_name, c_owner =>c_owner) loop
1319 htp.p(
1320 ' <h5>Queue Details</h5>
1321 <TABLE cellpadding="1" cellspacing="0"
1322 summary="dba_queues information">
1323 <tr><th scope="row" align="left"><b> Queue Table: </b></th> <td>'
1324 ||t_rec.queue_table||'</td></tr>
1325 <tr><th scope="row" align="left"><b> Queue Type: </b></th> <td>'
1326 ||t_rec.queue_type||'</td></tr>
1327 <tr><th scope="row" align="left"><b> Max Retries: </b></th> <td>'
1328 ||t_rec.max_retries||'</td></tr>
1329 <tr><th scope="row" align="left"><b> Retry Delay: </b></th> <td>'
1330 ||t_rec.retry_delay||'</td></tr>
1331 <tr><th scope="row" align="left"><b> Enqueue Enabled: </b></th> <td>'
1332 ||t_rec.enqueue_enabled||'</td></tr>
1333 <tr><th scope="row" align="left"><b> Dequeue Enabled: </b></th> <td>'
1334 ||t_rec.dequeue_enabled||'</td></tr>
1335 <tr><th scope="row" align="left"><b> Retention: </b></th> <td>'
1336 ||t_rec.retention||'</td></tr>
1337 <tr><th scope="row" align="left"><b> Comment: </b></th> <td>'
1338 ||t_rec.user_comment||'</td></tr>
1339 </table>');
1340 end loop;
1341 end ls_queue;
1342
1343
1344 procedure ls_type(c_name in dba_types.type_name%type
1345 , c_owner in dba_types.owner%type) is
1346
1347 cursor cur_type(c_name in dba_types.type_name%type
1348 , c_owner in dba_types.owner%type) is
1349 SELECT TYPECODE
1350 , ATTRIBUTES
1351 , METHODS
1352 , PREDEFINED
1353 , INCOMPLETE
1354 FROM SYS.DBA_TYPES
1355 where owner = c_owner
1356 and type_name = c_name;
1357
1358 cursor cur_collection(c_name in dba_types.type_name%type
1359 , c_owner in dba_types.owner%type) is
1360 SELECT OWNER
1361 , TYPE_NAME
1362 , COLL_TYPE
1363 , UPPER_BOUND
1364 , ELEM_TYPE_MOD
1365 , ELEM_TYPE_OWNER
1366 , ELEM_TYPE_NAME
1367 , LENGTH
1368 , PRECISION
1369 , SCALE
1370 , CHARACTER_SET_NAME
1371 , ELEM_STORAGE
1372 , NULLS_STORED
1373 FROM SYS.DBA_COLL_TYPES
1374 where owner= c_owner
1375 and type_name= c_name;
1376
1377
1378
1379
1380 cursor cur_attribute(c_name in dba_types.type_name%type
1381 , c_owner in dba_types.owner%type) is
1382 SELECT OWNER
1383 , TYPE_NAME
1384 , ATTR_NAME
1385 , ATTR_TYPE_MOD
1386 , ATTR_TYPE_OWNER
1387 , ATTR_TYPE_NAME
1388 , LENGTH
1389 , PRECISION
1390 , SCALE
1391 , CHARACTER_SET_NAME
1392 , ATTR_NO
1393 FROM SYS.DBA_TYPE_ATTRS
1394 where owner = c_owner
1395 and type_name = c_name
1396 order by attr_no;
1397
1398 cursor cur_method(c_name in dba_types.type_name%type
1399 , c_owner in dba_types.owner%type) is
1400 SELECT OWNER
1401 , TYPE_NAME
1402 , METHOD_NAME
1403 , METHOD_NO
1404 , METHOD_TYPE
1405 , PARAMETERS
1406 , RESULTS
1407 FROM SYS.DBA_TYPE_METHODS
1408 where owner = c_owner
1409 and type_name = c_name
1410 order by method_no;
1411
1412 cursor cur_mparam(c_name in dba_types.type_name%type
1413 , c_owner in dba_types.owner%type
1414 , c_method in dba_method_params.method_name%type
1415 , n_methno in dba_method_params.method_no%type) is
1416
1417 SELECT PARAM_NAME
1418 , PARAM_NO
1419 , PARAM_TYPE_NAME
1420 FROM SYS.DBA_METHOD_PARAMS
1421 where owner = c_owner
1422 and type_name = c_name
1423 and method_name = c_method
1424 and method_no = n_methno
1425 order by param_no;
1426
1427 cursor cur_mresult(c_name in dba_types.type_name%type
1428 , c_owner in dba_types.owner%type
1429 , c_method in dba_method_params.method_name%type
1430 , n_methno in dba_method_params.method_no%type) is
1431
1432 SELECT RESULT_TYPE_NAME
1433 FROM SYS.DBA_METHOD_RESULTS
1434 where owner = c_owner
1435 and type_name = c_name
1436 and method_name = c_method
1437 and method_no = n_methno;
1438
1439
1440
1441
1442 begin
1443
1444 for t_rec in cur_type(c_name=>c_name, c_owner =>c_owner) loop
1445 htp.p(
1446 ' <h5>Type Details</h5>
1447 <TABLE cellpadding="1" cellspacing="0"
1448 summary="dba_types information">
1449 <tr><th scope="row" align="left"><b> Type Code: </b></th> <td>'
1450 ||t_rec.typecode||'</td></tr>
1454 ||t_rec.incomplete||'</td></tr>
1451 <tr><th scope="row" align="left"><b> Predefined: </b></th> <td>'
1452 ||t_rec.predefined||'</td></tr>
1453 <tr><th scope="row" align="left"><b> Incomplete: </b></th> <td>'
1455 </table>');
1456
1457 if t_rec.typecode = 'COLLECTION' then
1458 for c_rec in cur_collection(c_name=>c_name, c_owner =>c_owner) loop
1459 htp.p(
1460 ' <h5>Collection Details</h5>
1461 <TABLE cellpadding="1" cellspacing="0"
1462 summary="dba_coll_types information">
1463 <tr><th scope="row" align="left"><b> Collection Type: </b></th> <td>'
1464 ||c_rec.coll_type||'</td></tr>
1465
1466 <tr><th scope="row" align="left"><b> Element Type: </b></th> <td>');
1467 if c_rec.elem_type_owner is not null then
1468 uiutil.a_href_gen(c_type => 'TYPE'
1469 , c_link => 'etrm_pnav.show_object?c_name='|| c_rec.elem_type_name
1470 ||'&c_owner='||c_rec.ELEM_TYPE_OWNER
1471 ||'&c_type=TYPE'
1472 , c_display => c_rec.elem_type_name);
1473 else
1474 htp.p(nvl(c_rec.elem_type_name, '<br>'));
1475 end if;
1476
1477
1478 htp.p('</td></tr>
1479 </table>');
1480 end loop;
1481 end if;
1482
1483
1484
1485 if t_rec.attributes > 0 then
1486 htp.p('
1487 <h5> Attributes</h5>
1488 <table cellpadding=1 cellspacing=0 border=1
1489 summary="Attributes for this type">
1490 <TR>
1491 <TH class="OraTableColumnHeader" id="name">Name</TH>
1492 <TH class="OraTableColumnHeader" id="datatype"> Datatype</TH>
1493 <TH class="OraTableColumnHeader" id="length">Length</TH>
1494 <TH class="OraTableColumnHeader" id="prcn">Prcn</TH>
1495 </TR>');
1496
1497 for a_rec in cur_attribute(c_name=>c_name, c_owner =>c_owner) loop
1498 htp.p('
1499 <TR> <TD Class="OraTableCellText" headers="name">'
1500 ||a_rec.attr_name||'</TD>
1501 <TD Class="OraTableCellText" headers="datatype">');
1502 if a_rec.ATTR_TYPE_OWNER is not null then
1503 uiutil.a_href_gen(c_type => 'TYPE'
1504 , c_link => 'etrm_pnav.show_object?c_name='|| a_rec.attr_type_name
1505 ||'&c_owner='||a_rec.ATTR_TYPE_OWNER
1506 ||'&c_type=TYPE'
1507 , c_display => a_rec.attr_type_name);
1508 else
1509 htp.p(nvl(a_rec.attr_type_name, '<br>'));
1510 end if;
1511 htp.p('</TD>');
1512 htp.p('<TD Class="OraTableCellText" headers="length">');
1513
1514 if a_rec.length is not null then
1515 htp.p('('||a_rec.length||')');
1516 else
1517 htp.p('<br>');
1518 end if;
1519
1520 htp.p('</TD>
1521 <TD Class="OraTableCellText" headers="prcn">'
1522 ||nvl(to_char(a_rec.precision), '<br>')||'</TD>
1523 </TR>');
1524 end loop;
1525 htp.p('</table>');
1526 end if;
1527
1528 if t_rec.methods > 0 then
1529 htp.p('
1530 <h5> Methods</h5>
1531 <table cellpadding=1 cellspacing=0 border=1
1532 summary="Attributes for this type">
1533 <TR>
1534 <TH class="OraTableColumnHeader" id="name">Name</TH>
1535 <TH class="OraTableColumnHeader" id="param"> Parameters</TH>
1536 <TH class="OraTableColumnHeader" id="res">Result</TH>
1537 </TR>');
1538
1539 for a_rec in cur_method(c_name=>c_name, c_owner =>c_owner) loop
1540 htp.p('
1541 <TR> <TD Class="OraTableCellText" headers="name">'
1542 ||a_rec.method_name||'</TD>
1543 <TD Class="OraTableCellText" headers="param">');
1544
1545 if a_rec.parameters > 0 then
1546 for p_rec in cur_mparam(c_name => c_name
1547 , c_owner => c_owner
1548 , c_method => a_rec.method_name
1549 , n_methno => a_rec.method_no) loop
1550 htp.p('<b>'||p_rec.param_name || '</b><i>('||p_rec.param_type_name||')</i><br>');
1551 end loop;
1552 else
1553 htp.p('<br>');
1554 end if;
1555 htp.p('</TD>');
1556
1557
1558 htp.p('<TD Class="OraTableCellText" headers="res">');
1559
1560 if a_rec.results > 0 then
1561 for p_rec in cur_mresult(c_name => c_name
1562 , c_owner => c_owner
1563 , c_method => a_rec.method_name
1564 , n_methno => a_rec.method_no) loop
1565 htp.p(p_rec.result_type_name||'<br>');
1566 end loop;
1567 else
1568 htp.p('<br>');
1569 end if;
1570
1571 htp.p('</TD>
1572 </TR>');
1573 end loop;
1577
1574 htp.p('</table>');
1575 end if;
1576
1578 end loop;
1579 end ls_type;
1580
1581 procedure ls_dependencies(n_object_id IN dba_objects.object_id%type) is
1582
1583
1584 cursor cur_depend(n_object_id IN dba_objects.object_id%type)
1585 is select d_obj# object_id
1586 , etrm_pnav.get_object_name(d_obj#, 'USER') owner
1587 , etrm_pnav.get_object_name(d_obj#, 'ITEM') item
1588 from sys.dependency$
1589 where p_obj# = n_object_id
1590 order by etrm_pnav.get_object_name(d_obj#);
1591 --
1592 cursor cur_depend2(n_object_id IN dba_objects.object_id%type)
1593 is select p_obj# object_id
1594 , etrm_pnav.get_object_name(p_obj#, 'USER') owner
1595 , etrm_pnav.get_object_name(p_obj#, 'ITEM') item
1596 from sys.dependency$
1597 where d_obj# = n_object_id
1598 order by etrm_pnav.get_object_name(p_obj#);
1599
1600 v_first boolean;
1601 v_owner dba_objects.owner%type;
1602
1603 begin
1604 v_first := TRUE;
1605 v_owner := 'foRce_mE_to_diff';
1606 htp.p('<a name="dependencies"></a>
1607 <p><a href="#top-of-page">[top of page]</a></p>');
1608
1609 for a_rec in cur_depend2(n_object_id) loop
1610 IF v_first Then
1611 htp.p('<b>'||etrm_pnav.get_object_name(n_object_id)
1612 || ' references the following: </b><p>');
1613 htp.p('<dl>');
1614 v_first := FALSE;
1615 end if;
1616
1617 if a_rec.owner = v_owner then
1618 null;
1619 else
1620 v_owner := a_rec.owner;
1621 htp.p('<dt>');
1622 if a_rec.owner is not null then
1623 htp.p(uiutil.el_image('SCHEMA')||'<b>'||a_rec.owner||'</b>');
1624 end if;
1625 htp.p('</dt>');
1626 end if;
1627
1628
1629 htp.p('<dd>');
1630 a_href_gen(a_rec.object_id);
1631 if get_object_name(a_rec.object_id, 'TYPE') in ('PACKAGE', 'PACKAGE BODY', 'PROCEDURE')
1632 then htp.prn(' - ');
1633 uiutil.a_href_gen(c_type => 'NONE'
1634 , c_link => 'etrm_pnav.show_dependent_code?n_object_id='
1635 || n_object_id
1636 || '&c_name='
1637 || get_object_name(a_rec.object_id, 'ITEM')
1638 , c_display => ' <i>show dependent code</i>');
1639 end if;
1640
1641
1642 htp.p('</dd>');
1643 end loop;
1644
1645
1646 IF v_first Then
1647 htp.p('<p>'||etrm_pnav.get_object_name(n_object_id)|| ' does not reference any database object<p>');
1648 else
1649 htp.p('</dl>');
1650 v_first := TRUE;
1651 end if;
1652
1653
1654 v_first := TRUE;
1655 v_owner := 'foRce_mE_to_diff';
1656
1657 for a_rec in cur_depend(n_object_id) loop
1658 IF v_first Then
1659 htp.p('<b>'||etrm_pnav.get_object_name(n_object_id)
1660 || ' is referenced by following: </b><p>');
1661 htp.p('<dl>');
1662 v_first := FALSE;
1663 end if;
1664
1665 if a_rec.owner = v_owner then
1666 null;
1667 else
1668 v_owner := a_rec.owner;
1669 htp.p('<dt>');
1670 if a_rec.owner is not null then
1671 htp.p(uiutil.el_image('SCHEMA')||'<b>'||a_rec.owner||'</b>');
1672 end if;
1673 htp.p('</dt>');
1674 end if;
1675
1676 htp.p('<dd>');
1677 a_href_gen(a_rec.object_id);
1678 if get_object_name(a_rec.object_id, 'TYPE') in ('PACKAGE', 'PACKAGE BODY', 'PROCEDURE')
1679 then htp.prn(' - ');
1680 uiutil.a_href_gen(c_type => 'NONE'
1681 , c_link => 'etrm_pnav.show_dependent_code?n_object_id='
1682 || a_rec.object_id
1683 || '&c_name='
1684 || get_object_name(n_object_id, 'ITEM')
1685 , c_display => ' <i>show dependent code</i>');
1686 end if;
1687
1688 htp.p('</dd>');
1689 end loop;
1690
1691
1692 IF v_first Then
1693 htp.p('<p>'||etrm_pnav.get_object_name(n_object_id)|| ' is not referenced by any database object<p>');
1694 else
1695 htp.p('</dl>');
1696 v_first := TRUE;
1697 end if;
1698
1699 end ls_dependencies;
1700
1701
1702 procedure ls_source(c_name in dba_objects.object_name%type
1703 , c_type in dba_objects.object_type%type
1704 , c_owner in dba_objects.owner%type) is
1705
1706 cursor cur_source(c_name in dba_objects.object_name%type
1707 , c_type in dba_objects.object_type%type
1708 , c_owner in dba_objects.owner%type)
1709 is select line
1710 , text
1711 from dba_source
1712 where name = c_name
1713 and type = c_type
1714 and owner = c_owner;
1715
1716 cursor cur_vw_source(c_name in dba_objects.object_name%type
1717 , c_owner in dba_objects.owner%type)
1718 is select text
1722
1719 from dba_views
1720 where view_name = c_name
1721 and owner = c_owner;
1723 cursor cur_mview(c_name in dba_objects.object_name%type
1724 , c_owner in dba_objects.owner%type)
1725 is select query
1726 from dba_mviews
1727 where mview_name = c_name
1728 and owner = c_owner;
1729
1730 cursor cur_trigger(c_name in dba_objects.object_name%type
1731 , c_owner in dba_objects.owner%type)
1732 is select description
1733 , trigger_type
1734 , triggering_event
1735 , column_name
1736 , referencing_names
1737 , when_clause
1738 , trigger_body
1739 from dba_triggers
1740 where trigger_name = c_name
1741 and owner = c_owner;
1742
1743
1744 v_viewtext varchar2(32000);
1745 begin
1746
1747 if c_type = 'VIEW' then
1748 for t_rec in cur_vw_source(c_name, c_owner) loop
1749 htp.p('<h5>View Text - Preformatted</h5>');
1750 htp.p('<pre>');
1751 htp.p(t_rec.text);
1752 htp.p('</pre>');
1753
1754 v_viewtext := upper(t_rec.text);
1755 v_viewtext := replace(v_viewtext , 'SELECT ', '<b>SELECT </b>');
1756 v_viewtext := replace(v_viewtext , 'UNION ', '<b>UNION </b>');
1757 v_viewtext := replace(v_viewtext, ' FROM ', '<br><b> FROM </b>');
1758 v_viewtext := replace(v_viewtext , ' WHERE ', '<br><b> WHERE </b>');
1759 v_viewtext := replace(v_viewtext , ',', '<br>, ');
1760 v_viewtext := replace(v_viewtext , ' AND ', '<br> AND ');
1761
1762 htp.p('<h5>View Text - HTML Formatted</h5>');
1763 htp.p('<p>');
1764 htp.p(v_viewtext);
1765 htp.p('</p>');
1766 end loop;
1767 elsif c_type = 'MATERIALIZED VIEW' then
1768 for t_rec in cur_mview(c_name, c_owner) loop
1769 htp.p('<pre>');
1770 htp.p(t_rec.query);
1771 htp.p('</pre>');
1772 end loop;
1773 elsif c_type = 'TRIGGER' then
1774 for t_rec in cur_trigger(c_name, c_owner) loop
1775 htp.p('<h5>Description</h5>');
1776 htp.p('<pre>'||t_rec.description||'</pre>');
1777 htp.p('<h5>Type</h5>');
1778 htp.p('<pre>'||t_rec.trigger_type||'</pre>');
1779 htp.p('<h5>Event</h5>');
1780 htp.p('<pre>'||t_rec.triggering_event||'</pre>');
1781 htp.p('<h5>Column</h5>');
1782 htp.p('<pre>'||t_rec.column_name||'</pre>');
1783 htp.p('<h5>When</h5>');
1784 htp.p('<pre>'||t_rec.when_clause||'</pre>');
1785 htp.p('<h5>Referencing</h5>');
1786 htp.p('<pre>'||t_rec.referencing_names||'</pre>');
1787 htp.p('<h5>Body</h5>');
1788 htp.p('<pre>'||t_rec.trigger_body||'</pre>');
1789 end loop;
1790 else
1791 htp.p('<pre>');
1792 for t_rec in cur_source(c_name, c_type, c_owner) loop
1793 uiutil.txt_p(t_rec.line||' '||t_rec.text);
1794 end loop;
1795 htp.p('</pre>');
1796 end if;
1797
1798 end ls_source;
1799
1800 procedure download_source(c_name in dba_objects.object_name%type
1801 , c_type in dba_objects.object_type%type
1802 , c_owner in dba_objects.owner%type) is
1803
1804 cursor cur_source(c_name in dba_objects.object_name%type
1805 , c_type in dba_objects.object_type%type
1806 , c_owner in dba_objects.owner%type)
1807 is select line||': '||text text
1808 from dba_source
1809 where name = c_name
1810 and type = c_type
1811 and owner = c_owner;
1812
1813 cursor cur_vw_source(c_name in dba_objects.object_name%type
1814 , c_owner in dba_objects.owner%type)
1815 is select text
1816 from dba_views
1817 where view_name = c_name
1818 and owner = c_owner;
1819
1820 begin
1821 htp.p('Content-type: application/octet-stream');
1822 htp.p('Content-Disposition: attachement; filename='||c_name||'.sql');
1823 htp.p('Content-Transfer-Encoding: base64');
1824 -- htp.p('Content-Disposition: inline; filename='||c_name||'.txt');
1825 htp.p('');
1826
1827 if c_type = 'VIEW' then
1828 for t_rec in cur_vw_source(c_name, c_owner) loop
1829 htp.prn(t_rec.text);
1830 end loop;
1831 else
1832 for t_rec in cur_source(c_name, c_type, c_owner) loop
1833 htp.prn(t_rec.text);
1834 end loop;
1835 end if;
1836
1837 htp.p('');
1838 htp.prn(chr(4));
1839 end download_source;
1840
1841 procedure ls_errors(c_name IN dba_objects.object_name%type
1842 , c_type IN dba_objects.object_type%type
1843 , c_owner IN dba_objects.owner%type) is
1844
1845
1846 cursor cur_errors(c_name IN dba_objects.object_name%type
1847 , c_type IN dba_objects.object_type%type
1848 , c_owner IN dba_objects.owner%type)
1849 is select line
1850 , position
1851 , text
1852 from dba_errors
1853 where name = c_name
1854 and owner = c_owner
1855 and type = c_type
1856 order by sequence;
1857
1858
1859 v_first boolean;
1860
1861 begin
1862 v_first := TRUE;
1863
1864 for e_rec in cur_errors(c_name, c_type, c_owner) loop
1865 IF v_first Then
1866 htp.p('<h5> Error Text</h5>');
1867 htp.p('<table cellpadding=1 cellspacing=0 border=1
1871 htp.p('<TH class="OraTableColumnHeader" id="posn"> Pos''n</TH>');
1868 summary="Errors recorded in dba_errors">');
1869 htp.p('<TR>');
1870 htp.p('<TH class="OraTableColumnHeader" id="line">Line</TH>');
1872 htp.p('<TH class="OraTableColumnHeader" id="text">Text</TH></TR>');
1873 v_first := FALSE;
1874 end if;
1875
1876 htp.prn('<TR> <TD Class="OraTableCellText" headers="line">');
1877 htp.prn(e_rec.line);
1878 htp.p('</TD>');
1879 htp.prn('<TD Class="OraTableCellText" headers="posn">');
1880 htp.prn(e_rec.position);
1881 htp.p('</TD>');
1882 htp.prn('<TD Class="OraTableCellText" headers="text">');
1883 htp.prn(e_rec.text);
1884 htp.p('</TD>');
1885
1886 htp.p('</TD></TR>');
1887 end loop;
1888 IF v_first Then
1889 htp.p(c_name|| ' no errors<p>');
1890 else
1891 htp.p('</table>');
1892 v_first := TRUE;
1893 end if;
1894
1895 end ls_errors;
1896
1897 -----------------------------------------------------------------------------
1898 -- Public Procedures
1899 -----------------------------------------------------------------------------
1900 procedure ls_apps is
1901
1902 begin
1903
1904 uiutil.cabo1;
1905 uiutil.cabo2a;
1906 etrm_pnav.draw_buttons;
1907 uiutil.cabo2b;
1908 etrm_pnav.draw_tabs;
1909 uiutil.cabo3;
1910
1911 htp.p('<h3>DBA Objects</h3>');
1912 htp.p('<dl>');
1913
1914 htp.p('<dt>');
1915 uiutil.a_href_gen(c_type => 'APP'
1916 , c_link => 'etrm_pnav.ls_apps?c_mode=APPS'
1917 , c_display => 'Applications Product Prefixes');
1918 htp.p('</dt>');
1919 htp.p('<dt>');
1920 uiutil.a_href_gen(c_type => 'APP'
1921 , c_link => 'etrm_pnav.ls_apps?c_mode=USER'
1922 , c_display => 'Database Schemas');
1923 htp.p('</dt>');
1924
1925 htp.p('</dl>');
1926
1927 uiutil.cabo4;
1928 etrm_pnav.quick_list;
1929 uiutil.cabo5;
1930 -- draw_form;
1931 uiutil.cabo6;
1932 end ls_apps;
1933
1934
1935
1936
1937 procedure ls_apps(c_mode in varchar2) is
1938
1939 cursor cur_apps
1940 is
1941 select count(table_name) app_count
1942 from dba_tables
1943 where owner = 'APPLSYS'
1944 and table_name = 'FND_APPLICATION';
1945
1946 cursor cur_schema
1947 is select username
1948 from dba_users
1949 order by username;
1950
1951
1952
1953
1954 vsearch_name varchar2(80);
1955 vapps_installation boolean := TRUE;
1956 fnd_name varchar2(50);
1957 fnd_title varchar2(512);
1958
1959 type curtype is ref cursor;
1960 fnd_cv curtype;
1961 sql_stmt varchar2(1024) :=
1962 'select decode(app.application_short_name, ''SQLAP'', ''AP''
1963 , ''SQLGL'', ''GL''
1964 , app.application_short_name) application_short_name
1965 , tl.application_name
1966 from fnd_application app
1967 , fnd_application_tl tl
1968 where tl.application_id = app.application_id
1969 order by decode(app.application_short_name, ''SQLAP'', ''AP''
1970 , ''SQLGL'', ''GL''
1971 , app.application_short_name)';
1972
1973
1974
1975 begin
1976
1977 for a_rec in cur_apps loop
1978 if (a_rec.app_count = 0
1979 or c_mode = 'USER')
1980 then vapps_installation := FALSE;
1981 else
1982 vapps_installation := TRUE;
1983 end if;
1984 end loop;
1985
1986 uiutil.cabo1;
1987 uiutil.cabo2a;
1988 etrm_pnav.draw_buttons;
1989 uiutil.cabo2b;
1990 etrm_pnav.draw_tabs;
1991 uiutil.cabo3;
1992 if vapps_installation then
1993
1994 htp.p('<dl>');
1995 htp.p('<dt><A HREF="etrm_pnav.ls_object"><img src="/images/repository.gif" alt="Root"></a>
1996 <b>Applications Product Prefixes</b></dt>');
1997 open fnd_cv for sql_stmt;
1998 loop
1999 fetch fnd_cv into fnd_name, fnd_title;
2000 exit when fnd_cv%NOTFOUND;
2001 vsearch_name := fnd_name ||'\_*';
2002 htp.p('<dt>');
2003 uiutil.a_href_gen(c_type => 'APP'
2004 , c_link => 'etrm_pnav.ls_object?c_name='
2005 || vsearch_name
2006 , c_display => fnd_name||' - '||fnd_title);
2007
2008 htp.p('</dt>');
2009 end loop;
2010 if fnd_cv%ISOPEN then close fnd_cv; end if;
2011
2012 htp.p('</dl>');
2013 else
2014
2015 htp.p('<dl>');
2016 htp.p('<dt><A HREF="etrm_pnav.ls_object"><img src="/images/repository.gif" alt="Root"></a>
2017 <b>Database Schemas</b></dt>');
2018 for s_rec in cur_schema loop
2019
2020 htp.p('<dt>');
2021 uiutil.a_href_gen(c_type => 'APP'
2022 , c_link => 'etrm_pnav.ls_object?c_name=*'
2026
2023 ||'&c_owner='
2024 ||s_rec.username
2025 , c_display => s_rec.username);
2027 htp.p('</dt>');
2028 end loop;
2029 htp.p('</dl>');
2030 end if;
2031 uiutil.cabo4;
2032 etrm_pnav.quick_list;
2033 uiutil.cabo5;
2034 -- draw_form;
2035 uiutil.cabo6;
2036 end ls_apps;
2037
2038
2039
2040 procedure ls_object(c_name in dba_objects.object_name%type := '%'
2041 , c_owner in dba_objects.owner%type := '%'
2042 , c_type in dba_objects.object_type%type := '%'
2043 , c_status in dba_objects.status%type := '%') is
2044
2045 v_context varchar2(1024);
2046 v_name dba_objects.object_name%type;
2047 v_owner dba_objects.owner%type;
2048 v_type dba_objects.object_type%type;
2049 v_status dba_objects.status%type;
2050
2051 begin
2052 v_context := 'c_name=' ||replace(c_name, '%', '*')||
2053 '&c_owner=' ||replace(c_owner, '%', '*')||
2054 '&c_type=' ||replace(c_type, '%', '*')||
2055 '&c_status='||replace(c_status, '%', '*'); -- http drops trailing %
2056 v_name := replace(c_name, '*', '%');
2057 v_owner:= replace(c_owner, '*', '%');
2058 v_type := replace(c_type, '*', '%');
2059 v_status:= replace(c_status, '*', '%');
2060
2061 if v_type in ('TABLE', 'VIEW', 'PACKAGE', 'PACKAGE BODY')
2062 then v_name := upper(v_name);
2063 end if;
2064
2065 -- Write context information to cookie
2066 -- This wraps the following packages in an HTTP header
2067 owa_util.mime_header('text/html', FALSE);
2068
2069 -- Send some value to the target cookie for the next browser call
2070 owa_cookie.send('etrm_pnav_context', v_context);
2071
2072 -- Note: The following would cause the target cookie to expire
2073 -- owa_cookie.remove('etrm_pnav_context');
2074
2075 owa_util.http_header_close;
2076
2077 if (v_name = '%'
2078 and v_owner = '%'
2079 and v_type = '%'
2080 and v_status in('%', 'VALID'))
2081 then ls_apps;
2082 elsif (v_name != '%'
2083 and v_type != '%')
2084 then
2085 ls_object_type(v_name, v_owner, v_type, v_status);
2086 elsif (v_owner != '%'
2087 and v_type != '%')
2088 then
2089 ls_object_type(v_name, v_owner, v_type, v_status);
2090 else
2091 ls_count(v_name, v_owner, v_type, v_status);
2092 end if;
2093 end ls_object;
2094
2095 procedure show_object(c_name in dba_objects.object_name%type
2096 , c_owner in dba_objects.owner%type
2097 , c_type in dba_objects.object_type%type) is
2098
2099 v_object_id dba_objects.object_id%type;
2100 begin
2101 uiutil.cabo1(c_title=> FND_CSS_PKG.Encode(c_type) ||' - '||FND_CSS_PKG.Encode(c_owner)||'.'||FND_CSS_PKG.Encode(c_name));
2102 htp.p('<body bgcolor="#ffffff" onload="javascript:window.focus();">');
2103 htp.p('<a name="top-of-page"></a>');
2104 htp.p('<p><img src="/images/dba_header.gif" alt="DBA Data">
2105 <a href="#dependencies">[Dependency Information]</a>
2106 <hr size="2" width="100%" noshade align="left"></p>');
2107
2108
2109 htp.p('<h1>'||FND_CSS_PKG.Encode(c_type)||': '||FND_CSS_PKG.Encode(c_owner)||'.'||FND_CSS_PKG.Encode(c_name)||'</h1>');
2110 v_object_id := dba_object_info(c_name, c_owner, c_type);
2111
2112
2113
2114 if c_type in ('PACKAGE', 'PACKAGE BODY')
2115 then
2116 htp.p('<p>');
2117
2118 htp.p('<a href="etrm_pnav.show_details?c_name='
2119 ||utl_url.escape(c_name)
2120 ||'&c_owner='
2121 ||utl_url.escape(c_owner)
2122 ||'&c_type=PACKAGE'
2123 ||'&c_detail_type=source">[Package]</a>');
2124 htp.p('<a href="etrm_pnav.show_details?c_name='
2125 ||utl_url.escape(c_name)
2126 ||'&c_owner='
2127 ||utl_url.escape(c_owner)
2128 ||'&c_type=PACKAGE%20BODY'
2129 ||'&c_detail_type=source">[Package Body]</a>');
2130 htp.p('<a href="etrm_pnav.show_sql?n_object_id='
2131 ||get_object_id(c_name, c_owner, 'PACKAGE BODY')
2132 ||'">[SQL Statements]</a>');
2133 htp.p('</p>');
2134
2135
2136 elsif c_type in ('PROCEDURE', 'FUNCTION', 'TYPE BODY', 'JAVA SOURCE')
2137 then
2138 htp.p('<p>');
2139 htp.p('<a href="etrm_pnav.show_details?c_name='
2140 ||utl_url.escape(c_name)
2141 ||'&c_owner='
2142 ||utl_url.escape(c_owner)
2143 ||'&c_type='||c_type
2144 ||'&c_detail_type=source">[Source]</a>');
2145 htp.p('</p>');
2146 elsif (c_type = 'TABLE')
2147 then
2148 ls_table(c_name, c_owner);
2149 etrm_pnav.ls_table(v_object_id);
2150 ls_columns(c_name, c_owner);
2151 elsif (c_type = 'VIEW')
2152 then ls_table(c_name, c_owner);
2153 htp.p('<p><a href="etrm_pnav.show_details?c_name='
2154 ||utl_url.escape(c_name)
2155 ||'&c_owner='
2159 ||'&c_detail_type=source">[View Source]</a></p>');
2156 ||utl_url.escape(c_owner)
2157 ||'&c_type='
2158 ||c_type
2160 ls_columns(c_name, c_owner);
2161 elsif (c_type = 'MATERIALIZED VIEW')
2162 then
2163 ls_mview(c_name, c_owner);
2164 htp.p('<p><a href="etrm_pnav.show_details?c_name='
2165 ||utl_url.escape(c_name)
2166 ||'&c_owner='
2167 ||utl_url.escape(c_owner)
2168 ||'&c_type='
2169 ||c_type
2170 ||'&c_detail_type=source">[View Source Query]</a></p>');
2171 ls_columns(c_name, c_owner);
2172 elsif (c_type = 'TRIGGER')
2173 then
2174 ls_trigger(c_name, c_owner);
2175 htp.p('<p><a href="etrm_pnav.show_details?c_name='
2176 || replace(c_name, '+', '%2B')
2177 ||'&c_owner='
2178 ||utl_url.escape(c_owner)
2179 ||'&c_type='
2180 ||c_type
2181 ||'&c_detail_type=source">[View Trigger Source]</a></p>');
2182 elsif (c_type = 'INDEX')
2183 then
2184 ls_index(c_name, c_owner);
2185 elsif (c_type = 'QUEUE')
2186 then
2187 ls_queue(c_name, c_owner);
2188 elsif (c_type = 'SYNONYM')
2189 then
2190 ls_synonym(c_name, c_owner);
2191 elsif (c_type = 'CLUSTER')
2192 then
2193 ls_columns(c_name, c_owner, 'N');
2194 elsif (c_type = 'TYPE')
2195 then
2196 ls_type(c_name, c_owner);
2197 end if;
2198
2199 htp.p('<h3>Dependencies</h3>');
2200
2201
2202 etrm_pnav.ls_dependencies(v_object_id);
2203
2204
2205
2206 htp.p('</p>');
2207 uiutil.prn_copyright;
2208 htp.p('</body>');
2209 end show_object;
2210
2211 procedure show_details(c_name in dba_objects.object_name%type
2212 , c_owner in dba_objects.owner%type
2213 , c_type in dba_objects.object_type%type
2214 , c_detail_type in varchar2) is
2215 begin
2216 if c_detail_type = 'download'
2217 then download_source(c_name, c_type, c_owner);
2218 else
2219 uiutil.cabo1(c_title=> c_type ||' - '||c_owner||'.'||c_name);
2220 htp.p('<img src="/images/dba_header.gif" alt="DBA Data">
2221 <hr size="2" width="100%" noshade align="left">');
2222
2223 htp.p('<body bgcolor="#ffffff" onload="javascript:window.focus();">');
2224 htp.p('<h1>'||FND_CSS_PKG.Encode(c_type)||': '||FND_CSS_PKG.Encode(c_owner)||'.'||FND_CSS_PKG.Encode(c_name)||'</h1>');
2225
2226 if c_detail_type = 'source'
2227 then htp.p('<h3>Source</h3>');
2228 ls_source(replace(c_name, '%23', '#'), c_type, c_owner);
2229 elsif c_detail_type = 'exception'
2230 then htp.p('<h3>Exceptions</h3>');
2231 ls_errors(c_name, c_type, c_owner);
2232 else
2233 htp.p('<p>No further details are available</p>');
2234 end if;
2235
2236 uiutil.prn_copyright;
2237 htp.p('</body>');
2238 end if;
2239 end show_details;
2240
2241 procedure show_dependent_code(n_object_id in dba_objects.object_id%type
2242 , c_name in dba_objects.object_name%type)
2243 is
2244 cursor cur_find_line(n_object_id in dba_objects.object_id%type
2245 , c_name in dba_objects.object_name%type) is
2246 select line
2247 , source
2248 from sys.source$
2249 where upper(source) like c_name
2250 and obj# = n_object_id;
2251
2252 cursor cur_print_line(n_object_id in dba_objects.object_id%type
2253 , n_line in sys.source$.line%type) is
2254 select line
2255 , source
2256 from sys.source$
2257 where line > (n_line - 5)
2258 and line < (n_line +5)
2259 and obj# = n_object_id;
2260
2261 v_string varchar2(512);
2262 v_line sys.source$.line%type;
2263 v_first boolean := TRUE;
2264
2265 begin
2266 v_string := '%'||upper(c_name)||'%';
2267 uiutil.cabo1(c_title=> c_name || ' dependencies');
2268 htp.p('<img src="/images/dba_header.gif" alt="DBA Data">
2269 <hr size="2" width="100%" noshade align="left">');
2270
2271 htp.p('<h1>'||get_object_name(n_object_id)||' dependencies on '||FND_CSS_PKG.Encode(c_name)||'</h1>');
2272 for t_rec in cur_find_line(n_object_id, v_string) loop
2273 v_first := FALSE;
2274 v_line := t_rec.line;
2275 uiutil.txt_prn1('<h5>Line '||t_rec.line||': '||t_rec.source||'</h5>');
2276 htp.p('<p>');
2277 for l_rec in cur_print_line(n_object_id, v_line) loop
2278 if l_rec.line = v_line then
2279 uiutil.txt_prn1('<b>'||l_rec.line||': '||l_rec.source||'</b>');
2280 else
2281 uiutil.txt_prn1(l_rec.line||': '||l_rec.source);
2282 end if;
2283 end loop;
2284 htp.p('</p>');
2285 end loop;
2286 if v_first = TRUE then
2287 htp.p('<p>These dependencies are not visible in source code. They may be indirect dependencies
2288 or the source code may be encrypted</p>');
2289 end if;
2290 end show_dependent_code;
2291
2292 procedure show_sql(n_object_id in dba_objects.object_id%type)
2293 is
2294
2298 from sys.source$
2295 cursor cur_find_line(n_object_id in dba_objects.object_id%type) is
2296 select line
2297 , source
2299 where obj# = n_object_id;
2300
2301
2302 v_line sys.source$.line%type;
2303 v_prevline sys.source$.line%type := 1;
2304
2305 v_firstline sys.source$.line%type := 0;
2306 v_lastline sys.source$.line%type := 0;
2307
2308 v_first boolean := TRUE;
2309 type source_table is table of sys.source$.source%type index by binary_integer;
2310 v_source source_table;
2311
2312 type line_rec is record
2313 ( select_line number(32)
2314 , term_line number(32)
2315 , status varchar2(16));
2316
2317 type line_table is table of line_rec index by binary_integer;
2318 v_select_stmt line_table;
2319 v_stmt_count binary_integer;
2320 begin
2321 uiutil.cabo1(c_title=> ' SQL Statements');
2322 htp.p('<img src="/images/dba_header.gif" alt="DBA Data">
2323 <hr size="2" width="100%" noshade align="left">');
2324 htp.p('<h1>'||get_object_name(n_object_id)||' SQL Statements</h1>');
2325 htp.p('<p>The following lines contain the word ''select'', ''insert'', ''update'' or ''delete'':</p>');
2326
2327
2328 for t_rec in cur_find_line(n_object_id) loop
2329 v_source(t_rec.line) := t_rec.source;
2330 end loop;
2331
2332 v_firstline := v_source.first;
2333 v_lastline := v_source.last;
2334
2335 v_stmt_count := 0;
2336 v_line := v_source.first;
2337 while v_line < v_lastline loop
2338 if ((upper(v_source(v_line)) like '%SELECT%')
2339 or (upper(v_source(v_line)) like '%INSERT%')
2340 or (upper(v_source(v_line)) like '%UPDATE%')
2341 or (upper(v_source(v_line)) like '%DELETE%'))
2342 and
2343 ltrim(v_source(v_line), ' ') not like '--%'
2344 then
2345 v_stmt_count := v_stmt_count +1;
2346 v_select_stmt(v_stmt_count).select_line := v_line;
2347 v_select_stmt(v_stmt_count).term_line := v_line;
2348 v_select_stmt(v_stmt_count).status := 'VALID';
2349 end if;
2350 v_line := v_source.next (v_line);
2351 end loop;
2352
2353
2354 for i in 1 .. v_stmt_count loop
2355 if v_select_stmt(i).select_line < (v_prevline +1)
2356 then v_select_stmt(i).status := 'INVALID';
2357 end if;
2358 -- find ;
2359 v_first := true;
2360 v_line := v_select_stmt(i).term_line;
2361 while v_first loop
2362 if upper(v_source(v_line)) like '%;%' then
2363 v_select_stmt(i).term_line := v_line;
2364 v_prevline := v_line;
2365 v_first := false;
2366 end if;
2367 v_line := v_line +1;
2368 end loop;
2369 end loop;
2370
2371
2372 for i in 1 .. v_stmt_count loop
2373 if v_select_stmt(i).status = 'VALID' then
2374 htp.p('<h5>Line: '||v_select_stmt(i).select_line||'</h5>');
2375 htp.p('<pre>');
2376 for j in v_select_stmt(i).select_line .. v_select_stmt(i).term_line loop
2377 htp.prn(v_source(j));
2378 end loop;
2379 htp.p('</pre>');
2380 end if;
2381 end loop;
2382
2383
2384 if v_stmt_count = 0 then
2385 htp.p('<p>No SQL Statements are visable in the source code.
2386 The package body may be wrapped (encrypted)</p>');
2387 end if;
2388 end show_sql;
2389
2390 procedure prn_object_type(c_name in dba_objects.object_name%type := '%'
2391 , c_owner in dba_objects.owner%type := '%'
2392 , c_type in dba_objects.object_type%type := '%'
2393 , c_status in dba_objects.status%type := '%') is
2394
2395 cursor cur_object(c_name in dba_objects.object_name%type
2396 , c_owner in dba_objects.owner%type
2397 , c_type in dba_objects.object_type%type
2398 , c_status in dba_objects.status%type := '%') is
2399 select owner, object_name
2400 from dba_objects
2401 where object_name like c_name ESCAPE '\'
2402 and object_type like c_type
2403 and owner like c_owner
2404 and status like c_status
2405 order by owner, object_name;
2406
2407 v_name varchar2(80);
2408 v_owner dba_objects.owner%type;
2409 v_type dba_objects.object_type%type;
2410 v_first boolean := TRUE;
2411
2412
2413 begin
2414
2415
2416 v_name := replace(c_name, '*', '%'); -- http drops trailing %
2417
2418
2419 for a_rec in cur_object(v_name
2420 , replace(c_owner, '*', '%')
2421 , c_type
2422 , replace(c_status, '*', '%')) loop
2423 if v_first = TRUE
2424 then v_first := FALSE;
2425 else
2426 htp.p('<br>');
2427 end if;
2428
2429 uiutil.a_javascript_gen(c_type => c_type
2430 , c_link => 'etrm_pnav.show_object?c_name='
2431 || a_rec.object_name
2432 ||'&c_owner='
2436 , c_display => a_rec.owner||'.'||a_rec.object_name);
2433 ||a_rec.owner
2434 ||'&c_type='
2435 ||c_type
2437
2438 end loop;
2439
2440 if v_first = TRUE then
2441 htp.p('Not implemented in this database');
2442 end if;
2443
2444
2445 end prn_object_type;
2446
2447
2448
2449 end etrm_pnav;