1 package body AD_ZD_TABLE as
2 /* $Header: ADZDTMB.pls 120.78.12020000.27 2013/06/21 13:24:42 rputchak ship $ */
3
4 C_PACKAGE constant varchar2(80) := 'ad.plsql.ad_zd_table.';
5
6 /*
7 ** --------------------------------------------------------------------
8 ** Internal
9 ** --------------------------------------------------------------------
10 */
11
12 -- log shortcut
13 procedure LOG(X_MODULE varchar2, X_LOG_TYPE varchar2, X_MESSAGE varchar2) is
14 begin
15 ad_zd.log(x_module, x_log_type, x_message);
16 end;
17
18 -- error shortcut
19 procedure ERROR(X_MODULE varchar2, X_MESSAGE varchar2) is
20 begin
21 ad_zd.error(x_module, x_message);
22 end;
23
24 -- exec shortcut (varchar2)
25 procedure EXEC(
26 X_SQL in varchar2,
27 X_LOG_MOD in varchar2,
28 X_IGNORE in boolean default false,
29 X_PHASE in varchar2 default null) is
30 begin
31 if x_phase is null then
32 -- immediate execution
33 ad_zd.exec(x_sql, x_log_mod, x_ignore);
34 else
35 -- deferred execution: < Control won't come here as of 22-Aug-2012 >
36 ad_zd_parallel_exec.load(
37 x_phase => x_phase,
38 x_sql => x_sql,
39 x_unique => true );
40 end if;
41 end;
42
43 -- exec shortcut (clob)
44 procedure EXEC(
45 X_SQL in clob,
46 X_LOG_MOD in varchar2,
47 X_IGNORE in boolean default false,
48 X_PHASE in varchar2 default null) is
49 begin
50 if x_phase is null then
51 -- immediate execution
52 ad_zd.exec(x_sql, x_log_mod, x_ignore);
53 else
54 -- deferred execution: <Control won't come here as of 22-Aug-2012 >
55 ad_zd_parallel_exec.load(
56 x_phase => x_phase,
57 x_sql => x_sql,
58 x_unique => true);
59 end if;
60 end;
61
62 -- Util function to get Newline
63 --
64 function local_chr(ascii_chr in number) return varchar2 is
65 lang varchar2(255);
66 begin
67 lang := userenv('LANGUAGE');
68 return(convert(chr(ascii_chr),
69 substr(lang, instr(lang,'.') + 1), 'US7ASCII'));
70 end local_chr;
71
72 /*
73 ** Stores patched table name for finalize and cutover processing
74 ** AD_PATCHED_TABLES.STATUS:
75 ** 'N' - New
76 ** 'C' - Completed
77 ** 'U' - Updated
78 */
79 procedure STORE(X_TABLE_OWNER in varchar2, X_TABLE_NAME in varchar2) is
80 L_STATUS varchar2(1);
81 begin
82
83 -- Get existing table status
84 select status
85 into l_status
86 from ad_patched_tables
87 where owner = x_table_owner
88 and name = x_table_name;
89
90 -- Set "Completed" table back "Updated" status
91 if (l_status = 'C') then
92 update ad_patched_tables
93 set status='U'
94 where owner = x_table_owner
95 and name = x_table_name;
96 commit;
97 end if;
98
99 exception when no_data_found then
100 -- Add missing table with "New" status
101 insert into ad_patched_tables(owner, name, status)
102 values (x_table_owner, x_table_name, 'N');
103 commit;
104
105 end STORE;
106
107
108
109 /* ==========================================================================================
110 **
111 ** Editioning View Tools
112 **
113 ** =========================================================================================*/
114
115 --
116 -- Check if Editioning View exists for this table
117 -- return: 'Y' or 'N'
118 --
119 function EV_EXISTS(
120 X_TABLE_OWNER in varchar2,
121 X_TABLE_NAME in varchar2) return varchar2
122 is
123 L_EV_NAME varchar2(30);
124 begin
125 select ev.view_name
126 into l_ev_name
127 from dba_editioning_views ev
128 where ev.owner = x_table_owner
129 and ev.view_name = ad_zd_table.ev_view(x_table_name);
130
131 return 'Y';
132
133 exception
134 when no_data_found then
135 return 'N';
136 end EV_EXISTS;
137
138 --
139 -- Return Editioning View name for given Table
140 --
141 function EV_VIEW(
142 X_TABLE_NAME in varchar2) return varchar2
143 is
144 begin
145 return substrb(x_table_name,1,29)||'#';
146 end EV_VIEW;
147
148 --
149 -- Return Table name for given Editioning View
150 -- Note: EV must exist
151 --
152 function EV_TABLE( X_EV_OWNER in varchar2,
153 X_EV_NAME in varchar2) return varchar2
154 is
155 L_TABLE_NAME varchar2(30);
156 begin
157 select ev.table_name
158 into l_table_name
159 from dba_editioning_views ev
160 where ev.owner = x_ev_owner
161 and ev.view_name = replace(x_ev_name, '$', '#');
162
163 return l_table_name;
164 end EV_TABLE;
165
166 --
167 -- Return EV View Column name for given Table Column
168 --
169 function EV_VIEW_COLUMN(
170 X_COLUMN_NAME in varchar2) return varchar2
171 is
172 begin
173 if x_column_name like '%#_%' then
174 -- this is a versioned column, strip the version
175 return substrb(x_column_name, 1, instrb(x_column_name,'#',-1)-1);
176 end if;
177 return x_column_name;
178 end EV_VIEW_COLUMN;
179
180 --
181 -- Return Table Column for given EV View Column
182 -- In other words, translate logical table.column to actual table.column
183 -- Note: EV must exist
184 --
185 function EV_TABLE_COLUMN(
186 X_EV_OWNER in varchar2,
187 X_EV_NAME in varchar2,
188 X_COLUMN_NAME in varchar2) return varchar2
189 is
190 L_COLUMN_NAME varchar2(30);
191 begin
192 select evc.table_column_name
193 into l_column_name
194 from dba_editioning_view_cols evc
195 where evc.owner = replace(x_ev_owner, '$', '#')
196 and evc.view_name = x_ev_name
197 and evc.view_column_name = x_column_name;
198
199 return l_column_name;
200 end EV_TABLE_COLUMN;
201
202 --
203 -- Returns the Table Column Revision Tag for
204 -- a given column name. Returns '0' if the
205 -- column name does not have revision tag.
206 --
207 function EV_TABLE_COLUMN_REVISION(
208 X_COLUMN_NAME in varchar2) return varchar2
209 is
210 l_col_revision varchar2(10);
211 begin
212 if x_column_name like '%#_%' then
213 return substrb(x_column_name, instrb(x_column_name,'#', -1) + 1, length(x_column_name));
214 end if;
215 return '0';
216 end;
217
218
219 /*
220 ** Is the table a Seed Data Table? Returns'Y'/'N'
221 */
222 function IS_SEED(
223 X_TABLE_OWNER in varchar2,
224 X_TABLE_NAME in varchar2) return varchar2
225 is
226 L_IS_SEED varchar2(1);
227 begin
228 begin
229 select 'Y'
230 into l_is_seed
231 from dba_tab_columns c
232 where c.owner = x_table_owner
233 and c.table_name = x_table_name
234 and c.column_name = 'ZD_EDITION_NAME';
235 exception
236 when no_data_found then
237 return 'N';
238 end;
239
240 return 'Y';
241 end;
242
243
244 /*
245 ** Fetch Multiple DDLs
246 ** x_object_type - object type (TRIGGER)
247 ** x_object_owner - Trigger owner
248 ** x_object_name - trigger name
249 */
250 function FETCH_MULTIPLE_DDLS (
251 X_OBJECT_OWNER in varchar2,
252 X_OBJECT_NAME in varchar2,
253 X_OBJECT_TYPE in varchar2) return SYS.KU$_DDLS
254 is
255 C_MODULE varchar2(80) := c_package||'fetch_multiple_ddls';
256 L_OPEN_HANDLE number;
257 L_TRANSFORM_HANDLE number;
258 L_DDL_STMTS sys.ku$_ddls;
259 begin
260 log(c_module, 'STATEMENT',
261 'Getting DDLs for '||x_object_owner||'.'||x_object_name||', '|| x_object_type);
262
263 l_open_handle := dbms_metadata.open(x_object_type);
264 dbms_metadata.set_filter(l_open_handle, 'SCHEMA', x_object_owner);
265 dbms_metadata.set_filter(l_open_handle, 'NAME', x_object_name);
266 l_transform_handle := dbms_metadata.add_transform(l_open_handle, 'DDL');
267 dbms_metadata.set_transform_param(l_transform_handle, 'SQLTERMINATOR', false);
268
269 l_ddl_stmts := dbms_metadata.fetch_ddl(l_open_handle);
270 dbms_metadata.close(l_open_handle);
271
272 return l_ddl_stmts;
273 end FETCH_MULTIPLE_DDLS;
274
275
276 /*
277 ** Alter triggers for a given table
278 **
279 ** X_MODE - COMPILE or 'ENABLE' or 'DISABLE' the trigger
280 **
281 */
282 procedure ALTER_TRIGGER( X_TRIGGER_OWNER in varchar2,
283 X_TRIGGER_NAME in varchar2,
284 X_MODE in varchar2)
285 is
286 C_MODULE varchar2(80) := c_package||'alter_trigger';
287 L_STMT varchar2(256);
288 begin
289 l_stmt := 'alter trigger "'||x_trigger_owner||'"."'|| x_trigger_name ||'" '||x_mode;
290 exec (l_stmt, c_module, false); -- ignore errors
291 end;
292
293 --
294 -- This procedure copies the Object grants from Table to EV.
295 --
296 -- NOTE: All grants can NOT be copied because some grants may not
297 -- be applicable on a VIEW while same may be applicable on
298 -- a table.
299 --
300 -- View PRIVILEGES
301 -- ==============
302 -- DEBUG, DELETE, INSERT, MERGE, REFERENCES, SELECT, UNDER, UPDATE
303 --
304 -- TABLE PRIVILEGES :
305 -- ==================
306 -- ALTER, DELETE, DEBUG, INDEX, INSERT, REFERENCES, SELECT, UPDATE
307 --
308 procedure COPY_GRANTS(
309 X_TABLE_OWNER in varchar2,
310 X_TABLE_NAME in varchar2,
311 X_EV_NAME in varchar2)
312 is
313
314 C_MODULE varchar2(127) := c_package||'COPY_GRANTS';
315 L_STR varchar2(1026);
316 L_EV_STR_PRIVILEGE varchar2(1024);
317 cursor C_GRANTS(P_TABLE_OWNER varchar2,
318 P_TABLE_NAME varchar2,
319 P_VIEW_NAME varchar2) is
320 select distinct
321 tpt.grantee
322 , tpt.privilege
323 , tpt.grantable
324 , tpt.hierarchy
325 from
326 dba_tab_privs tpt
327 where tpt.owner = p_table_owner
328 and tpt.table_name = p_table_name
329 and tpt.privilege in ('SELECT', 'UPDATE', 'INSERT', 'DELETE', 'DEBUG')
330 and tpt.grantee <> 'SYSTEM'
331 and not exists
332 ( select 'x'
333 from dba_tab_privs tpv
334 where tpv.owner = tpt.owner
335 and tpv.table_name = p_view_name
336 and tpv.grantee = tpt.grantee
337 and tpv.privilege = tpt.privilege
338 )
339 order by grantee;
340
341 begin
342
343 l_ev_str_privilege := 'GRANT ';
344
345 begin
346 for grant_rec in c_grants (x_table_owner, x_table_name, x_ev_name)
347 loop
348
349 l_ev_str_privilege := l_ev_str_privilege || grant_rec.privilege || ' ON "' ||
350 x_table_owner || '"."'||x_ev_name || '" TO "' ||
351 grant_rec.grantee || '" ' ;
352
353 if (nvl(grant_rec.grantable, 'NO') = 'YES' ) then
354 l_ev_str_privilege := l_ev_str_privilege || ' WITH GRANT OPTION ';
355 end if;
356
357 if (nvl(grant_rec.hierarchy, 'NO') = 'YES' and grant_rec.privilege='SELECT' ) then
358 l_ev_str_privilege := l_ev_str_privilege || ' WITH HIERARCHY OPTION ';
359 end if;
360
361 -- Ignore if any error
362 exec(l_ev_str_privilege, c_module, true);
363 l_ev_str_privilege := 'GRANT ';
364
365 end loop;
366 exception
367 when others then
368 raise;
369 end;
370
371 end COPY_GRANTS;
372
373 --
374 -- Moves a trigger defined on a table / EV to a table synonym
375 -- This signature becomes non-public, after the changes for Bug 13597311
376 -- GB TODO: use EV instead of synonym
377 procedure MOVE_TRIGGER(
378 X_TRIGGER_OWNER varchar2,
379 X_TRIGGER_NAME Varchar2,
380 X_TRIGGER_STATUS varchar2,
381 X_VALID_STATUS varchar2,
382 X_TABLE_OWNER varchar2,
383 X_TABLE_NAME varchar2,
384 X_SYNONYM_OWNER varchar2,
385 X_SYNONYM_NAME varchar2 )
386 is
387 C_MODULE varchar2(127) := c_package ||'move_trigger';
388 L_DDL_LOB clob;
389 L_INDEX pls_integer :=0;
390 L_SYN_OWNER varchar2(30);
391 L_SYNONYM_NAME varchar2(30);
392 L_DDL_STMTS sys.ku$_ddls;
393
394 -- Match#1 : exact owner.table
395 L_REG_EXP_PATTERN_1 varchar2(127):= '[[:space:]]ON[[:space:]]*("?' || X_TABLE_OWNER ||
396 '"?)[.]?"?'|| X_TABLE_NAME ||'"?[[:space:]]';
397
398 -- Match#2 : owner.EV
399 L_REG_EXP_PATTERN_2 varchar2(127) := '[[:space:]]ON[[:space:]]*("?' || X_TABLE_OWNER ||
400 '"?)[.]?"?'|| ev_view(X_TABLE_NAME) ||'"?[[:space:]]';
401
402 -- Match#2_1 : trg-owner.EV
403 -- SSTOMAR:
404 -- It has been found if synonym points to EV then DBMS_METADATA returns
405 -- [... ON "trg-owner"."EV-name"] , NOT [ ... ON "ev-owner"."ev-name" ]
406 L_REG_EXP_PATTERN_2_1 varchar2(127) := '[[:space:]]ON[[:space:]]*("?' || X_TRIGGER_OWNER ||
407 '"?)[.]?"?'|| ev_view(X_TABLE_NAME) ||'"?[[:space:]]';
408
409 -- Match#3 : Generic and
410 -- For SYNONYM: will be modified for each synonym before match.
411 L_REG_EXP_PATTERN_3 varchar2(127):= '[[:space:]]ON[[:space:]]*("?[A-Z_0-9]*"?)[.]?"?' ||
412 X_TABLE_NAME ||'"?[[:space:]]';
413
414 -- This is used to replace ALTER TRIGGER ... ENABLE/DISABLE statement
415 -- Bug 12820852 ( some triggers has $ in name)
416 L_ALTER_REG_PATTERN varchar2(127):= 'ALTER[[:space:]]*TRIGGER[[:space:]]*("?'
417 || X_TRIGGER_OWNER || '"?)[.]?"?' ||
418 replace(replace(X_TRIGGER_NAME, '$', '\$'), '+', '\+') ||
419 '"?[[:space:]]*[A-Z]*(;?)';
420
421 -- Match regular pattern will be replaced with following string [ ON APPS.<TABLE-SYNONYM> ]
422 -- Added new-line for bug-12800936
423 L_REG_EXP_REPLACE_STR varchar2(127):= local_chr(10) || ' ON "' || X_SYNONYM_OWNER || '"."'|| X_SYNONYM_NAME ||'" ';
424
425 -- NOTE : if there is any issue with below SQL
426 -- revert back to old one because this has to use only for
427 -- 'MTL_SYSTEM_ITEMS_B' table is not being replaced as
428 -- synonym is defined on APPS synonym.
429 --
430 -- Non-Apps synonyms
431 --
432 cursor C_TAB_SYN (P_TABLE_OWNER varchar2, P_TABLE_NAME varchar2) is
433 with p( owner, synonym_name) as
434 (
435 select owner, synonym_name
436 from dba_synonyms
437 where table_name= p_table_name
438 and table_owner = p_table_owner
439 union all
440 select s.owner , s.synonym_name
441 from dba_synonyms s,
442 p
443 where s.table_name = p.synonym_name
444 and s.table_owner = p.owner
445 and s.table_owner in
446 ( select oracle_username
447 from system.fnd_oracle_userid
448 where read_only_flag in ('A','B', 'E', /*'U',*/ 'C')
449 )
450 )
451 cycle owner, synonym_name set cyclemarker to 'Y' default 'N'
452 select distinct synonym_name from p where cyclemarker = 'N' ;
453
454 SUCEESS_WITH_COMPILE_ERR exception;
455 pragma exception_init(suceess_with_compile_err, -24344);
456 begin
457
458 log(c_module, 'PROCEDURE', 'begin: ' ||x_trigger_owner||'.'||x_trigger_name);
459 --
460 -- Bug 13597311. If trigger is in invalid state, then attempt to compile it
461 -- If compilation fails with hard ORA error like
462 -- ORA-25006: cannot specify this column in UPDATE OF clause
463 -- then abort moving the table name to synonym.
464 --
465 if (upper(x_valid_status) <> 'VALID') THEN
466 begin
467 alter_trigger(x_trigger_owner, x_trigger_name, 'COMPILE');
468 exception
469 when suceess_with_compile_err then
470 -- progress, assuming the compilation error will go away subsequently
471 null;
472 when others then
473 -- ORA-04045: errors during recompilation/revalidation of ...
474 -- ORA-25006: cannot specify this column in UPDATE OF clause
475 if((sqlcode = -25006) or
476 (sqlcode = -4045 and instr (sqlerrm, 'ORA-25006') > 0 ) ) then
477 -- disable the trigger and abandon the movement
478 log(c_module, 'ERROR', 'The trigger ' ||x_trigger_owner||'.'||x_trigger_name ||
479 ' is invalid and it could not be compiled. Autopatch will disable the trigger.' ||
480 ' You must not attempt to use the system before resolving the error with Oracle Support.' ||
481 ' Data consistency may be compromised if you start using the system before resolving the error.' ||
482 ' Error message follows.');
483 log(c_module, 'ERROR', sqlerrm);
484 -- Can NOT DISABLE a trigger which has ORA-25006 issue.
485 --alter_trigger(x_trigger_owner, x_trigger_name, 'DISABLE');
486 return;
487 end if;
488 -- In ALL other cases raise error, so AD_ZD_TABLE.UPGRADE call will be marked as FAILED,
489 -- and worker would consider in next attempt.
490 log(c_module, 'ERROR', x_trigger_owner||'.'||x_trigger_name || ': '|| sqlerrm);
491 raise;
492 end;
493 end if;
494
495 -- Get DDLs of this trigger.
496 l_ddl_stmts := fetch_multiple_ddls(x_trigger_owner,x_trigger_name, 'TRIGGER');
497 if(l_ddl_stmts is not null and l_ddl_stmts.count > 0 ) then
498 for i in 1 .. l_ddl_stmts.count loop
499 l_ddl_lob := l_ddl_stmts(i).ddltext;
500 -- Actual trigger definition will have "ON" keyword
501 if(i = 1) then
502 -- Match <tabe owner>.<table name>
503 l_index := regexp_instr(l_ddl_lob, l_reg_exp_pattern_1, 1,1,0, 'i' );
504 if(l_index > 0 ) then -- MATCH#1:
505 log(c_module, 'STATEMENT','Index->' || l_index || ' Found match of-> '|| l_reg_exp_pattern_1 );
506 -- Start search from that point, replace the owner.table-name with syn-owner.sysn-name
507 l_ddl_lob := regexp_replace(l_ddl_lob,
508 l_reg_exp_pattern_1,
509 l_reg_exp_replace_str,
510 l_index, 1, 'i' ); -- first occurrence only
511 else
512 -- MATCH#2: match <tabl owner>.<EV name>
513 l_index := regexp_instr(l_ddl_lob, l_reg_exp_pattern_2, 1,1,0, 'i' );
514 if(l_index > 0) then
515 log(c_module, 'STATEMENT',
516 'Found match of '||ev_view(X_TABLE_NAME)||' in trigger definition');
517 l_ddl_lob := regexp_replace(l_ddl_lob,
518 l_reg_exp_pattern_2,
519 l_reg_exp_replace_str, l_index, 1, 'i' );
520 else
521 -- MATCH#2_1: Match <Trigger owner>.<EV name>
522 l_index := regexp_instr(l_ddl_lob, l_reg_exp_pattern_2_1, 1,1,0, 'i' );
523 if(l_index > 0) then
524 log(c_module, 'STATEMENT', 'Found match of <trigger owner>.'
525 || ev_view(X_TABLE_NAME) ||' in trigger definition');
526 l_ddl_lob := regexp_replace(l_ddl_lob,
527 l_reg_exp_pattern_2_1,
528 l_reg_exp_replace_str, l_index, 1, 'i' );
529 else
530 log(c_module, 'STATEMENT',
531 'No match found so far and in last step of matching: ->' );
532 -- MATCH#3: Match with <generic owner name>.<table name>
533 l_index := regexp_instr(l_ddl_lob, l_reg_exp_pattern_3, 1,1,0, 'i' );
534 if( l_index > 0 ) then
535 log(c_module, 'STATEMENT', 'Match found at index : ->' || l_index );
536 l_ddl_lob := regexp_replace(l_ddl_lob,
537 l_reg_exp_pattern_3,
538 l_reg_exp_replace_str, l_index, 1, 'i' );
539 else
540 -- Check if trigger has been defined on any other SYNONYMs ( other than APPS synonym)
541 l_index := 0;
542 log(c_module, 'STATEMENT','Checking if it has been defined on a table-synonym' );
543 for syn_rec in c_tab_syn (x_table_owner, x_table_name) loop
544 l_synonym_name := syn_rec.synonym_name;
545 l_reg_exp_pattern_3 := '[[:space:]]ON[[:space:]]*("?[A-Z_0-9]*"?)[.]?"?' || l_synonym_name ||'"?[[:space:]]';
546 l_index := regexp_instr(l_ddl_lob, l_reg_exp_pattern_3, 1,1,0, 'i' );
547 exit when l_index > 0;
548 end loop;
549
550 if(l_index > 0 ) then
551 log(c_module, 'STATEMENT',
552 'Found match, Trigger has been defined on table-synonym : ->' ||
553 l_index || ' Pattern->' || l_reg_exp_pattern_3 );
554 l_ddl_lob := regexp_replace(l_ddl_lob,
555 l_reg_exp_pattern_3,
556 l_reg_exp_replace_str,
557 l_index, /* from that position */
558 1, /* first occurrence */
559 'i' /* case-insensitive */
560 );
561 end if;
562 end if; --- end of MATCH#3
563 end if; --- end of MATCH#2_1
564 end if; -- end of MATCH#2
565 end if; -- end of MATCH#1
566
567 -- Drop trigger
568 exec(x_sql => 'DROP TRIGGER "' || x_trigger_owner || '"."' || x_trigger_name || '"',
569 x_log_mod => c_module,
570 x_ignore => false);
571
572 end if; -- end of if [ i =1 ]
573
574 exec(x_sql => l_ddl_lob, x_log_mod => c_module, x_ignore => false);
575 end loop;
576 end if;
577
578 log(c_module, 'PROCEDURE', 'end');
579 exception
580 when others then
581 raise;
582 end MOVE_TRIGGER;
583
584
585 --
586 -- Move triggers from Table to EV.
587 --
588 -- So: [ TRIGGERS ] --> [ SYNONYM ] --> [ EV ]---> [ TABLE ]
589 --
590 -- Steps
591 -- 1- Drop Trigger
592 -- 2- Recreate Trigger with same definition on top of synonym (EV)
593 --
594 procedure MOVE_TRIGGERS(
595 X_TABLE_OWNER varchar2,
596 X_TABLE_NAME varchar2,
597 X_EV_NAME varchar2)
598 is
599 C_MODULE varchar2(127) := c_package||'move_triggers';
600 L_VALID_STATUS varchar2(10);
601 L_EV_NAME varchar2(30);
602 L_SYNONYM_NAME varchar2(30);
603 L_SYNONYM_OWNER varchar2(30);
604
605 -- triggers defined on a table
606 CURSOR c_trg is
607 select owner, trigger_name, trigger_type, status
608 from dba_triggers
609 where table_owner in
610 ( select oracle_username from system.fnd_oracle_userid
611 where read_only_flag in ('A','B', 'E', 'U', 'C') )
612 -- EXCLUDE: trigger generated by the Oracle Text Indexing
613 and trigger_name not like 'DR$%'
614 -- EXCLUDE: Editioned Data Storage Maintenance Trigger Name
615 and trigger_name <> ad_zd_seed.eds_trigger(x_table_name)
616 and trigger_name <> ad_zd_seed.eds_fcet(x_table_name)
617 -- EXCLUDE: cross edition triggers.
618 and crossedition = 'NO'
619 and table_owner = x_table_owner
620 and (
621 (table_name = x_table_name and base_object_type='TABLE')
622 or
623 (table_name=ev_view(x_table_name) and base_object_type='VIEW')
624 )
625 and owner in
626 (select oracle_username from system.fnd_oracle_userid
627 where read_only_flag in ('A','B', 'E', 'U', 'C') );
628
629 begin
630 log(C_MODULE, 'PROCEDURE', 'begin: '||x_table_owner||'.'||x_table_name);
631
632 l_ev_name := nvl(X_EV_NAME, ev_view(X_TABLE_NAME));
633
634 -- Get APPS synonym of that table or synonym pointing to table EV
635 begin
636 select syn.owner, syn.synonym_name
637 into l_synonym_owner, l_synonym_name
638 from dba_synonyms syn
639 where syn.owner = ad_zd.apps_schema
640 and syn.table_owner = x_table_owner
641 and syn.table_name = l_ev_name
642 and rownum < 2;
643 exception
644 when no_data_found then
645 -- internal error, should probably fail here
646 log(c_module, 'ERROR', 'APPS synonym not found for table '||x_table_owner||'.'||x_table_name);
647 l_synonym_owner := x_table_owner;
648 l_synonym_name := x_table_name;
649 end;
650
651 begin
652 for trg_rec in c_trg loop
653
654 -- Bug 13597311. Adding valid state to decide
655 -- whether to force recompile a trigger or not.
656 -- This can be also done in CURSOR c_trg by joining
657 -- dba_objects. At present there is a huge performance
658 -- drop in the resultant query. Taking suggestion from
659 -- perf team.
660 -- TODO: If perf team can suggest a single query, it would
661 -- help optimize further.
662
663 select status
664 into l_valid_status
665 from dba_objects
666 where owner = trg_rec.owner
667 and object_name = trg_rec.trigger_name
668 and object_type = 'TRIGGER';
669
670 move_trigger(
671 trg_rec.owner,
672 trg_rec.trigger_name,
673 trg_rec.status,
674 l_valid_status,
675 x_table_owner,
676 x_table_name,
677 l_synonym_owner,
678 l_synonym_name);
679 end loop;
680 exception
681 when others then
682 raise;
683 end;
684 log(C_MODULE, 'PROCEDURE', 'end');
685 END MOVE_TRIGGERS;
686
687 --
688 -- Moves VPD policies from table to EV
689 --
690 procedure MOVE_VPD_POLICIES(
691 X_TABLE_OWNER in varchar2,
692 X_TABLE_NAME in varchar2,
693 X_EV_NAME in varchar2 )
694 is
695 c_module varchar2(127) := c_package||'move_vpd_policies';
696
697 l_object_owner varchar2(30);
698 l_object_name varchar2(30);
699 l_policy_group varchar2(30);
700 l_policy_name varchar2(30);
701 l_pf_owner varchar2(30);
702 l_package varchar2(30);
703 l_function varchar2(65);
704 l_sel varchar2(3);
705 l_ins varchar2(3);
706 l_upd varchar2(3);
707 l_del varchar2(3);
708 l_idx varchar2(3);
709
710 l_policy_type varchar2(24);
711 l_chk_option varchar2(5) ;
712 l_enable varchar2(5);
713 l_static_policy varchar2(5);
714 l_long_predicate varchar2(5);
715
716
717 -- variables to store 'true' or 'false' boolean value as string
718 l_b_chk_option varchar2(5) ;
719 l_b_enable varchar2(5);
720 l_b_static_policy varchar2(5);
721 l_b_long_predicate varchar2(5);
722
723
724 l_stmt_types varchar2(64);
725 l_first BOOLEAN := true;
726 l_sql varchar2(32767) ;
727 l_ev_name varchar2(30);
728
729 cursor C_VPD (X_OWNER varchar2, X_NAME varchar2)
730 is
731 select object_owner ,
732 object_name ,
733 policy_group ,
734 policy_name ,
735 pf_owner ,
736 package ,
737 function ,
738 sel ,
739 ins ,
740 upd ,
741 del ,
742 idx ,
743 -- chk_option ,
744 decode(chk_option, 'YES', 'true', 'false'),
745 --enable ,
746 decode(enable, 'YES', 'true', 'false'),
747 --static_policy ,
748 decode(static_policy ,'YES', 'true', 'false'),
749 policy_type ,
750 --long_predicate
751 decode(long_predicate, 'YES', 'true', 'false')
752 from dba_policies
753 where object_owner = x_owner
754 and object_name = x_name
755 and policy_name = UPPER(policy_name) -- EXCLUDE: internal polciy, if any.
756 and lower(policy_name) <> 'ad_zd_seed'; -- EXCLUDE: AD_ZD_SEED policies .
757
758 begin
759 log(c_module, 'PROCEDURE', 'begin: '||x_table_owner||'.'||x_table_name);
760 begin
761 l_ev_name := nvl(X_EV_NAME, ev_view(X_TABLE_NAME));
762 open c_vpd(x_table_owner, x_table_name);
763 fetch c_vpd INTO
764 l_object_owner ,
765 l_object_name ,
766 l_policy_group ,
767 l_policy_name ,
768 l_pf_owner ,
769 l_package ,
770 l_function ,
771 l_sel ,
772 l_ins ,
773 l_upd ,
774 l_del ,
775 l_idx ,
776 l_chk_option ,
777 l_enable ,
778 l_static_policy ,
779 l_policy_type ,
780 l_long_predicate ;
781
782 while (c_vpd%found) loop
783 log(c_module, 'EVENT', 'Drop VPD policy: ' ||l_policy_name||' from '||x_table_owner||'.'||x_table_name );
784 -- If here, policy exist for this table
785 -- Drop from table
786 l_sql := 'BEGIN DBMS_RLS.DROP_POLICY ( ' ||
787 ' object_schema =>''' || X_TABLE_OWNER || ''', ' ||
788 ' object_name => ''' || X_TABLE_NAME || ''', ' ||
789 ' policy_name => ''' || l_policy_name || '''); END; ' ;
790 -- Drop policy from table
791 exec(l_sql, c_module);
792
793 -- SELECT
794 if( l_sel = 'YES' ) then
795 l_stmt_types := 'SELECT ';
796 l_first := false;
797 end if;
798
799 -- INSERT
800 if( l_ins = 'YES' ) then
801 if l_first then
802 l_stmt_types := 'INSERT';
803 l_first := false;
804 else
805 l_stmt_types := l_stmt_types || ', INSERT';
806 END if;
807 end if;
808
809 -- UPDATE
810 if( l_upd = 'YES' ) then
811 if l_first then
812 l_stmt_types := 'UPDATE';
813 l_first := false;
814 else
815 l_stmt_types := l_stmt_types || ', UPDATE';
816 end if;
817 end if;
818
819 -- DELETE
820 if( l_del = 'YES' ) then
821 if l_first then
822 l_stmt_types := 'DELETE';
823 l_first := false;
824 else
825 l_stmt_types := l_stmt_types || ', DELETE';
826 end if;
827 end if;
828
829 -- INDEX
830 if( l_idx = 'YES' ) then
831 if l_first then
832 l_stmt_types := 'INDEX';
833 l_first := false;
834 else
835 l_stmt_types := l_stmt_types || ', INDEX';
836 end if;
837 end if;
838
839 if(l_package is not null ) then
840 -- "pkg_name"."fun_name"
841 l_function := l_package || '.' || l_function ;
842 end if;
843
844 if(l_policy_type is not null ) then
845 l_policy_type := 'DBMS_RLS.' || l_policy_type;
846 end if;
847
848 -- ReAssign VPD to EV
849 l_sql := 'BEGIN DBMS_RLS.ADD_GROUPED_POLICY( ' ||
850 ' object_schema=>''' || x_table_owner || ''', ' ||
851 ' object_name=>''' || l_ev_name || ''', ' ||
852 ' policy_group=>''' || l_policy_group || ''', ' ||
853 ' policy_name=>''' || l_policy_name || ''', ' ||
854 ' function_schema=>''' || l_pf_owner || ''', ' ||
855 ' policy_function=>''' || l_function || ''', ' ||
856 ' statement_types=>''' || l_stmt_types || ''', ' || /* statement_types =>'SELECT,INDEX, INSERT, UPDATE, DELETE.*/
857 ' update_check=>' || l_chk_option || ', ' || /* BOOLEAN value, so extra single quote required otherwise that would become
858 string */
859 ' enable=>' || l_enable || ', ' || /* BOOLEAN */
860 ' static_policy=>' || l_static_policy || ', ' || /* BOOLEAN */
861 ' policy_type=>' || l_policy_type || ', ' || /* integer VALUE */
862 ' long_predicate=>' || l_long_predicate || ', ' || /* BOOLEAN */
863 ' sec_relevant_cols=>NULL, ' ||
864 ' sec_relevant_cols_opt=>NULL); END; ' ;
865
866 log(c_module, 'EVENT', 'Add VPD policy: ' ||l_policy_name||' to '||x_table_owner||'.'||l_ev_name );
867 exec(l_sql, c_module);
868
869 fetch c_vpd INTO
870 l_object_owner ,
871 l_object_name ,
872 l_policy_group ,
873 l_policy_name ,
874 l_pf_owner ,
875 l_package ,
876 l_function ,
877 l_sel ,
878 l_ins ,
879 l_upd ,
880 l_del ,
881 l_idx ,
882 l_chk_option ,
883 l_enable ,
884 l_static_policy ,
885 l_policy_type ,
886 l_long_predicate ;
887
888 end loop;
889
890 if c_vpd%isopen then -- cursor was not already closed
891 close c_vpd;
892 end if;
893
894 exception
895 when others then
896 if c_vpd%isopen then -- cursor was not already closed
897 close c_vpd;
898 end if;
899
900 log(c_module, 'ERROR', substr(sqlerrm, 1, 2000));
901 raise;
902 end;
903 log(c_module, 'PROCEDURE', 'end');
904 END MOVE_VPD_POLICIES ;
905
906 --
907 -- Drops public synonyms and re-create corresponding synonyms in dependent
908 -- schema.
909 --
910 procedure FIX_PUBLIC_SYNONYM (x_table_owner varchar2,
911 x_table_name varchar2,
912 x_synonym_name varchar2,
913 x_ev_name varchar2)
914 is
915 c_module varchar2(127) := c_package||'fix_public_synonym';
916 l_sql varchar2(1024);
917
918 -- MV tables already will be excluded by AD_ZD_TABLE.UPGRADE API
919 -- Check recursive dependency as well:
920 -- e.g: View --> synonym --> public-synonym
921 --
922 cursor c_dependents (p_synonym_name varchar2) is
923 select distinct d.owner --, d1.name, d1.type
924 from dba_dependencies d
925 where d.owner in ( select oracle_username
926 from system.fnd_oracle_userid
927 where read_only_flag in ('A','B', 'E', 'U', 'C' )
928 )
929 and d.referenced_type = 'SYNONYM'
930 and d.referenced_owner = 'PUBLIC'
931 and d.referenced_name = p_synonym_name
932 and not exists ( select 1
933 from dba_synonyms
934 where owner = d.owner
935 and synonym_name= p_synonym_name
936 and table_owner = x_table_owner
937 and table_name in (x_table_name, x_ev_name)
938 );
939
940 begin
941
942 log(c_module, 'PROCEDURE',
943 'begin: '||x_table_owner||'.'||x_table_name||', '||x_synonym_name);
944
945 for dependent in c_dependents ( x_synonym_name) loop
946
947 l_sql := 'CREATE SYNONYM "' || dependent.owner|| '"."' || x_synonym_name ||
948 '" FOR "'|| x_table_owner || '"."' || nvl(x_ev_name, ev_view(x_table_name)) || '"' ;
949
950 log(c_module, 'EVENT', 'Create Synonym: "' || dependent.owner || '"."' || x_synonym_name ||'"') ;
951 exec(l_sql, c_module, false);
952 end loop;
953
954 -- drop public synonym
955 l_sql := 'DROP PUBLIC SYNONYM "' || x_synonym_name || '" FORCE' ;
956 log(c_module, 'EVENT', 'Drop Public Synonym: "' || x_synonym_name ||'"');
957 exec (l_sql, c_module, true);
958
959 log(c_module, 'PROCEDURE', 'end');
960 end FIX_PUBLIC_SYNONYM;
961
962 --
963 -- Drops public synonyms and re-create corresponding synonyms in dependent
964 -- schema.
965 --
966 procedure FIX_PUBLIC_SYNONYMS(x_table_owner varchar2,
967 x_table_name varchar2,
968 x_ev_name varchar2)
969 is
970 c_module varchar2(127) := c_package||'fix_public_synonyms';
971
972 cursor c_pub_syn is
973 select syn.synonym_name
974 from dba_synonyms syn,
975 DBA_TABLES tab
976 where syn.owner='PUBLIC'
977 and syn.table_owner in ( select oracle_username
978 from system.fnd_oracle_userid
979 where read_only_flag in ('E', 'A', 'B', 'C')
980 )
981 and syn.table_owner = x_table_owner
982 and syn.table_name = x_table_name
983 and tab.owner = syn.table_owner
984 and tab.table_name = syn.table_name;
985
986
987 begin
988 log(c_module, 'PROCEDURE', 'begin: '||x_table_owner||'.'||x_table_name);
989
990 for pub_syn in c_pub_syn loop
991 fix_public_synonym(x_table_owner, x_table_name, pub_syn.synonym_name, x_ev_name);
992 end loop;
993
994 log(c_module, 'PROCEDURE', 'end');
995 end FIX_PUBLIC_SYNONYMS;
996
997
998 --
999 -- Generate Editioning View
1000 --
1001 -- Generates an Editioning View for the specified table.
1002 -- Table columns have names with the following structure
1003 --
1004 -- <logical_name>[#<version>]
1005 --
1006 -- The generated editioning view will map each logical column name
1007 -- to the latest version table column for that logical name.
1008 --
1009 procedure GENERATE_EV(
1010 X_TABLE_OWNER varchar2,
1011 X_TABLE_NAME varchar2 )
1012 is
1013 C_MODULE varchar2(127) := c_package||'generate_ev';
1014 L_EV_NAME varchar2(30);
1015 L_EV_STMT varchar2(32676);
1016 L_EV_LOB_STMT clob;
1017 L_EV_FIRST boolean;
1018
1019 cursor C_EV_COLUMNS(X_TABLE_OWNER varchar2, X_TABLE_NAME varchar2) is
1020 select
1021 ad_zd_table.ev_view_column(col.column_name) view_column_name
1022 , max(col.column_name) table_column_name
1023 , min(nvl(evc.view_column_id, 1000+col.column_id)) view_column_id
1024 from dba_tab_columns col,
1025 dba_editioning_view_cols evc
1026 where col.owner = x_table_owner
1027 and col.table_name = x_table_name
1028 and evc.owner(+) = col.owner
1029 and evc.view_name(+) = ad_zd_table.ev_view(col.table_name)
1030 and evc.view_column_name(+) = ad_zd_table.ev_view_column(col.column_name)
1031 group by ad_zd_table.ev_view_column(col.column_name)
1032 order by view_column_id;
1033
1034 begin
1035
1036 -- set up EV creation statement
1037 l_ev_name := ev_view(x_table_name);
1038 l_ev_stmt := 'create or replace editioning view "'||
1039 x_table_owner||'"."'||l_ev_name||'" as select ';
1040
1041 log(c_module, 'EVENT', 'Generate EV '||x_table_owner||'.'||l_ev_name);
1042
1043 begin
1044 -- Loop thru each EV column
1045 l_ev_first := true;
1046 for evcrec in c_ev_columns(x_table_owner, x_table_name) loop
1047
1048 -- add initial statement or separater as needed
1049 if l_ev_first then
1050 l_ev_first := false;
1051 dbms_lob.createtemporary (l_ev_lob_stmt, false, DBMS_LOB.CALL);
1052 dbms_lob.writeappend(lob_loc => l_ev_lob_stmt,
1053 amount => length(l_ev_stmt),
1054 buffer => l_ev_stmt);
1055 else
1056 l_ev_stmt := ', ';
1057 dbms_lob.writeappend(lob_loc => l_ev_lob_stmt,
1058 amount => length(l_ev_stmt),
1059 buffer => l_ev_stmt);
1060 end if;
1061
1062 -- add column mapping to EV creation statement
1063 l_ev_stmt := evcrec.table_column_name||' '||evcrec.view_column_name;
1064 dbms_lob.writeappend(lob_loc => l_ev_lob_stmt,
1065 amount => length(l_ev_stmt),
1066 buffer => l_ev_stmt);
1067
1068 end loop;
1069
1070 -- complete the EV creation statement and execute
1071 if (l_ev_lob_stmt is not null and dbms_lob.getlength(l_ev_lob_stmt) > 0 ) then
1072
1073 l_ev_stmt := ' from "'||x_table_owner||'"."'||x_table_name||'"';
1074 dbms_lob.writeappend(lob_loc => l_ev_lob_stmt,
1075 amount => length(l_ev_stmt),
1076 buffer => l_ev_stmt);
1077
1078 -- Immediate execute. [patching case]
1079 exec(l_ev_lob_stmt, c_module);
1080 end if; -- END : If l_ev_lob_stmt is not null and dbms_lob.getlength(l_ev_lob_stmt) > 0
1081
1082 if (dbms_lob.isTemporary(l_ev_lob_stmt)=1) then
1083 dbms_lob.freeTemporary(l_ev_lob_stmt);
1084 end if;
1085
1086 exception
1087 when others then
1088 if (dbms_lob.isTemporary(l_ev_lob_stmt)=1) then
1089 dbms_lob.freeTemporary(l_ev_lob_stmt);
1090 end if;
1091 log(c_module, 'ERROR',
1092 x_table_owner||'.'||x_table_name || ': ' || substr(sqlerrm, 1, 2000));
1093 raise;
1094 end;
1095
1096 end GENERATE_EV;
1097
1098
1099 --
1100 -- Install Editioning View
1101 --
1102 -- Only needed for first time installation of an EV.
1103 -- - moves table-level VPD policies to editioning view
1104 -- - copies table-level grants to editioning view
1105 -- - Points table synonyms to editioning view
1106 --
1107 -- Note: assumes EV is already generated
1108 --
1109 procedure INSTALL_EV(
1110 X_TABLE_OWNER varchar2,
1111 X_TABLE_NAME varchar2 )
1112 is
1113 C_MODULE varchar2(80) := c_package||'install_ev';
1114 C_APPS_SCHEMA varchar2(30) := ad_zd.apps_schema;
1115 L_EV_NAME varchar2(30);
1116 L_SYN_OWNER varchar2(30);
1117 L_SYN_NAME varchar2(30);
1118 L_STMT varchar2(32000);
1119 L_SYN_EXISTS boolean;
1120
1121 cursor C_SYNONYMS(x_table_owner varchar2, x_table_name varchar2, x_ev_name varchar2) is
1122 select
1123 syn.owner owner
1124 , syn.synonym_name synonym_name
1125 , syn.table_name table_name
1126 from dba_synonyms syn
1127 where syn.table_owner = x_table_owner
1128 and syn.table_name in (x_table_name, x_ev_name)
1129 and syn.owner in
1130 ( select oracle_username
1131 from system.fnd_oracle_userid
1132 where read_only_flag in ('A','B', 'E', 'U', 'C') );
1133
1134 begin
1135 log(c_module, 'PROCEDURE', 'begin: '||X_TABLE_OWNER||'.'||X_TABLE_NAME);
1136
1137 l_ev_name := ev_view(x_table_name);
1138
1139 -- loop through synonyms for table/EV
1140 l_syn_exists := false;
1141 for synrec in c_synonyms(x_table_owner, x_table_name, l_ev_name) loop
1142 l_syn_exists := true;
1143
1144 -- Point table synonyms to EV
1145 if synrec.table_name <> l_ev_name then
1146 l_stmt := 'create or replace synonym "'||synrec.owner||'"."'||
1147 synrec.synonym_name||'" for "'||x_table_owner||'"."' || l_ev_name ||'"' ;
1148
1149 log(c_module, 'EVENT',
1150 'Point Synonym "'||synrec.owner||'"."'||synrec.synonym_name||'" to EV');
1151 exec(l_stmt, c_module);
1152 end if;
1153 end loop;
1154
1155 -- Move VPD policies to EV
1156 move_vpd_policies(x_table_owner, x_table_name, l_ev_name);
1157 -- Copy Table Grants to EV
1158 copy_grants(x_table_owner, x_table_name, l_ev_name);
1159
1160 -- Create a synonym in APPS schema if there are none.
1161 -- This is for new table installation.
1162 if not l_syn_exists then
1163 l_stmt := 'create synonym "'||c_apps_schema|| '"."' ||x_table_name||
1164 '" for "'||x_table_owner||'"."'||l_ev_name||'"';
1165 log(c_module, 'EVENT',
1166 'Create Synonym '||c_apps_schema||'.'||x_table_name||' to EV');
1167 exec(l_stmt, c_module, true);
1168 end if;
1169
1170 log(c_module, 'PROCEDURE', 'end');
1171 end INSTALL_EV;
1172
1173
1174
1175
1176 --
1177 -- Upgrade Effectively Editioned Table
1178 -- - Generate and Install Editioning View
1179 -- - Fix table synonyms to point to EV
1180 -- - Move triggers to EV
1181 --
1182 procedure UPGRADE(
1183 X_TABLE_OWNER in varchar2,
1184 X_TABLE_NAME in varchar2 )
1185 is
1186 C_MODULE varchar2(80) := c_package||'upgrade';
1187 L_TABLE_OWNER varchar2(30);
1188 L_TABLE_NAME varchar2(30);
1189
1190 begin
1191 log(c_module, 'PROCEDURE', 'begin: '||x_table_owner||'.'||x_table_name);
1192
1193 -- Verify table exists
1194 begin
1195 select tab.owner, tab.table_name
1196 into l_table_owner, l_table_name
1197 from dba_tables tab
1198 where tab.owner = x_table_owner
1199 and tab.table_name = x_table_name;
1200 exception
1201 when no_data_found then
1202 error(c_module, 'Table '||nvl(x_table_owner,'<null>')||'.'||nvl(x_table_name,'<null>')||' does not exist.');
1203 end;
1204
1205 -- Generate Editioning View
1206 generate_ev(x_table_owner, x_table_name);
1207
1208 -- Install Editioning View (handles synonyms, VPD policies)
1209 install_ev(x_table_owner, x_table_name);
1210
1211 -- Move triggers to SYNONYM from table
1212 move_triggers(x_table_owner, x_table_name, ad_zd_table.ev_view(x_table_name));
1213
1214 -- cleanup PUBLIC synonyms
1215 fix_public_synonyms(x_table_owner, x_table_name, ad_zd_table.ev_view(x_table_name));
1216
1217 -- upgrade seed data tables
1218 if is_seed(x_table_owner, x_table_name) = 'Y' then
1219 ad_zd_seed.upgrade(x_table_name);
1220 end if;
1221
1222 commit;
1223 log(c_module, 'PROCEDURE', 'end '||x_table_owner ||'.'|| x_table_name);
1224 end UPGRADE;
1225
1226
1227
1228 --
1229 -- Upgrade all developer-managed EBS tables with an editioning view
1230 --
1231 -- Note: we maintain an explicit list of known application-managed
1232 -- tables that are excluded from upgrade (the regexp_like section).
1233 -- This application-managed table name patterns must stay in synch
1234 -- with what is documented in the Database Object Development .
1235 --
1236 procedure UPGRADE_DB
1237 is
1238
1239 C_MODULE varchar2(127) := c_package||'upgrade_db';
1240
1241 -- EBS Tables that need EVs
1242 -- - owned by EBS product schema
1243 -- - not a known DB internal table pattern
1244 -- - not a known application managed table pattern
1245 -- - not an AD internal table
1246 -- - not a Queue Table
1247 -- - not a Materialized View Container Table
1248 -- - has an APPS synonym
1249 cursor C_UPGRADE_TABLES is
1250 select
1251 tab.owner table_owner
1252 , tab.table_name table_name
1253 from dba_tables tab
1254 where tab.owner in
1255 ( select oracle_username from system.fnd_oracle_userid
1256 where read_only_flag in ('A','E','B') )
1257 and tab.temporary = 'N'
1258 and tab.secondary = 'N'
1259 /* not an application-managed dynamic table */
1260 and not regexp_like(tab.table_name, '^AQ\$', 'c')
1261 and not regexp_like(tab.table_name, '^AW\$', 'c')
1262 and not regexp_like(tab.table_name, '^MLOG\$', 'c')
1263 and not regexp_like(tab.table_name, '^BSC_DI_[0-9_]+$', 'c')
1264 and not regexp_like(tab.table_name, '^BSC_D_.+$', 'c')
1265 and not regexp_like(tab.table_name, '^FA_ARCHIVE_ADJUSTMENT_.+$', 'c')
1266 and not regexp_like(tab.table_name, '^FA_ARCHIVE_DETAIL_.+$', 'c')
1267 and not regexp_like(tab.table_name, '^FA_ARCHIVE_SUMMARY_.+$', 'c')
1268 and not regexp_like(tab.table_name, '^GL_DAILY_POST_INT_.+$', 'c')
1269 and not regexp_like(tab.table_name, '^GL_INTERCO_BSV_INT_[0-9]+$', 'c')
1270 and not regexp_like(tab.table_name, '^GL_MOVEMERGE_BAL_[0-9]+$', 'c')
1271 and not regexp_like(tab.table_name, '^GL_MOVEMERGE_INTERIM_[0-9]+$', 'c')
1272 and not regexp_like(tab.table_name, '^XLA_GLT_[0-9]+$', 'c')
1273 and not regexp_like(tab.table_name, '^ICX_POR_C[0-9]+.*$', 'c')
1274 and not regexp_like(tab.table_name, '^ICX_POR_UPLOAD_[0-9]+.*$', 'c')
1275 and not regexp_like(tab.table_name, '^IGI_SLS_[0-9]+$', 'c')
1276 and not regexp_like(tab.table_name, '^JTF_TAE_[0-9]+.*$', 'c')
1277 and not regexp_like(tab.table_name, '^JTY_[0-9]+_.*$', 'c')
1278 and not regexp_like(tab.table_name, '^ZPBDATA[0-9]+_EXCPT_T$', 'c')
1279 and not regexp_like(tab.table_name, '^ZX_DATA_UPLOAD_.*$', 'c')
1280 /* not an AD infrastructure table table */
1281 and tab.table_name not in
1282 ( 'AD_DEFERRED_JOBS',
1283 'AD_TABLE_INDEX_INFO',
1284 'FND_INSTALL_PROCESSES' )
1285 and not exists /* not a queue table */
1286 ( select qt.owner, qt.queue_table
1287 from dba_queue_tables qt
1288 where qt.owner = tab.owner
1289 and qt.queue_table = tab.table_name )
1290 and not exists /* not an MV container table */
1291 ( select mv.owner, mv.container_name
1292 from dba_mviews mv
1293 where mv.owner = tab.owner
1294 and mv.container_name = tab.table_name )
1295 and exists /* has apps synonym to base table */
1296 ( select syn.table_owner, syn.table_name
1297 from dba_synonyms syn
1298 where syn.table_owner = tab.owner
1299 and syn.table_name = tab.table_name
1300 and syn.owner = ad_zd.apps_schema )
1301 and not exists /* not an obsolete table */
1302 ( select
1303 fou.oracle_username owner
1304 , aoo.object_name object_name
1305 from
1306 system.fnd_oracle_userid fou
1307 , fnd_product_installations fpi
1308 , ad_obsolete_objects aoo
1309 where fpi.application_id = aoo.application_id
1310 and fou.oracle_id = fpi.oracle_id
1311 and fou.oracle_username = tab.owner
1312 and aoo.object_name = tab.table_name
1313 and aoo.object_type = 'TABLE' )
1314 order by tab.owner, tab.table_name;
1315
1316 -- EV Tables that are not fully upgraded
1317 -- - synonyms that point to base table
1318 -- - triggers on base table
1319 -- - TODO: vpd policies on base table
1320 cursor C_REUPGRADE_TABLES is
1321 select ev.owner table_owner, ev.table_name
1322 from dba_editioning_views ev
1323 where ev.owner in
1324 ( select oracle_username from system.fnd_oracle_userid
1325 where read_only_flag in ('A','E','B') )
1326 and ( exists /* uncoverted synonyms */
1327 ( select syn.synonym_name
1328 from dba_synonyms syn
1329 where syn.owner in
1330 ( select oracle_username from system.fnd_oracle_userid
1331 where read_only_flag in ('A', 'B', 'C', 'E', 'U') )
1332 and syn.table_owner = ev.owner
1333 and syn.table_name = ev.table_name ) or
1334 exists /* unmoved triggers */
1335 ( select trg.trigger_name
1336 from dba_triggers trg
1337 where trg.owner in
1338 ( select oracle_username from system.fnd_oracle_userid
1339 where read_only_flag in ('A', 'B', 'C', 'E', 'U') )
1340 and trg.trigger_name not like '%$%' /* system trigger */
1341 and trg.crossedition = 'NO'
1342 and trg.table_owner = ev.owner
1343 and trg.table_name = ev.table_name )
1344 )
1345 order by table_owner, table_name;
1346
1347 begin
1348 log(c_module, 'PROCEDURE', 'begin: no parameter(s)' );
1349
1350 -- Process tables that need upgrade
1351 for tab_rec in c_upgrade_tables loop
1352 log(c_module, 'STATEMENT', 'Store Upgrade action for table '||tab_rec.table_name );
1353 ad_zd_parallel_exec.load(
1354 x_phase => ad_zd_parallel_exec.c_phase_upgrade_table,
1355 x_sql => 'begin ad_zd_table.upgrade('''||tab_rec.table_owner ||''', '''|| tab_rec.table_name || '''); end;' ,
1356 x_unique => false );
1357 end loop;
1358
1359 -- Process tables that need re-upgrade.
1360 for tab_rec in c_reupgrade_tables loop
1361 log(c_module, 'STATEMENT', 'Store Re-Upgrade action for table '||tab_rec.table_name );
1362 ad_zd_parallel_exec.load(
1363 x_phase => ad_zd_parallel_exec.c_phase_upgrade_table,
1364 x_sql => 'begin ad_zd_table.upgrade('''||tab_rec.table_owner ||''', '''|| tab_rec.table_name || '''); end;' ,
1365 x_unique => false);
1366
1367 end loop;
1368
1369 log(c_module, 'PROCEDURE', 'end' );
1370
1371 end UPGRADE_DB;
1372
1373
1374 /*
1375 ** Downgrade Table (remove EV layer)
1376 **
1377 ** X_TABLE_OWNER / X_TABLE_NAME - the table
1378 ** TODO: move triggers and VPD policies back to Table
1379 */
1380 procedure DOWNGRADE(
1381 X_TABLE_OWNER in varchar2,
1382 X_TABLE_NAME in varchar2)
1383 is
1384 C_MODULE varchar2(80) := c_package||'downgrade';
1385 L_EV_NAME varchar2(30);
1386 L_TABLE_OWNER varchar2(30);
1387 L_TABLE_NAME varchar2(30);
1388
1389 cursor C_SYNONYMS(x_table_owner varchar2, x_table_name varchar2) is
1390 select syn.owner owner, syn.synonym_name name
1391 from dba_synonyms syn
1392 where syn.table_owner = x_table_owner
1393 and syn.table_name = ad_zd_table.ev_view(x_table_name)
1394 and syn.owner <> 'PUBLIC';
1395
1396 begin
1397 log( c_module, 'PROCEDURE', 'begin: '||x_table_owner||'.'||x_table_name);
1398
1399 -- Verify table exists
1400 begin
1401 select tab.owner, tab.table_name
1402 into l_table_owner, l_table_name
1403 from dba_tables tab
1404 where tab.owner = x_table_owner
1405 and tab.table_name = x_table_name;
1406 exception
1407 when no_data_found then
1408 error(c_module, 'Table '||nvl(x_table_owner,'<null>')||'.'||nvl(x_table_name,'<null>')||' does not exist.');
1409 end;
1410
1411 -- Change synonyms
1412 for synrec in c_synonyms(x_table_owner, x_table_name) loop
1413 log(c_module, 'EVENT',
1414 'Point Synonym back to Table: '||synrec.owner||'.'||synrec.name);
1415 exec('create or replace synonym "'||
1416 synrec.owner||'"."'||synrec.name||'" for "'||
1417 x_table_owner||'"."'||x_table_name||'"', c_module);
1418 end loop;
1419
1420 -- Drop EV
1421 l_ev_name := ad_zd_table.ev_view(x_table_name);
1422 log(c_module, 'EVENT', 'Drop EV '||x_table_owner||'.'||l_ev_name);
1423 exec('drop view "'||x_table_owner||'"."'||l_ev_name ||'"', c_module, true);
1424
1425 log( c_module, 'PROCEDURE', 'end');
1426 end DOWNGRADE;
1427
1428
1429 /*
1430 ** --------------------------------------------------------------------
1431 ** Forward Crossedition Trigger Tools
1432 ** --------------------------------------------------------------------
1433 */
1434
1435
1436 --
1437 -- Update table (fake update) in order to apply Crossedition Trigger.
1438 -- If X_EV_NAME is supplied, the update is executed on the EV instead of the table.
1439 -- This is used for seed data synchronization.
1440 --
1441 procedure UPDATE_4FCET(
1442 X_TABLE_OWNER varchar2,
1443 X_TABLE_NAME varchar2,
1444 X_TRIGGER_NAME varchar2,
1445 X_COLUMN_NAME varchar2,
1446 X_EV_NAME varchar2)
1447 is
1448 C_MODULE varchar2(80) := c_package || 'update_4fcet';
1449 L_STMT varchar2(1024);
1450 L_STATUS number;
1451 L_TASK_NAME varchar2(64);
1452 L_ERROR varchar2(32000);
1453
1454 DUPLICATE_TASK_ERROR exception;
1455 pragma exception_init(duplicate_task_error, -29497);
1456
1457 -- Conflict Triggers will block the apply of a forward crossedition trigger
1458 -- - directly on the table
1459 -- - owned by other than the current user
1460 cursor C_CONFLICT_TRIGGERS(X_TABLE_OWNER varchar2, X_TABLE_NAME varchar2) is
1461 select
1462 trg.owner owner
1463 , trg.trigger_name trigger_name
1464 , trg.status status
1465 from
1466 dba_triggers trg
1467 where trg.owner <> user
1468 and trg.table_owner = x_table_owner
1469 and trg.table_name = x_table_name
1470 order by 1, 2;
1471 begin
1472 -- disable any conflict triggers (workaround for DB Bug 13951889)
1473 -- TODO: remember explicit list of disabled triggers, only reenable those
1474 for crec in c_conflict_triggers(x_table_owner, x_table_name) loop
1475 if (crec.status = 'ENABLED') then
1476 log(c_module, 'STATEMENT', 'Disable conflict trigger '||crec.owner||'.'||crec.trigger_name);
1477 exec('alter trigger "'||crec.owner||'"."'||crec.trigger_name||'" disable', c_module);
1478 end if;
1479 end loop;
1480
1481 -- For large table, do parallel update
1482 l_stmt := 'update /*+ rowid (tbl) */ '||
1483 x_table_owner||'.'||nvl(x_ev_name, x_table_name)||' tbl set '||
1484 x_column_name||'='||x_column_name||' where rowid between :start_id and :end_id ';
1485
1486 log(c_module, 'STATEMENT', 'Parallel Update for '||x_trigger_name);
1487 log(c_module, 'STATEMENT', 'SQL: '||l_stmt);
1488
1489 -- create task, handle possible conflicting task
1490 l_task_name := x_trigger_name;
1491 begin
1492 log(c_module, 'STATEMENT', 'Create task: '||l_task_name);
1493 dbms_parallel_execute.create_task(l_task_name);
1494 exception
1495 when duplicate_task_error then
1496 log(c_module, 'STATEMENT', 'Drop duplicate task: '||l_task_name);
1497 dbms_parallel_execute.drop_task(l_task_name);
1498 log(c_module, 'STATEMENT', 'Create task (retry): '||l_task_name);
1499 dbms_parallel_execute.create_task(l_task_name);
1500 end;
1501
1502 dbms_parallel_execute.create_chunks_by_rowid(
1503 l_task_name, x_table_owner, x_table_name, true, 10000);
1504
1505 -- 'apply_crossedition_trigger' parameter is required to make cross edition trigger fire
1506 -- 'parallel_level' of null uses default parallelism.
1507 dbms_parallel_execute.run_task(
1508 l_task_name, l_stmt, dbms_sql.native,
1509 apply_crossedition_trigger=>'"'||x_trigger_name ||'"', parallel_level=>null);
1510
1511 -- If any error, Try again
1512 l_status := dbms_parallel_execute.task_status(l_task_name);
1513 if (l_status = dbms_parallel_execute.processed_with_error or
1514 l_status = dbms_parallel_execute.crashed ) then
1515 log(c_module, 'WARNING', 'Update failed, status='||to_char(l_status)||', Retrying...');
1516 dbms_parallel_execute.resume_task(l_task_name);
1517 end if;
1518
1519 -- If still error, report problem
1520 l_status := dbms_parallel_execute.task_status(l_task_name);
1521 if (l_status = dbms_parallel_execute.processed_with_error or
1522 l_status = dbms_parallel_execute.crashed ) then
1523 error(c_module, 'Update failed, status = '||to_char(l_status));
1524 end if;
1525
1526 log(c_module, 'STATEMENT', 'Successful Parallel Update, dropping task: '||l_task_name);
1527 dbms_parallel_execute.drop_task(l_task_name);
1528
1529 -- re-enable conflict triggers
1530 -- TODO: Enable only those triggers which were DISABLED by this API.
1531 for crec in c_conflict_triggers(x_table_owner, x_table_name) loop
1532 if (crec.status = 'DISABLED') then
1533 log(c_module, 'STATEMENT', 'Re-enable conflict trigger '||crec.owner||'.'||crec.trigger_name);
1534 exec('alter trigger "'||crec.owner||'"."'||crec.trigger_name||'" enable', c_module, true);
1535 end if;
1536 end loop;
1537
1538 exception
1539 when others then
1540 l_error := sqlerrm;
1541 begin
1542 dbms_parallel_execute.drop_task(l_task_name);
1543 exception
1544 when others then null;
1545 end;
1546 error(c_module, l_error);
1547 end update_4fcet;
1548
1549 --
1550 -- Apply Crossedition Trigger
1551 --
1552 -- Enables crossedition trigger, then applies to table
1553 -- If the trigger "follows" other triggers or non-existant
1554 -- triggers, this procedure will recursively enable or create
1555 -- the referenced triggers first, as needed.
1556 --
1557 -- Note: we expect the trigger to be created already.
1558 --
1559 -- Multilple crossedition triggers may be applied on the same table. For
1560 -- performance purposes, only the APPLY call for the last trigger will
1561 -- will be executed, which will fire all triggers on the table in a single
1562 -- pass update.
1563 --
1564 procedure APPLY(X_CET_NAME in varchar2)
1565 is
1566 C_MODULE varchar2(80) := c_package||'apply';
1567 C_CET_OWNER varchar2(30) := ad_zd.apps_schema;
1568 L_STATUS varchar2(8);
1569 L_CROSSEDITION varchar2(8);
1570 L_TABLE_OWNER varchar2(30);
1571 L_TABLE_NAME varchar2(30);
1572 L_UPD_COLUMN varchar2(30);
1573 L_UPD_EV_NAME varchar2(30);
1574 L_UPD_CET_NAME varchar2(30);
1575 L_STMT varchar2(1000);
1576 L_ERROR varchar2(2000);
1577 L_EXISTS varchar2(1);
1578 L_SCN number := null;
1579
1580 -- predecessor triggers must fire before the trigger to be applied
1581 cursor C_PREDECESSORS(X_OWNER varchar2, X_NAME varchar2) is
1582 select
1583 tord.referenced_trigger_owner ref_trg_owner
1584 , tord.referenced_trigger_name ref_trg_name
1585 , rtrg.status trg_status
1586 from
1587 dba_trigger_ordering tord
1588 , dba_triggers rtrg
1589 where tord.trigger_owner = x_owner
1590 and tord.trigger_name = x_name
1591 and tord.ordering_type = 'FOLLOWS'
1592 and rtrg.owner(+) = tord.referenced_trigger_owner
1593 and rtrg.trigger_name(+) = tord.referenced_trigger_name
1594 order by 1, 2;
1595
1596 cursor C_PREDECESSOR_CHAIN(X_OWNER varchar2, X_NAME varchar2) is
1597 select
1598 tord.referenced_trigger_owner ref_trg_owner
1599 , tord.referenced_trigger_name ref_trg_name
1600 from
1601 dba_trigger_ordering tord
1602 start with
1603 tord.trigger_owner = x_owner
1604 and tord.trigger_name = x_name
1605 and tord.ordering_type = 'FOLLOWS'
1606 connect by
1607 tord.trigger_owner = prior tord.referenced_trigger_owner
1608 and tord.trigger_name = prior tord.referenced_trigger_name
1609 and tord.ordering_type = 'FOLLOWS';
1610
1611 begin
1612 log(c_module, 'PROCEDURE', 'begin: '||x_cet_name);
1613
1614 -- Get CET info
1615 begin
1616 select trg.status, trg.crossedition, trg.table_owner, trg.table_name
1617 into l_status, l_crossedition, l_table_owner, l_table_name
1618 from dba_triggers trg
1619 where trg.crossedition in ('FORWARD', 'REVERSE')
1620 and trg.owner = c_cet_owner
1621 and trg.trigger_name = x_cet_name;
1622 exception
1623 when no_data_found then
1624 error(c_module, 'Crossedition Trigger '||c_cet_owner||'.'||x_cet_name||' does not exist');
1625 end;
1626
1627 -- If CET is already enabled, we are done
1628 if l_status = 'ENABLED' then
1629 log(c_module, 'PROCEDURE', 'end - noop');
1630 return;
1631 end if;
1632
1633 log(c_module, 'EVENT', 'Apply Crossedition Trigger: '||c_cet_owner||'.'||x_cet_name);
1634
1635 -- check for missing/disabled predecessor triggers
1636 -- TODO: what about RCET "PRECEDES" ordering_type?
1637 for prerec in c_predecessors(c_cet_owner, x_cet_name) loop
1638 if prerec.trg_status is NULL then
1639
1640 -- missing predecessor trigger, create stub trigger to satisfy dependency
1641 log(c_module, 'STATEMENT', 'Creating Stub CET: '|| c_cet_owner||'.'||prerec.ref_trg_name);
1642
1643 -- use first column of base table as triggering column for stub trigger
1644 -- this is to minimize uncessary firing of the stub
1645 select col.column_name into l_upd_column
1646 from dba_tab_columns col
1647 where col.owner = l_table_owner
1648 and col.table_name = l_table_name
1649 and col.column_id = 1;
1650
1651 -- create stub trigger, enabled
1652 l_stmt :=
1653 'create trigger "'||prerec.ref_trg_owner||'"."'||prerec.ref_trg_name||
1654 '" before insert or update of '||l_upd_column||' on "'||
1655 l_table_owner||'"."'||l_table_name||
1656 '" for each row '||l_crossedition||' crossedition begin null; end; ';
1657
1658 exec(l_stmt, c_module);
1659
1660 elsif prerec.trg_status = 'DISABLED' then
1661
1662 -- disabled predecessor, apply it (recurses to first trigger)
1663 apply(prerec.ref_trg_name);
1664
1665 end if;
1666 end loop;
1667
1668 -- TODO: check for triggering columns, these are NOT supported
1669 -- because column assignments from preceding triggers do not count as
1670 -- triggering column updates for following triggers.
1671
1672 -- Compile/Enable CET
1673 -- TODO: slight risk this is done already by a parallel thread, should check
1674 exec('alter trigger "'||c_cet_owner||'"."'||x_cet_name||'" compile', c_module);
1675 exec('alter trigger "'||c_cet_owner||'"."'||x_cet_name||'" enable', c_module);
1676
1677 -- If this is a reverse crossedition trigger, we are done.
1678 if l_crossedition = 'REVERSE' then
1679 log(c_module, 'PROCEDURE', 'end - reverse');
1680 return;
1681 end if;
1682
1683 -- If this is not the final trigger in FCET firing order, we are done.
1684 begin
1685 select 'Y' into l_exists from dual where exists
1686 ( select tord.trigger_name
1687 from dba_trigger_ordering tord
1688 where tord.referenced_trigger_owner = c_cet_owner
1689 and tord.referenced_trigger_name = x_cet_name
1690 and tord.ordering_type = 'FOLLOWS' );
1691
1692 log(c_module, 'PROCEDURE', 'end: '|| c_cet_owner || '.' || x_cet_name || ' - update deferred');
1693 return;
1694 exception
1695 when no_data_found then null;
1696 end;
1697
1698 -- At this point we are applying the final trigger of the FCET firing chain
1699 -- Execute the table update to apply all FCET triggers in the firing chain
1700
1701 -- Wait on pending DML so that update will not be overwritten by stale data
1702 log(c_module, 'STATEMENT', 'Waiting on pending DML for '||l_table_owner||'.'||l_table_name);
1703 if not dbms_utility.wait_on_pending_dml(l_table_owner||'.'||l_table_name, null, l_scn) then
1704 exec('alter trigger "'||c_cet_owner||'"."'||x_cet_name||'" disable', c_module);
1705 error(c_module, 'Pending transactions block apply of '||x_cet_name);
1706 end if;
1707
1708 -- Get Update Column
1709 -- Any column will do, but should be least-indexed and smallest column for best performance
1710 if (x_cet_name = ad_zd_seed.eds_fcet(l_table_name)) then
1711
1712 -- for seed data sync FCET, update least indexed smallest column in EV
1713 l_upd_ev_name := ad_zd_table.ev_view(l_table_name);
1714 select x.view_column_name into l_upd_column from
1715 ( select evc.view_column_name, count(idc.index_name), col.data_length
1716 from dba_tab_columns col, dba_ind_columns idc, dba_editioning_view_cols evc
1717 where col.owner = l_table_owner
1718 and col.table_name = l_table_name
1719 and evc.owner = col.owner
1720 and evc.view_name = l_upd_ev_name
1721 and evc.table_column_name = col.column_name
1722 and idc.table_owner(+) = col.owner
1723 and idc.table_name(+) = col.table_name
1724 and idc.column_name(+) = col.column_name
1725 group by evc.view_column_name, col.data_length
1726 order by count(idc.index_name), col.data_length ) x
1727 where rownum = 1;
1728
1729 else
1730
1731 -- for ordinary FCET, update least indexed smallest column in base table
1732 l_upd_ev_name := NULL;
1733 select x.column_name into l_upd_column from
1734 ( select col.column_name, count(idc.index_name), col.data_length
1735 from dba_tab_columns col, dba_ind_columns idc
1736 where col.owner = l_table_owner
1737 and col.table_name = l_table_name
1738 and idc.table_owner(+) = col.owner
1739 and idc.table_name(+) = col.table_name
1740 and idc.column_name(+) = col.column_name
1741 group by col.column_name, col.data_length
1742 order by count(idc.index_name), col.data_length ) x
1743 where rownum = 1;
1744
1745 end if;
1746
1747 -- Get Update FCET
1748 -- First in FCET in trigger firing order, will be last returned from this query
1749 l_upd_cet_name := x_cet_name;
1750 for cetrec in c_predecessor_chain(c_cet_owner, x_cet_name) loop
1751 l_upd_cet_name := cetrec.ref_trg_name;
1752 end loop;
1753
1754 -- update table
1755 begin
1756 ad_zd_table.update_4fcet(l_table_owner, l_table_name, l_upd_cet_name, l_upd_column, l_upd_ev_name);
1757 exception
1758 when others then
1759 l_error := substrb(sqlerrm, 1, 2000);
1760 -- disable the trigger
1761 exec('alter trigger "'||c_cet_owner||'"."'||x_cet_name||'" disable', c_module);
1762 error(c_module, 'Could not apply Crossedition Trigger "'||
1763 c_cet_owner||'"."'||x_cet_name||'": '||l_error);
1764 end;
1765
1766 log(c_module, 'PROCEDURE', 'end - update');
1767 end APPLY;
1768
1769
1770
1771
1772 /*===================================================================================
1773 **
1774 ** Index Tools
1775 **
1776 ** ===================================================================================
1777 */
1778
1779 -- Revised Index Name from Original Index Name
1780 function REVISED_INDEX_NAME(X_ORIGINAL_INDEX_NAME in varchar2) return varchar2 is
1781 begin
1782 return translate(x_original_index_name, '_', '~');
1783 end REVISED_INDEX_NAME;
1784
1785 -- Regular expression for Revised Index Names
1786 function REVISED_INDEX_REGEXP return varchar2 is
1787 begin
1788 return '^[A-Z][0-9A-Z~$]*~[0-9A-Z$]*$';
1789 end REVISED_INDEX_REGEXP;
1790
1791 -- Original Index Name from Revised Index Name
1792 function ORIGINAL_INDEX_NAME(X_REVISED_INDEX_NAME in varchar2) return varchar2 is
1793 begin
1794 return translate(x_revised_index_name, '~', '_');
1795 end ORIGINAL_INDEX_NAME;
1796
1797 -- Regular expression for Original Index Names
1798 function ORIGINAL_INDEX_REGEXP return varchar2 is
1799 begin
1800 return '^[A-Z][0-9A-Z_$]*_[0-9A-Z$]*$';
1801 end ORIGINAL_INDEX_REGEXP;
1802
1803
1804 /*
1805 ** Revise Index
1806 **
1807 ** Generates a revised index using the latest revised columns:
1808 ** 1) query the columns for the original index
1809 ** 2) use editioning view to map logical columns to new columns
1810 ** 3) create new index using new columns
1811 **
1812 ** TODO: Constraints
1813 */
1814 procedure REVISE_INDEX(
1815 X_INDEX_OWNER in varchar2,
1816 X_INDEX_NAME in varchar2)
1817 is
1818 C_MODULE varchar2(80) := c_package||'revise_index';
1819 L_REVISED_NAME varchar2(30);
1820 L_TABLE_OWNER varchar2(30);
1821 L_TABLE_NAME varchar2(30);
1822 L_PRE_STMT varchar2(1000);
1823 L_COL_STMT varchar2(30000);
1824 L_POST_STMT varchar2(1000);
1825 L_NEW_COLUMN varchar2(4000);
1826 L_FIRST boolean;
1827
1828 cursor C_INDEX_COLUMNS(x_owner varchar2, x_name varchar2) is
1829 select idc.column_name
1830 from dba_ind_columns idc
1831 where idc.index_owner = x_owner
1832 and idc.index_name = x_name
1833 order by column_position;
1834 begin
1835 log(c_module, 'PROCEDURE', 'begin: '||x_index_owner||'.'||x_index_name);
1836
1837 -- drop existing revised index, if any
1838 begin
1839 select idx.index_name
1840 into l_revised_name
1841 from dba_indexes idx
1842 where idx.owner = x_index_owner
1843 and idx.index_name = ad_zd_table.revised_index_name(x_index_name);
1844
1845 exec('drop index "'||x_index_owner||'"."'||l_revised_name||'"', c_module);
1846 exception
1847 when no_data_found then
1848 null;
1849 end;
1850
1851 -- generate pre/post statement
1852 -- TODO: use DBMS_METADATA
1853 select table_owner, table_name,
1854 'create '||
1855 decode(index_type,
1856 'NORMAL', decode(uniqueness, 'UNIQUE', 'UNIQUE', ''),
1857 index_type)||
1858 ' index "'||owner||'"."'||ad_zd_table.revised_index_name(index_name)||'"'||
1859 ' on "'||table_owner||'"."'||table_name||'"' I_HEADER,
1860 decode(nvl(tablespace_name,'???'),'???','','tablespace '||tablespace_name)||
1861 ' storage (initial '||nvl(initial_extent, 128*1024)/1024||'K '||
1862 'next '||nvl(next_extent, 128*1024)/1024||'K)'
1863 into l_table_owner, l_table_name, l_pre_stmt, l_post_stmt
1864 from dba_indexes
1865 where owner = x_index_owner
1866 and index_name = x_index_name;
1867
1868 -- prepare column clause
1869 l_col_stmt := ' (';
1870 l_first := TRUE;
1871 -- for each old index column...
1872 for icolrec in c_index_columns(x_index_owner, x_index_name) loop
1873 -- get new revised column name
1874 if icolrec.column_name = 'ZD_EDITION_NAME' then
1875 l_new_column := icolrec.column_name;
1876 else
1877 l_new_column :=
1878 ev_table_column(l_table_owner, ev_view(l_table_name), ev_view_column(icolrec.column_name));
1879 end if;
1880
1881 -- add new column to index creation statement
1882 if l_first then
1883 l_first := FALSE;
1884 else
1885 l_col_stmt := l_col_stmt||', ';
1886 end if;
1887 l_col_stmt := l_col_stmt||l_new_column;
1888 end loop;
1889 l_col_stmt := l_col_stmt||') ';
1890
1891 -- Create Revised Index
1892 -- try ONLINE first, then regular mode.
1893 begin
1894 log(c_module, 'EVENT', 'Revise Index: '||x_index_name);
1895
1896 exec(l_pre_stmt||l_col_stmt||l_post_stmt||' online', c_module);
1897 exception
1898 when others then
1899 -- maximum key length exceeded error?
1900 -- try to build the index without the ONLINE keyword
1901 if sqlcode = -1450 then
1902 exec(l_pre_stmt || l_col_stmt || l_post_stmt, c_module);
1903 else
1904 raise;
1905 end if;
1906 end;
1907
1908 log(c_module, 'PROCEDURE', 'end');
1909
1910
1911 end REVISE_INDEX;
1912
1913
1914 /*
1915 ** Revise Indexes for Table
1916 **
1917 ** Locates each out-of-date index for a table, and revises that index
1918 **
1919 ** TODO: Index Orgainized Talbes
1920 */
1921 procedure REVISE_INDEXES(
1922 X_TABLE_OWNER in varchar2,
1923 X_TABLE_NAME in varchar2)
1924 is
1925 C_MODULE varchar2(80) := c_package||'revise_indexes';
1926 L_INDEX_OWNER varchar2(30);
1927 L_INDEX_NAME varchar2(30);
1928
1929 -- Out Of Date indexes for a table
1930 -- A) Original Index with out of date columns and no Revised Index
1931 -- B) Revised Index with out of date columns
1932 cursor c_ood_indexes(x_table_owner varchar2, x_table_name varchar2) is
1933 select idx.owner, idx.index_name, idx.index_type, idx.partitioned
1934 from dba_indexes idx
1935 where idx.owner in
1936 ( select oracle_username from system.fnd_oracle_userid
1937 where read_only_flag in ('A', 'B', 'E', 'U') )
1938 and idx.owner = idx.table_owner
1939 and idx.table_owner = x_table_owner
1940 and idx.table_name = x_table_name
1941 and regexp_like(idx.index_name, ad_zd_table.original_index_regexp, 'c')
1942 and (
1943 (
1944 /* Revised Index does not exist and Original Index is out of date */
1945 not exists
1946 ( select idt.index_name
1947 from dba_indexes idt
1948 where idt.owner = idx.owner
1949 and idt.index_name = ad_zd_table.revised_index_name(idx.index_name) )
1950 and exists
1951 ( select col.column_name
1952 from dba_tab_columns col, dba_ind_columns idc
1953 where col.owner = idx.table_owner
1954 and col.table_name = idx.table_name
1955 and idc.index_owner = idx.owner
1956 and idc.index_name = idx.index_name
1957 and ad_zd_table.ev_view_column(idc.column_name)=ad_zd_table.ev_view_column(col.column_name)
1958 and col.column_name > idc.column_name )
1959 )
1960 or
1961 (
1962 /* Revised Index exists, but is out of date */
1963 exists
1964 ( select idt.index_name
1965 from dba_indexes idt
1966 where idt.owner = idx.owner
1967 and idt.index_name = ad_zd_table.revised_index_name(idx.index_name) )
1968 and exists
1969 ( select col.column_name
1970 from dba_tab_columns col, dba_ind_columns idc
1971 where col.owner = idx.table_owner
1972 and col.table_name = idx.table_name
1973 and idc.index_owner = idx.owner
1974 and idc.index_name = ad_zd_table.revised_index_name(idx.index_name)
1975 and ad_zd_table.ev_view_column(idc.column_name)=ad_zd_table.ev_view_column(col.column_name)
1976 and col.column_name > idc.column_name )
1977 )
1978 )
1979 order by 1, 2;
1980
1981 begin
1982 log(c_module, 'PROCEDURE',
1983 'begin: '||nvl(x_table_owner, 'NULL')||'.'||nvl(x_table_name, 'NULL'));
1984
1985 -- revise each out-of-date index for the table
1986 for idxrec in c_ood_indexes(x_table_owner, x_table_name) loop
1987 if idxrec.index_type = 'IOT - TOP' then
1988 error(c_module, 'Automatic Revision of Index Organized Table not yet implemented: '||idxrec.owner||'.'||idxrec.index_name);
1989 elsif idxrec.index_type = 'FUNCTION-BASED NORMAL' then
1990 error(c_module, 'Automatic Revision of Function-based Index not yet implemented: '||idxrec.owner||'.'||idxrec.index_name);
1991 elsif idxrec.index_type = 'DOMAIN' then
1992 error(c_module, 'Automatic Revision of Domain Index not yet implemented: '||idxrec.owner||'.'||idxrec.index_name);
1993 elsif idxrec.partitioned = 'YES' then
1994 error(c_module, 'Automatic Revision of Partitioned Index not yet implemented: '||idxrec.owner||'.'||idxrec.index_name);
1995 else
1996 revise_index(idxrec.owner, idxrec.index_name);
1997 end if;
1998 end loop;
1999
2000 log(c_module, 'PROCEDURE', 'end');
2001 end REVISE_INDEXES;
2002
2003
2004 /*
2005 ** Cutover to revised Indexes for given table
2006 **
2007 ** X_TABLE_OWNER - table owner filter (match any owner if NULL)
2008 ** X_TABLE_NAME - table name filter (match any table if NULL)
2009 **
2010 ** TODO: Constraints
2011 */
2012 procedure CUTOVER_INDEXES(
2013 X_TABLE_OWNER in varchar2,
2014 X_TABLE_NAME in varchar2)
2015 is
2016 C_MODULE varchar2(80) := c_package||'cutover_indexes';
2017 -- revised indexes
2018 cursor C_REVISED_INDEXES(X_TABLE_OWNER varchar2, X_TABLE_NAME varchar2) is
2019 select
2020 ridx.owner owner
2021 , ridx.index_name revised_index
2022 , ridx.table_owner table_owner
2023 , ridx.table_name table_name
2024 , oidx.index_name original_index
2025 , con.constraint_name constraint_name
2026 from
2027 dba_indexes ridx
2028 , dba_indexes oidx
2029 , dba_constraints con
2030 where ridx.owner in
2031 ( select oracle_username from system.fnd_oracle_userid
2032 where read_only_flag in ('A', 'B', 'E', 'U') )
2033 and ridx.table_owner = x_table_owner
2034 and ridx.table_name = x_table_name
2035 and regexp_like(ridx.index_name, ad_zd_table.revised_index_regexp, 'c')
2036 and oidx.owner(+) = ridx.owner
2037 and oidx.index_name(+) = ad_zd_table.original_index_name(ridx.index_name)
2038 and con.owner(+) = ridx.table_owner
2039 and con.table_name(+) = ridx.table_name
2040 and con.index_owner(+) = ridx.owner
2041 and con.index_name(+) = ad_zd_table.original_index_name(ridx.index_name)
2042 order by 1, 2;
2043
2044 begin
2045 log(c_module, 'PROCEDURE',
2046 'begin: '||nvl(x_table_owner,'NULL')||'.'||nvl(x_table_name,'NULL'));
2047
2048 -- for each new/revised index
2049 for idxrec in c_revised_indexes(x_table_owner, x_table_name) loop
2050
2051 log( c_module,'EVENT', 'Cutover index: '||idxrec.owner||'.'||idxrec.revised_index);
2052
2053 -- drop unique constraint or index, if it exists
2054 if idxrec.constraint_name is not null then
2055 exec('alter table "'||idxrec.table_owner||'"."'||idxrec.table_name||
2056 '" drop constraint "'||idxrec.constraint_name||'" cascade keep index', c_module);
2057 end if;
2058 if idxrec.original_index is not null then
2059 exec('drop index "'||idxrec.owner||'"."'||idxrec.original_index||'"', c_module);
2060 end if;
2061
2062 -- rename the revised index to the original name
2063 exec('alter index "'||idxrec.owner||'"."'||idxrec.revised_index||'" '||
2064 'rename to "'||original_index_name(idxrec.revised_index)||'"', c_module);
2065
2066 end loop;
2067
2068 log( c_module, 'PROCEDURE', 'end');
2069 end CUTOVER_INDEXES;
2070
2071
2072
2073
2074 /*
2075 ** --------------------------------------------------------------------
2076 ** Event Interfaces
2077 ** --------------------------------------------------------------------
2078 */
2079
2080
2081 /*
2082 ** Patch Table
2083 **
2084 ** X_TABLE_OWNER - table owner
2085 ** X_TABLE_NAME - table name
2086 **
2087 ** This procedure must be called after an existing table is patched with
2088 ** - Add column
2089 ** - Create index
2090 */
2091 procedure PATCH(
2092 X_TABLE_OWNER in varchar2,
2093 X_TABLE_NAME in varchar2)
2094 is
2095 C_MODULE varchar2(80) := c_package||'patch';
2096
2097 begin
2098 log(c_module,'EVENT', 'Patch Table: '||x_table_owner||'.'||x_table_name);
2099
2100 -- If there is an EV then do related processing
2101 if ad_zd_table.ev_exists(x_table_owner, x_table_name) = 'Y' then
2102
2103 -- Cannot regenerate EV from RUN edition if PATCH edition exists
2104 if ad_zd.get_edition_type = 'RUN' and ad_zd.get_edition('PATCH') is not null then
2105 error(c_module, 'Cannot PATCH table from Run Edition while Patch Edition exists');
2106 end if;
2107
2108 -- Generate revised editioning view
2109 generate_ev(x_table_owner, x_table_name);
2110
2111 -- For seed data tables, call patch processing in seed data manager
2112 if is_seed(x_table_owner, x_table_name) = 'Y' then
2113 ad_zd_seed.patch(x_table_owner, x_table_name);
2114 end if;
2115
2116 end if;
2117
2118 -- Store table for Finalize/Cutover processing
2119 store(x_table_owner, x_table_name);
2120
2121 end PATCH;
2122
2123
2124 /*
2125 ** Finalize Tables (get ready for cutover)
2126 **
2127 ** Process tables that have been marked as Patched
2128 ** Check for un-applied Forward Crossedition Triggers
2129 ** Revise Out-of-date Indexes
2130 */
2131 procedure FINALIZE is
2132 C_MODULE varchar2(80) := c_package||'finalize';
2133
2134 cursor C_PATCHED_TABLES is
2135 select owner, name, status
2136 from ad_patched_tables
2137 where status in ('N', 'U');
2138 begin
2139 log(c_module,'PROCEDURE', 'begin: no parameter(s)');
2140
2141 -- TODO: check for unapplied (disabled) CETS
2142
2143 for tablerec in c_patched_tables loop
2144 log(c_module, 'STATEMENT', 'Process table '|| tablerec.owner||'.'||tablerec.name);
2145
2146 -- Revise Out-of-date Indexs
2147 revise_indexes(tablerec.owner, tablerec.name);
2148
2149 -- Store cutover action: Check for uniqueness also:22-Aug-2012
2150 if (tablerec.status = 'N') then
2151 ad_zd_parallel_exec.load(
2152 x_phase => ad_zd_parallel_exec.c_phase_cutover,
2153 x_sql => 'begin ad_zd_table.cutover('||
2154 ''''||tablerec.owner||''', '||
2155 ''''||tablerec.name||'''); end;',
2156 x_unique => true);
2157 end if;
2158
2159 -- Mark as Complete
2160 update ad_patched_tables
2161 set status = 'C'
2162 where owner = tablerec.owner
2163 and name = tablerec.name;
2164 commit;
2165
2166 end loop;
2167
2168 log(c_module,'PROCEDURE', 'end');
2169 end FINALIZE;
2170
2171
2172
2173 /*
2174 ** Cutover Table
2175 **
2176 ** X_TABLE_OWNER - table owner filter (match any owner if NULL)
2177 ** X_TABLE_NAME - table name filter (match any table if NULL)
2178 **
2179 ** Cutover revised indexes
2180 ** Set obsolete columns to nullable
2181 ** remove obsolete column constraints
2182 **
2183 ** Note: X_EXECUTE is unused and should be eliminated. Storage of cutover DDL
2184 ** is done in the FINALIZE call.
2185 */
2186 procedure CUTOVER(
2187 X_TABLE_OWNER varchar2,
2188 X_TABLE_NAME varchar2,
2189 X_EXECUTE boolean default true)
2190 is
2191 C_MODULE varchar2(80) := c_package||'cutover';
2192
2193 -- Obsolete columns
2194 cursor C_OBSOLETE_COLUMNS(X_OWNER varchar2, X_TABLE_NAME varchar2) is
2195 select col.owner, col.table_name, col.column_name, col.nullable, col.data_default
2196 from dba_tables tab
2197 , dba_tab_columns col
2198 , dba_editioning_views ev
2199 where tab.owner in
2200 ( select oracle_username
2201 from system.fnd_oracle_userid
2202 where read_only_flag in ('A','E','B') )
2203 and col.owner = tab.owner
2204 and col.table_name = tab.table_name
2205 and col.owner = x_owner
2206 and col.table_name = x_table_name
2207 and col.column_name <> 'ZD_EDITION_NAME'
2208 and ev.owner = col.owner
2209 and ev.view_name = substrb(col.table_name, 1, 29)||'#'
2210 and not exists
2211 ( select evc.table_column_name
2212 from dba_editioning_view_cols evc
2213 where evc.owner = ev.owner
2214 and evc.view_name = ev.view_name
2215 and evc.table_column_name = col.column_name )
2216 order by col.owner, col.table_name, col.column_name;
2217
2218 -- Multi-Column constraints on obsolete column
2219 cursor C_COLUMN_CONSTRAINTS(X_OWNER varchar2, X_TABLE_NAME varchar2, X_COLUMN_NAME varchar2) is
2220 select cc.constraint_name
2221 from dba_cons_columns cc
2222 where cc.owner = x_owner
2223 and cc.table_name = x_table_name
2224 and cc.column_name = x_column_name;
2225 begin
2226 log(c_module, 'PROCEDURE',
2227 'begin: '||nvl(x_table_owner,'ALL')||'.'||nvl(x_table_name,'ALL'));
2228
2229 -- Cutover Indexes
2230 cutover_indexes(x_table_owner, x_table_name);
2231
2232 -- Remove constraints from obsolete columns
2233 -- - Not Null Constraints
2234 -- - Multi-column constraints
2235 for colrec in c_obsolete_columns(x_table_owner, x_table_name) loop
2236
2237 -- remove Not Null constraint if column has no default value
2238 if (colrec.nullable = 'N' and
2239 (colrec.data_default is null or upper(colrec.data_default) = 'NULL')) then
2240 log(c_module, 'STATEMENT',
2241 'Alter obsolete column to be nullable: '||
2242 colrec.owner||'.'||colrec.table_name||'.'||colrec.column_name);
2243
2244 exec('alter table "'||colrec.owner||'"."'||colrec.table_name||'"'||
2245 ' modify ("'||colrec.column_name||'" null)', c_module);
2246 end if;
2247
2248 -- remove multi-column constraints involving this column, if any
2249 for conrec in c_column_constraints(colrec.owner, colrec.table_name, colrec.column_name) loop
2250 log(c_module, 'STATEMENT',
2251 'Drop Obsolete column constraint: '||
2252 colrec.owner||'.'||colrec.table_name||'.'||conrec.constraint_name);
2253
2254 exec('alter table "'||colrec.owner||'"."'||colrec.table_name||'"'||
2255 ' drop constraint "'||conrec.constraint_name||'"', c_module);
2256 end loop;
2257
2258 end loop;
2259
2260 begin
2261 delete from ad_patched_tables
2262 where owner = x_table_owner
2263 and name = x_table_name;
2264 exception
2265 when no_data_found then
2266 null;
2267 end;
2268
2269 log(c_module, 'PROCEDURE', 'end');
2270 end CUTOVER;
2271
2272
2273 /*
2274 ** Cleanup Table
2275 **
2276 ** X_TABLE_OWNER - filter for tables to clean (match any owner if NULL)
2277 ** X_TABLE_NAME - filter for tables to clean (match any table if NULL)
2278 ** X_CLEAN_MODE - how to clean
2279 ** 'QUICK' - standard post-patch cleanup, drops indexes, triggers
2280 ** 'FULL' - include obsolete columns (danger, should be fully actualized)
2281 **
2282 ** Drop CETS
2283 ** Mark obsolete columns as unused (FULL)
2284 **
2285 ** TODO: use parallel workers to process cleanup actions more quickly.
2286 ** But this cleanup API must not return control until all cleanup
2287 ** processing is complete.
2288 */
2289 procedure CLEANUP(
2290 X_TABLE_OWNER in varchar2 default NULL,
2291 X_TABLE_NAME in varchar2 default NULL,
2292 X_CLEAN_MODE in varchar2 default 'QUICK')
2293 is
2294 C_MODULE varchar2(80) := c_package||'cleanup';
2295 L_INDEX_OWNER varchar2(30);
2296 L_INDEX_NAME varchar2(30);
2297 L_TABLE_OWNER varchar2(30);
2298 L_TABLE_NAME varchar2(30);
2299 L_COLUMN_NAME varchar2(30);
2300
2301 -- Crossedition Triggers
2302 cursor C_CETS(X_TABLE_OWNER varchar2, X_TABLE_NAME varchar2) is
2303 select trg.owner, trg.trigger_name
2304 from dba_triggers trg
2305 where trg.crossedition in ('FORWARD', 'REVERSE')
2306 and trg.table_owner = nvl(x_table_owner, trg.table_owner)
2307 and trg.table_name = nvl(x_table_name, trg.table_name)
2308 order by trg.owner, trg.trigger_name;
2309
2310 -- Obsolete Columns
2311 cursor C_OBSOLETE_COLUMNS(X_TABLE_OWNER varchar2, X_TABLE_NAME varchar2) is
2312 select col.owner, col.table_name, col.column_name
2313 from dba_tab_columns col
2314 , dba_editioning_views ev
2315 where ev.owner in
2316 ( select oracle_username
2317 from system.fnd_oracle_userid
2318 where read_only_flag in ('A','E','B')
2319 )
2320 and ev.owner = nvl(x_table_owner, col.owner)
2321 and ev.table_name = nvl(x_table_name, col.table_name)
2322 and col.column_name <> 'ZD_EDITION_NAME'
2323 and ev.owner = col.owner
2324 and ev.table_name = col.table_name
2325 and not exists
2326 ( select evc.table_column_name
2327 from dba_editioning_view_cols evc
2328 where evc.owner = ev.owner
2329 and evc.view_name = ev.view_name
2330 and evc.table_column_name = col.column_name
2331 )
2332 order by col.owner, col.table_name, col.column_name;
2333
2334
2335 -- EV Tables with unused columns
2336 -- Currently not used, but in future, use this to find tables that need online redef
2337 cursor C_UNUSED_COL_TABS(X_TABLE_OWNER varchar2, X_TABLE_NAME varchar2) is
2338 select ev.owner, ev.table_name
2339 from dba_unused_col_tabs uct, dba_editioning_views ev
2340 where uct.owner in
2341 ( select oracle_username
2342 from system.fnd_oracle_userid
2343 where read_only_flag in ('A','E','B') )
2344 and uct.owner = nvl(x_table_owner, uct.owner)
2345 and uct.table_name = nvl(x_table_name, uct.table_name)
2346 and ev.owner = uct.owner
2347 and ev.table_name = uct.table_name
2348 order by 1, 2;
2349
2350 begin
2351 log(c_module, 'PROCEDURE',
2352 'begin: '||nvl(x_table_owner,'ALL')||'.'||nvl(x_table_name,'ALL')||', '||x_clean_mode);
2353
2354 -- Disable CETS
2355 log(c_module, 'EVENT', 'Disable Crossedition Triggers');
2356 for cetrec in c_cets(x_table_owner, x_table_name) loop
2357 exec('alter trigger "'||cetrec.owner||'"."'||cetrec.trigger_name||'" DISABLE', c_module, true);
2358 end loop;
2359
2360 -- Cleanup CETS
2361 log(c_module, 'EVENT', 'Drop Crossedition Triggers');
2362 for cetrec in c_cets(x_table_owner, x_table_name) loop
2363 exec('drop trigger "'||cetrec.owner||'"."'||cetrec.trigger_name||'"', c_module, true);
2364 end loop;
2365
2366 -- Additional actions for FULL cleanup
2367 if x_clean_mode = 'FULL' then
2368
2369 -- Mark unused columns
2370 log(c_module, 'EVENT', 'Mark unused columns');
2371 for colrec in c_obsolete_columns(x_table_owner, x_table_name) loop
2372 exec('alter table "'||colrec.owner||'"."'||colrec.table_name||'"'||
2373 ' set unused ('||colrec.column_name||')', c_module, true);
2374 end loop;
2375
2376 end if;
2377
2378 log(c_module, 'PROCEDURE', 'end');
2379 end CLEANUP;
2380
2381
2382 /*
2383 ** Abort patch edition
2384 **
2385 ** Drop new or revised indexes
2386 ** Remove unused column not null constratints
2387 ** Note: CETS are dropped when the Patch Edition is dropped
2388 ** Note: Unused columns are handled during FULL cleanup
2389 */
2390 procedure ABORT is
2391 C_MODULE varchar2(80) := c_package||'abort';
2392
2393 -- Patched Indexes: created since the patch edition
2394 cursor C_PATCHED_INDEXES is
2395 select idx.owner, idx.index_name,
2396 con.owner table_owner, con.table_name, con.constraint_name
2397 from
2398 dba_indexes idx
2399 , dba_constraints con
2400 where idx.owner in
2401 ( select oracle_username from system.fnd_oracle_userid
2402 where read_only_flag in ('A', 'B', 'E', 'U') )
2403 and regexp_like(idx.index_name, ad_zd_table.revised_index_regexp, 'c')
2404 and con.owner(+) = idx.table_owner
2405 and con.table_name(+) = idx.table_name
2406 and con.index_name(+) = idx.index_name
2407 order by 1, 2;
2408
2409 -- Unused Not-Null columns with no default
2410 cursor C_UNUSED_NN_COLUMNS is
2411 select col.owner, col.table_name, col.column_name, col.data_default
2412 from dba_tab_columns col
2413 , dba_editioning_views ev
2414 where col.owner in
2415 ( select oracle_username
2416 from system.fnd_oracle_userid
2417 where read_only_flag in ('A','E','B') )
2418 and col.nullable = 'N'
2419 and col.table_name not like '%#'
2420 and (col.default_length is null or col.default_length = 4)
2421 and ev.owner = col.owner
2422 and ev.view_name = substrb(col.table_name, 1, 29)||'#'
2423 and not exists
2424 ( select evc.table_column_name
2425 from dba_editioning_view_cols evc
2426 where evc.owner = ev.owner
2427 and evc.view_name = ev.view_name
2428 and evc.table_column_name = col.column_name )
2429 order by col.owner, col.table_name, col.column_name;
2430
2431 begin
2432 log(c_module, 'PROCEDURE', 'begin: no parameter(s)');
2433
2434 -- Drop patched indexes
2435 for idxrec in c_patched_indexes loop
2436 if idxrec.constraint_name is not null then
2437 log(c_module, 'EVENT', 'Drop patched constraint: '||
2438 idxrec.table_owner||'.'||idxrec.table_name||'/'||idxrec.constraint_name);
2439 exec('alter table '||idxrec.table_owner||'."'||idxrec.table_name||'" '||
2440 'drop constraint "'||idxrec.constraint_name||'" cascade', c_module, true);
2441 end if;
2442 log(c_module, 'EVENT', 'Drop patched index: '||idxrec.owner||'.'||idxrec.index_name);
2443 exec('drop index "'||idxrec.owner||'"."'||idxrec.index_name||'"', c_module, true);
2444 end loop;
2445
2446 -- Alter unused not-null columns with no default value to be nullable
2447 for colrec in c_unused_nn_columns loop
2448 if colrec.data_default is null or upper(colrec.data_default) = 'NULL' then
2449 log(c_module, 'EVENT', 'Alter unused column to be nullable: '||
2450 colrec.owner||'.'||colrec.table_name || '.' || colrec.column_name );
2451 exec('alter table "'||colrec.owner||'"."'||colrec.table_name||'"'||
2452 ' modify ('||colrec.column_name||' null)', c_module, true);
2453 end if;
2454 end loop;
2455
2456 -- Clear patched table list
2457 delete from ad_patched_tables;
2458 commit;
2459
2460 log(c_module, 'PROCEDURE', 'end');
2461 end ABORT;
2462
2463 /*
2464 * Drop an editioned table
2465 *
2466 * TODO: Drop Synonyms pointing to Editioning View
2467 * Drop VPD policies on Editioning View
2468 * Drop Editioning View
2469 * If Running in PATCH edition
2470 * Store deferred DDL to drop table in CLEANUP
2471 * If running in RUN edition
2472 * Drop table immediately
2473 *
2474 */
2475 procedure DROP_TABLE (
2476 X_TABLE_OWNER in varchar2,
2477 X_TABLE_NAME in varchar2,
2478 X_DROP_STMT in varchar2,
2479 X_UPD_STMT in varchar2,
2480 X_DROPPED out nocopy varchar2 )
2481 is
2482 C_MODULE varchar2(80) := c_package || 'DROP';
2483 l_table_owner varchar2(30);
2484 l_ev_name varchar2(30);
2485 l_count varchar2(30);
2486 l_appsname varchar2(30);
2487 l_defer_stmt varchar2(2000);
2488
2489 cursor C_SYNONYMS(x_table_owner varchar2, x_ev_name varchar2) is
2490 select syn.owner owner, syn.synonym_name name
2491 from dba_synonyms syn
2492 where syn.table_owner = x_table_owner
2493 and syn.table_name = x_ev_name
2494 and syn.owner <> 'PUBLIC';
2495
2496 begin
2497 l_appsname := ad_zd.apps_schema;
2498 l_table_owner := trim(nvl(x_table_owner, l_appsname));
2499 log(C_MODULE, 'PROCEDURE', 'begin: '|| l_table_owner ||'.'||x_table_name);
2500
2501 -- Verify table exists
2502 select count(1)
2503 into l_count
2504 from dba_tables tab
2505 where tab.owner = l_table_owner
2506 and tab.table_name = x_table_name;
2507
2508 if (l_count = 0) then
2509 -- Table doesn't exist.
2510 if (x_upd_stmt is not null) then
2511 exec(x_upd_stmt, c_module, true);
2512 end if;
2513 X_DROPPED := 'Y';
2514 else
2515 l_ev_name := ad_zd_table.ev_view(x_table_name);
2516
2517 -- Drop synonyms pointing to EV
2518 for synrec in c_synonyms(l_table_owner, l_ev_name) loop
2519 log(c_module, 'EVENT', 'Dropping synonym '||synrec.owner||'.'||synrec.name);
2520 exec ('drop synonym "' || synrec.owner||'"."'||synrec.name||'"', c_module, true);
2521 end loop;
2522
2523 -- Drop Edition view
2524 log(c_module, 'EVENT', 'Drop EV '||l_table_owner||'.'||l_ev_name);
2525 exec('drop view "'||l_table_owner||'"."'||l_ev_name ||'"', c_module, true);
2526
2527 -- (Deferred) drop table logic
2528 if ad_zd.get_edition_type = 'RUN' then
2529 -- Drop table
2530 log(c_module, 'EVENT', 'Drop table '||l_table_owner||'.'||x_table_name);
2531 begin
2532 exec(x_drop_stmt, c_module);
2533
2534 -- Update dropped status
2535 if (x_upd_stmt is not null) then
2536 exec(x_upd_stmt, c_module, true);
2537 end if;
2538
2539 X_DROPPED := 'Y';
2540
2541 exception
2542 when others then
2543 if (sqlcode <> -942) then
2544 log(c_module, 'ERROR', 'Error while dropping table '
2545 ||l_table_owner||'.'||x_table_name || ': ' || substr(sqlerrm, 1, 2000));
2546 X_DROPPED := 'N';
2547 else
2548 -- Update dropped status
2549 if (x_upd_stmt is not null) then
2550 exec(x_upd_stmt, c_module, true);
2551 end if;
2552
2553 X_DROPPED := 'Y';
2554 end if;
2555 end;
2556
2557 else -- If running from PATCH edition
2558
2559 -- Defer drop table to next cleanup
2560 log (c_module, 'EVENT', 'Defer drop table ' ||
2561 l_table_owner||'.'||x_table_name|| ' to next cleanup');
2562
2563 if (x_upd_stmt is not null) then
2564 l_defer_stmt := 'begin execute immediate '''|| regexp_replace(x_drop_stmt, '''', '''''') || '''; ' ||
2565 'execute immediate ''' || regexp_replace(x_upd_stmt, '''', '''''') ||
2566 '''; exception when others then null; end;';
2567
2568 ad_zd.load_ddl ('CLEANUP', l_defer_stmt);
2569 else
2570 ad_zd.load_ddl ('CLEANUP', x_drop_stmt);
2571 end if;
2572
2573 -- Return 'N' as the table object is not deleted yet.
2574 X_DROPPED := 'N';
2575 end if;
2576 end if;
2577
2578 log( c_module, 'PROCEDURE', 'end');
2579 commit;
2580 end DROP_TABLE;
2581
2582 end AD_ZD_TABLE;
[Home] [Help]