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