1 package body ad_invoker as
2 /* $Header: adinvspb.pls 120.0 2005/05/25 11:49:07 appldev noship $ */
3
4 --
5 -- Global private SQL cursors
6 --
7
8 cursor REWRITE_NOT_INCR is
9 select /*+ ORDERED */
10 u.name owner, o.name object_name,
11 decode(o.type#, 7, 'PROCEDURE', 8, 'FUNCTION', 9, 'PACKAGE', '??')
12 object_type
13 from fnd_oracle_userid fou, sys.user$ u, sys.obj$ o, sys.procedure$ p
14 where fou.read_only_flag = 'U'
15 and fou.install_group_num = 1
16 and u.name = fou.oracle_username
17 and o.owner# = u.user#
18 and o.type# in (7, 8, 9)
19 and o.name not in ('APPS_DDL', 'APPS_ARRAY_DDL')
20 and o.obj# = p.obj#
21 and bitand(p.options, 16) <> 16;
22
23 cursor REWRITE_INCR is
24 select /*+ FULL(o), ORDERED */
25 u.name owner, o.name object_name,
26 decode(o.type#, 7, 'PROCEDURE', 8, 'FUNCTION', 9, 'PACKAGE', '??')
27 object_type
28 from sys.obj$ o, ad_timestamps t, fnd_oracle_userid fou, sys.user$ u,
29 sys.procedure$ p
30 where fou.read_only_flag = 'U'
31 and fou.install_group_num = 1
32 and u.name = fou.oracle_username
33 and o.owner# = u.user#
34 and o.type# in (7, 8, 9)
35 and o.name not in ('APPS_DDL', 'APPS_ARRAY_DDL')
36 and o.obj# = p.obj#
37 and bitand(p.options, 16) <> 16
38 and t.type = 'Invoker Maintenance'
39 and t.attribute = 'Last Run'
40 and o.mtime >= t.timestamp;
41
42 cursor GRANTS_NOT_INCR is
43 select do.owner, do.object_name, do.object_type, u.oracle_username
44 from dba_objects do, fnd_oracle_userid u
45 where do.owner =
46 (select o.oracle_username
47 from fnd_oracle_userid o
48 where o.read_only_flag = 'U'
49 and o.install_group_num = 1)
50 and do.object_type in ('PACKAGE', 'PROCEDURE', 'FUNCTION',
51 'JAVA CLASS')
52 and do.object_name not in ('APPS_DDL', 'APPS_ARRAY_DDL')
53 and u.read_only_flag = 'U'
54 and u.install_group_num > 1;
55
56 cursor GRANTS_INCR is
57 select do.owner, do.object_name, do.object_type, u.oracle_username
58 from dba_objects do, fnd_oracle_userid u, ad_timestamps t
59 where do.owner =
60 (select o.oracle_username
61 from fnd_oracle_userid o
62 where o.read_only_flag = 'U'
63 and o.install_group_num = 1)
64 and do.object_type in ('PACKAGE', 'PROCEDURE', 'FUNCTION',
65 'JAVA CLASS')
66 and do.object_name not in ('APPS_DDL', 'APPS_ARRAY_DDL')
67 and u.read_only_flag = 'U'
68 and u.install_group_num > 1
69 and t.type = 'Invoker Maintenance'
70 and t.attribute = 'Last Run'
71 and do.last_ddl_time >= t.timestamp;
72
73 cursor MRC_GRANTS_OBJ_ID (c_apps_schema in varchar2) is
74 select do.owner, do.object_name, do.object_type
75 from dba_objects do
76 where do.owner = upper(c_apps_schema)
77 and do.object_type in ('PACKAGE', 'PROCEDURE', 'FUNCTION',
78 'JAVA CLASS')
79 and do.object_name not in ('APPS_DDL', 'APPS_ARRAY_DDL')
80 and do.object_name not like 'FFP%';
81
82 -- Private data types
83
84 type rowid_list is table of rowid index by binary_integer;
85
86 type integer_list is table of integer index by binary_integer;
87
88 type owner_list_t is table of varchar2(30) index by binary_integer;
89
90 type object_name_list_t is table of varchar2(128) index by binary_integer;
91
92 type object_type_list_t is table of varchar2(18) index by binary_integer;
93
94 --
95 -- Procedures and Functions
96 --
97
98 procedure get_rewrite_pkgs
99 (is_incremental in varchar2)
100 --
101 -- Loads the AD_INVOKER_TASKS table with the list of packages,
102 -- procedures, and functions in the first APPS schema that need to
103 -- be re-written for Invoker's Rights
104 --
105 -- is_incremental can be either
106 -- 'TRUE' - only process PL/SQL objects that have changed since last run
107 -- 'FALSE' - process all applicable PL/SQL objects
108 --
109 -- If for some reason the row for 'Invoker Maintenance', 'Last Run'
110 -- is missing from AD_TIMESTAMPS, get_rewrite_pkgs() will fail in
111 -- incremental mode. This can be fixed by running the whole
112 -- Invoker's Rights processing logic in non-incremental mode
113 -- (via adadmin "Compile APPS Schema(s)")
114 --
115 is
116 found_authid varchar2(10);
117 invoker_flag varchar2(10);
118 begin
119 ad_apps_private.error_buf := null;
120 --
121 -- Insert rows into ad_invoker_tasks for all packages, procedures, and
122 -- functions that do not contain the 'AUTHID' keyword
123 --
124 -- Only look in the first APPS schema.
125 --
126
127 if is_incremental = 'FALSE' then
128
129 for c1 in REWRITE_NOT_INCR loop
130
131 classify_plsql_object(c1.owner, c1.object_name, c1.object_type,
132 found_authid, invoker_flag);
133
134 if found_authid = 'FALSE' then
135
136 insert into ad_invoker_tasks (
137 phase, owner, name, type, other_schema,
138 worker, done_flag, authid_flag, invoker_flag, boundary_flag)
139 values (
140 1, c1.owner, c1.object_name, c1.object_type, c1.owner,
141 0, 'N', null, null, 'N');
142
143 end if;
144 -- end if no AUTHID keyword found -> needs rewrite
145
146 end loop;
147 -- end loop through PL/SQL specification objects in first APPS schema
148
149 elsif is_incremental = 'TRUE' then
150
151 for c1 in REWRITE_INCR loop
152
153 classify_plsql_object(c1.owner, c1.object_name, c1.object_type,
154 found_authid, invoker_flag);
155
156 if found_authid = 'FALSE' then
157
158 insert into ad_invoker_tasks (
159 phase, owner, name, type, other_schema,
160 worker, done_flag, authid_flag, invoker_flag, boundary_flag)
161 values (
162 1, c1.owner, c1.object_name, c1.object_type, c1.owner,
163 0, 'N', null, null, 'N');
164
165 end if;
166 -- end if no AUTHID keyword found -> needs rewrite
167
168 end loop;
169 -- end loop through PL/SQL specification objects in first APPS schema
170 -- that have changed since the last run
171
172 else
173 raise_application_error(-20000,
174 'is_incremental must be either TRUE or FALSE');
175 end if;
176
177 exception
178 when others then
179 ad_apps_private.error_buf := 'get_rewrite_pkgs(): '||
180 ad_apps_private.error_buf;
181 raise;
182 end get_rewrite_pkgs;
183
184
185 procedure add_phase_boundary
186 (num_workers in integer)
187 --
188 -- Loads the AD_INVOKER_TASKS table with a phase boundary,
189 -- which consists of one row per worker with the boundary_flag set
190 --
191 is
192 num_workers_local pls_integer;
193 counter pls_integer;
194 statement varchar2(300);
195 begin
196 ad_apps_private.error_buf := null;
197 --
198 -- Insert a phase boundary
199 -- (actually a new phase with one row per worker)
200 --
201
202 -- Actual length: 225
203
204 statement :=
205 'insert into ad_invoker_tasks ('||
206 'phase, owner, name, type, other_schema,'||
207 'worker, done_flag, authid_flag, invoker_flag, boundary_flag)'||
208 'values ('||
209 '2, ''Boundary'', ''Boundary'', ''Boundary'', to_char(:wrk_num),'||
210 ':wrk_num, ''N'', null, null, ''Y'')';
211
212 for counter in 1..num_workers loop
213
214 EXECUTE IMMEDIATE statement
215 using counter, counter;
216
217 end loop;
218
219 exception
220 when others then
221 ad_apps_private.error_buf := 'add_phase_boundary('||
222 num_workers||'): '||
223 ad_apps_private.error_buf;
224 raise;
225 end add_phase_boundary;
226
227
228 procedure get_grant_pkgs
229 (is_incremental in varchar2)
230 --
231 -- Loads the AD_INVOKER_TASKS table with the list of packages,
232 -- procedures, functions and Java objects in the first APPS schema
233 -- in a cross product with all of the other APPS schemas.
234 -- Will have to verify the grants/synonyms for each combination of
235 -- first and (2-N)th APPS schemas
236 --
237 -- is_incremental can be either
238 -- 'TRUE' - only process objects that have changed since last run
239 -- 'FALSE' - process all applicable objects
240 --
241 -- If for some reason the row for 'Invoker Maintenance', 'Last Run'
242 -- is missing from AD_TIMESTAMPS, get_grant_pkgs() will fail in
243 -- incremental mode. This can be fixed by running the whole
244 -- Invoker's Rights processing logic in non-incremental mode
245 -- (via adadmin "Compile APPS Schema(s)")
246 --
247 is
248 begin
249 ad_apps_private.error_buf := null;
250 --
251 -- Create rows for each grant task. Insert some data so later updates
252 -- can't cause row chaining.
253 --
254 -- If only one APPS schema, no rows are returned
255 --
256 -- Will update the rows later as follows:
257 --
258 -- authid_flag=N, invoker_flag=null
259 -- if no AUTHID keyword in PL/SQL object source
260 --
261 -- authid_flag=Y, invoker_flag=D
262 -- if AUTHID DEFINER in PL/SQL object source
263 --
264 -- authid_flag=Y, invoker_flag=I
265 -- if AUTHID CURRENT_USER in PL/SQL object source
266 --
267
268 if is_incremental = 'FALSE' then
269
270 for c1 in GRANTS_NOT_INCR loop
271
272 insert into ad_invoker_tasks (
273 phase, owner, name, type, other_schema,
274 worker, done_flag, authid_flag, invoker_flag, boundary_flag)
275 values (
276 3, c1.owner, c1.object_name, c1.object_type, c1.oracle_username,
277 0, 'N', 'X', 'X', 'N');
278
279 end loop;
280 -- end loop through PL/SQL specification objects in first APPS schema
281
282 elsif is_incremental = 'TRUE' then
283
284 for c1 in GRANTS_INCR loop
285
286 insert into ad_invoker_tasks (
287 phase, owner, name, type, other_schema,
288 worker, done_flag, authid_flag, invoker_flag, boundary_flag)
289 values (
290 3, c1.owner, c1.object_name, c1.object_type, c1.oracle_username,
291 0, 'N', 'X', 'X', 'N');
292
293 end loop;
294 -- end loop through PL/SQL specification objects in first APPS schema
295 -- that have changed since the last run
296
297 else
298 raise_application_error(-20000,
299 'is_incremental must be either TRUE or FALSE');
300 end if;
301
302 exception
303 when others then
304 ad_apps_private.error_buf := 'get_grant_pkgs(): '||
305 ad_apps_private.error_buf;
306 raise;
307 end get_grant_pkgs;
308
309
310 procedure invoker_maint_serial
311 (is_incremental in varchar2)
312 --
313 -- Runs the whole Invoker's Rights maintenance logic in serial
314 --
315 -- Does not log errors to a table: just fails if encounters errors
316 --
317 -- The parallel version is strongly recommended over this version
318 -- (just use this from AutoPatch in serial mode)
319 --
320 is
321 found_authid varchar2(10);
322 invoker_flag varchar2(10);
323 begin
324 --
325 -- Clear error buffer
326 --
327
328 ad_apps_private.error_buf := null;
329
330 --
331 -- Run rewrite logic
332 --
333
334 if is_incremental = 'FALSE' then
335
336 for c1 in REWRITE_NOT_INCR loop
337
338 classify_plsql_object(c1.owner, c1.object_name, c1.object_type,
339 found_authid, invoker_flag);
340
341 if found_authid = 'FALSE' then
342
343 rewrite_a_package(c1.owner, c1.object_name, c1.object_type,
344 c1.owner, 'FALSE');
345
346 end if;
347 -- end if no AUTHID keyword found -> needs rewrite
348
349 end loop;
350 -- end loop through PL/SQL specification objects in first APPS schema
351
352 elsif is_incremental = 'TRUE' then
353
354 for c1 in REWRITE_INCR loop
355
356 classify_plsql_object(c1.owner, c1.object_name, c1.object_type,
357 found_authid, invoker_flag);
358
359 if found_authid = 'FALSE' then
360
361 rewrite_a_package(c1.owner, c1.object_name, c1.object_type,
362 c1.owner, 'FALSE');
363
364 end if;
365 -- end if no AUTHID keyword found -> needs rewrite
366
367 end loop;
368 -- end loop through PL/SQL specification objects in first APPS schema
369 -- that have changed since the last run
370
371 else
372 raise_application_error(-20000,
373 'is_incremental must be either TRUE or FALSE [1]');
374 end if;
375
376 --
377 -- Run grant logic
378 --
379
380 if is_incremental = 'FALSE' then
381
382 for c1 in GRANTS_NOT_INCR loop
383
384 if (c1.object_type not like 'JAVA%') then
385
386 classify_plsql_object(c1.owner, c1.object_name, c1.object_type,
387 found_authid, invoker_flag);
388
389 if found_authid = 'FALSE' then
390
391 grant_a_package(c1.owner, c1.object_name, c1.object_type,
392 c1.oracle_username, 'N', null, 'FALSE');
393
394 else
395
396 grant_a_package(c1.owner, c1.object_name, c1.object_type,
397 c1.oracle_username, 'Y', invoker_flag, 'FALSE');
398
399 end if;
400 -- end if no AUTHID keyword found
401 else
402 grant_a_java_object(c1.owner, c1.object_name, c1.object_type,
403 c1.oracle_username, 'FALSE');
404 end if;
405
406 end loop;
407 -- end loop through PL/SQL specification objects in first APPS schema
408
409 elsif is_incremental = 'TRUE' then
410
411 for c1 in GRANTS_INCR loop
412
413 if (c1.object_type not like 'JAVA%') then
414
415 classify_plsql_object(c1.owner, c1.object_name, c1.object_type,
416 found_authid, invoker_flag);
417
418 if found_authid = 'FALSE' then
419
420 grant_a_package(c1.owner, c1.object_name, c1.object_type,
421 c1.oracle_username, 'N', null, 'FALSE');
422
423 else
424
425 grant_a_package(c1.owner, c1.object_name, c1.object_type,
426 c1.oracle_username, 'Y', invoker_flag, 'FALSE');
427
428 end if;
429 -- end if no AUTHID keyword found
430 else
431 grant_a_java_object(c1.owner, c1.object_name, c1.object_type,
432 c1.oracle_username, 'FALSE');
433 end if;
434 end loop;
435 -- end loop through PL/SQL specification objects in first APPS schema
436 -- that have changed since the last run
437
438 else
439 raise_application_error(-20000,
440 'is_incremental must be either TRUE or FALSE [2]');
441 end if;
442
443 --
444 -- Update timestamp
445 --
446
447 update_timestamp('Invoker Maintenance','Last Run');
448
449 exception
450 when others then
451 ad_apps_private.error_buf := 'invoker_maint_serial('||
452 is_incremental || '): '||
453 ad_apps_private.error_buf;
454 raise;
455 end invoker_maint_serial;
456
457
458 procedure invoker_mrc_grants
459 (apps_schema in varchar2,
460 mrc_schema in varchar2)
461 --
462 -- Runs the Invoker's Rights grants logic for MRC/MLS schemas
463 --
464 -- This replaces the non-invoker MRC logic that copies packages, procedures,
465 -- functions, and package bodies
466 --
467 is
468 found_authid varchar2(10);
469 invoker_flag varchar2(10);
470 owner_to_process owner_list_t;
471 object_name_to_process object_name_list_t;
472 object_type_to_process object_type_list_t;
473 row_index binary_integer;
474 row_count binary_integer;
475 the_owner varchar2(30);
476 the_obj_name varchar2(128);
477 the_obj_type varchar2(30);
478 the_mrc_schema varchar2(30);
479 begin
480 --
481 -- Clear error buffer
482 --
483
484 ad_apps_private.error_buf := null;
485
486 -- set variables
487
488 the_mrc_schema := upper(mrc_schema);
489
490 --
491 -- select row set to process into rows_to_process
492 --
493
494 row_index := 1;
495
496 for c1 in MRC_GRANTS_OBJ_ID(apps_schema) loop
497
498 owner_to_process(row_index) := c1.owner;
499 object_name_to_process(row_index) := c1.object_name;
500 object_type_to_process(row_index) := c1.object_type;
501
502 row_index := row_index + 1;
503 end loop;
504
505 -- end loop through PL/SQL specification objects in APPS schema
506
507 row_count := row_index - 1;
508
509 --
510 -- Run grant logic
511 --
512
513 for row_index in 1..row_count loop
514
515 if (object_type_to_process(row_index) not like 'JAVA%') then
516 classify_plsql_object(owner_to_process(row_index),
517 object_name_to_process(row_index),
518 object_type_to_process(row_index),
519 found_authid, invoker_flag);
520
521 if found_authid = 'FALSE' then
522
523 grant_a_package(owner_to_process(row_index),
524 object_name_to_process(row_index),
525 object_type_to_process(row_index),
526 the_mrc_schema, 'N', null, 'FALSE');
527
528 else
529
530 if invoker_flag = 'S' then
531
532 --
533 -- 'S' means this is a definer's rights package with the
534 -- /*nosync*/ comment. For regular APPS schemas, we do not
535 -- copy it. For MRC schemas, we want to treat it just like any
536 -- other definer's rights package: copy it to MRC
537 --
538
539 grant_a_package(owner_to_process(row_index),
540 object_name_to_process(row_index),
541 object_type_to_process(row_index),
542 the_mrc_schema, 'Y', 'D', 'FALSE');
543
544 else
545
546 grant_a_package(owner_to_process(row_index),
547 object_name_to_process(row_index),
548 object_type_to_process(row_index),
549 the_mrc_schema, 'Y', invoker_flag, 'FALSE');
550
551 end if;
552 -- end if invoker_flag = 'S'
553
554 end if;
555 -- end if no AUTHID keyword found
556 else
557 grant_a_java_object(owner_to_process(row_index),
558 object_name_to_process(row_index),
559 object_type_to_process(row_index),
560 the_mrc_schema, 'FALSE');
561 end if;
562
563 end loop;
564 -- end loop through PL/SQL specification objects in APPS schema
565
566 exception
567 when others then
568 ad_apps_private.error_buf := 'invoker_mrc_grants('||
569 apps_schema ||', '|| mrc_schema || '): '||
570 ad_apps_private.error_buf;
571 raise;
572 end invoker_mrc_grants;
573
574
575 procedure calculate_grant_types
576 (worker_num in integer)
577 --
578 -- Sets the authid_flag and invoker_flag for the PL/SQL objects
579 -- that this worker will process.
580 --
581 -- Have to do this in the worker because the grant logic is different
582 -- depending on whether a given package is Invoker's Rights or
583 -- Definer's Rights, and any package that is re-written will change from
584 -- Definer's Rights to Invoker's Rights.
585 --
586 is
587 cursor WORKER_GRANTS is
588 select rowid, owner, name, type
589 from ad_invoker_tasks
590 where phase = 3
591 and type not like 'JAVA%'
592 and worker = calculate_grant_types.worker_num;
593 found_authid varchar2(10);
594 invoker_flag varchar2(10);
595 begin
596 for c1 in WORKER_GRANTS loop
597
598 classify_plsql_object(c1.owner, c1.name, c1.type,
599 found_authid, invoker_flag);
600
601 if found_authid = 'FALSE' then
602
603 update ad_invoker_tasks
604 set authid_flag = 'N',
605 invoker_flag = null
606 where rowid = c1.rowid;
607
608 else
609
610 update ad_invoker_tasks
611 set authid_flag = 'Y',
612 invoker_flag = calculate_grant_types.invoker_flag
613 where rowid = c1.rowid;
614
615 end if;
616 -- end if no AUTHID keyword found
617
618 end loop;
619 -- end loop through grants that this worker will process
620
621 exception
622 when others then
623 ad_apps_private.error_buf := 'calculate_grant_types('||
624 worker_num||'): '||
625 ad_apps_private.error_buf;
626 raise;
627 end calculate_grant_types;
628
629
630 procedure assign_tasks
631 (num_workers in integer)
632 --
633 -- Distribute the tasks evenly among the workers within each phase
634 -- Start each phase with worker 1. Don't update any tasks in the
635 -- phase boundary rows.
636 --
637 is
638 cursor cs1 (phase_num in number) is
639 select rowid
640 from ad_invoker_tasks
641 where phase = phase_num;
642 counter pls_integer;
643 num_workers_local pls_integer;
644 begin
645 ad_apps_private.error_buf := null;
646 num_workers_local := num_workers;
647 --
648 -- Assign tasks for first phase (rewrite PL/SQL)
649 --
650 counter := 1;
651
652 for the_rec in cs1(1) loop
653
654 if mod(counter, num_workers_local + 1) = 0 then
655 counter := 1;
656 end if;
657
658 update ad_invoker_tasks
659 set worker = counter
660 where rowid = the_rec.rowid;
661
662 counter := counter + 1;
663
664 end loop;
665
666 --
667 -- Don't do anything to the second phase
668 -- (which actually serves as the phase boundary for the first phase)
669 --
670
671 --
672 -- Assign tasks for third phase (Do PL/SQL grants/synonyms)
673 --
674 counter := 1;
675
676 for the_rec in cs1(3) loop
677
678 if mod(counter, num_workers_local + 1) = 0 then
679 counter := 1;
680 end if;
681
682 update ad_invoker_tasks
683 set worker = counter
684 where rowid = the_rec.rowid;
685
686 counter := counter + 1;
687
688 end loop;
689
690 exception
691 when others then
692 ad_apps_private.error_buf := 'assign_tasks('||
693 num_workers||'): '||
694 ad_apps_private.error_buf;
695 raise;
696 end assign_tasks;
697
698
699 procedure apps_ddl_check
700 --
701 -- Doesn't do anything if no packages to process
702 -- If are packages to process:
703 -- Compiles any invalid APPS*DDL packages in the database
704 -- Checks for APPS_DDL and APPS_ARRAY_DDL packages in all APPS schemas
705 --
706 is
707 cursor ANY_INV_ROWS is
708 select count(*)
709 from ad_invoker_tasks
710 where phase in (1, 3);
711 cursor APPS_SCHEMAS is
712 select oracle_username
713 from fnd_oracle_userid
714 where read_only_flag = 'U';
715 num_rows number;
716 begin
717 --
718 -- Only run this check if we have anything to do
719 -- Otherwise, exit early
720 --
721 open ANY_INV_ROWS;
722
723 fetch ANY_INV_ROWS
724 into num_rows;
725
726 if ANY_INV_ROWS%NOTFOUND then
727 close ANY_INV_ROWS;
728 raise no_data_found;
729 else
730 close ANY_INV_ROWS;
731 end if;
732
733 if num_rows = 0 then
734 return;
735 end if;
736
737 --
738 -- If we got this far, there are rows to process, so we better make sure
739 -- that all of the APPS schemas have the proper APPS*DDL packages
740 --
741
742 -- First compile any invalid APPS*DDL packages in the whole database
743
744 ad_compile.compile_apps_ddl;
745
746 -- Then verify that the APPS*DDL packages in the APPS schemas are OK
747
748 for apps_rec in APPS_SCHEMAS loop
749
750 ad_apps_private.check_for_apps_ddl(apps_rec.oracle_username);
751
752 end loop;
753
754 exception
755 when others then
756 ad_apps_private.error_buf := 'apps_ddl_check(): '||
757 ad_apps_private.error_buf;
758 raise;
759 end apps_ddl_check;
760
761
762 procedure update_done
763 (what_row in rowid)
764 --
765 -- Sets AD_INVOKER_TASKS.DONE_FLAG to 'Y' for this particular row.
766 -- Commits the changes using an autonomous transaction
767 --
768 is
769 PRAGMA AUTONOMOUS_TRANSACTION;
770 begin
771
772 update ad_invoker_tasks
773 set done_flag = 'Y'
774 where rowid = what_row;
775
776 commit;
777
778 exception
779 when others then
780 ad_apps_private.error_buf := 'update_done('||
781 what_row||'): '||
782 ad_apps_private.error_buf;
783 raise;
784 end update_done;
785
786
787 procedure rewrite_pkgs
788 (worker_num in integer)
789 --
790 -- Re-writes the packages assigned to this worker to use
791 -- Invoker's Rights.
792 --
793 is
794 rows_to_process rowid_list;
795 row_index binary_integer;
796 row_count binary_integer;
797 the_owner varchar2(30);
798 obj_name varchar2(128);
799 obj_type varchar2(30);
800 other_schema varchar2(30);
801 is_done varchar2(2);
802 cursor c1 (worker_number in number) is
803 select rowid the_row_id
804 from ad_invoker_tasks
805 where phase = 1
806 and worker = worker_number;
807 cursor c2 (c_row_id in rowid) is
808 select owner, name, type, other_schema, done_flag
809 from ad_invoker_tasks
810 where rowid = c_row_id;
811 begin
812 --
813 -- clear error buffer
814 --
815 ad_apps_private.error_buf := null;
816
817 --
818 -- select row set to process into rows_to_process
819 --
820 row_index := 1;
821 for c1_rec in c1(worker_num) loop
822
823 rows_to_process(row_index) := c1_rec.the_row_id;
824
825 row_index := row_index + 1;
826 end loop;
827
828 row_count := row_index - 1;
829
830 --
831 -- actually process rows
832 --
833 for row_index in 1..row_count loop
834
835 open c2 (rows_to_process(row_index));
836
837 fetch c2
838 into the_owner, obj_name, obj_type, other_schema, is_done;
839
840 if c2%NOTFOUND then
841 close c2;
842 raise no_data_found;
843 end if;
844
845 close c2;
846
847 if nvl(is_done,'N') = 'N' then
848
849 rewrite_a_package(the_owner, obj_name, obj_type, other_schema, 'TRUE');
850
851 update_done(rows_to_process(row_index));
852
853 end if;
854
855 end loop;
856
857 exception
858 when others then
859 ad_apps_private.error_buf := 'rewrite_pkgs('||
860 worker_num||'): '||
861 ad_apps_private.error_buf;
862 raise;
863 end rewrite_pkgs;
864
865
866 procedure rewrite_a_package
867 (owner in varchar2,
868 name in varchar2,
869 type in varchar2,
870 new_owner in varchar2,
871 log_to_table in varchar2)
872 --
873 -- Rewrites a specific package to use Invoker's Rights.
874 --
875 -- if log_to_table is 'TRUE', writes errors to ad_parallel_compile_errors
876 --
877 -- Got most of the logic from ad_apps_private.copy_code
878 --
879 is
880 success_with_comp_error exception;
881 PRAGMA EXCEPTION_INIT(success_with_comp_error, -24344);
882 cursor c1 is
883 select text from dba_source
884 where owner = upper(rewrite_a_package.owner)
885 and name = upper(rewrite_a_package.name)
886 and type = upper(rewrite_a_package.type)
887 order by line;
888 row_count integer;
889 source_line varchar2(255);
890 new_source_line varchar2(300);
891 found_authid boolean;
892 authid_line number;
893 found_is_as boolean;
894 is_as_line number;
895 word_location number;
896 is_as_position number;
897 we_found_it varchar2(10);
898 timestamp varchar2(20);
899 begin
900 -- dbms_output.put_line('rewrite_a_package('
901 -- || owner ||', '|| name ||', '|| type ||', '
902 -- || new_owner || ', '|| log_to_table ||')');
903 -- return;
904
905 -- Validate PL/SQL object type
906
907 if upper(type) <> 'PACKAGE'
908 and upper(type) <> 'PROCEDURE'
909 and upper(type) <> 'FUNCTION' then
910
911 if log_to_table = 'TRUE' then
912
913 timestamp := to_char(sysdate,'YYYY-MM-DD:HH24:MI:SS');
914
915 insert into ad_parallel_compile_errors (
916 owner, worker_number, timestamp, type, text)
917 select upper(owner),
918 0,
919 timestamp,
920 '1 REWRITE ERROR',
921 upper(type)||' '||upper(owner)||'.'||upper(name)||
922 ': Object type "'||upper(type)||'" not supported.'
923 from sys.dual;
924
925 end if;
926 -- end if log errors/messages to ad_parallel_compile_errors
927
928 -- dbms_output.put_line('Do not know how to rewrite objects of type "'||
929 -- upper(type)||'".');
930
931 return;
932
933 end if;
934 -- end if not valid type
935
936 -- Initialize variables
937
938 found_authid := FALSE;
939 found_is_as := FALSE;
940
941 authid_line := 0;
942 is_as_line := 0;
943
944 --
945 -- get the source text
946 -- purposely start counter at 1 as later we add the
947 -- create or replace at line 1
948 --
949 -- parse the text while we're at it
950 --
951 row_count:=1;
952 for c1rec in c1 loop
953 row_count:=row_count+1;
954
955 if not found_is_as then
956
957 -- Check for AS
958
959 word_location := instr(upper(c1rec.text),'AS');
960
961 if word_location <> 0 then
962
963 -- Do more strict checking
964
965 verify_token_location(upper(c1rec.text), 'AS',
966 we_found_it, word_location);
967
968 if we_found_it = 'TRUE' then
969
970 found_is_as := TRUE;
971 is_as_line := row_count - 1;
972 is_as_position := word_location;
973 source_line := c1rec.text;
974
975 end if;
976 -- end if we really found AS
977
978 end if;
979 -- end if found AS using INSTR
980
981 -- Check for IS
982
983 word_location := instr(upper(c1rec.text),'IS');
984
985 if word_location <> 0 then
986
987 -- Do more strict checking
988
989 verify_token_location(upper(c1rec.text), 'IS',
990 we_found_it, word_location);
991
992 if we_found_it = 'TRUE' then
993
994 found_is_as := TRUE;
995 is_as_line := row_count - 1;
996 is_as_position := word_location;
997 source_line := c1rec.text;
998
999 end if;
1000 -- end if we really found IS
1001
1002 end if;
1003 -- end if found IS using INSTR
1004
1005 -- Check for AUTHID, and return if we find it
1006
1007 word_location := instr(upper(c1rec.text),'AUTHID');
1008
1009 if word_location <> 0 then
1010
1011 -- Do more strict checking
1012
1013 verify_token_location(upper(c1rec.text), 'AUTHID',
1014 we_found_it, word_location);
1015
1016 if we_found_it = 'TRUE' then
1017
1018 -- If we found an AUTHID keyword, return
1019
1020 found_authid := TRUE;
1021 authid_line := row_count - 1;
1022
1023 -- dbms_output.put_line('Found AUTHID at line '||authid_line);
1024
1025 if log_to_table = 'TRUE' then
1026
1027 timestamp := to_char(sysdate,'YYYY-MM-DD:HH24:MI:SS');
1028
1029 insert into ad_parallel_compile_errors (
1030 owner, worker_number, timestamp, type, text)
1031 select upper(owner),
1032 0,
1033 timestamp,
1034 '1 REWRITE MESSAGE',
1035 upper(type)||' '||upper(owner)||'.'||upper(name)||
1036 ' contains AUTHID - Not rewritten'
1037 from sys.dual;
1038
1039 end if;
1040 -- end if log errors/messages to ad_parallel_compile_errors
1041
1042 return;
1043
1044 end if;
1045 -- end if we really found AUTHID
1046
1047 end if;
1048 -- end if found AUTHID using INSTR
1049
1050 end if;
1051 -- end parse lines until we find the IS/AS keyword
1052
1053 -- build one line of sql statement in the global variable in
1054 -- the global array variable in to schema.
1055
1056 if (length (c1rec.text) > 255) then
1057
1058 -- text > 255 chars
1059 --
1060 -- log message to error table if requested
1061 -- ignore the error and return success
1062 -- (don't try to rewrite package)
1063 --
1064
1065 if log_to_table = 'TRUE' then
1066
1067 timestamp := to_char(sysdate,'YYYY-MM-DD:HH24:MI:SS');
1068
1069 insert into ad_parallel_compile_errors (
1070 owner, worker_number, timestamp, type, text)
1071 select upper(owner),
1072 0,
1073 timestamp,
1074 '1 REWRITE MESSAGE',
1075 upper(type)||' '||upper(owner)||'.'||upper(name)||
1076 ' contains line(s) > 255 chars - Not rewritten'
1077 from sys.dual;
1078
1079 end if;
1080 -- end if log errors/messages to ad_parallel_compile_errors
1081
1082 return;
1083
1084 else
1085
1086 -- text <= 255 chars
1087 --
1088 -- just add to array of source text lines
1089 --
1090
1091 ad_apps_private.do_array_assignment(rewrite_a_package.new_owner,
1092 c1rec.text, row_count);
1093
1094 end if;
1095 -- end if text <= 255 chars
1096
1097 end loop;
1098 -- end loop to get and parse source text
1099
1100 --
1101 -- Alter the line on which we found the IS|AS
1102 --
1103 -- The parsing above is fairly good, but there are still a
1104 -- few failure cases I can think of (maybe more?):
1105 --
1106 -- 1) it doesn't handle comments
1107 --
1108 -- We will ignore this for now
1109 --
1110 -- 2) if the line is already pretty long, we will fail below if adding
1111 -- AUTHID CURRENT_USER pushes the length past 255
1112 --
1113 -- If the line would be too long, just exit the procedure without
1114 -- rewriting the PL/SQL object. That's probably better than
1115 -- raising an exception. If someone wonders why their PL/SQL
1116 -- object didn't get rewritten, we can always just explain
1117 -- that the rewrite routine doesn't work if the IS/AS keyword
1118 -- is on a long line.
1119 --
1120
1121 if not found_is_as then
1122 --
1123 -- This should never happen
1124 -- If it does, just exit without editing the PL/SQL object
1125 --
1126
1127 -- dbms_output.put_line('No IS/AS keyword found in '||upper(type)||' '||
1128 -- upper(owner)||'.'||upper(name));
1129
1130 if log_to_table = 'TRUE' then
1131
1132 timestamp := to_char(sysdate,'YYYY-MM-DD:HH24:MI:SS');
1133
1134 insert into ad_parallel_compile_errors (
1135 owner, worker_number, timestamp, type, text)
1136 select upper(owner),
1137 0,
1138 timestamp,
1139 '1 REWRITE ERROR',
1140 upper(type)||' '||upper(owner)||'.'||upper(name)||
1141 ' has no IS/AS keyword - Not rewritten'
1142 from sys.dual;
1143
1144 end if;
1145 -- end if log errors/messages to ad_parallel_compile_errors
1146
1147 return;
1148
1149 end if;
1150 -- end if didn't find IS/AS keyword
1151
1152 --
1153 -- Rewrite PL/SQL object
1154 --
1155
1156 new_source_line := substr(source_line,1,is_as_position-1)||
1157 'AUTHID CURRENT_USER '||substr(source_line,is_as_position);
1158
1159 if length(new_source_line) > 255 then
1160
1161 if log_to_table = 'TRUE' then
1162
1163 timestamp := to_char(sysdate,'YYYY-MM-DD:HH24:MI:SS');
1164
1165 insert into ad_parallel_compile_errors (
1166 owner, worker_number, timestamp, type, text)
1167 select upper(owner),
1168 0,
1169 timestamp,
1170 '1 REWRITE ERROR',
1171 upper(type)||' '||upper(owner)||'.'||upper(name)||
1172 ' has IS/AS keyword on long line - Not rewritten'
1173 from sys.dual;
1174
1175 end if;
1176 -- end if log errors/messages to ad_parallel_compile_errors
1177
1178 return;
1179 end if;
1180 -- end if edited line is too long
1181
1182 ad_apps_private.do_array_assignment(rewrite_a_package.new_owner,
1183 new_source_line, is_as_line+1);
1184
1185 --
1186 -- once we have fetched and processed all source
1187 -- then create the object
1188 --
1189 declare
1190 statement varchar2(256);
1191 name_already_used exception;
1192 pragma exception_init(name_already_used, -955);
1193 begin
1194 statement := 'create or replace ';
1195 -- build the first line of the array of sql statement
1196 ad_apps_private.do_array_assignment(rewrite_a_package.new_owner,
1197 statement, 1);
1198 -- execute the array of statement.
1199 ad_apps_private.do_apps_array_ddl(rewrite_a_package.new_owner,
1200 1, row_count);
1201 exception
1202 when name_already_used then
1203 --
1204 -- This should never happen, but it doesn't hurt to leave this
1205 -- code here
1206 --
1207 -- first reset error buf
1208 ad_apps_private.error_buf := null;
1209 -- drop any synonym by such name and retry
1210 ad_apps_private.drop_object(rewrite_a_package.new_owner,
1211 upper(rewrite_a_package.name), 'SYNONYM');
1212 ad_apps_private.do_apps_array_ddl(rewrite_a_package.new_owner,
1213 1, row_count);
1214 end;
1215
1216 exception
1217 when success_with_comp_error then
1218 --
1219 -- Trap and ignore ORA-24344: success with compilation error
1220 -- This only happens on ORACLE 8
1221 --
1222 -- reset main error buffer
1223 ad_apps_private.error_buf := null;
1224 when others then
1225 declare
1226 sql_error varchar2(1996);
1227 begin
1228 if log_to_table = 'TRUE' then
1229
1230 timestamp := to_char(sysdate,'YYYY-MM-DD:HH24:MI:SS');
1231
1232 insert into ad_parallel_compile_errors (
1233 owner, worker_number, timestamp, type, text)
1234 select upper(owner),
1235 0,
1236 timestamp,
1237 '1 REWRITE ERROR - A',
1238 upper(type)||' '||upper(owner)||'.'||upper(name)||
1239 ': Generic error occurred.'
1240 from sys.dual;
1241
1242 sql_error := SQLERRM;
1243
1244 insert into ad_parallel_compile_errors (
1245 owner, worker_number, timestamp, type, text)
1246 select upper(owner),
1247 0,
1248 timestamp,
1249 '1 REWRITE ERROR - B',
1250 substr(upper(type)||' '||upper(owner)||'.'||upper(name)||
1251 ': '||sql_error,1,1996)
1252 from sys.dual;
1253
1254 if ad_apps_private.error_buf is not null then
1255
1256 insert into ad_parallel_compile_errors (
1257 owner, worker_number, timestamp, type, text)
1258 select upper(owner),
1259 0,
1260 timestamp,
1261 '1 REWRITE ERROR - C',
1262 substr(upper(type)||' '||upper(owner)||'.'||upper(name)||
1263 ': '||ad_apps_private.error_buf,1,1996)
1264 from sys.dual;
1265
1266 end if;
1267 -- error buf contains information
1268
1269 -- clear error buf
1270 ad_apps_private.error_buf := null;
1271
1272 else
1273 raise;
1274 end if;
1275 -- end if log errors/messages to ad_parallel_compile_errors
1276 exception
1277 -- should only fire if we have errors writing to the log table
1278 when others then
1279 ad_apps_private.error_buf := 'rewrite_a_package('
1280 || owner ||', '|| name ||', '|| type ||', '
1281 || new_owner || ', '|| log_to_table ||'): '||
1282 ad_apps_private.error_buf;
1283 raise;
1284 end;
1285 end rewrite_a_package;
1286
1287
1288 procedure rewrite_plsql_array
1289 (the_array in out nocopy dbms_sql.varchar2s,
1290 lb in number,
1291 ub in number,
1292 type in varchar2)
1293 --
1294 -- Rewrites the PL/SQL object creation text stored in the array
1295 -- to use Invoker's Rights.
1296 --
1297 -- DOES NOT CREATE THE OBJECT
1298 --
1299 -- Got most of the logic from rewrite_a_package
1300 --
1301 is
1302 success_with_comp_error exception;
1303 PRAGMA EXCEPTION_INIT(success_with_comp_error, -24344);
1304 source_line varchar2(255);
1305 new_source_line varchar2(300);
1306 found_authid boolean;
1307 authid_line number;
1308 found_is_as boolean;
1309 is_as_line number;
1310 word_location number;
1311 is_as_position number;
1312 we_found_it varchar2(10);
1313 timestamp varchar2(20);
1314 begin
1315 -- Validate PL/SQL object type
1316
1317 if upper(type) <> 'PACKAGE'
1318 and upper(type) <> 'PROCEDURE'
1319 and upper(type) <> 'FUNCTION' then
1320
1321 raise_application_error(-20000,
1322 'Do not know how to rewrite objects of type "'|| upper(type)||'".');
1323
1324 end if;
1325 -- end if not valid type
1326
1327 -- Initialize variables
1328
1329 found_authid := FALSE;
1330 found_is_as := FALSE;
1331
1332 authid_line := 0;
1333 is_as_line := 0;
1334
1335 --
1336 -- loop through and parse source text
1337 --
1338
1339 for counter in lb..ub loop
1340
1341 if not found_is_as then
1342
1343 -- Check for AS
1344
1345 word_location := instr(upper(the_array(counter)),'AS');
1346
1347 if word_location <> 0 then
1348
1349 -- Do more strict checking
1350
1351 verify_token_location(upper(the_array(counter)), 'AS',
1352 we_found_it, word_location);
1353
1354 if we_found_it = 'TRUE' then
1355
1356 found_is_as := TRUE;
1357 is_as_line := counter;
1358 is_as_position := word_location;
1359 source_line := the_array(counter);
1360
1361 end if;
1362 -- end if we really found AS
1363
1364 end if;
1365 -- end if found AS using INSTR
1366
1367 -- Check for IS
1368
1369 word_location := instr(upper(the_array(counter)),'IS');
1370
1371 if word_location <> 0 then
1372
1373 -- Do more strict checking
1374
1375 verify_token_location(upper(the_array(counter)), 'IS',
1376 we_found_it, word_location);
1377
1378 if we_found_it = 'TRUE' then
1379
1380 found_is_as := TRUE;
1381 is_as_line := counter;
1382 is_as_position := word_location;
1383 source_line := the_array(counter);
1384
1385 end if;
1386 -- end if we really found IS
1387
1388 end if;
1389 -- end if found IS using INSTR
1390
1391 -- Check for AUTHID
1392
1393 word_location := instr(upper(the_array(counter)),'AUTHID');
1394
1395 if word_location <> 0 then
1396
1397 -- Do more strict checking
1398
1399 verify_token_location(upper(the_array(counter)), 'AUTHID',
1400 we_found_it, word_location);
1401
1402 if we_found_it = 'TRUE' then
1403
1404 -- We found an AUTHID keyword
1405
1406 found_authid := TRUE;
1407 authid_line := counter;
1408
1409 -- dbms_output.put_line('Found AUTHID at line '||authid_line);
1410
1411 end if;
1412 -- end if we really found AUTHID
1413
1414 end if;
1415 -- end if found AUTHID using INSTR
1416
1417 else
1418 -- if we already found the IS/AS keyword, break out of the loop
1419
1420 exit;
1421
1422 end if;
1423 -- end parse lines until we find the IS/AS keyword
1424
1425 end loop;
1426 -- end loop to get and parse source text
1427
1428 -- just exit if found authid keyword, as nothing needs to be rewritten
1429
1430 if found_authid then
1431 return;
1432 end if;
1433
1434 --
1435 -- Alter the line on which we found the IS|AS
1436 --
1437 -- The parsing above is fairly good, but there are still a
1438 -- few failure cases I can think of (maybe more?):
1439 --
1440 -- 1) it doesn't handle comments
1441 --
1442 -- We will ignore this for now
1443 --
1444 -- 2) if the line is already pretty long, we will fail below if adding
1445 -- AUTHID CURRENT_USER pushes the length past 255
1446 --
1447 -- If the line would be too long, just exit the procedure without
1448 -- rewriting the PL/SQL object. That's probably better than
1449 -- raising an exception. If someone wonders why their PL/SQL
1450 -- object didn't get rewritten, we can always just explain
1451 -- that the rewrite routine doesn't work if the IS/AS keyword
1452 -- is on a long line.
1453 --
1454
1455 if not found_is_as then
1456 --
1457 -- This should never happen
1458 --
1459 -- If it does, exit without rewriting the source text
1460 --
1461
1462 return;
1463
1464 end if;
1465 -- end if didn't find IS/AS keyword
1466
1467 --
1468 -- Rewrite PL/SQL object creation line
1469 --
1470
1471 new_source_line := substr(source_line,1,is_as_position-1)||
1472 'AUTHID CURRENT_USER '||substr(source_line,is_as_position);
1473
1474 if length(new_source_line) > 255 then
1475
1476 raise_application_error(-20000,
1477 'Source text too long at line '||is_as_position);
1478
1479 end if;
1480 -- end if edited line is too long
1481
1482 the_array(is_as_line) := new_source_line;
1483
1484 exception
1485 when others then
1486 ad_apps_private.error_buf := 'rewrite_plsql_array('
1487 || ' <array>, '|| lb ||', ' || ub ||', '
1488 || type || ' ): '||
1489 ad_apps_private.error_buf;
1490 raise;
1491 end rewrite_plsql_array;
1492
1493
1494 procedure grant_pkgs
1495 (worker_num in integer)
1496 --
1497 -- Creates/verifies the correct grants for all packages and Java classes
1498 -- assigned to this worker
1499 --
1500 is
1501 cursor c1 (worker_number in number) is
1502 select rowid, owner, name, type, other_schema,
1503 authid_flag, invoker_flag, done_flag
1504 from ad_invoker_tasks
1505 where phase = 3
1506 and worker = worker_number;
1507 begin
1508 --
1509 -- clear error buffer
1510 --
1511 ad_apps_private.error_buf := null;
1512
1513 for c1_rec in c1(worker_num) loop
1514
1515 if nvl(c1_rec.done_flag,'N') = 'N' then
1516
1517 if (c1_rec.type not like 'JAVA%') then
1518 grant_a_package(c1_rec.owner, c1_rec.name, c1_rec.type,
1519 c1_rec.other_schema, c1_rec.authid_flag, c1_rec.invoker_flag,
1520 'TRUE');
1521 else
1522 grant_a_java_object(c1_rec.owner, c1_rec.name, c1_rec.type,
1523 c1_rec.other_schema, 'TRUE');
1524 end if;
1525
1526 update_done(c1_rec.rowid);
1527
1528 end if;
1529
1530 end loop;
1531
1532 exception
1533 when others then
1534 ad_apps_private.error_buf := 'grant_pkgs('||
1535 worker_num||'): '||
1536 ad_apps_private.error_buf;
1537 raise;
1538 end grant_pkgs;
1539
1540
1541 procedure grant_a_package
1542 (owner in varchar2,
1543 name in varchar2,
1544 type in varchar2,
1545 new_owner in varchar2,
1546 authid_flag in varchar2,
1547 invoker_flag in varchar2,
1548 log_to_table in varchar2)
1549 --
1550 -- Creates/verifies the correct grants for a specific package
1551 --
1552 -- if log_to_table is 'TRUE', writes errors to ad_parallel_compile_errors
1553 --
1554 is
1555 invoker_plsql boolean;
1556 definer_plsql boolean;
1557 nosync_plsql boolean;
1558 statement varchar2(200);
1559 synonym_ok boolean;
1560 any_obj_w_name boolean;
1561 type_of_object varchar2(30);
1562 obj_type_new varchar2(30);
1563 timestamp varchar2(20);
1564 exact_code_match varchar2(10);
1565 begin
1566 -- dbms_output.put_line('grant_a_package('
1567 -- || owner ||', '|| name ||', '|| type ||', '|| new_owner
1568 -- || ', '|| authid_flag || ', '|| invoker_flag
1569 -- || ', '|| log_to_table ||')');
1570 -- return;
1571
1572 -- Validate PL/SQL object type
1573
1574 if upper(type) <> 'PACKAGE'
1575 and upper(type) <> 'PROCEDURE'
1576 and upper(type) <> 'FUNCTION' then
1577
1578 if log_to_table = 'TRUE' then
1579
1580 timestamp := to_char(sysdate,'YYYY-MM-DD:HH24:MI:SS');
1581
1582 insert into ad_parallel_compile_errors (
1583 owner, worker_number, timestamp, type, text)
1584 select upper(owner),
1585 0,
1586 timestamp,
1587 '3 GRANT ERROR',
1588 upper(type)||' '||upper(owner)||'.'||upper(name)||
1589 ': Object type "'||upper(type)||'" not supported.'
1590 from sys.dual;
1591
1592 return;
1593
1594 else
1595
1596 raise_application_error(-20000,upper(type)||' '||upper(owner)||
1597 '.'||upper(name)||': Object type "'||upper(type)||
1598 '" not supported.');
1599
1600 end if;
1601 -- end if log errors/messages to ad_parallel_compile_errors
1602
1603 end if;
1604 -- end if not valid type
1605
1606 -- Init variables
1607
1608 invoker_plsql := FALSE;
1609 definer_plsql := FALSE;
1610 nosync_plsql := FALSE;
1611
1612 --
1613 -- Decide how to handle this PL/SQL object, based on
1614 -- authid_flag and invoker_flag
1615
1616 if authid_flag = 'N' then
1617
1618 definer_plsql := TRUE;
1619
1620 elsif authid_flag = 'Y' then
1621
1622 if invoker_flag = 'I' then
1623
1624 invoker_plsql := TRUE;
1625
1626 elsif invoker_flag = 'D' then
1627
1628 definer_plsql := TRUE;
1629
1630 elsif invoker_flag = 'S' then
1631
1632 nosync_plsql := TRUE;
1633
1634 else
1635
1636 if log_to_table = 'TRUE' then
1637
1638 timestamp := to_char(sysdate,'YYYY-MM-DD:HH24:MI:SS');
1639
1640 insert into ad_parallel_compile_errors (
1641 owner, worker_number, timestamp, type, text)
1642 select upper(owner),
1643 0,
1644 timestamp,
1645 '3 GRANT ERROR',
1646 upper(type)||' '||upper(owner)||'.'||upper(name)||
1647 ': Invalid value for invoker_flag: "'||invoker_flag||'"'
1648 from sys.dual;
1649
1650 return;
1651
1652 else
1653
1654 raise_application_error(-20000,upper(type)||' '||upper(owner)||
1655 '.'||upper(name)||': Invalid value for invoker_flag: "'||
1656 invoker_flag||'"');
1657
1658 end if;
1659 -- end if log errors to table
1660
1661 end if;
1662 -- end if statement to handle/validate settings for invoker_flag
1663
1664 else
1665
1666 if log_to_table = 'TRUE' then
1667
1668 timestamp := to_char(sysdate,'YYYY-MM-DD:HH24:MI:SS');
1669
1670 insert into ad_parallel_compile_errors (
1671 owner, worker_number, timestamp, type, text)
1672 select upper(owner),
1673 0,
1674 timestamp,
1675 '3 GRANT ERROR',
1676 upper(type)||' '||upper(owner)||'.'||upper(name)||
1677 ': Invalid value for authid_flag: "'||authid_flag||'"'
1678 from sys.dual;
1679
1680 return;
1681
1682 else
1683
1684 raise_application_error(-20000,upper(type)||' '||upper(owner)||
1685 '.'||upper(name)||': Invalid value for authid_flag: "'||
1686 authid_flag||'"');
1687
1688 end if;
1689 -- end if log errors to table
1690
1691 end if;
1692 -- end if statement to figure out how to handle this PL/SQL object
1693
1694 --
1695 -- Perform correct logic based on classification of this PL/SQL object
1696 --
1697
1698 if invoker_plsql then
1699
1700 --
1701 -- Create grant
1702 --
1703
1704 statement := 'grant all on "'||upper(name)||'" to '||upper(new_owner)||
1705 ' with grant option';
1706
1707 ad_apps_private.do_apps_ddl(upper(owner), statement);
1708
1709 --
1710 -- Check for correct synonym
1711 --
1712
1713 ad_apps_private.exact_synonym_match(upper(new_owner), upper(name),
1714 upper(owner), upper(name), synonym_ok, any_obj_w_name,
1715 type_of_object);
1716
1717 if not synonym_ok then
1718
1719 --
1720 -- drop any existing object with this name
1721 --
1722
1723 if any_obj_w_name then
1724
1725 if type_of_object = 'PKG_S_AND_B'
1726 or type_of_object = 'PACKAGE'
1727 or type_of_object = 'PACKAGE BODY' then
1728 -- existing object is package
1729
1730 ad_apps_private.drop_object(upper(new_owner), upper(name),
1731 'PACKAGE');
1732 else
1733 -- existing object not package
1734
1735 ad_apps_private.drop_object(upper(new_owner), upper(name),
1736 type_of_object);
1737 end if;
1738 -- end if existing object is package
1739
1740 end if;
1741 -- end if any existing object with this name
1742
1743 --
1744 -- then create correct synonym
1745 --
1746
1747 statement := 'create synonym '||upper(name)||' for '||upper(owner)||
1748 '.'||upper(name);
1749
1750 ad_apps_private.do_apps_ddl(upper(new_owner), statement);
1751
1752 end if;
1753 -- end if synonym was not exactly correct
1754
1755 elsif definer_plsql then
1756
1757 --
1758 -- Check to see if there is any object in the other schema
1759 -- with this name
1760 --
1761
1762 ad_apps_private.exact_synonym_match(upper(new_owner), upper(name),
1763 upper(owner), upper(name), synonym_ok, any_obj_w_name,
1764 type_of_object);
1765
1766 if any_obj_w_name then
1767
1768 --
1769 -- if not the right type of object, drop it and recreate as
1770 -- a copy of the current object
1771 --
1772
1773 if type_of_object <> upper(type)
1774 and not ( upper(type) = 'PACKAGE'
1775 and type_of_object = 'PKG_S_AND_B') then
1776
1777 --
1778 -- Drop existing object from other schema
1779 --
1780
1781 if type_of_object = 'PKG_S_AND_B'
1782 or type_of_object = 'PACKAGE'
1783 or type_of_object = 'PACKAGE BODY' then
1784 -- existing object is package
1785
1786 ad_apps_private.drop_object(upper(new_owner), upper(name),
1787 'PACKAGE');
1788 else
1789 -- existing object not package
1790
1791 ad_apps_private.drop_object(upper(new_owner), upper(name),
1792 type_of_object);
1793 end if;
1794 -- end if existing object is package
1795
1796 --
1797 -- Create a copy of this object in the other schema
1798 --
1799
1800 ad_apps_private.copy_code(upper(name), upper(type), upper(owner),
1801 upper(new_owner));
1802
1803 --
1804 -- If object is a package and the package body exists
1805 -- in the source schema, also copy the package body
1806 -- to the other schema
1807 --
1808
1809 ad_apps_private.exact_synonym_match(upper(owner), upper(name),
1810 upper(owner), upper(name), synonym_ok, any_obj_w_name,
1811 type_of_object);
1812
1813 if any_obj_w_name and type_of_object = 'PKG_S_AND_B' then
1814
1815 ad_apps_private.copy_code(upper(name), 'PACKAGE BODY', upper(owner),
1816 upper(new_owner));
1817
1818 end if;
1819 -- end if object is package spec, and corresponding body exists
1820
1821 else
1822
1823 --
1824 -- Correct type in other schema
1825 -- Compare to validate that it exactly matches the object
1826 -- from the owner schema
1827 --
1828
1829 ad_apps_private.compare_code(upper(name), upper(type), upper(owner),
1830 upper(new_owner), 'FULL', exact_code_match);
1831
1832 --
1833 -- If code matches exactly, don't do anything
1834 -- If code doesn't match exactly, recopy code from owner schema
1835 -- to new schema
1836 --
1837
1838 if exact_code_match <> 'TRUE' then
1839
1840 ad_apps_private.copy_code(upper(name), upper(type), upper(owner),
1841 upper(new_owner));
1842
1843 end if;
1844 -- end if code does not match exactly
1845
1846 --
1847 -- If object is a package and the package body exists
1848 -- in the source schema, also compare the package body text
1849 -- with the package body in the other schema
1850 --
1851
1852 -- save object type in other schema for use below
1853
1854 obj_type_new := type_of_object;
1855
1856 -- now find out if this is a package that also has a body
1857
1858 ad_apps_private.exact_synonym_match(upper(owner), upper(name),
1859 upper(owner), upper(name), synonym_ok, any_obj_w_name,
1860 type_of_object);
1861
1862 if any_obj_w_name and type_of_object = 'PKG_S_AND_B' then
1863
1864 --
1865 -- if type in other schema is just 'PACKAGE', unconditionally
1866 -- copy the body over
1867 --
1868 -- if type is 'PKG_S_AND_B', then compare the bodies and then
1869 -- only copy the body over if not identical
1870 --
1871
1872 if obj_type_new = 'PACKAGE' then
1873
1874 ad_apps_private.copy_code(upper(name), 'PACKAGE BODY',
1875 upper(owner), upper(new_owner));
1876
1877 elsif obj_type_new = 'PKG_S_AND_B' then
1878
1879 --
1880 -- Compare pkg bodies
1881 --
1882
1883 ad_apps_private.compare_code(upper(name), 'PACKAGE BODY',
1884 upper(owner), upper(new_owner), 'FULL', exact_code_match);
1885
1886 --
1887 -- If code matches exactly, don't do anything
1888 -- If code doesn't match exactly, recopy code from owner schema
1889 -- to new schema
1890 --
1891
1892 if exact_code_match <> 'TRUE' then
1893
1894 ad_apps_private.copy_code(upper(name), 'PACKAGE BODY',
1895 upper(owner), upper(new_owner));
1896
1897 end if;
1898 -- end if code does not match exactly
1899
1900 else
1901
1902 if log_to_table = 'TRUE' then
1903
1904 timestamp := to_char(sysdate,'YYYY-MM-DD:HH24:MI:SS');
1905
1906 insert into ad_parallel_compile_errors (
1907 owner, worker_number, timestamp, type, text)
1908 select upper(owner),
1909 0,
1910 timestamp,
1911 '3 GRANT ERROR',
1912 upper(type)||' '||upper(owner)||'.'||upper(name)||
1913 ': Internal error [pkg]'
1914 from sys.dual;
1915
1916 return;
1917
1918 else
1919
1920 raise_application_error(-20000,upper(type)||' '||upper(owner)||
1921 '.'||upper(name)||': Internal error [pkg]');
1922
1923 end if;
1924 -- end if log errors to table
1925
1926 end if;
1927 -- end if this is a package spec and body combination, but
1928 -- only the spec exists in the other schema
1929
1930 end if;
1931 -- end if object is package spec, and corresponding body exists
1932
1933 end if;
1934 -- end if object types differ
1935
1936 else
1937
1938 --
1939 -- No object with this name. Copy object to other schema
1940 --
1941
1942 ad_apps_private.copy_code(upper(name), upper(type), upper(owner),
1943 upper(new_owner));
1944
1945 --
1946 -- If object is a package and the package body exists
1947 -- in the source schema, also copy the package body
1948 -- to the other schema
1949 --
1950
1951 ad_apps_private.exact_synonym_match(upper(owner), upper(name),
1952 upper(owner), upper(name), synonym_ok, any_obj_w_name,
1953 type_of_object);
1954
1955 if any_obj_w_name and type_of_object = 'PKG_S_AND_B' then
1956
1957 ad_apps_private.copy_code(upper(name), 'PACKAGE BODY', upper(owner),
1958 upper(new_owner));
1959
1960 end if;
1961 -- end if object is package spec, and corresponding body exists
1962
1963 end if;
1964 -- end if there is already an object in the dest schema with this name
1965
1966 elsif nosync_plsql then
1967
1968 -- Don't do anything for Definer's Rights PL/SQL that contains the
1969 -- /*nosync*/ comment
1970
1971 return;
1972
1973 else
1974
1975 if log_to_table = 'TRUE' then
1976
1977 timestamp := to_char(sysdate,'YYYY-MM-DD:HH24:MI:SS');
1978
1979 insert into ad_parallel_compile_errors (
1980 owner, worker_number, timestamp, type, text)
1981 select upper(owner),
1982 0,
1983 timestamp,
1984 '3 GRANT ERROR',
1985 upper(type)||' '||upper(owner)||'.'||upper(name)||
1986 ': Internal error [classification]'
1987 from sys.dual;
1988
1989 return;
1990
1991 else
1992
1993 raise_application_error(-20000,upper(type)||' '||upper(owner)||
1994 '.'||upper(name)||': Internal error [classification]');
1995
1996 end if;
1997 -- end if log errors to table
1998
1999 end if;
2000 -- End process PL/SQL object based on its type
2001
2002 exception
2003 when others then
2004 declare
2005 sql_error varchar2(1996);
2006 begin
2007 if log_to_table = 'TRUE' then
2008
2009 timestamp := to_char(sysdate,'YYYY-MM-DD:HH24:MI:SS');
2010
2011 insert into ad_parallel_compile_errors (
2012 owner, worker_number, timestamp, type, text)
2013 select upper(owner),
2014 0,
2015 timestamp,
2016 '3 GRANT ERROR - A',
2017 upper(type)||' '||upper(owner)||'.'||upper(name)||
2018 ': Generic error occurred.'
2019 from sys.dual;
2020
2021 sql_error := SQLERRM;
2022
2023 insert into ad_parallel_compile_errors (
2024 owner, worker_number, timestamp, type, text)
2025 select upper(owner),
2026 0,
2027 timestamp,
2028 '3 GRANT ERROR - B',
2029 substr(upper(type)||' '||upper(owner)||'.'||upper(name)||
2030 ': '||sql_error,1,1996)
2031 from sys.dual;
2032
2033 if ad_apps_private.error_buf is not null then
2034
2035 insert into ad_parallel_compile_errors (
2036 owner, worker_number, timestamp, type, text)
2037 select upper(owner),
2038 0,
2039 timestamp,
2040 '3 GRANT ERROR - C',
2041 substr(upper(type)||' '||upper(owner)||'.'||upper(name)||
2042 ': '||ad_apps_private.error_buf,1,1996)
2043 from sys.dual;
2044
2045 end if;
2046 -- error buf contains information
2047
2048 -- clear error buf
2049 ad_apps_private.error_buf := null;
2050
2051 else
2052 raise;
2053 end if;
2054 -- end if log errors/messages to ad_parallel_compile_errors
2055 exception
2056 -- should only fire if we have errors writing to the log table
2057 when others then
2058 ad_apps_private.error_buf := 'grant_a_package('
2059 || owner ||', '|| name ||', '|| type ||', '|| new_owner
2060 || ', '|| authid_flag || ', '|| invoker_flag
2061 || ', '|| log_to_table ||'): '||
2062 ad_apps_private.error_buf;
2063 raise;
2064 end;
2065 end grant_a_package;
2066
2067 procedure grant_a_java_object
2068 (owner in varchar2,
2069 name in varchar2,
2070 type in varchar2,
2071 new_owner in varchar2,
2072 log_to_table in varchar2)
2073 --
2074 -- Creates/verifies the correct grants for a specific Java object
2075 --
2076 -- if log_to_table is 'TRUE', writes errors to ad_parallel_compile_errors
2077 --
2078 is
2079 statement varchar2(200);
2080 synonym_ok boolean;
2081 any_obj_w_name boolean;
2082 type_of_object varchar2(30);
2083 obj_type_new varchar2(30);
2084 timestamp varchar2(20);
2085 begin
2086 -- Validate Java object type
2087
2088 if (upper(type) not in ('JAVA CLASS')) then
2089
2090 if log_to_table = 'TRUE' then
2091
2092 timestamp := to_char(sysdate,'YYYY-MM-DD:HH24:MI:SS');
2093
2094 insert into ad_parallel_compile_errors (
2095 owner, worker_number, timestamp, type, text)
2096 select upper(owner),
2097 0,
2098 timestamp,
2099 '3 GRANT ERROR',
2100 upper(type)||' '||upper(owner)||'.'||name||
2101 ': Object type "'||upper(type)||'" not supported.'
2102 from sys.dual;
2103
2104 return;
2105
2106 else
2107
2108 raise_application_error(-20000,upper(type)||' '||upper(owner)||
2109 '.'||name||': Object type "'||upper(type)||
2110 '" not supported.');
2111
2112 end if;
2113 -- end if log errors/messages to ad_parallel_compile_errors
2114
2115 end if;
2116 -- end if not valid type
2117
2118 --
2119 -- Create grant
2120 --
2121
2122 statement := 'grant all on "'||name||'" to '||upper(new_owner)||
2123 ' with grant option';
2124
2125 ad_apps_private.do_apps_ddl(upper(owner), statement);
2126
2127 --
2128 -- Check for correct synonym
2129 --
2130
2131 ad_apps_private.exact_synonym_match(upper(new_owner), name,
2132 upper(owner), name, synonym_ok, any_obj_w_name,
2133 type_of_object);
2134
2135 if not synonym_ok then
2136
2137 --
2138 -- drop any existing object with this name
2139 --
2140
2141 if any_obj_w_name then
2142
2143 if type_of_object = 'JAVA CLASS'
2144 or type_of_object = 'JAVA RESOURCE' then
2145 -- existing object is Java object
2146
2147 ad_apps_private.drop_object(upper(new_owner), name,
2148 type_of_object);
2149 else
2150 -- existing object not Java object
2151
2152
2153 ad_apps_private.drop_object(upper(new_owner), name,
2154 type_of_object);
2155 end if;
2156 -- end if existing object is Java object
2157
2158 end if;
2159 -- end if any existing object with this name
2160
2161 --
2162 -- then create correct synonym
2163 --
2164
2165 statement := 'create synonym "'||name||'" for '||upper(owner)||
2166 '."'||name||'"';
2167
2168 ad_apps_private.do_apps_ddl(upper(new_owner), statement);
2169
2170 end if;
2171 -- end if synonym was not exactly correct
2172
2173 -- End process Java object
2174
2175 exception
2176 when others then
2177 declare
2178 sql_error varchar2(1996);
2179 begin
2180 if log_to_table = 'TRUE' then
2181
2182 timestamp := to_char(sysdate,'YYYY-MM-DD:HH24:MI:SS');
2183
2184 insert into ad_parallel_compile_errors (
2185 owner, worker_number, timestamp, type, text)
2186 select upper(owner),
2187 0,
2188 timestamp,
2189 '3 GRANT ERROR - A',
2190 upper(type)||' '||upper(owner)||'.'||name||
2191 ': Generic error occurred.'
2192 from sys.dual;
2193
2194 sql_error := SQLERRM;
2195
2196 insert into ad_parallel_compile_errors (
2197 owner, worker_number, timestamp, type, text)
2198 select upper(owner),
2199 0,
2200 timestamp,
2201 '3 GRANT ERROR - B',
2202 substr(upper(type)||' '||upper(owner)||'.'||name||
2203 ': '||sql_error,1,1996)
2204 from sys.dual;
2205
2206 if ad_apps_private.error_buf is not null then
2207
2208 insert into ad_parallel_compile_errors (
2209 owner, worker_number, timestamp, type, text)
2210 select upper(owner),
2211 0,
2212 timestamp,
2213 '3 GRANT ERROR - C',
2214 substr(upper(type)||' '||upper(owner)||'.'||name||
2215 ': '||ad_apps_private.error_buf,1,1996)
2216 from sys.dual;
2217
2218 end if;
2219 -- error buf contains information
2220
2221 -- clear error buf
2222 ad_apps_private.error_buf := null;
2223
2224 else
2225 raise;
2226 end if;
2227 -- end if log errors/messages to ad_parallel_compile_errors
2228 exception
2229 -- should only fire if we have errors writing to the log table
2230 when others then
2231 ad_apps_private.error_buf := 'grant_a_java_object('
2232 || owner ||', '|| name ||', '|| type ||', '|| new_owner
2233 || ', '|| log_to_table ||'): '||
2234 ad_apps_private.error_buf;
2235 raise;
2236 end;
2237 end grant_a_java_object;
2238
2239
2240
2241 -- Bug 3611969 : FIXED FILE.SQL.35 GSCC WARNINGS
2242 -- sraghuve (07/05/2004)
2243
2244 procedure update_timestamp
2245 (in_type in varchar2,
2246 in_attribute in varchar2)
2247 is
2248 begin
2249 update_timestamp
2250 (in_type => in_type,
2251 in_attribute => in_attribute,
2252 in_timestamp => sysdate);
2253
2254 end;
2255
2256
2257
2258 procedure update_timestamp
2259 (in_type in varchar2,
2260 in_attribute in varchar2,
2261 in_timestamp in date)
2262 --
2263 -- Updates/Inserts the row in AD_TIMESTAMPS for the specified
2264 -- type and attribute
2265 --
2266 is
2267 begin
2268 --
2269 -- First try to update
2270 --
2271 update ad_timestamps
2272 set timestamp = in_timestamp
2273 where type = in_type
2274 and attribute = in_attribute;
2275
2276 if SQL%ROWCOUNT = 1 then
2277 return;
2278 end if;
2279 --
2280 -- Insert if no rows updated
2281 --
2282 insert into ad_timestamps
2283 (type, attribute, timestamp)
2284 values (in_type, in_attribute, in_timestamp);
2285
2286 exception
2287 when others then
2288 ad_apps_private.error_buf := 'update_timestamp('
2289 || in_type ||', '|| in_attribute ||', '||
2290 to_char(in_timestamp,'YYYY-MM-DD:HH24:MI:SS') ||'): '||
2291 ad_apps_private.error_buf;
2292 raise;
2293 end update_timestamp;
2294
2295
2296 procedure verify_token_location
2297 (input_string in varchar2,
2298 input_token in varchar2,
2299 token_found out nocopy varchar2,
2300 token_location out nocopy number)
2301 --
2302 -- Makes sure a given token does exist as a word in the input string
2303 -- A word is delimited by white space on either side
2304 -- (unless at the beginning or end of the string, in which case it is
2305 -- only delimited by white space on one side)
2306 --
2307 -- token_found returns either 'TRUE' or 'FALSE'
2308 --
2309 -- token_location is the index of the token in the string (if found)
2310 -- It's the same value that would be returned by the INSTR function
2311 --
2312 -- If the given token exists in multiple places in the string as a word,
2313 -- we just return the first occurrence
2314 --
2315 -- Added (SYS_CONTEXT('USERENV','LANGUAGE') for MRC bug 3697501
2316 --
2317
2318
2319 is
2320
2321 a_space constant varchar2(10) :=
2322 CONVERT(CHR(32), substr(SYS_CONTEXT('USERENV','LANGUAGE'),
2323 instr(SYS_CONTEXT('USERENV','LANGUAGE'),'.')+1),
2324 'US7ASCII');
2325
2326 a_tab constant varchar2(10) :=
2327 CONVERT(CHR(9), substr(SYS_CONTEXT('USERENV','LANGUAGE'),
2328 instr(SYS_CONTEXT('USERENV','LANGUAGE'),'.')+1),
2329 'US7ASCII');
2330
2331 a_newline constant varchar2(10) :=
2332 CONVERT(CHR(10), substr(SYS_CONTEXT('USERENV','LANGUAGE'),
2333 instr(SYS_CONTEXT('USERENV','LANGUAGE'),'.')+1),
2334 'US7ASCII');
2335
2336 a_carr_return constant varchar2(10) :=
2337 CONVERT(CHR(13), substr(SYS_CONTEXT('USERENV','LANGUAGE'),
2338 instr(SYS_CONTEXT('USERENV','LANGUAGE'),'.')+1),
2339 'US7ASCII');
2340
2341 token_index number;
2342 look_for_next number;
2343 string_length number;
2344 token_length number;
2345 begin
2346 string_length := length(input_string);
2347 token_length := length(input_token);
2348 look_for_next := 1;
2349
2350 --
2351 -- Find first occurrence of token in string that is
2352 -- delimited by white space.
2353 -- If find an occurrence that is not delimited by white space
2354 -- look for the next occurrence
2355 -- Break out of loop if found token delimited by white space
2356 -- or if couldn't find the token delmited by white space
2357 --
2358 loop
2359 token_index := instr(input_string,input_token,1,look_for_next);
2360
2361 -- Exit if we didn't find the token
2362
2363 if token_index = 0 then
2364 exit;
2365 end if;
2366
2367 -- Check for white space before token
2368
2369 if token_index > 1 then
2370
2371 if substr(input_string,token_index-1,1) <> a_space
2372 and substr(input_string,token_index-1,1) <> a_tab
2373 and substr(input_string,token_index-1,1) <> a_newline
2374 and substr(input_string,token_index-1,1) <> a_carr_return then
2375
2376 goto try_next_occurrence;
2377
2378 end if;
2379 -- end if no white space before token
2380
2381 end if;
2382 -- end if token not at start of string
2383
2384 -- Check for white space after token
2385
2386 if token_index+token_length <= string_length then
2387
2388 if substr(input_string,token_index+token_length,1) <> a_space
2389 and substr(input_string,token_index+token_length,1) <> a_tab
2390 and substr(input_string,token_index+token_length,1) <> a_newline
2391 and substr(input_string,token_index+token_length,1) <> a_carr_return
2392 then
2393
2394 goto try_next_occurrence;
2395
2396 else
2397
2398 -- Found token delimited by white space
2399
2400 exit;
2401
2402 end if;
2403 -- end if no white space after token
2404
2405 else
2406
2407 -- Found token at end of string
2408 exit;
2409
2410 end if;
2411 -- end if token not at end of string
2412
2413 <<try_next_occurrence>>
2414
2415 look_for_next := look_for_next + 1;
2416
2417 end loop;
2418
2419 -- Set return values
2420
2421 token_location := token_index;
2422
2423 if token_index = 0 then
2424 token_found := 'FALSE';
2425 else
2426 token_found := 'TRUE';
2427 end if;
2428
2429 exception
2430 when others then
2431 ad_apps_private.error_buf := 'verify_token_location('
2432 || input_string ||', '|| input_token ||'): '||
2433 ad_apps_private.error_buf;
2434 raise;
2435 end verify_token_location;
2436
2437
2438 procedure classify_plsql_object
2439 (owner in varchar2,
2440 name in varchar2,
2441 type in varchar2,
2442 has_authid out nocopy varchar2,
2443 invoker_flag out nocopy varchar2)
2444 --
2445 -- Parses the source text for the given PL/SQL object
2446 --
2447 -- Sets has_authid to 'TRUE' if the object contains the AUTHID keyword
2448 -- sets has_authid to 'FALSE' otherwise
2449 --
2450 -- Sets invoker_flag to 'I' (Invoker's Rights; AUTHID CURRENT_USER)
2451 -- or 'D' (Definer's Rights; AUTHID DEFINER) if has_authid is 'TRUE'
2452 -- Sets invoker_flag to 'S' for Definer's Rights packages containing
2453 -- the /*nosync*/ comment (exactly as written, and delimited by whitespace)
2454 -- The /*nosync*/ comment must appear before the IS/AS keyword.
2455 --
2456 -- For wrapped package specs (procedures, functions), assumes definer's
2457 -- rights, as we really have no way to parse them. Not clear why anyone
2458 -- would want to wrap these objects, though...
2459 --
2460 -- 2/6/01 R Lotero
2461 --
2462 -- Add "customer hack" for Fidelity. If package doesn't contain an AUTHID
2463 -- clause, and package doesn't contain a valid Header string either, assume
2464 -- it's not an Oracle Apps package and maintain the default behavior (no
2465 -- AUTHID clause implies Definer Rights). To do this, say we found an AUTHID
2466 -- clause and that the package was explicitly declared as Definer Rights,
2467 -- even though this is not true.
2468 --
2469 is
2470 cursor c1 is
2471 select text from dba_source
2472 where owner = upper(classify_plsql_object.owner)
2473 and name = upper(classify_plsql_object.name)
2474 and type = upper(classify_plsql_object.type)
2475 order by line;
2476 cursor PKG_HEADER (c_owner in varchar2,
2477 c_name in varchar2,
2478 c_type in varchar2) is
2479 select
2480 substr(s.text, instr(s.text,'$Header'||': '),
2481 ((instr(s.text,' $', instr(s.text,'$Header'||': ')) + 2)
2482 - instr(s.text,'$Header'||': ')))
2483 from dba_source s
2484 where s.owner= upper(c_owner)
2485 and s.name = upper(c_name)
2486 and s.type = upper(c_type)
2487 and s.line between 2 and 5
2488 and s.text like '%$Header'||': % $%';
2489 header_string varchar2(500);
2490 row_count number;
2491 found_authid boolean;
2492 authid_line number;
2493 found_is_as boolean;
2494 found_wrapped boolean;
2495 is_as_line number;
2496 word_location number;
2497 is_as_position number;
2498 we_found_it varchar2(10);
2499 found_aid_type boolean;
2500 aid_type_line number;
2501 authid_type varchar2(10);
2502 found_nosync boolean;
2503 nosync_line number;
2504 begin
2505 -- Initialize return variables
2506
2507 has_authid := 'FALSE';
2508 invoker_flag := 'X';
2509
2510 -- Validate PL/SQL object type
2511
2512 if upper(type) <> 'PACKAGE'
2513 and upper(type) <> 'PROCEDURE'
2514 and upper(type) <> 'FUNCTION' then
2515
2516 raise_application_error(-20000,
2517 'Do not know how to classify objects of type "'||upper(type)||'".');
2518
2519 end if;
2520 -- end if not valid type
2521
2522 -- Initialize variables
2523
2524 found_authid := FALSE;
2525 found_is_as := FALSE;
2526 found_aid_type := FALSE;
2527 found_nosync := FALSE;
2528 found_wrapped := FALSE;
2529
2530 authid_line := 0;
2531 is_as_line := 0;
2532 aid_type_line := 0;
2533 nosync_line := 0;
2534
2535 --
2536 -- parse the source text
2537 --
2538 -- Exit loop when we find the IS/AS keyword
2539 --
2540
2541 row_count := 0;
2542
2543 for c1rec in c1 loop
2544
2545 row_count:=row_count+1;
2546
2547 -- Check for AUTHID
2548
2549 word_location := instr(upper(c1rec.text),'AUTHID');
2550
2551 if word_location <> 0 then
2552
2553 -- Do more strict checking
2554
2555 verify_token_location(upper(c1rec.text), 'AUTHID',
2556 we_found_it, word_location);
2557
2558 if we_found_it = 'TRUE' then
2559
2560 found_authid := TRUE;
2561 authid_line := row_count;
2562
2563 end if;
2564 -- end if we really found AUTHID
2565
2566 end if;
2567 -- end if found AUTHID using INSTR
2568
2569 -- If found AUTHID, check for authid type
2570
2571 if found_authid then
2572
2573 -- Check for CURRENT_USER keyword
2574
2575 word_location := instr(upper(c1rec.text),'CURRENT_USER');
2576
2577 if word_location <> 0 then
2578
2579 -- Do more strict checking
2580
2581 verify_token_location(upper(c1rec.text), 'CURRENT_USER',
2582 we_found_it, word_location);
2583
2584 if we_found_it = 'TRUE' then
2585
2586 found_aid_type := TRUE;
2587 aid_type_line := row_count;
2588 authid_type := 'I';
2589
2590 end if;
2591 -- end if we really found CURRENT_USER
2592
2593 end if;
2594 -- end if found CURRENT_USER using INSTR
2595
2596 -- Check for DEFINER keyword
2597
2598 word_location := instr(upper(c1rec.text),'DEFINER');
2599
2600 if word_location <> 0 then
2601
2602 -- Do more strict checking
2603
2604 verify_token_location(upper(c1rec.text), 'DEFINER',
2605 we_found_it, word_location);
2606
2607 if we_found_it = 'TRUE' then
2608
2609 found_aid_type := TRUE;
2610 aid_type_line := row_count;
2611 authid_type := 'D';
2612
2613 end if;
2614 -- end if we really found DEFINER
2615
2616 end if;
2617 -- end if found DEFINER using INSTR
2618
2619 end if;
2620 -- end if check for AUTHID type
2621
2622 -- Check for /*nosync*/ comment
2623 -- Must be specified exactly as above (case-insensitive) and be
2624 -- delimited by whitespace
2625
2626 word_location := instr(upper(c1rec.text),'/*NOSYNC*/');
2627
2628 if word_location <> 0 then
2629
2630 -- Do more strict checking
2631
2632 verify_token_location(upper(c1rec.text), '/*NOSYNC*/',
2633 we_found_it, word_location);
2634
2635 if we_found_it = 'TRUE' then
2636
2637 found_nosync := TRUE;
2638 nosync_line := row_count;
2639
2640 end if;
2641 -- end if we really found /*NOSYNC*/
2642
2643 end if;
2644 -- end if found /*NOSYNC*/ using INSTR
2645
2646 -- Check for AS
2647 -- Break out of the loop if we find it
2648
2649 word_location := instr(upper(c1rec.text),'AS');
2650
2651 if word_location <> 0 then
2652
2653 -- Do more strict checking
2654
2655 verify_token_location(upper(c1rec.text), 'AS',
2656 we_found_it, word_location);
2657
2658 if we_found_it = 'TRUE' then
2659
2660 found_is_as := TRUE;
2661 is_as_line := row_count;
2662 exit;
2663
2664 end if;
2665 -- end if we really found AS
2666
2667 end if;
2668 -- end if found AS using INSTR
2669
2670 -- Check for IS
2671 -- Break out of the loop if we find it
2672
2673 word_location := instr(upper(c1rec.text),'IS');
2674
2675 if word_location <> 0 then
2676
2677 -- Do more strict checking
2678
2679 verify_token_location(upper(c1rec.text), 'IS',
2680 we_found_it, word_location);
2681
2682 if we_found_it = 'TRUE' then
2683
2684 found_is_as := TRUE;
2685 is_as_line := row_count;
2686 exit;
2687
2688 end if;
2689 -- end if we really found IS
2690
2691 end if;
2692 -- end if found IS using INSTR
2693
2694 -- Check for WRAPPED
2695 -- Just make a note of it if we find it
2696
2697 word_location := instr(upper(c1rec.text),'WRAPPED');
2698
2699 if word_location <> 0 then
2700
2701 -- Do more strict checking
2702
2703 verify_token_location(upper(c1rec.text), 'WRAPPED',
2704 we_found_it, word_location);
2705
2706 if we_found_it = 'TRUE' then
2707
2708
2709 found_wrapped := TRUE;
2710 is_as_line := row_count;
2711 exit;
2712
2713 end if;
2714 -- end if we really found WRAPPED
2715
2716 end if;
2717 -- end if found WRAPPED using INSTR
2718
2719 end loop;
2720 -- end loop to parse source text
2721
2722 if not found_is_as then
2723
2724 -- Should only happen for wrapped package specs,
2725 -- wrapped procedures, or wrapped functions
2726
2727 if found_wrapped then
2728
2729 -- It's wierd to wrap a package spec, procedure, or function
2730 -- It's also annoying, because then we can't parse it to find
2731 -- out if it's invoker's rights or definer's rights.
2732 --
2733 -- Treat all wrapped specs, procedures, or functions as
2734 -- definer's rights objects. This is the safest strategy.
2735 --
2736
2737 has_authid := 'TRUE';
2738 invoker_flag := 'D';
2739 return;
2740
2741 else
2742
2743 --
2744 -- This should never happen, but it happens on Dev115
2745 -- Use the "safe" strategy from above: say it's Definer's Rights
2746 --
2747
2748 has_authid := 'TRUE';
2749 invoker_flag := 'D';
2750 return;
2751
2752 -- raise_application_error(-20000,
2753 -- 'No IS/AS keyword found in '||upper(type)||' '||
2754 -- upper(owner)||'.'||upper(name));
2755
2756 end if;
2757 -- end if found WRAPPED keyword
2758
2759 end if;
2760 -- end if didn't find IS/AS keyword
2761
2762 -- Set return values
2763
2764 if found_authid then
2765
2766 has_authid := 'TRUE';
2767
2768 if found_aid_type then
2769
2770 if authid_type = 'D' or authid_type = 'I' then
2771
2772 --
2773 -- Reset authid_type to 'S' for Definer's Rights objects
2774 -- that contain the /*nosync*/ comment
2775 --
2776 if authid_type = 'D' then
2777 if found_nosync then
2778 authid_type := 'S';
2779 end if;
2780 end if;
2781
2782 invoker_flag := authid_type;
2783 else
2784 raise_application_error(-20000,
2785 'Found AUTHID keyword, but did not find authid type');
2786 end if;
2787 -- end if valid authid type
2788
2789 end if;
2790 -- end if found authid type
2791
2792 else
2793
2794 -- Didn't find AUTHID keyword.
2795
2796 -- Check for valid Header string in package. If found, don't do anything.
2797 -- If not found, assume not an Oracle Apps package and treat it as if it
2798 -- had an explicit AUTHID DEFINER clause.
2799
2800 open PKG_HEADER(owner, name, type);
2801
2802 fetch PKG_HEADER
2803 into header_string;
2804
2805 if PKG_HEADER%NOTFOUND then
2806 -- no header. Assume not an Oracle Apps pkg
2807
2808 close PKG_HEADER;
2809
2810 has_authid := 'TRUE';
2811 invoker_flag := 'D';
2812
2813 else
2814 -- has header. Assume an Oracle Apps pkg
2815
2816 close PKG_HEADER;
2817
2818 end if;
2819 -- end if pkg has valid Header string
2820
2821 end if;
2822 -- end if found AUTHID keyword
2823
2824 exception
2825 when others then
2826 ad_apps_private.error_buf := 'classify_plsql_object('
2827 || owner ||', '|| name ||', '|| type ||'): '||
2828 ad_apps_private.error_buf;
2829 raise;
2830 end classify_plsql_object;
2831
2832
2833 procedure classify_plsql_array
2834 (the_array in dbms_sql.varchar2s,
2835 lb in number,
2836 ub in number,
2837 type in varchar2,
2838 has_authid out nocopy varchar2,
2839 invoker_flag out nocopy varchar2)
2840 --
2841 -- Parses the PL/SQL source text stored in the varchar2s array
2842 --
2843 -- Sets has_authid to 'TRUE' if the object contains the AUTHID keyword
2844 -- sets has_authid to 'FALSE' otherwise
2845 --
2846 -- Sets invoker_flag to 'I' (Invoker's Rights; AUTHID CURRENT_USER)
2847 -- or 'D' (Definer's Rights; AUTHID DEFINER) if has_authid is 'TRUE'
2848 -- Sets invoker_flag to 'S' for Definer's Rights packages containing
2849 -- the /*nosync*/ comment (exactly as written, and delimited by whitespace)
2850 -- The /*nosync*/ comment must appear before the IS/AS keyword.
2851 --
2852 -- For wrapped package specs (procedures, functions), assumes definer's
2853 -- rights, as we really have no way to parse them. Not clear why anyone
2854 -- would want to wrap these objects, though...
2855 --
2856 -- 2/6/01 R Lotero
2857 --
2858 -- Deliberately don't add "customer hack" we added to classify_plsql_object.
2859 --
2860 -- This routine is only called from AD_DDL to create packages dynamically.
2861 -- In most cases dynamically-created packages should not contain Header
2862 -- strings because these are basically meaningless in a dynamically-created
2863 -- object. This means that even Oracle Apps dynamically-created packages
2864 -- should not contain Header strings, so the header -vs- no header
2865 -- distinction central to the "customer hack" is not useful for
2866 -- deciding if a given dynamically-created package belongs to Oracle Apps
2867 -- or to a customer.
2868 --
2869 is
2870 found_authid boolean;
2871 authid_line number;
2872 found_is_as boolean;
2873 found_wrapped boolean;
2874 is_as_line number;
2875 word_location number;
2876 is_as_position number;
2877 we_found_it varchar2(10);
2878 found_aid_type boolean;
2879 aid_type_line number;
2880 authid_type varchar2(10);
2881 found_nosync boolean;
2882 nosync_line number;
2883 begin
2884 -- Initialize return variables
2885
2886 has_authid := 'FALSE';
2887 invoker_flag := 'X';
2888
2889 -- Validate PL/SQL object type
2890
2891 if upper(type) <> 'PACKAGE'
2892 and upper(type) <> 'PROCEDURE'
2893 and upper(type) <> 'FUNCTION' then
2894
2895 raise_application_error(-20000,
2896 'Do not know how to classify objects of type "'||upper(type)||'".');
2897
2898 end if;
2899 -- end if not valid type
2900
2901 -- Initialize variables
2902
2903 found_authid := FALSE;
2904 found_is_as := FALSE;
2905 found_aid_type := FALSE;
2906 found_nosync := FALSE;
2907 found_wrapped := FALSE;
2908
2909 authid_line := 0;
2910 is_as_line := 0;
2911 aid_type_line := 0;
2912 nosync_line := 0;
2913
2914 --
2915 -- parse the source text
2916 --
2917 -- Exit loop when we find the IS/AS keyword
2918 --
2919
2920 for counter in lb..ub loop
2921
2922 -- Check for AUTHID
2923
2924 word_location := instr(upper(the_array(counter)),'AUTHID');
2925
2926 if word_location <> 0 then
2927
2928 -- Do more strict checking
2929
2930 verify_token_location(upper(the_array(counter)), 'AUTHID',
2931 we_found_it, word_location);
2932
2933 if we_found_it = 'TRUE' then
2934
2935 found_authid := TRUE;
2936 authid_line := counter;
2937
2938 end if;
2939 -- end if we really found AUTHID
2940
2941 end if;
2942 -- end if found AUTHID using INSTR
2943
2944 -- If found AUTHID, check for authid type
2945
2946 if found_authid then
2947
2948 -- Check for CURRENT_USER keyword
2949
2950 word_location := instr(upper(the_array(counter)),'CURRENT_USER');
2951
2952 if word_location <> 0 then
2953
2954 -- Do more strict checking
2955
2956 verify_token_location(upper(the_array(counter)), 'CURRENT_USER',
2957 we_found_it, word_location);
2958
2959 if we_found_it = 'TRUE' then
2960
2961 found_aid_type := TRUE;
2962 aid_type_line := counter;
2963 authid_type := 'I';
2964
2965 end if;
2966 -- end if we really found CURRENT_USER
2967
2968 end if;
2969 -- end if found CURRENT_USER using INSTR
2970
2971 -- Check for DEFINER keyword
2972
2973 word_location := instr(upper(the_array(counter)),'DEFINER');
2974
2975 if word_location <> 0 then
2976
2977 -- Do more strict checking
2978
2979 verify_token_location(upper(the_array(counter)), 'DEFINER',
2980 we_found_it, word_location);
2981
2982 if we_found_it = 'TRUE' then
2983
2984 found_aid_type := TRUE;
2985 aid_type_line := counter;
2986 authid_type := 'D';
2987
2988 end if;
2989 -- end if we really found DEFINER
2990
2991 end if;
2992 -- end if found DEFINER using INSTR
2993
2994 end if;
2995 -- end if check for AUTHID type
2996
2997 -- Check for /*nosync*/ comment
2998 -- Must be specified exactly as above (case-insensitive) and be
2999 -- delimited by whitespace
3000
3001 word_location := instr(upper(the_array(counter)),'/*NOSYNC*/');
3002
3003 if word_location <> 0 then
3004
3005 -- Do more strict checking
3006
3007 verify_token_location(upper(the_array(counter)), '/*NOSYNC*/',
3008 we_found_it, word_location);
3009
3010 if we_found_it = 'TRUE' then
3011
3012 found_nosync := TRUE;
3013 nosync_line := counter;
3014
3015 end if;
3016 -- end if we really found /*NOSYNC*/
3017
3018 end if;
3019 -- end if found /*NOSYNC*/ using INSTR
3020
3021 -- Check for AS
3022 -- Break out of the loop if we find it
3023
3024 word_location := instr(upper(the_array(counter)),'AS');
3025
3026 if word_location <> 0 then
3027
3028 -- Do more strict checking
3029
3030 verify_token_location(upper(the_array(counter)), 'AS',
3031 we_found_it, word_location);
3032
3033 if we_found_it = 'TRUE' then
3034
3035 found_is_as := TRUE;
3036 is_as_line := counter;
3037 exit;
3038
3039 end if;
3040 -- end if we really found AS
3041
3042 end if;
3043 -- end if found AS using INSTR
3044
3045 -- Check for IS
3046 -- Break out of the loop if we find it
3047
3048 word_location := instr(upper(the_array(counter)),'IS');
3049
3050 if word_location <> 0 then
3051
3052 -- Do more strict checking
3053
3054 verify_token_location(upper(the_array(counter)), 'IS',
3055 we_found_it, word_location);
3056
3057 if we_found_it = 'TRUE' then
3058
3059 found_is_as := TRUE;
3060 is_as_line := counter;
3061 exit;
3062
3063 end if;
3064 -- end if we really found IS
3065
3066 end if;
3067 -- end if found IS using INSTR
3068
3069 -- Check for WRAPPED
3070 -- Just make a note of it if we find it
3071
3072 word_location := instr(upper(the_array(counter)),'WRAPPED');
3073
3074 if word_location <> 0 then
3075
3076 -- Do more strict checking
3077
3078 verify_token_location(upper(the_array(counter)), 'WRAPPED',
3079 we_found_it, word_location);
3080
3081 if we_found_it = 'TRUE' then
3082
3083 found_wrapped := TRUE;
3084 is_as_line := counter;
3085 exit;
3086
3087 end if;
3088 -- end if we really found WRAPPED
3089
3090 end if;
3091 -- end if found WRAPPED using INSTR
3092
3093 end loop;
3094 -- end loop to parse source text
3095
3096 if not found_is_as then
3097
3098 -- Should only happen for wrapped package specs,
3099 -- wrapped procedures, or wrapped functions
3100
3101 if found_wrapped then
3102
3103 -- It's wierd to wrap a package spec, procedure, or function
3104 -- It's also annoying, because then we can't parse it to find
3105 -- out if it's invoker's rights or definer's rights.
3106 --
3107 -- Treat all wrapped specs, procedures, or functions as
3108 -- definer's rights objects. This is the safest strategy.
3109 --
3110
3111 has_authid := 'TRUE';
3112 invoker_flag := 'D';
3113 return;
3114
3115 else
3116
3117 --
3118 -- This should never happen, but it happens on Dev115
3119 -- Use the "safe" strategy from above: say it's Definer's Rights
3120 --
3121
3122 has_authid := 'TRUE';
3123 invoker_flag := 'D';
3124 return;
3125
3126 end if;
3127 -- end if found WRAPPED keyword
3128
3129 end if;
3130 -- end if didn't find IS/AS keyword
3131
3132 -- Set return values
3133
3134 if found_authid then
3135
3136 has_authid := 'TRUE';
3137
3138 if found_aid_type then
3139
3140 if authid_type = 'D' or authid_type = 'I' then
3141
3142 --
3143 -- Reset authid_type to 'S' for Definer's Rights objects
3144 -- that contain the /*nosync*/ comment
3145 --
3146 if authid_type = 'D' then
3147 if found_nosync then
3148 authid_type := 'S';
3149 end if;
3150 end if;
3151
3152 invoker_flag := authid_type;
3153 else
3154 raise_application_error(-20000,
3155 'Found AUTHID keyword, but did not find authid type');
3156 end if;
3157 -- end if valid authid type
3158
3159 end if;
3160 -- end if found authid type
3161
3162 end if;
3163 -- end if found AUTHID keyword
3164
3165 exception
3166 when others then
3167 ad_apps_private.error_buf := 'classify_plsql_array('
3168 || ' <array>, '|| lb ||', '|| ub ||', '|| type ||'): '||
3169 ad_apps_private.error_buf;
3170 raise;
3171 end classify_plsql_array;
3172
3173
3174 end ad_invoker;
[Home] [Help]