1 package ad_apps_private as
2 /* $Header: adaprs.pls 120.0 2005/05/25 11:44:44 appldev noship $ */
3
4 --
5 -- Declare type for tables of objects given special treatment
6 -- These are usually the result of patches after the release goes
7 -- out. In general, we don't want any of these at the start of a release
8 --
9
10 type TableNameType is table of varchar2(30)
11 index by binary_integer;
12
13 --
14 -- Global variables and common sql statements used multiple times
15 --
16
17 error_buf varchar2(32760);
18 is_mls boolean;
19 is_mc boolean;
20
21 ign_select_part1 constant varchar2(100) :=
22 'select distinct decode(install_group_num,0,1,install_group_num) from ';
23 ign_select_part2 constant varchar2(200) :=
24 '.fnd_product_installations '||
25 'where db_status in ( ''I'' , ''S'' ) '||
26 'and not (install_group_num != 0 '||
27 'and exists ( '||
28 'select null from ';
29 ign_select_part3 constant varchar2(200) :=
30 '.fnd_product_installations '||
31 'where install_group_num > 0)) '||
32 'order by decode(install_group_num,0,1,install_group_num)';
33
34 ign_schema_select_part1 constant varchar2(100) :=
35 'select distinct ou.oracle_username from ';
36 ign_schema_select_part2 constant varchar2(100) :=
37 '.fnd_oracle_userid ou, ';
38 -- note the final where condition is missing. This differs and is added later
39 ign_schema_select_part3 constant varchar2(200) :=
40 '.fnd_product_installations pi '||
41 'where ou.oracle_id = pi.oracle_id '||
42 'and pi.db_status in ( ''I'' , ''S'' ) '||
43 'and pi.install_group_num ';
44
45 --
46 -- These PL/SQL tables together logically implement a PL/SQL
47 -- table of records that tracks information on objects we need to treat
48 -- specially. So far, we only refer to this information in the
49 -- create_grants_and_synonyms() procedure, and in advrfapp.sql
50 --
51 -- I probably should really implement this as a table of records...
52 --
53
54 -- The logical columns for the exception objects list
55
56 prod_short_name TableNameType;
57 base_name TableNameType;
58 base_type TableNameType;
59 exception_type TableNameType;
60 trigger_obj_schema TableNameType;
61 trigger_obj_type TableNameType;
62 trigger_obj_name TableNameType;
63 apps_name TableNameType;
64 apps_type TableNameType;
65 points_to_schema TableNameType;
66 points_to_name TableNameType;
67
68 -- The number of elements in the exception objects list
69
70 list_count number;
71
72 --
73 -- Array used to check to see if we have already done the following check:
74 -- Some programs that use ad_ddl call it hundreds of times and we don't need to
75 -- do the check if we've already done it. The check will be done in
76 -- check_for_apps_ddl
77 --
78 type schema_check_table is table of dba_objects.owner%type index by binary_integer;
79 schema_check schema_check_table;
80 --
81
82 --
83 -- Procedures dealing with APPS_DDL and APPS_ARRAY_DDL
84 --
85
86 procedure do_apps_ddl (schema_name in varchar2,
87 ddl_text in varchar2);
88
89 procedure do_apps_ddl (schema_name in varchar2,
90 ddl_text in varchar2,
91 abbrev_stmt in varchar2);
92 --
93 -- Purpose
94 -- Execute the SQL statement in the schema <username>
95 -- This is done by creating the following pl/sql block:
96 -- begin <username>.apps_ddl.apps_ddl(:<ddl_text>); end;
97 -- Arguments
98 -- schema_name The schema in which to run the statement
99 -- ddl_text The SQL statement to run
100 -- abbrev_stmt Replace ddl_text with '$statement$' in stack trace?
101 -- Example
102 -- none
103
104 procedure do_apps_array_ddl (schema_name in varchar2,
105 lb in integer,
106 ub in integer);
107
108 procedure do_apps_array_ddl (schema_name in varchar2,
109 lb in integer,
110 ub in integer,
111 add_newline in varchar2);
112 -- Purpose
113 -- Execute a array of sql statement in the schema <username>
114 -- This array should be already built in do_array_assignment
115 -- Arguments
116 -- schema_name The schema in which to run the statement
117 -- ddl_text dummy variable, for future use
118 -- lb, ub the upper and lower bound of the array
119 -- add_newline Should we add a newline after each line of input
120 -- text? Don't want this for long views, but
121 -- probably do want this for packages.
122 -- Example
123 -- none
124 -- Notes
125 -- 1. Requires that create_apps_ddl, and do_array_assignment,
126 -- 2. This procedure requires that execute on sys.dbms_sys_sql be
127 -- granted to username before calling.
128 -- 3. Before calling this function, one should call
129 -- do_array_assignment to build the array.
130
131 procedure do_array_assignment (schema_name in varchar2,
132 ddl_text in varchar2,
133 rowcount in integer);
134 -- Build an array of sqlcode in a global variable glprogtext
135 -- in package APPS_ARRAY_DDL in schema username
136 -- Argument
137 -- username, schema
138 -- ddl_text, one line of plsql code.
139 -- rowcount, the line number
140
141 procedure check_for_apps_ddl (schema_name in varchar2);
142
143 -- Verify that APPS_DDL and APPS_ARRAY_DDL exist and are valid
144 -- in "schema_name"
145 -- Fail if not there or not valid
146
147 --
148 -- Procedures dealing with Oracle Schemas
149 --
150
151 function check_if_schema_exists (schema_name in varchar2)
152 return boolean;
153 --
154 -- Purpose
155 -- Check for existance of username in dba_users
156 -- Arguments
157 -- username The oracle user name to check
158 -- Output
159 -- boolean - TRUE if user exists, else FALSE
160 -- Example
161 -- none
162 -- Notes
163 -- 1. none
164 --
165
166 --
167 -- Utility functions
168 --
169
170 procedure create_grants_and_synonyms
171 (install_group_num in number,
172 from_schema in varchar2,
173 aol_schema in varchar2,
174 apps_schema in varchar2);
175
176 procedure create_grants_and_synonyms
177 (install_group_num in number,
178 from_schema in varchar2,
179 aol_schema in varchar2,
180 apps_schema in varchar2,
181 force in varchar2);
182
183 --
184 -- Purpose
185 -- Create grants from base schema sequences and tables to the
186 -- APPS schema(s), and create synonyms in the APPS schema(s)
187 -- corresponding to the base schema objects.
188 -- Arguments
189 -- install_group_num Install Group Number
190 -- from_schema Schema that contains the sequences and tables
191 -- aol_schema Schema that contains AOL tables
192 -- apps_schema Name of the regular APPS schema
193 -- force Drop and re-create synonyms in this APPS schema
194 -- for objects in this base schema?
195 -- Example
196 -- none
197 -- Notes
198 -- 1. none
199 --
200
201 procedure get_apps_schema_name (ign in number,
202 aol_or_apps_schema in varchar2,
203 apps_schema out nocopy varchar2,
204 apps_mls_schema out nocopy varchar2);
205 --
206 -- Purpose
207 -- Create name for appsuser accounts (regular and MLS versions)
208 -- Arguments
209 -- ign Install Group Number
210 -- aol_schema Schema that contains AOL tables
211 -- apps_schema Name of the regular schema
212 -- apps_mls_schema Name of the MLS schema
213 -- Example
214 -- none
215 -- Notes
216 -- 1. none
217 --
218
219 procedure drop_object (target_schema in varchar2,
220 object_name in varchar2,
221 object_type in varchar2);
222 -- Drop the object_name from the target_schema handling most
223 -- OK Oracle Errors (i.e. object does not exist)
224 -- Arguments
225 -- target_schema The schema which owns the object
226 -- object_name The name of the object to drop
227 -- object_type The type of object to drop
228 -- Example
229 -- none
230 -- Notes
231 -- none
232 --
233
234 procedure copy_view (view_name in varchar2,
235 from_schema in varchar2,
236 to_schema in varchar2);
237 --
238 -- Purpose
239 -- The procedure copies a view from one schema to another.
240 -- Arguments
241 -- view_name View to be copied
242 -- from_schema Schema to copy from
243 -- to_schema Schema to copy to
244 -- Example
245 -- none
246 -- Notes
247 -- This is actually a wrapper routine that calls copy_view_internal
248 -- and handles "deadlock when recompiling dependent object" errors
249 -- that can occur when creating a view.
250 --
251
252 procedure copy_huge_view(view_name in varchar2,
253 fromschema in varchar2,
254 toschema in varchar2);
255 --
256 -- Purpose
257 -- Copy a view with source text > 32 K
258 -- Can also copy smaller views, but is probably slower than
259 -- the normal copy_view routine
260 -- Arguments
261 -- view_name The name of the view to copy
262 -- fromschema The source schema
263 -- toschema The destination schema
264 -- Example
265 -- none
266 -- Notes
267 -- 1. none
268 --
269
270 procedure compare_view_text(view_name in varchar2,
271 fromschema in varchar2,
272 toschema in varchar2,
273 from_len in number,
274 to_len in number,
275 equal out nocopy varchar2);
276 --
277 -- Purpose
278 -- Compare the text of two views, and determine whether
279 -- they are identical
280 -- Arguments
281 -- view_name The name of the view to copy
282 -- fromschema The source schema
283 -- toschema The destination schema
284 -- from_len View length in the source schema
285 -- to_len View length in the destination schema
286 -- equal Are the views equal?
287 -- Example
288 -- none
289 -- Notes
290 -- If the views are equal, the equal parameter is set to 'TRUE'
291 -- Otherwise, the equal parameter is set to 'FALSE'
292 --
293
294 procedure copy_code (object_name in varchar2,
295 object_type in varchar2,
296 from_schema in varchar2,
297 to_schema in varchar2);
298 --
299 -- Purpose
300 -- The procedure copies a code object (package spec, package body,
301 -- procedure, function) from one schema to another
302 -- Arguments
303 -- object_name code object to be copied
304 -- object_type type of object to be copied
305 -- one of ak_org _private.g_package_spec
306 -- ak_org _private.g_package_body
307 -- ak_org _private.g_procedure
308 -- ak_org _private.g_function
309 -- from_schema Schema to copy from
310 -- to_schema Schema to copy to
311 -- Example
312 -- none
313 -- Notes
314 -- 1. none
315 --
316
317 procedure compare_code (object_name in varchar2,
318 object_type in varchar2,
319 from_schema in varchar2,
320 to_schema in varchar2,
321 comp_level in varchar2,
322 equal out nocopy varchar2);
323 --
324 -- Purpose
325 -- The procedure compares a code object (package spec, package body,
326 -- procedure, function) from one schema with the same object
327 -- in a different schema.
328 -- Arguments
329 -- object_name code object to be copied
330 -- object_type type of object to be copied
331 -- from_schema Schema to copy from
332 -- to_schema Schema to copy to
333 -- comp_level Level of comparison (see notes)
334 -- equal are the two objects equal?
335 -- Example
336 -- none
337 -- Notes
338 -- sets equal to TRUE if the two objects are identical
339 -- sets equal to FALSE if the objects are not identical
340 --
341 -- comp_level valid values, and what they mean:
342 --
343 -- none : return "equal" without comparing objects
344 --
345 -- lines : compare number of source lines in each object
346 -- return "equal" if same number of source lines
347 --
348 -- chars : compare number of source lines and number of
349 -- source chars in each object
350 -- return "equal" if same number of source lines and chars
351 --
352 -- full : compare number of source lines in each object,
353 -- then compare actual source text
354 -- return "equal" only if exactly equal
355 --
356
357 procedure create_base_gs (base_schema in varchar2,
358 apps_schema in varchar2);
359
360 procedure create_base_gs (base_schema in varchar2,
361 apps_schema in varchar2,
362 force in varchar2);
363 --
364 -- Purpose
365 -- The procedure creates grants from all base schema tables and
366 -- sequences to the APPS schema; and also (re-)creates all missing
367 -- or incorrect synonyms in APPS.
368 --
369 -- Arguments
370 -- base_schema the base applications schema
371 -- apps_schema the APPS schema
372 -- force drop, then recreate all synonyms in APPS
373 -- corresponding to objects in this base schema
374 --
375 -- Example
376 -- none
377 -- Notes
378 -- 1. uses apps_ddl
379 --
380
381 procedure create_gs (object_owner_schema in varchar2,
382 to_schema in varchar2,
383 object_name in varchar2,
384 with_option in boolean,
385 privs in varchar2,
386 grant_from_schema in varchar2 default null);
387 --
388 -- Purpose
389 -- Create a grant/synonym for object in <fromuser> to object in
390 -- <touser> with 'all' privileges on object <object_name>, optionally
391 -- 'with grant option'
392 -- Arguments
393 -- fromuser Schema in which object exists
394 -- touser Schema in which to create synonym and grant to
395 -- object_name Name of object to create synonym for
396 -- with_option TRUE - grant is created 'with grant option'
397 -- FALSE - grant is created without grant option
398 -- Example
399 -- none
400 -- Notes
401 -- 1. This implementation uses 'apps_ddl'. So 'apps_ddl' must exist.
402 --
403
404
405 --
406 -- Procedures used to create an APPS/MLS schema
407 --
408
409 procedure create_synonyms (from_schema in varchar2,
410 to_schema in varchar2,
411 grant_from_schema in varchar2 default null);
412 --
413 -- Purpose
414 -- Copy synonyms from one schema to another
415 -- Arguments
416 -- from_schema Schema to copy from
417 -- to_schema Schema to copy to
421 -- 1. none
418 -- Example
419 -- none
420 -- Notes
422 --
423
424 procedure copy_odd_synonyms (fromschema in varchar2,
425 toschema in varchar2);
426 --
427 -- Purpose
428 -- Copy odd synonyms (those that have a different name than base table)
429 -- Arguments
430 -- fromschema Schema to copy from
431 -- toschema Schema to copy to
432 -- Example
433 -- none
434 -- Notes
435 -- 1. none
436 --
437
438 procedure create_special_views (install_group_num in number,
439 aol_schema in varchar2,
440 apps_schema in varchar2,
441 create_mls_views in boolean);
442 --
443 -- Purpose
444 -- Create mls views in the appsuser account.
445 -- For each table listed in <aoluser>.ak_translated_columns an
446 -- mls view is created in the <appsuser> account.
447 -- Example
448 -- none
449 -- Notes
450 -- 1. none
451 --
452
453 procedure copy_views (aoluser in varchar2,
454 fromschema in varchar2,
455 toschema in varchar2);
456 --
457 -- Purpose
458 -- Copy views from schema <fromschema> to schema <toschema>
459 -- Arguments
460 -- aoluser Schema for AOL objects
461 -- fromschema Schema to copy views from
462 -- toschema Schema to copy views to
463 -- Example
464 -- none
465 -- Notes
466 -- 1. none
467 --
468
469 procedure copy_stored_progs
470 (fromschema in varchar2,
471 toschema in varchar2,
472 p_object_type in varchar2,
473 p_subset in varchar2,
474 compare_level in varchar2);
475 --
476 -- Purpose
477 -- Copy stored programs from schema <fromschema> to schema <toschema>
478 -- Arguments
479 -- fromschema Schema to copy from
480 -- toschema Schema to copy to
481 -- p_object_type 'A' - All, 'B' Bodies, 'S' Specs, ...
482 -- p_subset Only copy objects like 'subset%'
483 -- compare_level How precisely we compare objects
484 -- (to determine whether to copy from src to dst)
485 -- Example
486 -- none
487 -- Notes
488 --
489 -- In general, we want to only re-copy from src to dest if the object
490 -- in src is different from the object in dest.
491 --
492 -- Comparison algorithm:
493 --
494 -- We use the following logic to compare code objects in src schema
495 -- with code objects in destination schema.
496 --
497 -- First check: if object not in dest schema, copy to dest schema
498 -- If object in dest schema, go to next check.
499 --
500 -- Second check: if object in src schema does not have revision
501 -- information, do a full text comparison between the object in src
502 -- and the object in dest schemas. This full compare always happens
503 -- for objects without revision information (ie, the compare_level
504 -- argument is ignored here). If objects different,
505 -- copy from src to dest. If objects same, don't copy.
506 -- If object in src schema has revision information, go to next check.
507 --
508 -- Third check: if object in dest schema does not have revision
509 -- information, copy src to dest. If dest schema object has revision
510 -- info, go to next check.
511 --
512 -- Fourth check: compare revisions between src and dest object.
513 -- If different, copy src to dest. If same, continue to next check.
514 -- Note: we compare the whole header string, not just the revision number
515 --
516 -- Fifth check: we know that src and dest object have same revisions.
517 -- Do object comparison according to the compare_level argument to
518 -- see if the objects are identical. If identical (as far as we checked)
519 -- do not copy src to dest. If not identical, copy src to dest.
520 --
521 -- compare_level possible values and meanings:
522 --
523 -- none: identical revisions means identical objects.
524 --
525 -- lines: verify that objects have same number of source lines
526 --
527 -- chars: verify that objects have same number of source lines
528 -- and same number of source characters
529 --
530 -- full: verify that objects have same number of source lines,
531 -- then compare actual source text.
532 --
533
534 procedure exact_synonym_match (syn_own_schema in varchar2,
535 syn_name in varchar2,
536 tab_owner in varchar2,
537 tab_name in varchar2,
538 exact_match out nocopy boolean,
539 is_obj_w_name out nocopy boolean,
540 typ_exist_obj out nocopy varchar2);
541 --
542 -- Purpose
543 -- Check to see if the specified schema contains a synonym that is
544 -- defined exactly as would be created with the statement:
545 -- 'CREATE SYNONYM syn_own_schema.syn_name FOR tab_owner.tab_name;'
549 -- If does not find exact match, checks to see if syn_own_schema contains
546 --
547 -- If finds exact match, returns exact_match = TRUE
548 --
550 -- any object named syn_name. If it finds such an object, it returns
551 -- is_obj_w_name = TRUE and typ_exist_obj = <type of object found>
552 --
553 -- Types returned are standard object types (as found in dba_objects),
554 -- except for the case where there is both a PACKAGE and a PACKAGE BODY
555 -- named syn_name in the schema. In this case, we return
556 -- typ_exist_obj = 'PKG_S_AND_B'.
557 --
558 -- Arguments
559 -- syn_own_schema The schema in which we look for the specified synonym
560 -- syn_name The name of the synonym
561 -- tab_owner The schema containing the object that the synonym
562 -- points to
563 -- tab_name The name of the object that the synonym points to
564 -- exact_match Did we find an exact match for the specified synonym?
565 -- is_obj_w_name Is there any object in the schema named syn_name?
566 -- typ_exist_obj The type of the existing object (if any object exists)
567 -- Example
568 -- none
569 -- Notes
570 -- see comments above
571 --
572
573 procedure recomp_referenced_objs (object_name in varchar2,
574 object_type in varchar2,
575 obj_list_schema in varchar2,
576 recompile_schema in varchar2);
577 --
578 -- Purpose
579 -- Recompile all objects referenced by a given object.
580 -- Used mainly in case where we get a deadlock trying to create a view.
581 -- (See RDBMS bugs 547700 and 481974; fixed in RDBMS v8.1.4)
582 -- This can occur when copying a view from the APPS schema to the MRC
583 -- schema. The algorithm is:
584 -- - get list of objects the view depends on from the APPS schema
585 -- - recompile this set of objects in the MRC schema if they exist
586 -- and are not already valid in the MRC schema.
587 -- (ignore all errors during recompile)
588 --
589 -- Arguments
590 -- object_name Name of the object for which we compile dependent
591 -- objects
592 -- object_type Type of the object <object name>
593 -- obj_list_schema Schema we search to find all of the dependent
594 -- objects
595 -- recompile_schema Schema in which we do the actual object recompile
596 --
597 -- Example
598 -- none
599 -- Notes
600 -- Making this a public procedure in case it's useful elsewhere
601 --
602
603 --
604 -- Other Misc procedures
605 --
606
607 procedure build_view_columns (from_schema in varchar2,
608 view_name in varchar2,
609 out_column_text out nocopy varchar2);
610 --
611 -- Purpose
612 -- The procedure builds the column name list for a view
613 -- Arguments
614 -- from_schema Schema view exists in
615 -- view_name Name of the view
616 -- out_column_text output
617 -- Example
618 -- none
619 -- Notes
620 -- 1. none
621 --
622
623 procedure show_exception_list;
624 --
625 -- Purpose
626 -- Displays list of exception objects
627 -- Must 'set serverout on' in Sql*Plus in order to see the output
628 -- Arguments
629 -- None
630
631 procedure load_exception_list;
632 --
633 -- Purpose
634 -- Loads the global list of exception objects
635 --
636 -- Arguments
637 -- none
638 --
639 -- Example
640 -- none
641 --
642
643 function matching_exception_object (base_schema_name in varchar2,
644 base_object_name in varchar2,
645 base_object_type in varchar2,
646 except_type in varchar2,
647 apps_schema_name in varchar2,
648 found_cust_obj out nocopy varchar2,
649 cust_obj_correct out nocopy varchar2,
650 index_to_object out nocopy number)
651 return boolean;
652 --
653 -- Purpose
654 -- Determine if the specified object is one of the exception objects
655 -- AND we need to treat it as an exception (ie, the patch which creates
656 -- the exception has been applied).
657 --
658 -- Arguments
659 -- base_schema_name Name of the base schema
660 -- base_object_name Name of object in base schema
661 -- base_object_type Type of object in base schema
662 -- except_type Type of exception
663 -- apps_schema_name Name of the APPS schema
664 -- found_cust_obj Did we find the customized object?
665 -- Valid values are: 'TRUE' or 'FALSE'.
666 -- cust_obj_correct Was the customized object correct?
667 -- Valid values are: 'TRUE' or 'FALSE'.
668 -- If we found the customized object, this will
669 -- always be TRUE, except maybe for synonyms.
673 --
670 -- index_to_object The index to this object on the exception objects
671 -- list. Useful if customized object not correct
672 -- or missing.
674 -- Example
675 -- none
676 --
677 -- Notes
678 -- correct_in_apps and index_to_object should not be used if the
679 -- function returns FALSE, as their values will not be meaningful.
680 --
681
682 procedure initialize (aol_schema in varchar2);
683 --
684 -- Purpose
685 -- Initialize variables is_mc and is_mls
686 -- This cannot be done in the initialization section of the package
687 -- because it requires the aol_schema. Therefore this procedure
691 --
688 -- must be called by every public procedure.
689 -- Argument
690 -- aol_schema, AOL schema name
692
693
694 function compare_releases(release_1 in varchar2, release_2 in varchar2)
695 return boolean;
696 --
697 --
698 -- Purpose
699 --
700 -- Bug 3258312 : Check if we are on a release >= 11.5.10.
701 -- If we are, then, mrc is obsoleted. This function helps
702 -- us do the comparison.
703 --
704 --
705
706
707 end ad_apps_private;
[Home] [Help]