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