1 package body ad_ddl as
2 /* $Header: adddlb.pls 120.1 2005/10/25 05:33:25 sksreedh noship $ */
3
4 --
5 -- Global variables
6 -- Added the folling for bug# 4583342
7 -- InstallGroupNumber_Cache_Table_Type
8 TYPE Ign_Cache_Tbl_Type IS TABLE OF NUMBER INDEX BY VARCHAR2 (40);
9
10 g_Ign_Cache_Tbl Ign_Cache_Tbl_Type;
11 --
12 --
13 -- PRIVATE PROCEDURES/FUNCTIONS
14 --
15
16 procedure private_do_ddl
17 (p_apps_schema in varchar2,
18 p_applsys_schema in varchar2,
19 oracle_schema in varchar2,
20 statement_type in number,
21 statement in varchar2,
22 object_name in varchar2);
23
24 procedure private_do_array_ddl
25 (p_apps_schema in varchar2,
26 p_applsys_schema in varchar2,
27 oracle_schema in varchar2,
28 statement_type in number,
29 lb in integer,
30 ub in integer,
31 object_name in varchar2);
32
33 procedure do_at_tab_a_seq_acd_ind
34 (datasai_oracle_schema in varchar2,
35 datasai_statement in varchar2);
36
37 procedure do_acd_trigger
38 (dat_install_group_num in number,
39 dat_apps_schema in varchar2,
40 dat_statement in varchar2);
41
42 procedure do_a_view_cd_syn_ad_pack
43 (davcsap_install_group_num in number,
44 davcsap_apps_schema in varchar2,
45 davcsap_statement in varchar2);
46
47 procedure do_cd_tab_cd_seq
48 (dctcs_install_group_num in number,
49 dctcs_apps_schema in varchar2,
50 dctcs_oracle_schema in varchar2,
51 dctcs_statement_type in integer,
52 dctcs_object_name in varchar2,
53 dctcs_statement in varchar2);
54
55 procedure do_cd_view
56 (dcv_install_group_num in number,
57 dcv_apps_schema in varchar2,
58 dcv_statement_type in integer,
59 dcv_object_name in varchar2,
60 dcv_statement in varchar2);
61
62 procedure do_array_c_view
63 (dcv_install_group_num in number,
64 dcv_apps_schema in varchar2,
65 dcv_object_name in varchar2,
66 dcv_lb in integer,
67 dcv_ub in integer);
68
69 procedure array_assign_and_execute
70 (p_schema_name in varchar2,
71 p_lb in integer,
72 p_ub in integer,
73 add_newline in varchar2);
74
75 procedure array_assign_and_execute
76 (p_schema_name in varchar2,
77 p_lb in integer,
78 p_ub in integer);
79
80
81 procedure get_array_statement
82 (p_lb in integer,
83 p_ub in integer);
84
85 --
86 -- PUBLIC PROCEDURES/FUNCTIONS
87 --
88
89 procedure do_ddl
90 (applsys_schema in varchar2,
91 application_short_name in varchar2,
92 statement_type in integer,
93 statement in varchar2,
94 object_name in varchar2)
95 is
96 c_statement varchar2(10000);
97 dummy_boolean varchar2(30);
98 status varchar2(1);
99 industry varchar2(1);
100 oracle_schema varchar2(30);
101 apps_schema varchar2(30);
102 apps_mls_schema varchar2(30);
103 begin
104 ad_ddl.error_buf := null;
105 ad_apps_private.error_buf := null;
106 gbl_statement := statement;
107
108 -- from the APPLSYS schema get an APPS schema so that we can access
109 -- the procedure FND_INSTALLATION that exists there
110 ad_apps_private.get_apps_schema_name( 0, applsys_schema,
111 apps_schema, apps_mls_schema);
112
113
114 if upper(application_short_name) not in ('INTERMEDIA','APPS') then
115 begin
116 c_statement:='declare x boolean; '||
117 'begin x := '||upper(apps_schema)||'.fnd_installation.'||
118 'get_app_info_other('''||upper(application_short_name)||
119 ''', '''||upper(apps_schema)||''', :status, :industry, '||
120 ':oracle_schema); '||
121 'if x = TRUE then :dummy_boolean := ''TRUE''; '||
122 'elsif x = FALSE then :dummy_boolean := ''FALSE''; '||
123 'else :dummy_boolean := null; end if; end;';
124
125 EXECUTE IMMEDIATE c_statement
126 using OUT status, OUT industry,
127 OUT oracle_schema, OUT dummy_boolean;
128
129 exception
130 when others then
131 ad_ddl.error_buf := 'c_statement='||c_statement||': '||
132 ad_ddl.error_buf;
133 raise;
134 end;
135
136 if dummy_boolean <> 'TRUE' then
137 raise_application_error(-20000,'Call to GET_APP_INFO_OTHER() failed: '||
138 'USER='||user||', Status='||status||', Industry='||industry||
139 ', Schema='|| oracle_schema||', STMT='||c_statement);
140 end if;
141 else
142 if upper(application_short_name) = 'INTERMEDIA' then
143 oracle_schema :='CTXSYS';
144 else
145 select ORACLE_USERNAME
146 into
147 oracle_schema
148 from fnd_oracle_userid where read_only_flag='U';
149 end if;
150 end if;
151
152
153 private_do_ddl(apps_schema, applsys_schema, oracle_schema,
154 statement_type, statement, upper(object_name));
155
156 exception
157 when others then
158 ad_ddl.error_buf := 'do_ddl('||applsys_schema||', '||
159 application_short_name||
160 ', '||statement_type||', $statement$, '||
161 object_name||'): '||
162 ad_ddl.error_buf||': '||ad_apps_private.error_buf||
163 ': substr($statement$,1,255)='''||
164 substr(gbl_statement,1,255)||'''';
165 raise;
166 end do_ddl;
167
168
169 --
170 -- Private functions/procedures
171 --
172
173 procedure private_do_ddl
174 (p_apps_schema in varchar2,
175 p_applsys_schema in varchar2,
176 oracle_schema in varchar2,
177 statement_type in number,
178 statement in varchar2,
179 object_name in varchar2)
180 is
181 install_group_num number;
182 l_apps_schema varchar2(30);
183 l_mls_apps_schema varchar2(30);
184 l_mrc_schema_name varchar2(30);
185 rows_processed integer;
186 c integer;
187 begin
188
189 if ad_apps_private.is_mls is null
190 or ad_apps_private.is_mc is null then
191 ad_apps_private.initialize(p_apps_schema);
192 end if;
193
194 -- get the install_group_num from the oracle_schema that the object
195 -- is to be created in.
196 if upper(oracle_schema) not in ('CTXSYS') then
197 IF ( g_Ign_Cache_Tbl.COUNT <> 0 AND g_Ign_Cache_Tbl.EXISTS (oracle_schema) ) THEN
198 install_group_num := g_Ign_Cache_Tbl(oracle_schema);
199 ELSE
200 DECLARE -- Block Find Ign
201 c integer;
202 rows_processed number;
203 c_statement varchar2(2000);
204 begin
205 c := dbms_sql.open_cursor;
206 c_statement:='select install_group_num from '||
207 p_apps_schema||'.fnd_oracle_userid '||
208 'where oracle_username = upper(:oracle_schema) '||
209 'and install_group_num is not null';
210 dbms_sql.parse(c, c_statement, dbms_sql.native);
211 dbms_sql.bind_variable(c,'oracle_schema',oracle_schema,30);
212 dbms_sql.define_column(c,1,install_group_num);
213 rows_processed := dbms_sql.execute(c);
214 if dbms_sql.fetch_rows(c) > 0 then
215 dbms_sql.column_value(c,1,install_group_num);
216
217 else
218 raise no_data_found;
219 end if;
220 dbms_sql.close_cursor(c);
221 g_Ign_Cache_Tbl(oracle_schema) := install_group_num;
222 exception
223 when others then
224 dbms_sql.close_cursor(c);
225 ad_ddl.error_buf := 'c_statement='||c_statement||': '||
226 ad_ddl.error_buf;
227 raise;
228 END ; -- Block Find Ign
229 END IF ;
230 else
231 install_group_num:=-99;
232 end if;
233
234
235
236 -- Check for APPS*DDL packages
237
238 ad_apps_private.check_for_apps_ddl(p_apps_schema);
239 ad_apps_private.check_for_apps_ddl(oracle_schema);
240
241
242 if install_group_num <> -99 then
243 --
244 --
245 if ad_apps_private.is_mls then
246 -- Get name of mls schema
247 ad_apps_private.get_apps_schema_name(install_group_num, p_applsys_schema,
248 l_apps_schema, l_mls_apps_schema);
249 -- check for APPS*DDL in mls schema
250 ad_apps_private.check_for_apps_ddl(l_mls_apps_schema);
251 end if;
252
253 end if;
254
255 --
256 -- Based on type of action determine what to do
257 --
258 --
259 if statement_type = ad_ddl.alter_sequence
260 then do_at_tab_a_seq_acd_ind(oracle_schema, statement);
261 elsif statement_type = ad_ddl.alter_table
262 then do_at_tab_a_seq_acd_ind(oracle_schema, statement);
263 elsif statement_type = ad_ddl.alter_trigger
264 then do_acd_trigger(install_group_num, p_apps_schema, statement);
265 elsif statement_type = ad_ddl.alter_view
266 then do_a_view_cd_syn_ad_pack(install_group_num, p_apps_schema,
267 statement);
268 elsif statement_type = ad_ddl.create_index
269 then do_at_tab_a_seq_acd_ind(oracle_schema, statement);
270 elsif statement_type = ad_ddl.create_sequence
271 then do_cd_tab_cd_seq(install_group_num, p_apps_schema, oracle_schema,
272 statement_type, object_name, statement);
273 elsif statement_type = ad_ddl.create_synonym
274 then do_a_view_cd_syn_ad_pack(install_group_num, p_apps_schema,
275 statement);
276 elsif statement_type = ad_ddl.create_table
277 then do_cd_tab_cd_seq(install_group_num, p_apps_schema, oracle_schema,
278 statement_type, object_name, statement);
279 elsif statement_type = ad_ddl.create_trigger
280 then do_acd_trigger(install_group_num, p_apps_schema, statement);
281 elsif statement_type = ad_ddl.create_view
282 then do_cd_view(install_group_num, p_apps_schema, statement_type,
283 object_name, statement);
284 elsif statement_type = ad_ddl.drop_index
285 then do_at_tab_a_seq_acd_ind(oracle_schema, statement);
286 elsif statement_type = ad_ddl.drop_sequence
287 then do_cd_tab_cd_seq(install_group_num, p_apps_schema, oracle_schema,
288 statement_type, object_name, statement);
289 elsif statement_type = ad_ddl.drop_synonym
290 then do_a_view_cd_syn_ad_pack(install_group_num, p_apps_schema,
291 statement);
292 elsif statement_type = ad_ddl.drop_table
293 then do_cd_tab_cd_seq(install_group_num, p_apps_schema, oracle_schema,
294 statement_type, object_name, statement);
295 elsif statement_type = ad_ddl.drop_trigger
296 then do_acd_trigger(install_group_num, p_apps_schema, statement);
297 elsif statement_type = ad_ddl.drop_view
298 then do_cd_view(install_group_num, p_apps_schema, statement_type,
299 object_name, statement);
300 elsif statement_type = ad_ddl.truncate_table
301 then do_at_tab_a_seq_acd_ind(oracle_schema, statement);
302 elsif statement_type = ad_ddl.alter_package
303 then do_a_view_cd_syn_ad_pack(install_group_num, p_apps_schema,
304 statement);
305 elsif statement_type = ad_ddl.drop_package
306 then do_a_view_cd_syn_ad_pack(install_group_num, p_apps_schema,
307 statement);
308 elsif statement_type = ad_ddl.create_grants
309 then do_at_tab_a_seq_acd_ind(oracle_schema, statement);
310
311 -- fix bug 2804640, it returns base schema
312 -- The issue in do_ddl() was that synonyms were not created in
313 -- base schemas, but in APPS and APPS_MRC
314
315 elsif statement_type = ad_ddl.create_base_synonym
316 then do_at_tab_a_seq_acd_ind(oracle_schema, statement);
317 elsif statement_type = ad_ddl.drop_base_synonym
318 then do_at_tab_a_seq_acd_ind(oracle_schema, statement);
319 end if;
320 exception
321 when others then
322 ad_ddl.error_buf := 'private_do_ddl('||p_apps_schema||', '||
323 p_applsys_schema||', '||oracle_schema||
324 ', '||statement_type||', $statement$, '||
325 object_name||'): '||ad_ddl.error_buf;
326 raise;
327 end;
328
329
330
331 procedure do_at_tab_a_seq_acd_ind
332 (datasai_oracle_schema in varchar2,
333 datasai_statement in varchar2)
334 is
335 begin
336 ad_apps_private.do_apps_ddl(datasai_oracle_schema,datasai_statement,
337 'TRUE');
338 exception
339 when others then
340 ad_ddl.error_buf := 'do_a_tab_a_seq_acd_ind('||datasai_oracle_schema||
341 ', $statement$): '||ad_ddl.error_buf;
342 raise;
343 end;
344
345
346 procedure do_acd_trigger
347 (dat_install_group_num in number,
348 dat_apps_schema in varchar2,
349 dat_statement in varchar2)
350 is
351 success_with_comp_error exception;
352 PRAGMA EXCEPTION_INIT(success_with_comp_error, -24344);
353 l_apps_schema varchar2(30);
354 l_mls_apps_schema varchar2(30);
355 begin
356 -- get the apps_schema_names for this install group
357 ad_apps_private.get_apps_schema_name(dat_install_group_num,
358 dat_apps_schema, l_apps_schema, l_mls_apps_schema);
359
360
361 -- execute the trigger code in the apps schema
362 ad_apps_private.do_apps_ddl(l_apps_schema,dat_statement, 'TRUE');
363
364 exception
365 when success_with_comp_error then
366 --
367 -- Trap and ignore ORA-24344: success with compilation error
368 -- This only happens on ORACLE 8
369 --
370 -- reset main error buffer
371 ad_apps_private.error_buf := null;
372 when others then
373 ad_ddl.error_buf := 'do_acd_trigger('||dat_install_group_num||', '||
374 dat_apps_schema||', $statement$): '||ad_ddl.error_buf;
375 raise;
376 end;
377
378
379 procedure do_a_view_cd_syn_ad_pack
380 (davcsap_install_group_num in number,
381 davcsap_apps_schema in varchar2,
382 davcsap_statement in varchar2)
383 is
384 success_with_comp_error exception;
385 PRAGMA EXCEPTION_INIT(success_with_comp_error, -24344);
386
387 --
388 -- rkagrawa: Fixed bug 2823541. When dropping synonyms, trap and ignore
389 -- ORA-01434 error, so that even if the synonym does not exist in apps schema,
390 -- apps_mls_schema or apps_mrc_schema, the procedure call is successful in
391 -- dropping the synonym in other schemas
392 --
393
394 synonym_does_not_exist exception;
395 PRAGMA EXCEPTION_INIT(synonym_does_not_exist, -1434);
396
397 l_apps_schema varchar2(30);
398 l_mls_apps_schema varchar2(30);
399 l_mrc_schema_name varchar2(30);
400 print_local_sql boolean;
401 begin
402
403
404 if ad_apps_private.is_mls is null
405 or ad_apps_private.is_mc is null then
406 ad_apps_private.initialize(davcsap_apps_schema);
407 end if;
408
409 if davcsap_install_group_num <> 0 then
410 -- if this is a non 0 install group then perform this only
411 -- for that apps account
412
413 -- get the apps_schema_names for this install group
414 if davcsap_install_group_num <> -99 then
415 ad_apps_private.get_apps_schema_name(davcsap_install_group_num,
416 davcsap_apps_schema, l_apps_schema, l_mls_apps_schema);
417 else
418 l_apps_schema:='CTXSYS';
419 end if;
420
421
422 -- execute the alter view in the apps schema
423 begin
424 ad_apps_private.do_apps_ddl(l_apps_schema, davcsap_statement,
425 'TRUE');
426 exception
427 when success_with_comp_error then
428 --
429 -- Trap and ignore ORA-24344: success with compilation error
430 -- This only happens on ORACLE 8
431 --
432 -- reset main error buffer
433 ad_apps_private.error_buf := null;
434
435 --
436 -- rkagrawa: Fixed bug 2823541. When dropping synonyms, trap and ignore
437 -- ORA-01434 error, so that even if the synonym does not exist in apps schema,
438 -- apps_mls_schema or apps_mrc_schema, the procedure call is successful in
439 -- dropping the synonym in other schemas
440 --
441 when synonym_does_not_exist then
442 ad_apps_private.error_buf := null;
443
444 end;
445
446 if davcsap_install_group_num <> -99 then
447
448 if ad_apps_private.is_mls then
449 -- execute the alter view in the apps_mls schema
450 begin
451 ad_apps_private.do_apps_ddl(l_mls_apps_schema,
452 davcsap_statement, 'TRUE');
453 exception
454 when success_with_comp_error then
455 --
456 -- Trap and ignore ORA-24344: success with compilation error
457 -- This only happens on ORACLE 8
458 --
459 -- reset main error buffer
460 ad_apps_private.error_buf := null;
461
462 --
463 -- rkagrawa: Fixed bug 2823541. When dropping synonyms, trap and ignore
464 -- ORA-01434 error, so that even if the synonym does not exist in apps schema,
465 -- apps_mls_schema or apps_mrc_schema, the procedure call is successful in
466 -- dropping the synonym in other schemas
467 --
468 when synonym_does_not_exist then
469 ad_apps_private.error_buf := null;
470
471 end;
472 end if;
473
474 end if;
475
476 else
477 -- if this is a 0 install group then perform this for all
478 -- apps accounts (all install groups)
479 declare
480 l_apps_schema varchar2(30);
481 l_mls_apps_schema varchar2(30);
482 c integer;
483 rows_processed number;
484 c_statement varchar2(2000);
485 l_install_group_num number;
486 begin
487 print_local_sql := TRUE;
488 c := dbms_sql.open_cursor;
489 c_statement:='select install_group_num from '||
490 davcsap_apps_schema||'.fnd_oracle_userid '||
491 'where read_only_flag = ''U'' '||
492 'order by install_group_num';
493 dbms_sql.parse(c, c_statement, dbms_sql.native);
494 dbms_sql.define_column(c,1,l_install_group_num);
495 rows_processed := dbms_sql.execute(c);
496 print_local_sql := FALSE;
497 loop
498 if dbms_sql.fetch_rows(c) > 0 then
499 dbms_sql.column_value(c,1,l_install_group_num);
500 ad_apps_private.get_apps_schema_name(l_install_group_num,
501 davcsap_apps_schema, l_apps_schema, l_mls_apps_schema);
502 -- execute the alter view in the apps schema
503 begin
504 ad_apps_private.do_apps_ddl(l_apps_schema,
505 davcsap_statement, 'TRUE');
506 exception
507 when success_with_comp_error then
508 --
509 -- Trap and ignore ORA-24344: success with compilation error
510 -- This only happens on ORACLE 8
511 --
512 -- reset main error buffer
513 ad_apps_private.error_buf := null;
514
515 --
516 -- rkagrawa: Fixed bug 2823541. When dropping synonyms, trap and ignore
517 -- ORA-01434 error, so that even if the synonym does not exist in apps schema,
518 -- apps_mls_schema or apps_mrc_schema, the procedure call is successful in
519 -- dropping the synonym in other schemas
520 --
521 when synonym_does_not_exist then
522 ad_apps_private.error_buf := null;
523
524 end;
525
526 if ad_apps_private.is_mls then
527 -- execute the alter view in the apps_mls schema
528 begin
529 ad_apps_private.do_apps_ddl(l_mls_apps_schema,
530 davcsap_statement, 'TRUE');
531 exception
532 when success_with_comp_error then
533 --
534 -- Trap and ignore ORA-24344: success with compilation error
535 -- This only happens on ORACLE 8
536 --
537 -- reset main error buffer
538 ad_apps_private.error_buf := null;
539
540 --
541 -- rkagrawa: Fixed bug 2823541. When dropping synonyms, trap and ignore
542 -- ORA-01434 error, so that even if the synonym does not exist in apps schema,
543 -- apps_mls_schema or apps_mrc_schema, the procedure call is successful in
544 -- dropping the synonym in other schemas
545 --
546 when synonym_does_not_exist then
547 ad_apps_private.error_buf := null;
548
549 end;
550 end if;
551
552 else
553 dbms_sql.close_cursor(c);
554 exit;
555 end if;
556 end loop;
557 exception
558 when others then
559 dbms_sql.close_cursor(c);
560 if print_local_sql then
561 ad_ddl.error_buf := 'c_statement='||c_statement||': '||
562 ad_ddl.error_buf;
563 end if;
564 raise;
565 end;
566 end if;
567
568 exception
569 when others then
570 ad_ddl.error_buf := 'do_a_view_cd_syn_ad_pack('||
571 davcsap_install_group_num||', '
572 ||davcsap_apps_schema||', $statement$): '||
573 ad_ddl.error_buf;
574 raise;
575 end;
576
577
578 procedure do_cd_tab_cd_seq
579 (dctcs_install_group_num in number,
580 dctcs_apps_schema in varchar2,
581 dctcs_oracle_schema in varchar2,
582 dctcs_statement_type in integer,
583 dctcs_object_name in varchar2,
584 dctcs_statement in varchar2)
585 is
586 l_apps_schema varchar2(30);
587 l_mls_apps_schema varchar2(30);
588 l_mrc_apps_schema varchar2(30);
589 print_local_sql boolean;
590 begin
591
592 if ad_apps_private.is_mls is null
593 or ad_apps_private.is_mc is null then
594 ad_apps_private.initialize(dctcs_apps_schema);
595 end if;
596 -- do statement in base schema
597 if dctcs_statement_type = ad_ddl.drop_sequence then
598 ad_apps_private.drop_object(dctcs_oracle_schema, dctcs_object_name,
599 'SEQUENCE');
600 elsif dctcs_statement_type = ad_ddl.drop_table then
601 ad_apps_private.drop_object(dctcs_oracle_schema, dctcs_object_name,
602 'TABLE');
603 else -- it is a create
604 ad_apps_private.do_apps_ddl(dctcs_oracle_schema, dctcs_statement,
605 'TRUE');
606 end if;
607 -- now do the correct action in the apps schemas
608 if dctcs_install_group_num <> 0 then
609 -- if this is a non 0 install group then perform this only
610 -- for that apps account
611
612 -- get the apps_schema_names for this install group
613 ad_apps_private.get_apps_schema_name(dctcs_install_group_num,
614 dctcs_apps_schema, l_apps_schema, l_mls_apps_schema);
615
616 -- create/drop grant/synonym in apps schema
617 if dctcs_statement_type = ad_ddl.create_table
618 or dctcs_statement_type = ad_ddl.create_sequence then
619
620 ad_apps_private.create_gs(dctcs_oracle_schema, l_apps_schema,
621 dctcs_object_name, TRUE, 'ALL');
622 else -- this is a drop table/sequence
623 ad_apps_private.drop_object(l_apps_schema,
624 dctcs_object_name,'SYNONYM');
625 end if;
626
627 if ad_apps_private.is_mls then
628 -- create/drop grant/synonym in apps_mls schema
629 if dctcs_statement_type = ad_ddl.create_table
630 or dctcs_statement_type = ad_ddl.create_sequence then
631
632 ad_apps_private.create_gs(dctcs_oracle_schema,
633 l_mls_apps_schema,
634 dctcs_object_name, TRUE, 'ALL');
635 else -- this is a drop table/sequence
636 ad_apps_private.drop_object(l_mls_apps_schema,
637 dctcs_object_name,'SYNONYM');
638 end if;
639 end if;
640
641 else
642
643 -- if this is a 0 install group then perform this for all
644 -- apps accounts (all install groups)
645 declare
646 l_apps_schema varchar2(30);
647 l_mls_apps_schema varchar2(30);
648 l_mrc_apps_schema varchar2(30);
649 c integer;
650 rows_processed number;
651 c_statement varchar2(2000);
652 l_install_group_num number;
653 begin
654 print_local_sql := TRUE;
655 c := dbms_sql.open_cursor;
656 c_statement:='select distinct install_group_num from '||
657 dctcs_apps_schema||'.fnd_oracle_userid '||
658 'where read_only_flag = ''U'' '||
659 'order by install_group_num';
660 dbms_sql.parse(c, c_statement, dbms_sql.native);
661 dbms_sql.define_column(c,1,l_install_group_num);
662 rows_processed := dbms_sql.execute(c);
663 print_local_sql := FALSE;
664 loop
665
666 if dbms_sql.fetch_rows(c) > 0 then
667 dbms_sql.column_value(c,1,l_install_group_num);
668
669 -- get the apps_schema_names for this install group
670 ad_apps_private.get_apps_schema_name(l_install_group_num,
671 dctcs_apps_schema, l_apps_schema, l_mls_apps_schema);
672
673 -- create/drop grant/synonym in apps schema
674 if dctcs_statement_type = ad_ddl.create_table
675 or dctcs_statement_type = ad_ddl.create_sequence then
676
677 ad_apps_private.create_gs(dctcs_oracle_schema,
678 l_apps_schema,
679 dctcs_object_name, TRUE, 'ALL');
680 else -- this is a drop table/sequence
681 ad_apps_private.drop_object(l_apps_schema,
682 dctcs_object_name,'SYNONYM');
683 end if;
684
685 if ad_apps_private.is_mls then
686 -- create/drop grant/synonym in apps_mls schema
687 if dctcs_statement_type = ad_ddl.create_table
688 or dctcs_statement_type = ad_ddl.create_sequence then
689
690 ad_apps_private.create_gs(dctcs_oracle_schema,
691 l_mls_apps_schema, dctcs_object_name, TRUE, 'ALL');
692 else -- this is a drop table/sequence
693 ad_apps_private.drop_object(l_mls_apps_schema,
694 dctcs_object_name,'SYNONYM');
695 end if;
696 end if;
697
698 else
699 dbms_sql.close_cursor(c);
700 exit;
701 end if;
702 end loop;
703 exception
704 when others then
705 dbms_sql.close_cursor(c);
706 if print_local_sql then
707 ad_ddl.error_buf := 'c_statement='||c_statement||': '||
708 ad_ddl.error_buf;
709 end if;
710 raise;
711 end;
712 end if;
713 exception
714 when others then
715 ad_ddl.error_buf := 'do_cd_tab_cd_seq('||dctcs_install_group_num||', '||
716 dctcs_apps_schema||', '||dctcs_oracle_schema||', '||
717 dctcs_statement_type||', '||dctcs_object_name||
718 ', $statement$): '||ad_ddl.error_buf;
719 raise;
720 end;
721
722
723 procedure do_cd_view
724 (dcv_install_group_num in number,
725 dcv_apps_schema in varchar2,
726 dcv_statement_type in integer,
727 dcv_object_name in varchar2,
728 dcv_statement in varchar2)
729 is
730 success_with_comp_error exception;
731 PRAGMA EXCEPTION_INIT(success_with_comp_error, -24344);
732 l_apps_schema varchar2(30);
733 l_mls_apps_schema varchar2(30);
734 l_mrc_apps_schema varchar2(30);
735 print_local_sql boolean;
736 begin
737
738 if ad_apps_private.is_mls is null
739 or ad_apps_private.is_mc is null then
740 ad_apps_private.initialize(dcv_apps_schema);
741 end if;
742 -- now do the correct action in the apps schemas
743 if dcv_install_group_num <> 0 then
744 -- if this is a non 0 install group then perform this only
745 -- for that apps account
746
747 -- get the apps_schema_names for this install group
748 ad_apps_private.get_apps_schema_name(dcv_install_group_num,
749 dcv_apps_schema, l_apps_schema, l_mls_apps_schema);
750
751 -- create/drop view in apps schema
752 if dcv_statement_type = ad_ddl.drop_view then
753 ad_apps_private.drop_object(l_apps_schema,dcv_object_name,
754 'VIEW');
755 else -- create_view
756 begin
757 ad_apps_private.do_apps_ddl(l_apps_schema, dcv_statement,
758 'TRUE');
759 exception
760 when success_with_comp_error then
761 --
762 -- Trap and ignore ORA-24344: success with compilation error
763 -- This only happens on ORACLE 8
764 --
765 -- reset main error buffer
766 ad_apps_private.error_buf := null;
767 end;
768
769 end if;
770
771 if ad_apps_private.is_mls then
772 -- create/drop view in apps_mls schema
773 if dcv_statement_type = ad_ddl.drop_view then
774 ad_apps_private.drop_object(l_mls_apps_schema,
775 dcv_object_name,'VIEW');
776 else -- create_view
777 begin
778 ad_apps_private.do_apps_ddl(l_mls_apps_schema,
779 dcv_statement, 'TRUE');
780 exception
781 when success_with_comp_error then
782 --
783 -- Trap and ignore ORA-24344: success with compilation error
784 -- This only happens on ORACLE 8
785 --
786 -- reset main error buffer
787 ad_apps_private.error_buf := null;
788 end;
789 end if;
790 end if;
791
792 else
793 -- if this is a 0 install group then perform this for all
794 -- apps accounts (all install groups)
795 declare
796 l_apps_schema varchar2(30);
797 l_mls_apps_schema varchar2(30);
798 l_mrc_apps_schema varchar2(30);
799 c integer;
800 rows_processed number;
801 c_statement varchar2(2000);
802 l_install_group_num number;
803 begin
804 print_local_sql := TRUE;
805 c := dbms_sql.open_cursor;
806 c_statement:='select distinct install_group_num from '||
807 dcv_apps_schema||'.fnd_oracle_userid '||
808 'where read_only_flag = ''U'' '||
809 'order by install_group_num';
810 dbms_sql.parse(c, c_statement, dbms_sql.native);
811 dbms_sql.define_column(c,1,l_install_group_num);
812 rows_processed := dbms_sql.execute(c);
813 print_local_sql := FALSE;
814 loop
815 if dbms_sql.fetch_rows(c) > 0 then
816 dbms_sql.column_value(c,1,l_install_group_num);
817
818 -- get the apps_schema_names for this install group
819 ad_apps_private.get_apps_schema_name(l_install_group_num,
820 dcv_apps_schema, l_apps_schema, l_mls_apps_schema);
821
822 -- create/drop view in apps schema
823 if dcv_statement_type = ad_ddl.drop_view then
824 ad_apps_private.drop_object(l_apps_schema,
825 dcv_object_name,'VIEW');
826 else -- create_view
827 begin
828 ad_apps_private.do_apps_ddl(l_apps_schema,
829 dcv_statement, 'TRUE');
830 exception
831 when success_with_comp_error then
832 --
833 -- Trap and ignore ORA-24344: success with compilation error
834 -- This only happens on ORACLE 8
835 --
836 -- reset main error buffer
837 ad_apps_private.error_buf := null;
838 end;
839 end if;
840
841 if ad_apps_private.is_mls then
842 -- create/drop view in apps_mls schema
843 if dcv_statement_type = ad_ddl.drop_view then
844 ad_apps_private.drop_object(l_mls_apps_schema,
845 dcv_object_name,'VIEW');
846 else -- create_view
847 begin
848 ad_apps_private.do_apps_ddl(l_mls_apps_schema,
849 dcv_statement, 'TRUE');
850 exception
851 when success_with_comp_error then
852 --
853 -- Trap and ignore ORA-24344: success with compilation error
854 -- This only happens on ORACLE 8
855 --
856 -- reset main error buffer
857 ad_apps_private.error_buf := null;
858 end;
859 end if;
860 end if;
861
862 else
863 dbms_sql.close_cursor(c);
864 exit;
865 end if;
866 end loop;
867 exception
868 when others then
869 dbms_sql.close_cursor(c);
870 if print_local_sql then
871 ad_ddl.error_buf := 'c_statement='||c_statement||': '||
872 ad_ddl.error_buf;
873 end if;
874 raise;
875 end;
876 end if;
877 exception
878 when others then
879 ad_ddl.error_buf := 'do_cd_view('||dcv_install_group_num||', '||
880 dcv_apps_schema||', '||
881 dcv_statement_type||', '||dcv_object_name||
882 ', $statement$): '||ad_ddl.error_buf;
883 raise;
884 end do_cd_view;
885
886
887 procedure create_package
888 (applsys_schema in varchar2,
889 application_short_name in varchar2,
890 package_name in varchar2,
891 is_package_body in varchar2,
892 lb in integer,
893 ub in integer)
894 is
895 dummy varchar2(20);
896 begin
897 --
898 -- just call more robust create_plsql_object procedure
899 --
900 create_plsql_object(create_package.applsys_schema,
901 create_package.application_short_name,
902 create_package.package_name,
903 create_package.lb,
904 create_package.ub,
905 'TRUE',
906 dummy);
907 exception
908 when others then
909 ad_ddl.error_buf := 'create_package('||applsys_schema||', '||
910 application_short_name||
911 ', '||package_name||', '||is_package_body||', '||
912 lb||', '||ub||'): '||
913 ad_ddl.error_buf||': '||ad_apps_private.error_buf||
914 ': substr($statement$,1,255)='''||
915 substr(gbl_statement,1,255)||'''';
916 raise;
917 end create_package;
918
919
920 procedure create_plsql_object
921 (applsys_schema in varchar2,
922 application_short_name in varchar2,
923 object_name in varchar2,
924 lb in integer,
925 ub in integer,
926 insert_newlines in varchar2,
927 comp_error out nocopy varchar2)
928 is
929 success_with_comp_error exception;
930 PRAGMA EXCEPTION_INIT(success_with_comp_error, -24344);
931 c_statement varchar2(10000);
932 dummy_boolean varchar2(30);
933 status varchar2(1);
934 industry varchar2(1);
935 oracle_schema varchar2(30);
936 apps_schema varchar2(30);
937 apps_mls_schema varchar2(30);
938 first_apps_schema varchar2(30);
939 current_apps_schema varchar2(30);
940 install_group_num number;
941 print_local_sql boolean;
942 object_type varchar2(30);
943 obj_has_authid varchar2(10);
944 obj_invoker_flag varchar2(10);
945 is_invoker_object boolean;
946 invoker_flag_for_obj varchar2(10);
947 obj_is_correct_synonym boolean;
948 obj_exists_in_schema boolean;
949 obj_type_in_schema varchar2(30);
950 tmp_ign number;
951 tmp_apps_schema varchar2(30);
952 tmp_mls_schema varchar2(30);
953 tmp_mrc_schema varchar2(30);
954 effective_schema varchar2(30);
955 object_status varchar2(10);
956 invalid_count number;
957 upper_obj_name varchar2(30);
958 begin
959 --
960 -- Architecture:
961 --
962 -- Invoker's Rights objects have both spec and body in first APPS schema
963 -- Other APPS schemas and MRC schemas have grants/synonyms to spec
964 -- in first APPS schema
965 --
966 -- Definer's Rights objects have both spec and body in all APPS and MRC
967 -- schemas
968 --
969 -- Definer's Rights nosync objects have both spec and body ONLY
970 -- in the current APPS schema and its corresponding MRC schema.
971 -- These objects only make sense for MOA products. If an SOA product
972 -- tries to create a Definer's Rights nosync object, we treat it as
973 -- a normal Definer's Rights object.
974 --
975 -- Standalone procedures or functions are treated like package specs
976 -- Our standard prohibit them, but people sometimes use them anyway...
977 --
978
979 -- initialize variables
980
981 ad_ddl.error_buf := null;
982 ad_apps_private.error_buf := null;
983 gbl_statement := null;
984
985 comp_error := 'FALSE';
986 upper_obj_name := upper(object_name);
987
988 -- Determine object type
989
990 if upper(ad_ddl.glprogtext(lb)) like 'CREATE%PACKAGE%' then
991 if upper(ad_ddl.glprogtext(lb)) like 'CREATE%PACKAGE BODY%' then
992 object_type := 'PACKAGE BODY';
993 else
994 object_type := 'PACKAGE';
995 end if;
996 elsif upper(ad_ddl.glprogtext(lb)) like 'CREATE%PROCEDURE%' then
997 object_type := 'PROCEDURE';
998 elsif upper(ad_ddl.glprogtext(lb)) like 'CREATE%FUNCTION%' then
999 object_type := 'FUNCTION';
1000 else
1001 object_type := 'UNKNOWN';
1002 end if;
1003
1004 if object_type = 'UNKNOWN' then
1005 raise_application_error(-20000,
1006 'Unknown or unsupported object type in create_plsql_object()');
1007 end if;
1008
1009 -- from the APPLSYS schema get an APPS schema so that we can access
1010 -- the procedure FND_INSTALLATION that exists there
1011
1012 ad_apps_private.get_apps_schema_name( 0, applsys_schema,
1013 apps_schema, apps_mls_schema);
1014
1015 --
1016 -- compute effective user
1017 --
1018 -- If current user is a registered Oracle Applications schema,
1019 -- use current user. Otherwise, use the APPS schema returned above
1020 --
1021
1022 c_statement := 'select oracle_username from '||upper(apps_schema)||
1023 '.fnd_oracle_userid where oracle_username = USER';
1024
1025 begin
1026 EXECUTE IMMEDIATE c_statement
1027 into effective_schema;
1028 exception
1029 when no_data_found then
1030 effective_schema := apps_schema;
1031 end;
1032
1033
1034 if upper(application_short_name) not in ('INTERMEDIA','APPS') then
1035 -- dbms_output.put_line('effective_schema='||effective_schema);
1036
1037 -- Get product information based on effective schema
1038
1039 begin
1040 c_statement:='declare x boolean; '||
1041 'begin x := '||upper(apps_schema)||'.fnd_installation.'||
1042 'get_app_info_other('''||upper(application_short_name)||
1043 ''', '''||upper(effective_schema)||''', :status, :industry, '||
1044 ':oracle_schema); '||
1045 'if x = TRUE then :dummy_boolean := ''TRUE''; '||
1046 'elsif x = FALSE then :dummy_boolean := ''FALSE''; '||
1047 'else :dummy_boolean := null; end if; end;';
1048
1049 EXECUTE IMMEDIATE c_statement
1050 using OUT status, OUT industry,
1051 OUT oracle_schema, OUT dummy_boolean;
1052 --
1053 exception
1054 when others then
1055 ad_ddl.error_buf := 'c_statement='||c_statement||':'||
1056 ad_ddl.error_buf;
1057 raise;
1058 end;
1059 --
1060 if dummy_boolean <> 'TRUE' then
1061 raise_application_error(-20000,'Call to GET_APP_INFO_OTHER() failed: '||
1062 'USER='||user||', Status='||status||', Industry='||industry||
1063 ', Schema='|| oracle_schema||', STMT='||c_statement);
1064 end if;
1065 --
1066 else
1067 if upper(application_short_name) = 'INTERMEDIA' then
1068 oracle_schema :='CTXSYS';
1069 else
1070 select ORACLE_USERNAME
1071 into
1072 oracle_schema
1073 from
1074 fnd_oracle_userid where
1075 read_only_flag='U';
1076 end if;
1077 end if;
1078
1079
1080 -- initialize global flags if required
1081
1082 if ad_apps_private.is_mls is null
1083 or ad_apps_private.is_mc is null then
1084 ad_apps_private.initialize(apps_schema);
1085 end if;
1086
1087 -- Get the name of the first apps schema
1088
1089 begin
1090 c_statement := 'select oracle_username from '||
1091 apps_schema||'.fnd_oracle_userid '||
1092 'where install_group_num = 1 and read_only_flag = ''U''';
1093
1094 EXECUTE IMMEDIATE c_statement
1095 into first_apps_schema;
1096
1097 exception
1098 when others then
1099 ad_ddl.error_buf := 'c_statement='||c_statement||':'||
1100 ad_ddl.error_buf;
1101 raise;
1102 end;
1103
1104 -- Get the install_group_num for the oracle_schema that owns the object
1105 -- If non-zero, it is also the install_group_num for the current
1106 -- APPS schema.
1107
1108 if upper(oracle_schema) not in ('CTXSYS') then
1109
1110 declare
1111 c integer;
1112 rows_processed number;
1113 c_statement varchar2(2000);
1114 begin
1115 c := dbms_sql.open_cursor;
1116 c_statement:='select install_group_num from '||
1117 apps_schema||'.fnd_oracle_userid '||
1118 'where oracle_username = upper(:oracle_schema) '||
1119 'and install_group_num is not null';
1120 dbms_sql.parse(c, c_statement, dbms_sql.native);
1121 dbms_sql.bind_variable(c,'oracle_schema',oracle_schema,30);
1122 dbms_sql.define_column(c,1,install_group_num);
1123 rows_processed := dbms_sql.execute(c);
1124 if dbms_sql.fetch_rows(c) > 0 then
1125 dbms_sql.column_value(c,1,install_group_num);
1126
1127 else
1128 raise no_data_found;
1129 end if;
1130 dbms_sql.close_cursor(c);
1131 exception
1132 when others then
1133 dbms_sql.close_cursor(c);
1134 ad_ddl.error_buf := 'c_statement='||c_statement||': '||
1135 ad_ddl.error_buf;
1136 raise;
1137 end;
1138 else
1139
1140 install_group_num:=-99;
1141
1142 end if;
1143
1144
1145 -- Get the name of the APPS schema corresponding to the current
1146 -- install group num.
1147 -- Use the first APPS schema if the install group num is zero.
1148
1149 if install_group_num = -99 then
1150 current_apps_schema :='CTXSYS';
1151 else
1152
1153 if install_group_num <> 0 then
1154
1155 begin
1156 c_statement := 'select oracle_username from '||
1157 apps_schema||'.fnd_oracle_userid '||
1158 'where install_group_num = '||install_group_num||
1159 ' and read_only_flag = ''U''';
1160
1161 EXECUTE IMMEDIATE c_statement
1162 into current_apps_schema;
1163
1164 exception
1165 when others then
1166 ad_ddl.error_buf := 'c_statement='||c_statement||':'||
1167 ad_ddl.error_buf;
1168 raise;
1169 end;
1170
1171 else
1172
1173 current_apps_schema := first_apps_schema;
1174
1175 end if;
1176 end if;
1177 -- end get the current APPS schema name
1178
1179 -- classify the PL/SQL object
1180
1181 if object_type <> 'PACKAGE BODY' then
1182 -- not package body
1183
1184 ad_invoker.classify_plsql_array(ad_ddl.glprogtext, lb, ub,
1185 object_type, obj_has_authid, obj_invoker_flag);
1186
1187 if obj_has_authid = 'FALSE' then
1188
1189 ad_invoker.rewrite_plsql_array(ad_ddl.glprogtext, lb, ub,
1190 object_type);
1191
1192 is_invoker_object := TRUE;
1193 invoker_flag_for_obj := 'I';
1194
1195 else
1196
1197 if obj_invoker_flag = 'I' then
1198 is_invoker_object := TRUE;
1199 else
1200 is_invoker_object := FALSE;
1201
1202 if obj_invoker_flag = 'S' and install_group_num = 0 then
1203
1204 -- If an SOA product tries to create a Definer's Rights
1205 -- nosync object the results would be unsupportable, so
1206 -- do not allow this. Instead, if nosync requested for
1207 -- an SOA product's PL/SQL object, ignore the nosync keyword
1208 -- and copy it to all APPS schemas anyway
1209
1210 obj_invoker_flag := 'D';
1211 end if;
1212
1213 end if;
1214
1215 invoker_flag_for_obj := obj_invoker_flag;
1216
1217 end if;
1218
1219 else
1220 -- package body
1221
1222 -- complicated, as we must first look at the package spec
1223 -- to determine where to create the package body
1224 --
1225 -- check to see if the package spec exists in the current APPS
1226 -- schema. This will be the right place for Definer's Rights
1227 -- nosync packages.
1228 --
1229 -- If don't find in current APPS schema, look in the first APPS
1230 -- schema. This will be the right place for Invoker's Rights
1231 -- packages
1232 --
1233 -- For regular Definer's Rights packages, it shouldn't matter
1234 -- which APPS schema we look in, as the spec should be in both
1235 --
1236 -- Use exact_synonym_match procedure even though we are not
1237 -- looking for a synonym because it gives us the required
1238 -- information easily
1239 --
1240
1241 ad_apps_private.exact_synonym_match(current_apps_schema, upper_obj_name,
1242 current_apps_schema, upper_obj_name, obj_is_correct_synonym,
1243 obj_exists_in_schema, obj_type_in_schema);
1244
1245 if obj_exists_in_schema
1246 and obj_type_in_schema <> 'SYNONYM' then
1247 -- found the object in the current APPS schema
1248 -- if either package spec or both spec and body, parse the
1249 -- package spec to figure out what to do with the package body
1250 -- If neither, this is an error condition
1251 --
1252 -- Note that we would expect the object in the current APPS schema
1253 -- to be a synonym for Invoker's Rights, so we just roll over to
1254 -- the check in the first APPS schema in this case
1255
1256 if obj_type_in_schema = 'PACKAGE'
1257 or obj_type_in_schema = 'PKG_S_AND_B' then
1258
1259 -- found package spec in current APPS schema
1260 -- parse it to see what type it is
1261
1262 ad_invoker.classify_plsql_object(current_apps_schema, upper_obj_name,
1263 'PACKAGE', obj_has_authid, obj_invoker_flag);
1264
1265 -- If no authid clause, convert the package spec to Invoker's
1266 -- Rights.
1267
1268 if obj_has_authid = 'FALSE' then
1269 -- rewrite spec for Invoker's Rights
1270 -- put rewritten spec in first APPS schema
1271 -- and run grant logic for it
1272
1273 ad_invoker.rewrite_a_package(current_apps_schema, upper_obj_name,
1274 'PACKAGE', first_apps_schema, 'FALSE');
1275
1276 -- create/fix grants for package spec
1277
1278 tmp_ign := 1;
1279
1280 loop
1281 if tmp_ign <> 1 then
1282
1283 -- Get name for this APPS schema
1284
1285 -- exit loop if no data found
1286 -- barring data integrity issues, this means we've processed
1287 -- all of the APPS schemas (and corresponding MRC schemas)
1288
1289 begin
1290 ad_apps_private.get_apps_schema_name(tmp_ign,
1291 first_apps_schema, tmp_apps_schema, tmp_mls_schema);
1292 exception
1293 when no_data_found then
1294 exit;
1295 end;
1296
1297 -- Check for APPS*DDL
1298
1299 ad_apps_private.check_for_apps_ddl(tmp_apps_schema);
1300
1301 -- create grants
1302
1303 ad_invoker.grant_a_package(first_apps_schema, upper_obj_name,
1304 'PACKAGE', tmp_apps_schema, 'Y', 'I', 'FALSE');
1305
1306 end if;
1307 -- end if not first APPS schema
1308
1309 tmp_ign := tmp_ign + 1;
1310 end loop;
1311 -- end create/fix grants for package spec
1312
1313 is_invoker_object := TRUE;
1314 invoker_flag_for_obj := 'I';
1315
1316 else
1317 -- package spec has authid. set flags according to the value
1318
1319 if obj_invoker_flag = 'I' then
1320 is_invoker_object := TRUE;
1321 else
1322 is_invoker_object := FALSE;
1323
1324 if obj_invoker_flag = 'S' and install_group_num = 0 then
1325
1326 -- If an SOA product tries to create a Definer's Rights
1327 -- nosync object the results would be unsupportable, so
1328 -- do not allow this. Instead, if nosync requested for
1329 -- an SOA product's PL/SQL object, ignore the nosync keyword
1330 -- and copy it to all APPS schemas anyway
1331
1332 obj_invoker_flag := 'D';
1333 end if;
1334
1335 end if;
1336
1337 invoker_flag_for_obj := obj_invoker_flag;
1338
1339 end if;
1340 -- end if package spec contains authid keyword
1341
1342 else
1343 -- object found, but not a package spec or both spec and body
1344
1345 raise_application_error(-20000, '"'||upper_obj_name||
1346 '" in current APPS schema "'||current_apps_schema||
1347 '" is not a package specification. Cannot create package '||
1348 'body "'||upper_obj_name||'"');
1349
1350 end if;
1351 else
1352 if upper(current_apps_schema) ='CTXSYS' then
1353 first_apps_schema:=current_apps_schema;
1354 end if;
1355 -- didn't find object in current APPS schema.
1356 -- This probably an Invoker's Rights package or a normal
1357 -- Definer's Rights package. It's also possible that
1358 -- the package spec doesn't exist
1359
1360 -- Look for package spec in first APPS schema
1361
1362 ad_apps_private.exact_synonym_match(first_apps_schema, upper_obj_name,
1363 first_apps_schema, upper_obj_name, obj_is_correct_synonym,
1364 obj_exists_in_schema, obj_type_in_schema);
1365
1366 if obj_exists_in_schema then
1367 -- found the object in the first APPS schema
1368 -- if either package spec or both spec and body, parse the
1369 -- package spec to figure out what to do with the package body
1370 -- If neither, this is an error condition
1371
1372 if obj_type_in_schema = 'PACKAGE'
1373 or obj_type_in_schema = 'PKG_S_AND_B' then
1374
1375 -- found package spec in first APPS schema
1376 -- parse it to see what type it is
1377
1378 ad_invoker.classify_plsql_object(first_apps_schema,
1379 upper_obj_name, 'PACKAGE', obj_has_authid, obj_invoker_flag);
1380
1381 -- If no authid clause, convert the package spec to Invoker's
1382 -- Rights.
1383
1384 if obj_has_authid = 'FALSE' then
1385 -- rewrite spec for Invoker's Rights
1386 -- and run grant logic for it
1387
1388 ad_invoker.rewrite_a_package(first_apps_schema, upper_obj_name,
1389 'PACKAGE', first_apps_schema, 'FALSE');
1390
1391 -- create/fix grants for package spec
1392
1393 tmp_ign := 1;
1394
1395 loop
1396 if tmp_ign <> 1 then
1397
1398 -- Get name for this APPS schema
1399
1400 -- exit loop if no data found
1401 -- barring data integrity issues, this means we've processed
1402 -- all of the APPS schemas (and corresponding MRC schemas)
1403
1404 begin
1405 ad_apps_private.get_apps_schema_name(tmp_ign,
1406 first_apps_schema, tmp_apps_schema, tmp_mls_schema);
1407 exception
1408 when no_data_found then
1409 exit;
1410 end;
1411
1412 -- Check for APPS*DDL
1413
1414 ad_apps_private.check_for_apps_ddl(tmp_apps_schema);
1415
1416 -- create grants
1417
1418 ad_invoker.grant_a_package(first_apps_schema, upper_obj_name,
1419 'PACKAGE', tmp_apps_schema, 'Y', 'I', 'FALSE');
1420
1421 end if;
1422 -- end if not first APPS schema
1423
1424 tmp_ign := tmp_ign + 1;
1425 end loop;
1426 -- end create/fix grants for package spec
1427
1428 is_invoker_object := TRUE;
1429 invoker_flag_for_obj := 'I';
1430
1431 else
1432 -- package spec has authid. set flags according to the value
1433
1434 if obj_invoker_flag = 'I' then
1435 is_invoker_object := TRUE;
1436 else
1437 is_invoker_object := FALSE;
1438
1439 if obj_invoker_flag = 'S' and install_group_num = 0 then
1440
1441 -- If an SOA product tries to create a Definer's Rights
1442 -- nosync object the results would be unsupportable, so
1443 -- do not allow this. Instead, if nosync requested for
1444 -- an SOA product's PL/SQL object, ignore the nosync keyword
1445 -- and copy it to all APPS schemas anyway
1446
1447 obj_invoker_flag := 'D';
1448 end if;
1449
1450 end if;
1451
1452 invoker_flag_for_obj := obj_invoker_flag;
1453
1454 end if;
1455 -- end if package spec contains authid keyword
1456
1457 else
1458 -- object found, but not a package spec or both spec and body
1459
1460 raise_application_error(-20000, '"'||upper_obj_name||
1461 '" in first APPS schema "'||first_apps_schema||
1462 '" is not a package specification. Cannot create package '||
1463 'body "'||upper_obj_name||'"');
1464
1465 end if;
1466
1467 else
1468 -- package spec not found in either current or first APPS schemas
1469
1470 raise_application_error(-20000, 'No package specification '
1471 ||'found for package body "'||upper_obj_name||'"');
1472
1473 end if;
1474 -- end if package spec not found in first APPS schema
1475
1476 end if;
1477 -- end if package spec not in current APPS schema
1478
1479 end if;
1480 -- end if creating a package body
1481
1482 --
1483 -- Between object_type, is_invoker_object, and invoker_flag_for_obj
1484 -- we now have enough informaton to know what to do
1485 --
1486 -- If object is not a package body and we needed to rewrite the
1487 -- source text, we already did this.
1488 --
1489 -- If object is a package body, we already found and classified
1490 -- the spec (and possibly also rewrote it), so we know where to
1491 -- put the body
1492 --
1493 -- first_apps_schema, current_apps_schema, and install_group_num
1494 -- are also key information
1495 --
1496
1497 -- dbms_output.put_line('object_type='||object_type);
1498 -- if is_invoker_object then
1499 -- dbms_output.put_line('is_invoker_object=TRUE');
1500 -- else
1501 -- dbms_output.put_line('is_invoker_object=FALSE');
1502 -- end if;
1503 -- dbms_output.put_line('invoker_flag_for_obj='||invoker_flag_for_obj);
1504 -- dbms_output.put_line('first_apps_schema='||first_apps_schema);
1505 -- dbms_output.put_line('current_apps_schema='||current_apps_schema);
1506 -- dbms_output.put_line('install_group_num='||install_group_num);
1507
1508 if object_type <> 'PACKAGE BODY' then
1509 -- not package body
1510
1511 if upper(current_apps_schema) = 'CTXSYS' then
1512 first_apps_schema:=current_apps_schema;
1513 end if;
1514
1515 if is_invoker_object then
1516 -- Create in first APPS schema
1517 -- Create grants to other APPS schemas
1518
1519 -- Check for APPS*DDL
1520
1521 ad_apps_private.check_for_apps_ddl(first_apps_schema);
1522
1523 -- Create package in first APPS schema
1524
1525 begin
1526
1527 --dbms_output.put_line('creating package spec...');
1528
1529 array_assign_and_execute(first_apps_schema, lb, ub,
1530 insert_newlines);
1531
1532 -- dbms_output.put_line('successful');
1533
1534 exception
1535 when success_with_comp_error then
1536
1537 -- dbms_output.put_line('success w comp error');
1538
1539 -- reset main error buffer
1540
1541 ad_apps_private.error_buf := null;
1542
1543 -- record compilation error
1544
1545 comp_error := 'TRUE';
1546
1547 end;
1548
1549 -- create grants/synonyms to other APPS schemas
1550
1551 tmp_ign := 1;
1552
1553 if upper(first_apps_schema) <> 'CTXSYS' then
1554
1555 loop
1556 if tmp_ign <> 1 then
1557
1558 -- Get name for this APPS schema
1559
1560 -- exit loop if no data found
1561 -- barring data integrity issues, this means we've processed
1562 -- all of the APPS schemas (and corresponding MRC schemas)
1563
1564 begin
1565 ad_apps_private.get_apps_schema_name(tmp_ign,
1566 first_apps_schema, tmp_apps_schema, tmp_mls_schema);
1567 exception
1568 when no_data_found then
1569 exit;
1570 end;
1571
1572 -- Check for APPS*DDL
1573
1574 ad_apps_private.check_for_apps_ddl(tmp_apps_schema);
1575
1576 -- create grants
1577
1578 ad_invoker.grant_a_package(first_apps_schema, upper_obj_name,
1579 'PACKAGE', tmp_apps_schema, 'Y', 'I', 'FALSE');
1580
1581 end if;
1582 -- end if not first APPS schema
1583
1584 tmp_ign := tmp_ign + 1;
1585 end loop;
1586 -- end create grants to other APPS schemas
1587 end if;
1588
1589 else
1590
1591 if invoker_flag_for_obj = 'S' then
1592 -- Definer's Rights nosync object
1593 -- Create in current APPS schema
1594 -- no grants required
1595
1596 -- Check for APPS*DDL
1597
1598
1599 ad_apps_private.check_for_apps_ddl(current_apps_schema);
1600
1601 -- Drop existing synonym, if any
1602
1603 ad_apps_private.exact_synonym_match(current_apps_schema,
1604 upper_obj_name, current_apps_schema, upper_obj_name,
1605 obj_is_correct_synonym, obj_exists_in_schema, obj_type_in_schema);
1606
1607 if obj_exists_in_schema
1608 and obj_type_in_schema = 'SYNONYM' then
1609
1610 ad_apps_private.drop_object(current_apps_schema, upper_obj_name,
1611 'SYNONYM');
1612
1613 end if;
1614
1615 -- Create package in current APPS schema
1616
1617 begin
1618 array_assign_and_execute(current_apps_schema, lb, ub,
1619 insert_newlines);
1620 exception
1621 when success_with_comp_error then
1622 -- reset main error buffer
1623
1624 ad_apps_private.error_buf := null;
1625
1626 -- record compilation error
1627
1628 comp_error := 'TRUE';
1629
1630 end;
1631
1632 -- Do the same for corresponding MRC schema, if any
1633
1634 -- no grants required
1635
1636 else
1637 -- normal Definer's Rights object
1638 -- Create in all APPS schemas
1639 -- no grants required
1640
1641 -- because ad_invoker.grant_a_package synchronizes normal
1642 -- Definer's Rights packages in the first APPS schema with
1643 -- the corresponding packages in all other APPS schemas by
1644 -- comparing the source text and recreating in the other
1645 -- APPS schemas if required, this logic looks a lot like
1646 -- the Invoker's Rights logic above
1647
1648 -- Check for APPS*DDL
1649
1650
1651
1652 ad_apps_private.check_for_apps_ddl(first_apps_schema);
1653
1654
1655
1656 -- Create package in first APPS schema
1657
1658 begin
1659 array_assign_and_execute(first_apps_schema, lb, ub,
1660 insert_newlines);
1661
1662 exception
1663 when success_with_comp_error then
1664 -- reset main error buffer
1665
1666 ad_apps_private.error_buf := null;
1667
1668 -- record compilation error
1669
1670 comp_error := 'TRUE';
1671
1672 end;
1673
1674 -- create grants/synonyms to other APPS schemas
1675
1676 if (upper(first_apps_schema) <> 'CTXSYS') then
1677
1678 tmp_ign := 1;
1679 loop
1680 if tmp_ign <> 1 then
1681
1682 -- Get name for this APPS schema
1683
1684 -- exit loop if no data found
1685 -- barring data integrity issues, this means we've processed
1686 -- all of the APPS schemas (and corresponding MRC schemas)
1687
1688 begin
1689
1690 ad_apps_private.get_apps_schema_name(tmp_ign,
1691 first_apps_schema, tmp_apps_schema, tmp_mls_schema);
1692
1693 exception
1694 when no_data_found then
1695 exit;
1696 end;
1697
1698 -- Check for APPS*DDL
1699
1700 ad_apps_private.check_for_apps_ddl(tmp_apps_schema);
1701
1702 -- compare source text in this APPS schema with text in
1703 -- first APPS schema, and (re)create if not identical
1704
1705 ad_invoker.grant_a_package(first_apps_schema, upper_obj_name,
1706 'PACKAGE', tmp_apps_schema, 'Y', 'D', 'FALSE');
1707
1708 end if;
1709 -- end if not first APPS schema
1710
1711 tmp_ign := tmp_ign + 1;
1712 end loop;
1713
1714 end if;
1715 -- end copy package spec to other APPS schemas
1716
1717 -- no grants required
1718
1719 end if;
1720 -- end Definer's Rights nosync object
1721
1722 end if;
1723 -- end if Invoker's Rights object
1724 else
1725 -- package body
1726
1727 if is_invoker_object then
1728 -- Create in first APPS schema
1729 -- No grants required
1730
1731 -- Check for APPS*DDL
1732
1733 if upper(current_apps_schema) = 'CTXSYS' then
1734 first_apps_schema:=current_apps_schema;
1735 end if;
1736
1737 ad_apps_private.check_for_apps_ddl(first_apps_schema);
1738
1739 -- Create package body in first APPS schema
1740
1741
1742 begin
1743 array_assign_and_execute(first_apps_schema, lb, ub,
1744 insert_newlines);
1745 exception
1746 when success_with_comp_error then
1747 -- reset main error buffer
1748
1749 ad_apps_private.error_buf := null;
1750
1751 -- record compilation error
1752
1753 comp_error := 'TRUE';
1754
1755 end;
1756
1757 -- No grants required
1758
1759 else
1760
1761 if invoker_flag_for_obj = 'S' then
1762 -- Definer's Rights nosync object
1763 -- Create in current APPS schema
1764 -- no grants required
1765
1766 -- Check for APPS*DDL
1767
1768 ad_apps_private.check_for_apps_ddl(current_apps_schema);
1769
1770 -- Create package body in current APPS schema
1771
1772 begin
1773 array_assign_and_execute(current_apps_schema, lb, ub,
1774 insert_newlines);
1775 exception
1776 when success_with_comp_error then
1777 -- reset main error buffer
1778
1779 ad_apps_private.error_buf := null;
1780
1781 -- record compilation error
1782
1783 comp_error := 'TRUE';
1784
1785 end;
1786
1787 -- Do the same for corresponding MRC schema, if any
1788
1789 -- No grants required
1790
1791 else
1792 -- normal Definer's Rights object
1793 -- Create in all APPS schemas
1794 -- no grants required
1795
1796 tmp_ign := 1;
1797
1798 if upper(current_apps_schema) = 'CTXSYS' then
1799 array_assign_and_execute(current_apps_schema, lb, ub,
1800 insert_newlines);
1801 else
1802 loop
1803 -- Get name for this APPS schema
1804
1805 -- exit loop if no data found
1806 -- barring data integrity issues, this means we've processed
1807 -- all of the APPS schemas (and corresponding MRC schemas)
1808
1809 begin
1810 ad_apps_private.get_apps_schema_name(tmp_ign,
1811 first_apps_schema, tmp_apps_schema, tmp_mls_schema);
1812 exception
1813 when no_data_found then
1814 exit;
1815 end;
1816
1817 -- Check for APPS*DDL
1818
1819 ad_apps_private.check_for_apps_ddl(tmp_apps_schema);
1820
1821 -- Create package body in this APPS schema
1822
1823 begin
1824 array_assign_and_execute(tmp_apps_schema, lb, ub,
1825 insert_newlines);
1826 exception
1827 when success_with_comp_error then
1828 -- reset main error buffer
1829
1830 ad_apps_private.error_buf := null;
1831
1832 -- record compilation error
1833
1834 comp_error := 'TRUE';
1835
1836 end;
1837
1838 -- also create package body in corresponding MRC schema
1839
1840 tmp_ign := tmp_ign + 1;
1841 end loop;
1842 end if;
1843 -- end create package body in all APPS schemas
1844
1845 -- no grants required
1846
1847 end if;
1848 -- end if Definer's Rights nosync object
1849
1850 end if;
1851 -- end if Invoker's Rights object
1852
1853 end if;
1854 -- end if not package body
1855
1856 -- We used to get success_with_comp_error if a PL/SQL object
1857 -- was create with compilation errors, but that doesn't appear
1858 -- to be working now.
1859 --
1860 -- To workaround this, if comp_error is 'FALSE', check to see if
1861 -- it looks like the object created with 'VALID' status.
1862 --
1863 -- The exact query depends on whether the object is Invoker,
1864 -- Definer, or Definer nosync
1865 --
1866
1867
1868 if comp_error = 'FALSE' then
1869 if is_invoker_object then
1870
1871 -- Invoker's Rights object
1872 -- Check in first APPS schema
1873 begin
1874 c_statement := 'select status from dba_objects '||
1875 'where owner='''||upper(first_apps_schema)||
1876 ''' and object_name='''||upper_obj_name||
1877 ''' and object_type='''||upper(object_type)||'''';
1878
1879 EXECUTE IMMEDIATE c_statement
1880 into object_status;
1881
1882
1883
1884 exception
1885 when others then
1886 ad_ddl.error_buf := 'c_statement='||c_statement||':'||
1887 ad_ddl.error_buf;
1888 raise;
1889 end;
1890
1891 if object_status = 'INVALID' then
1892 comp_error := 'TRUE';
1893 end if;
1894
1895 else
1896 if invoker_flag_for_obj = 'S' then
1897 -- Definer's Rights nosync object
1898 -- Check in current APPS and MRC schemas
1899
1900 begin
1901 c_statement := 'select count(*) from dba_objects '||
1902 'where owner= '''||upper(current_apps_schema)||
1903 ''' and object_name='''||upper_obj_name||
1904 ''' and object_type='''||upper(object_type)||
1905 ''' and status=''INVALID''';
1906
1907 EXECUTE IMMEDIATE c_statement
1908 into invalid_count;
1909
1910 exception
1911 when others then
1912 ad_ddl.error_buf := 'c_statement='||c_statement||':'||
1913 ad_ddl.error_buf;
1914 raise;
1915 end;
1916
1917 if invalid_count <> 0 then
1918 comp_error := 'TRUE';
1919 end if;
1920
1921 else
1922 -- Definer's Rights object
1923 -- Check in all APPS and MRC schemas
1924 if (upper(first_apps_schema) <> 'CTXSYS') then
1925 begin
1926 c_statement := 'select count(*) from dba_objects '||
1927 'where object_name='''||upper_obj_name||
1928 ''' and object_type='''||upper(object_type)||
1929 ''' and owner in (select oracle_username '||
1930 'from '||upper(first_apps_schema)||'.fnd_oracle_userid '||
1931 'where read_only_flag in (''U'',''K'')) '||
1932 'and status=''INVALID''';
1933
1934 EXECUTE IMMEDIATE c_statement
1935 into invalid_count;
1936
1937 exception
1938 when others then
1939 ad_ddl.error_buf := 'c_statement='||c_statement||':'||
1940 ad_ddl.error_buf;
1941 raise;
1942 end;
1943
1944 if invalid_count <> 0 then
1945 comp_error := 'TRUE';
1946 end if;
1947 else
1948 comp_error :='FALSE';
1949 end if;
1950
1951
1952 end if;
1953 -- end if Definer's Rights nosync object
1954
1955 end if;
1956 -- end if Invoker's Rights object
1957
1958 end if;
1959 -- end if comp_error is 'FALSE'
1960 exception
1961 when others then
1962 ad_ddl.error_buf := 'create_plsql_object('||applsys_schema||', '||
1963 application_short_name||
1964 ', '||object_name||', '||
1965 lb||', '||ub||', '||insert_newlines||'): '||
1966 ad_ddl.error_buf||': '||ad_apps_private.error_buf||
1967 ': substr($statement$,1,255)='''||
1968 substr(gbl_statement,1,255)||'''';
1969 raise;
1970 end create_plsql_object;
1971
1972
1973 procedure build_package
1974 (ddl_text in varchar2,
1975 row_num in integer)
1976 is
1977 begin
1978 ad_ddl.error_buf := null;
1979
1980 ad_ddl.glprogtext(row_num) := ddl_text;
1981
1982 exception
1983 when others then
1984 ad_ddl.error_buf := 'build_package('||
1985 ddl_text||', '||row_num||'): '||ad_ddl.error_buf;
1986 raise;
1987 end build_package;
1988
1989
1990 procedure build_statement
1991 (ddl_text in varchar2,
1992 row_num in integer)
1993 is
1994 begin
1995 ad_ddl.error_buf := null;
1996
1997 ad_ddl.glprogtext(row_num) := ddl_text;
1998
1999 exception
2000 when others then
2001 ad_ddl.error_buf := 'build_statement('||
2002 ddl_text||', '||row_num||'): '||ad_ddl.error_buf;
2003 raise;
2004 end build_statement;
2005
2006
2007 procedure do_array_ddl
2008 (applsys_schema in varchar2,
2009 application_short_name in varchar2,
2010 statement_type in integer,
2011 lb in integer,
2012 ub in integer,
2013 object_name in varchar2)
2014 is
2015 c_statement varchar2(10000);
2016 dummy_boolean varchar2(30);
2017 status varchar2(1);
2018 industry varchar2(1);
2019 oracle_schema varchar2(30);
2020 apps_schema varchar2(30);
2021 apps_mls_schema varchar2(30);
2022 begin
2023 ad_ddl.error_buf := null;
2024 ad_apps_private.error_buf := null;
2025 gbl_statement := null;
2026
2027 -- from the APPLSYS schema get an APPS schema so that we can access
2028 -- the procedure FND_INSTALLATION that exists there
2029 ad_apps_private.get_apps_schema_name( 0, applsys_schema,
2030 apps_schema, apps_mls_schema);
2031
2032 if upper(application_short_name) not in ('INTERMEDIA','APPS') then
2033
2034 begin
2035 c_statement:='declare x boolean; '||
2036 'begin x := '||upper(apps_schema)||'.fnd_installation.'||
2037 'get_app_info_other('''||upper(application_short_name)||
2038 ''', '''||upper(apps_schema)||''', :status, :industry, '||
2039 ':oracle_schema); '||
2040 'if x = TRUE then :dummy_boolean := ''TRUE''; '||
2041 'elsif x = FALSE then :dummy_boolean := ''FALSE''; '||
2042 'else :dummy_boolean := null; end if; end;';
2043
2044 EXECUTE IMMEDIATE c_statement
2045 using OUT status, OUT industry,
2046 OUT oracle_schema, OUT dummy_boolean;
2047
2048 exception
2049 when others then
2050 ad_ddl.error_buf := 'c_statement='||c_statement||': '||
2051 ad_ddl.error_buf;
2052 raise;
2053 end;
2054
2055 if dummy_boolean <> 'TRUE' then
2056 raise_application_error(-20000,'Call to GET_APP_INFO_OTHER() failed: '||
2057 'USER='||user||', Status='||status||', Industry='||industry||
2058 ', Schema='|| oracle_schema||', STMT='||c_statement);
2059 end if;
2060 else
2061 if upper(application_short_name) = 'INTERMEDIA' then
2062 oracle_schema:='CTXSYS';
2063 else
2064 select ORACLE_USERNAME
2065 into
2066 oracle_schema
2067 from
2068 fnd_oracle_userid where
2069 read_only_flag='U';
2070 end if;
2071 end if;
2072
2073
2074 private_do_array_ddl(apps_schema, applsys_schema, oracle_schema,
2075 statement_type, lb, ub, upper(object_name));
2076
2077 exception
2078 when others then
2079 ad_ddl.error_buf := 'do_array_ddl('||applsys_schema||', '||
2080 application_short_name||
2081 ', '||statement_type||', '||lb||', '||ub||', '||
2082 object_name||'): '||
2083 ad_ddl.error_buf||': '||ad_apps_private.error_buf||
2084 ': substr($statement$,1,255)='''||
2085 substr(gbl_statement,1,255)||'''';
2086 raise;
2087 end do_array_ddl;
2088
2089
2090 procedure private_do_array_ddl
2091 (p_apps_schema in varchar2,
2092 p_applsys_schema in varchar2,
2093 oracle_schema in varchar2,
2094 statement_type in number,
2095 lb in integer,
2096 ub in integer,
2097 object_name in varchar2)
2098 is
2099 install_group_num number;
2100 l_apps_schema varchar2(30);
2101 l_mls_apps_schema varchar2(30);
2102 l_mrc_apps_schema varchar2(30);
2103 rows_processed integer;
2104 c integer;
2105 begin
2106
2107 if ad_apps_private.is_mls is null
2108 or ad_apps_private.is_mc is null then
2109 ad_apps_private.initialize(p_apps_schema);
2110 end if;
2111
2112 -- get the install_group_num from the oracle_schema that the object
2113 -- is to be created in.
2114 declare
2115 c integer;
2116 rows_processed number;
2117 c_statement varchar2(2000);
2118 begin
2119 c := dbms_sql.open_cursor;
2120 c_statement:='select install_group_num from '||
2121 p_apps_schema||'.fnd_oracle_userid '||
2122 'where oracle_username = upper(:oracle_schema) '||
2123 'and install_group_num is not null';
2124 dbms_sql.parse(c, c_statement, dbms_sql.native);
2125 dbms_sql.bind_variable(c,'oracle_schema',oracle_schema,30);
2126 dbms_sql.define_column(c,1,install_group_num);
2127 rows_processed := dbms_sql.execute(c);
2128 if dbms_sql.fetch_rows(c) > 0 then
2129 dbms_sql.column_value(c,1,install_group_num);
2130
2131 else
2132 raise no_data_found;
2133 end if;
2134 dbms_sql.close_cursor(c);
2135 exception
2136 when others then
2137 dbms_sql.close_cursor(c);
2138 ad_ddl.error_buf := 'c_statement='||c_statement||': '||
2139 ad_ddl.error_buf;
2140 raise;
2141 end;
2142
2143 -- Check for APPS*DDL packages
2144
2145 ad_apps_private.check_for_apps_ddl(p_apps_schema);
2146 ad_apps_private.check_for_apps_ddl(oracle_schema);
2147
2148 if ad_apps_private.is_mls then
2149 -- Get name of mls schema
2150 ad_apps_private.get_apps_schema_name(install_group_num, p_applsys_schema,
2151 l_apps_schema, l_mls_apps_schema);
2152 -- check for APPS*DDL in mls schema
2153 ad_apps_private.check_for_apps_ddl(l_mls_apps_schema);
2154 end if;
2155
2156 --
2157 -- Based on type of action determine what to do
2158 --
2159 if statement_type = ad_ddl.create_view then
2160 do_array_c_view(install_group_num, p_apps_schema,
2161 object_name, lb, ub);
2162 else
2163 raise_application_error(-20000,'Unsupported statement type '||
2164 statement_type);
2165 end if;
2166
2167 exception
2168 when others then
2169 ad_ddl.error_buf := 'private_do_array_ddl('||p_apps_schema||', '||
2170 p_applsys_schema||', '||oracle_schema||
2171 ', '||statement_type||', '||lb||', '||ub||', '||
2172 object_name||'): '||ad_ddl.error_buf;
2173 raise;
2174 end private_do_array_ddl;
2175
2176
2177 procedure do_array_c_view
2178 (dcv_install_group_num in number,
2179 dcv_apps_schema in varchar2,
2180 dcv_object_name in varchar2,
2181 dcv_lb in integer,
2182 dcv_ub in integer)
2183 is
2184 l_apps_schema varchar2(30);
2185 l_mls_apps_schema varchar2(30);
2186 l_mrc_apps_schema varchar2(30);
2187 print_local_sql boolean;
2188 begin
2189
2190 if ad_apps_private.is_mls is null
2191 or ad_apps_private.is_mc is null then
2192 ad_apps_private.initialize(dcv_apps_schema);
2193 end if;
2194 -- now do the correct action in the apps schemas
2195 if dcv_install_group_num <> 0 then
2196 -- if this is a non 0 install group then perform this only
2197 -- for that apps account
2198
2199 -- get the apps_schema_names for this install group
2200 ad_apps_private.get_apps_schema_name(dcv_install_group_num,
2201 dcv_apps_schema, l_apps_schema, l_mls_apps_schema);
2202
2203 -- create view in apps schema
2204 array_assign_and_execute(l_apps_schema, dcv_lb, dcv_ub);
2205
2206 if ad_apps_private.is_mls then
2207 -- create view in apps_mls schema
2208 array_assign_and_execute(l_mls_apps_schema, dcv_lb, dcv_ub);
2209 end if;
2210
2211 else
2212 -- if this is a 0 install group then perform this for all
2213 -- apps accounts (all install groups)
2214 declare
2215 l_apps_schema varchar2(30);
2216 l_mls_apps_schema varchar2(30);
2217 l_mrc_apps_schema varchar2(30);
2218 c integer;
2219 rows_processed number;
2220 c_statement varchar2(2000);
2221 l_install_group_num number;
2222 begin
2223 print_local_sql := TRUE;
2224 c := dbms_sql.open_cursor;
2225 c_statement:='select distinct install_group_num from '||
2226 dcv_apps_schema||'.fnd_oracle_userid '||
2227 'where read_only_flag = ''U'' '||
2228 'order by install_group_num';
2229 dbms_sql.parse(c, c_statement, dbms_sql.native);
2230 dbms_sql.define_column(c,1,l_install_group_num);
2231 rows_processed := dbms_sql.execute(c);
2232 print_local_sql := FALSE;
2233 loop
2234 if dbms_sql.fetch_rows(c) > 0 then
2235 dbms_sql.column_value(c,1,l_install_group_num);
2236
2237 -- get the apps_schema_names for this install group
2238 ad_apps_private.get_apps_schema_name(l_install_group_num,
2239 dcv_apps_schema, l_apps_schema, l_mls_apps_schema);
2240
2241 -- create view in apps schema
2242 array_assign_and_execute(l_apps_schema, dcv_lb, dcv_ub);
2243
2244 if ad_apps_private.is_mls then
2245 -- create view in apps_mls schema
2246 array_assign_and_execute(l_mls_apps_schema,
2247 dcv_lb, dcv_ub);
2248 end if;
2249
2250 else
2251 dbms_sql.close_cursor(c);
2252 exit;
2253 end if;
2254 end loop;
2255 exception
2256 when others then
2257 dbms_sql.close_cursor(c);
2258 if print_local_sql then
2259 ad_ddl.error_buf := 'c_statement='||c_statement||': '||
2260 ad_ddl.error_buf;
2261 end if;
2262 raise;
2263 end;
2264 end if;
2265 exception
2266 when others then
2267 ad_ddl.error_buf := 'do_array_c_view('||dcv_install_group_num||', '||
2268 dcv_apps_schema||', '||dcv_object_name||', '||
2269 dcv_lb||', '||dcv_ub||'): '||ad_ddl.error_buf;
2270 raise;
2271 end do_array_c_view;
2272
2273
2274
2275
2276 -- Bug 3611969 : FIXED FILE.SQL.35 GSCC WARNINGS
2277 -- sraghuve (07/05/2004)
2278
2279
2280 procedure array_assign_and_execute
2281 (p_schema_name in varchar2,
2282 p_lb in integer,
2283 p_ub in integer)
2284 is
2285 begin
2286
2287 array_assign_and_execute
2288 (p_schema_name => p_schema_name,
2289 p_lb => p_lb,
2290 p_ub => p_ub,
2291 add_newline => 'FALSE');
2292
2293 end;
2294
2295
2296
2297 procedure array_assign_and_execute
2298 (p_schema_name in varchar2,
2299 p_lb in integer,
2300 p_ub in integer,
2301 add_newline in varchar2)
2302 is
2303 begin
2304 --
2305 -- Copy DDL text to p_schema_name.apps_array_ddl.glprogtext
2306 --
2307
2308 for counter in p_lb..p_ub loop
2309 begin
2310 ad_apps_private.do_array_assignment(p_schema_name,
2311 ad_ddl.glprogtext(counter), counter);
2312 exception
2313 when others then
2314 ad_ddl.error_buf := ' At line '||counter||' of array DDL text '||
2315 ad_ddl.error_buf;
2316 raise;
2317 end;
2318 end loop;
2319
2320 --
2321 -- Execute array DDL statement
2322 --
2323
2324 ad_apps_private.do_apps_array_ddl(p_schema_name, p_lb, p_ub, add_newline);
2325
2326 exception
2327 when others then
2328 ad_ddl.error_buf := 'array_assign_and_execute('||
2329 p_schema_name||', '||p_lb||', '||p_ub||', '||add_newline||
2330 '): '||ad_ddl.error_buf;
2331 --
2332 -- load statement array into gbl_statement
2333 --
2334 get_array_statement(p_lb, p_ub);
2335
2336 raise;
2337 end array_assign_and_execute;
2338
2339
2340 procedure get_array_statement
2341 (p_lb in integer,
2342 p_ub in integer)
2343 is
2344 statement_length number;
2345 chunk_length number;
2346 array_index number;
2347 begin
2348 gbl_statement := null;
2349 statement_length := 0;
2350 array_index := p_lb;
2351
2352 loop
2353 if array_index > p_ub then
2354 exit;
2355 end if;
2356
2357 chunk_length := lengthb(ad_ddl.glprogtext(array_index));
2358
2359 if (statement_length + chunk_length) > 32760 then
2360 exit;
2361 end if;
2362
2363 if chunk_length > 0 then
2364 gbl_statement := gbl_statement || ad_ddl.glprogtext(array_index);
2365 statement_length := statement_length + chunk_length;
2366 end if;
2367
2368 array_index := array_index + 1;
2369 end loop;
2370
2371 exception
2372 when others then
2373 ad_ddl.error_buf := ad_ddl.error_buf||
2374 'get_array_statement('||p_lb||', '||p_ub||'): ';
2375 raise;
2376 end get_array_statement;
2377
2378
2379 procedure create_trigger_in_schema
2380 (schema_name in varchar2,
2381 ddl_text in varchar2) is
2382 success_with_comp_error exception;
2383 PRAGMA EXCEPTION_INIT(success_with_comp_error, -24344);
2384 uc_schema varchar2(30);
2385 begin
2386 -- Initialize global variables
2387
2388 ad_ddl.error_buf := null;
2389 ad_apps_private.error_buf := null;
2390 gbl_statement := ddl_text;
2391
2392 -- Upper-case schema name
2393
2394 uc_schema := substr(upper(schema_name),1,30);
2395
2396 -- Check for APPS*DDL in the schema
2397
2398 ad_apps_private.check_for_apps_ddl(uc_schema);
2399
2400 -- Execute create trigger statement using APPS_DDL
2401
2402 begin
2403 ad_apps_private.do_apps_ddl(uc_schema, ddl_text, 'TRUE');
2404 exception
2405 when success_with_comp_error then
2406 ad_apps_private.error_buf := null;
2407 end;
2408
2409 exception
2410 when others then
2411 ad_ddl.error_buf := 'create_trigger_in_schema('||schema_name||
2412 ', $statement$): '||ad_ddl.error_buf||': '||ad_apps_private.error_buf||
2413 ': substr($statement$,1,255)='''||
2414 substr(gbl_statement,1,255)||'''';
2415 raise;
2416 end create_trigger_in_schema;
2417
2418
2419 end ad_ddl;
[Home] [Help]