[Home] [Help]
PACKAGE BODY: APPS.AD_ZD_SEED
Source
1 PACKAGE BODY AD_ZD_SEED AS
2 /* $Header: ADZDSMB.pls 120.73.12020000.17 2013/05/09 12:39:01 rahulshr ship $ */
3
4 C_PACKAGE CONSTANT VARCHAR2(80) := 'ad.plsql.ad_zd_seed.';
5
6 /*
7 ** --------------------------------------------------------------------
8 ** Edition Data Storage - Public Helper Functions
9 ** --------------------------------------------------------------------
10 */
11
12 /* Editioned Data Storage Column Name */
13 function EDS_COLUMN return varchar2 is
14 begin
15 return 'ZD_EDITION_NAME';
16 end;
17
18 /* Editioned Data Storage Maintenance Trigger Name */
19 function EDS_TRIGGER(X_TABLE_NAME in varchar2) return varchar2 is
20 begin
21 return substr(upper(x_table_name), 1, 29)||'+';
22 end;
23
24 /* Editioned Data Storage VPD Function Name */
25 -- Note: function name must be un-quoted type due to bug in VPD
26 function EDS_FUNCTION(X_TABLE_NAME in varchar2) return varchar2 is
27 begin
28 return substr(upper(x_table_name), 1, 29)||'=';
29 end;
30
31 /* Editioned Data Storage VPD Policy Name */
32 function EDS_POLICY return varchar2 is
33 begin
34 return 'ZD_SEED';
35 end;
36
37 /* Editioned Data Storage Synchronization Trigger Name */
38 function EDS_FCET(X_TABLE_NAME in varchar2) return varchar2 is
39 begin
40 return substr(upper(x_table_name), 1, 29)||'>';
41 end;
42
43
44 /*
45 ** --------------------------------------------------------------------
46 ** Internal
47 ** --------------------------------------------------------------------
48 */
49
50
51 -- log shortcut
52 procedure LOG(X_MODULE varchar2, X_LEVEL varchar2, X_MESSAGE varchar2) is
53 begin
54 ad_zd.log(x_module, x_level, x_message);
55 end;
56
57 -- error shortcut
58 procedure ERROR(X_MODULE varchar2, X_MESSAGE varchar2) is
59 begin
60 ad_zd.error(x_module, x_message);
61 end;
62
63 -- exec shortcut
64 procedure EXEC(X_SQL in varchar2, X_LOG_MOD in varchar2, X_IGNORE in boolean default false) is
65 begin
66 ad_zd.exec(x_sql, x_log_mod, x_ignore);
67 end;
68
69 -- Get seed data table details from synonym, and validate along the way
70 procedure TRANSLATE_SYNONYM(
71 X_MODULE in varchar2,
72 X_SYNONYM_NAME in varchar2,
73 X_CHECK_SEED in boolean,
74 X_TABLE_OWNER out nocopy varchar2,
75 X_TABLE_NAME out nocopy varchar2)
76 is
77 L_OWNER varchar2(30);
78 L_VIEW_NAME varchar2(30);
79 L_TABLE_NAME varchar2(30);
80 begin
81 -- Get EV Name
82 begin
83 select s.table_owner, s.table_name
84 into l_owner, l_view_name
85 from dba_synonyms s
86 where owner = ad_zd.apps_schema
87 and synonym_name = x_synonym_name;
88 exception when no_data_found then
89 error(x_module, 'Synonym does not exist: '||nvl(x_synonym_name,'<null>'));
90 end;
91
92 -- Get Table Name
93 begin
94 l_table_name := ad_zd_table.ev_table(l_owner, l_view_name);
95 exception when no_data_found then
96 if x_check_seed then
97 error(x_module, 'Synonym does not point to an editioning view: '||x_synonym_name);
98 else
99 l_table_name := l_view_name;
100 end if;
101 end;
102
103 -- Validate that table suppports Editioned Data Storage
104 if x_check_seed then
105 if ad_zd_table.is_seed(l_owner, l_table_name) = 'N' then
106 error(x_module, 'Synonym does not point to a seed data table: '||x_synonym_name);
107 end if;
108 end if;
109
110 -- Return results
111 x_table_owner := l_owner;
112 x_table_name := l_table_name;
113 end;
114
115
116 /*
117 ** Aquire an exclusive lock on a Table
118 ** x_log_mod - calling module (for logging)
119 */
120 procedure LOCK_TABLE(X_OWNER in varchar2, X_TABLE_NAME in varchar2, X_LOG_MOD in varchar2) is
121 begin
122 exec('lock table "'||x_owner||'"."'||x_table_name||'" in exclusive mode', x_log_mod);
123 end LOCK_TABLE;
124
125
126
127 /*
128 ** Detect repeated prepare call on a table.
129 ** x_table_name - table name
130 */
131 function IS_PREPARED(X_TABLE_NAME in varchar2) return boolean
132 is
133 C_MODULE varchar2(80) := c_package||'is_prepared';
134 L_RETVAL boolean;
135 L_DUMMY number;
136 begin
137 begin
138 select 1 into l_dummy
139 from dba_source
140 where owner = ad_zd.apps_schema
141 and name = ad_zd_seed.eds_function(x_table_name)
142 and type = 'FUNCTION'
143 and instr(text, ad_zd.get_edition('PATCH')) <> 0;
144 l_retval := TRUE;
145 exception when no_data_found then
146 l_retval := FALSE;
147 end;
148 return l_retval;
149 end IS_PREPARED;
150
151
152
153 /*
154 ** Acquire an exclusive lock on a procedure entry
155 ** x_lock_name - lock name
156 */
157 function ACQUIRE_LOCK(x_lock_name in varchar2) return varchar2
158 is
159 C_MODULE varchar2(80) := c_package||'acquire_lock';
160 L_LOCK_NAME varchar2(128) := upper(x_lock_name);
161 L_LOCK_HANDLE varchar2(128) := null;
162 L_LOCK_STATUS integer;
163 begin
164 dbms_lock.allocate_unique(lockname => l_lock_name, lockhandle => l_lock_handle);
165 l_lock_status := dbms_lock.request(lockhandle => l_lock_handle,
166 lockmode => dbms_lock.x_mode,
167 timeout => dbms_lock.maxwait,
168 release_on_commit => false);
169
170 if (l_lock_status <> 0) then
171 error(c_module, 'Unable to acquire lock: '||x_lock_name||
172 ' lock_handle: '||l_lock_handle||' lock_status: '||l_lock_status);
173 end if;
174
175 log(c_module, 'STATEMENT', 'Lock acquired: '|| x_lock_name || ' -> ' || l_lock_handle);
176 return l_lock_handle;
177 end ACQUIRE_LOCK;
178
179
180 /*
181 ** Release the exclusive lock on a procedure exit
182 ** x_lock_handle - lock handle
183 */
184 procedure RELEASE_LOCK(x_lock_handle in varchar2)
185 is
186 C_MODULE varchar2(80) := c_package||'release_lock';
187 L_LOCK_STATUS integer;
188 begin
189 if x_lock_handle is null then
190 return;
191 end if;
192
193 l_lock_status := dbms_lock.release(x_lock_handle);
194 if l_lock_status <> 0 then
195 error(c_module, 'Unable to release lock: '||x_lock_handle||' lock_status: '||l_lock_status);
196 end if;
197
198 log(c_module, 'STATEMENT', 'Lock released: '|| x_lock_handle);
199 end RELEASE_LOCK;
200
201
202 /*
203 ** Fetch Multiple DDLs
204 ** x_table_owner - base table owner
205 ** x_table_name - base table name
206 ** x_object_type - object type (index | constraint)
207 ** x_object_owner - index / constraint owner
208 ** x_object_name - index / constraint name
209 */
210 function FETCH_MULTIPLE_DDLS (
211 X_TABLE_OWNER in varchar2,
212 X_TABLE_NAME in varchar2,
213 X_OBJECT_TYPE in varchar2,
214 X_OBJECT_OWNER in varchar2,
215 X_OBJECT_NAME in varchar2) return SYS.KU$_DDLS
216 is
217 C_MODULE varchar2(80) := c_package||'fetch_multiple_ddls';
218 L_OPEN_HANDLE number;
219 L_TRANSFORM_HANDLE number;
220 L_DDL_STMTS sys.ku$_ddls;
221 begin
222 log(c_module, 'STATEMENT',
223 'Getting DDLs for '||x_table_owner||'.'||x_table_name||', '||
224 x_object_type||', '||x_object_owner||'.'||x_object_name);
225
226 l_open_handle := dbms_metadata.open(x_object_type);
227 dbms_metadata.set_filter(l_open_handle, 'SCHEMA', x_object_owner);
228 dbms_metadata.set_filter(l_open_handle, 'NAME', x_object_name);
229 l_transform_handle := dbms_metadata.add_transform(l_open_handle, 'DDL');
230 dbms_metadata.set_transform_param(l_transform_handle, 'SQLTERMINATOR', false);
231 l_ddl_stmts := dbms_metadata.fetch_ddl(l_open_handle);
232 dbms_metadata.close(l_open_handle);
233
234 return l_ddl_stmts;
235 end FETCH_MULTIPLE_DDLS;
236
237
238 /*
239 ** Alter triggers for a given table
240 **
241 ** X_MODE - 'ENABLE' or 'DISABLE' the triggers
242 **
243 */
244 procedure ALTER_TRIGGERS(X_OWNER in varchar2, X_TABLE_NAME in varchar2, X_MODE in varchar2)
245 is
246 C_MODULE varchar2(80) := c_package||'alter_trigger';
247 L_STMT varchar2(1000);
248 L_TRIG_OWNER varchar2(30);
249 L_TRIG_NAME varchar2(30);
250
251 -- Table triggers (except for EDS Trigger)
252 -- TODO: remember which triggers were disabled, reenable only those
253 cursor C_TRIG_CUR(x_tab_owner varchar2, x_tab_name varchar2) is
254 select owner, trigger_name
255 from dba_triggers
256 where table_owner = x_tab_owner
257 and table_name in (x_tab_name, ad_zd_table.ev_view(x_tab_name))
258 and trigger_name <> ad_zd_seed.eds_trigger(x_tab_name)
259 and crossedition = 'NO';
260
261 begin
262 for trgrec in c_trig_cur(x_owner, x_table_name) loop
263 l_stmt := 'alter trigger '||trgrec.owner||'."'||trgrec.trigger_name||'" '||x_mode;
264 exec (l_stmt, c_module, TRUE); -- ignore errors
265 end loop;
266 end;
267
268
269 /*
270 ** Create ZD striping column on seed data table
271 */
272 procedure CREATE_ZD_COLUMN(X_OWNER in varchar2, X_TABLE_NAME in varchar2, X_EDITION_NAME in varchar2)
273 is
274 C_MODULE varchar2(80) := c_package||'create_zd_column';
275 L_STMT varchar2(2000);
276
277 begin
278 if ad_zd_table.is_seed(x_owner, x_table_name) = 'N' then
279 log(c_module, 'EVENT', 'Creating EDS Striping Column on '||x_owner||'.'||x_table_name);
280 l_stmt := 'alter table '||x_owner||'."'||x_table_name||
281 '" add (ZD_EDITION_NAME varchar2(30) default '''||x_edition_name||''' not null)';
282 else
283 log(c_module, 'EVENT', 'Updating existing EDS Striping Column on '||x_owner||'.'||x_table_name);
284 l_stmt := 'update '||x_owner||'."'||x_table_name||'"'||
285 ' set ZD_EDITION_NAME = '''||x_edition_name||''''||
286 ' where ZD_EDITION_NAME != '''||x_edition_name||'''';
287 end if;
288 exec(l_stmt, c_module);
289 end CREATE_ZD_COLUMN;
290
291
292 /*
293 ** Fix unique indexes on seed data table
294 **
295 ** Add ZD_EDITION_NAME as a leading column on all
296 ** unique Indexes
297 */
298 procedure FIX_INDEXES(X_TABLE_OWNER in varchar2, X_TABLE_NAME in varchar2)
299 is
300 C_MODULE varchar2(80) := c_package||'fix_indexes';
301 L_IND_OWNER varchar2(30);
302 L_IND_NAME varchar2(30);
303 L_CONSTRAINT_NAME varchar2(30);
304 L_CONSTRAINT_TYPE varchar2(30);
305 L_DDL_STMTS sys.ku$_ddls;
306 L_DDL_STMT varchar2(32767);
307
308 cursor C_IND_CUR (x_tab_owner varchar2, x_tab_name varchar2) is
309 select i.owner, i.index_name
310 from dba_indexes i
311 where i.table_owner = x_tab_owner
312 and i.table_name = x_tab_name
313 and i.uniqueness = 'UNIQUE'
314 and i.index_type <> 'LOB'
315 and not exists ( select null
316 from dba_ind_columns c
317 where c.index_owner = i.owner
318 and c.index_name = i.index_name
319 and c.column_name = 'ZD_EDITION_NAME' );
320
321 cursor C_CONSTRAINT_CUR (x_tab_owner varchar2, x_tab_name varchar2) is
322 select constraint_name, constraint_type
323 from dba_constraints o
324 where owner = x_tab_owner
325 and table_name = x_tab_name
326 and constraint_type in ('U','P')
327 and not exists ( select null
331 and i.column_name = 'ZD_EDITION_NAME' );
328 from dba_cons_columns i
329 where i.owner = o.owner
330 and i.constraint_name = o.constraint_name
332
333 begin
334 log(c_module, 'PROCEDURE', 'begin: '||x_table_owner||'.'||x_table_name);
335
336 open c_constraint_cur(x_table_owner,x_table_name);
337 fetch c_constraint_cur INTO l_constraint_name, l_constraint_type;
338 while (c_constraint_cur%found) loop
339 log(c_module, 'EVENT', 'Adding EDS Striping Column to constraint '||l_constraint_name);
340 lock_table(x_table_owner,x_table_name,c_module);
341 l_ddl_stmts := fetch_multiple_ddls(x_table_owner, x_table_name, 'CONSTRAINT', x_table_owner, l_constraint_name);
342
343 if l_constraint_type = 'U' then
344 l_ddl_stmt := 'alter table "'||x_table_owner||'"."'||x_table_name||'" drop constraint '||l_constraint_name||' drop index';
345 else
346 l_ddl_stmt := 'alter table "'||x_table_owner||'"."'||x_table_name||'" drop constraint '||l_constraint_name||' cascade drop index';
347 end if;
348 exec(l_ddl_stmt, c_module);
349
350 for i in 1 .. l_ddl_stmts.count loop
351 l_ddl_stmt := dbms_lob.substr(l_ddl_stmts(i).ddltext);
352 if i=1 then
353 l_ddl_stmt := regexp_replace(l_ddl_stmt,'\)',',"ZD_EDITION_NAME")', 1, 1);
354 end if;
355 exec(l_ddl_stmt, c_module);
356 end loop;
357
358 fetch c_constraint_cur INTO l_constraint_name, l_constraint_type;
359 end loop;
360 close c_constraint_cur;
361
362 open c_ind_cur(x_table_owner,x_table_name);
363 fetch c_ind_cur INTO l_ind_owner, l_ind_name;
364 while (c_ind_cur%found) loop
365 log(c_module, 'EVENT', 'Adding EDS Striping Column to index '||l_ind_owner||'.'||l_ind_name);
366 lock_table(x_table_owner,x_table_name,c_module);
367 l_ddl_stmts := fetch_multiple_ddls(x_table_owner, x_table_name, 'INDEX', l_ind_owner, l_ind_name);
368
369 l_ddl_stmt := 'drop index "'||l_ind_owner||'"."'||l_ind_name||'"';
370 exec(l_ddl_stmt,c_module);
371
372 for i in 1 .. l_ddl_stmts.count loop
373 l_ddl_stmt := dbms_lob.substr(l_ddl_stmts(i).ddltext);
374 if i=1 then
375 l_ddl_stmt := regexp_replace(l_ddl_stmt, '\)\s*([^,)])', ',"ZD_EDITION_NAME") \1', 1, 1);
376 end if;
377 exec(l_ddl_stmt, c_module);
378 end loop;
379
380 fetch c_ind_cur INTO l_ind_owner, l_ind_name;
381 end loop;
382 close c_ind_cur;
383
384 log(c_module, 'PROCEDURE', 'end');
385 end FIX_INDEXES;
386
387
388 /*
389 ** Create Guard Trigger to populate ZD_EDITION_NAME column on specified table
390 **
391 ** If X_EDITION_NAME is supplied,
392 ** the existing trigger will be saved, and a new trigger body will be generated
393 ** else
394 ** the saved trigger body will be restored
395 */
396 procedure CREATE_GUARD(
397 X_TABLE_OWNER in varchar2,
398 X_TABLE_NAME in varchar2,
399 X_EDITION_NAME in varchar2,
400 X_SAVE_BODY in out nocopy varchar2)
401 is
402 C_MODULE varchar2(80) := c_package||'create_guard';
403 L_EV_NAME varchar2(30) := ad_zd_table.ev_view(x_table_name);
404 L_TRIG_NAME varchar2(30) := ad_zd_seed.eds_trigger(x_table_name);
405 L_TRIG_BODY varchar2(32000);
406 L_TRIG_STMT varchar2(32000);
407
408 cursor C_TRIG_CUR (x_tab_owner varchar2, x_tab_name varchar2, x_trig_name varchar2) is
409 select trigger_body
410 from dba_triggers
411 where table_owner = x_tab_owner
412 and table_name = x_tab_name
413 and trigger_name = x_trig_name;
414
415 begin
416
417 -- If X_EDITION_NAME is supplied, then generate trigger body, otherwise use saved trigger body
418 if x_edition_name is not null then
419
420 -- Save old trigger body
421 open c_trig_cur (x_table_owner, l_ev_name, l_trig_name);
422 fetch c_trig_cur INTO x_save_body;
423 close c_trig_cur;
424
425 -- Generate new trigger body
426 l_trig_body :=
427 ' declare'||
428 ' l_current varchar2(30) := sys_context(''userenv'', ''current_edition_name'');'||
429 ' l_default varchar2(30);'||
430 ' begin '||
431 ' select property_value into l_default'||
432 ' from database_properties where property_name = ''DEFAULT_EDITION'';'||
433 ' if l_current > l_default and l_current != '''||x_edition_name||''' then'||
434 ' raise_application_error(-20002,''Seed Data Table has not been prepared for patching'');'||
435 ' end if;'||
436 ' if INSERTING then '||
437 ' :new.zd_edition_name := '''||x_edition_name||''';'||
438 ' end if;'||
439 ' end;';
440
441 else
442
443 -- Use saved trigger body
444 l_trig_body := x_save_body;
445
446 end if;
447
448 -- Create trigger statement
449 log(c_module, 'EVENT', 'Creating EDS Guard Trigger '||l_trig_name);
450 l_trig_stmt := ' create or replace trigger "'||ad_zd.apps_schema||'"."'||l_trig_name||'"'||
451 ' before insert or update or delete on "'||x_table_owner||'"."'||l_ev_name||'"'||
452 ' for each row '|| l_trig_body;
453 exec(l_trig_stmt, c_module);
454
455 end CREATE_GUARD;
456
457
458 /*
459 ** Create VPD Policy and policy function
460 **
461 ** Every seed data table will have a VPD Policy on it's EV
462 ** The policy simply adds where ZD_EDITION_NAME = '<EDITION_NAME'>
463 ** to every where clause
464 */
465 procedure CREATE_POLICY(
466 X_OWNER in varchar2,
467 X_TABLE_NAME in varchar2,
468 X_EDITION_NAME in varchar2)
469 is
473 L_EV_NAME varchar2(30) := ad_zd_table.ev_view(x_table_name);
470 C_MODULE varchar2(80) := c_package||'create_policy';
471 L_CUR integer;
472 L_POLICY_NAME varchar2(30) := ad_zd_seed.eds_policy;
474 L_POLICY_FUNC varchar2(30) := ad_zd_seed.eds_function(x_table_name);
475 L_STMT varchar2(1000);
476 L_TEXT varchar2(4000);
477
478 cursor C_POLICY_CUR (x_tab_owner varchar2, x_tab_name varchar2) is
479 select policy_name
480 from dba_policies
481 where object_owner = x_tab_owner
482 and object_name = ad_zd_table.ev_view(x_tab_name)
483 and policy_name = ad_zd_seed.eds_policy;
484
485 begin
486
487 -- Install VPD Function
488 log(c_module, 'EVENT', 'Creating EDS Filter Function '||l_policy_func);
489 l_stmt :=
490 'create or replace function '||
491 '"'||ad_zd.apps_schema||'"."'||l_policy_func||'"(x_schema in varchar2, x_table in varchar2) '||
492 'return varchar2 is '||
493 'begin return ''ZD_EDITION_NAME = '''''||x_edition_name||'''''''; end;';
494 exec (l_stmt, c_module);
495
496 -- Add VPD Policy to EV if missing
497 open c_policy_cur (x_owner, x_table_name);
498 fetch c_policy_cur INTO l_policy_name;
499 if (c_policy_cur%notfound) then
500 log(c_module, 'EVENT', 'Creating EDS Filter Policy '||x_owner||'.'||l_ev_name||', '||l_policy_name);
501 dbms_rls.add_policy(
502 object_schema => x_owner,
503 object_name => l_ev_name,
504 policy_name => l_policy_name,
505 function_schema => ad_zd.apps_schema,
506 policy_function => '"'||l_policy_func||'"',
507 policy_type => dbms_rls.static,
508 statement_types => 'select, update, delete, index');
509 end if;
510 close c_policy_cur;
511
512 end CREATE_POLICY;
513
514
515 /*
516 ** Create Data Synchronization FCET in PATCH edition
517 ** The trigger will sync updates from the RUN edition to the PATCH edition
518 **
519 ** X_COPY_DATA: true to copy the Run Edition seed data to the Patch Edition
520 ** false to skip the copy (when it was already done)
521 */
522 procedure CREATE_SYNC(
523 X_TABLE_OWNER in varchar2,
524 X_TABLE_NAME in varchar2,
525 X_COPY_DATA in boolean)
526 is
527 C_MODULE varchar2(80) := c_package||'create_sync';
528 L_TRIG_NAME varchar2(30) := ad_zd_seed.eds_fcet(x_table_name);
529 L_STMT varchar2(32767);
530 L_SAVE_STMT varchar2(32767); /* save data values */
531 L_OLD_KEY_STMT varchar2(3000); /* get old key values */
532 L_NEW_KEY_STMT varchar2(3000); /* get new key values */
533 L_REC_KEY_STMT varchar2(3000); /* key record type */
534 L_WHERE_STMT varchar2(3000); /* where key */
535 L_DISABLE varchar2(8);
536 L_FIRST boolean;
537 NL varchar(1) := '
538 ';
539
540 -- Table columns (excluding zd_edition_name)
541 cursor C_TAB_COLS(x_tab_owner varchar2, x_tab_name varchar2 ) is
542 select column_name
543 from dba_tab_columns
544 where owner = x_tab_owner
545 and table_name = x_tab_name
546 and column_name <> 'ZD_EDITION_NAME'
547 order by column_id;
548
549 -- Key columns (primary or unique key)
550 -- TODO: explore dba_ind_expressions.column_expression for function based indexes
551 cursor C_KEY_COLS(x_owner varchar2, x_tab_name varchar2 ) is
552 select
553 up.index_name
554 , ic.column_name
555 , nvl(col.nullable, 'Y') nullable
556 from
557 ( select i.owner, i.index_name, c.constraint_name
558 from dba_indexes i, dba_constraints c
559 where i.table_owner = x_owner
560 and i.table_name = x_tab_name
561 and i.uniqueness = 'UNIQUE'
562 and i.index_type = 'NORMAL' /* exclude other index types */
563 and c.owner(+) = i.table_owner
564 and c.table_name(+) = i.table_name
565 and c.index_owner(+) = i.owner
566 and c.index_name(+) = i.index_name
567 and c.constraint_type(+) = 'P'
568 and i.index_name not like '%~%' /*exclude revised indexes created by a patch */
569 and rownum = 1
570 order by 3,1,2 /* puts PK row first, if it exists */) up,
571 dba_ind_columns ic, dba_tab_columns col
572 where ic.index_owner = up.owner
573 and ic.index_name = up.index_name
574 and ic.column_name <> 'ZD_EDITION_NAME'
575 and ic.table_owner = x_owner
576 and ic.table_name = x_tab_name
577 and col.owner = x_owner
578 and col.table_name = x_tab_name
579 and col.column_name = ic.column_name
580 order by ic.column_position;
581
582 begin
583 log(c_module, 'PROCEDURE', 'begin: '||x_table_owner||'.'||x_table_name);
584
585 --
586 -- Construct SQL Fragments that depend on KEY COLUMNS
587 --
588 l_rec_key_stmt := ' type KEY_R is record (';
589 l_where_stmt := ' where ';
590 l_first := TRUE;
591 for crec in c_key_cols(x_table_owner, x_table_name) loop
592 -- append seperator string for second and subsequent keys
593 if l_first then
594 l_first := FALSE;
595 else
596 l_rec_key_stmt := l_rec_key_stmt||',';
597 l_old_key_stmt := l_old_key_stmt||NL;
598 l_new_key_stmt := l_new_key_stmt||NL;
599 l_where_stmt := l_where_stmt||' and ';
600 end if;
601
602 l_rec_key_stmt := l_rec_key_stmt||
603 NL||' '||crec.column_name||' '||x_table_owner||'.'||x_table_name||'.'||crec.column_name||'%TYPE';
604 l_old_key_stmt := l_old_key_stmt||
605 ' l_key(idx).'||crec.column_name||' := :old.'||crec.column_name||';';
609 if (crec.nullable = 'N') then
606 l_new_key_stmt := l_new_key_stmt||
607 ' l_key(idx).'||crec.column_name||' := :new.'||crec.column_name||';';
608
610 l_where_stmt := l_where_stmt||crec.column_name||'=l_key(j).'||crec.column_name;
611 else
612 l_where_stmt := l_where_stmt||
613 '(('||crec.column_name||' is null and l_key(j).'||crec.column_name||
614 ' is null) or ('||crec.column_name||'=l_key(j).'||crec.column_name||'))';
615 end if;
616 end loop;
617 l_rec_key_stmt := l_rec_key_stmt||' );';
618
619 -- check if there was no PK and we must copy data, then resort to insert-select
620 if l_first then
621 log(c_module, 'WARNING', 'Table does not support Synchronization. No Primary Key or Unique Index defined');
622 if x_copy_data then
623 log(c_module, 'EVENT', 'Copy Seed Data using insert-select: '||x_table_name);
624 exec('insert into "'||x_table_owner||'"."'||ad_zd_table.ev_view(x_table_name)||
625 '" select * from "'||x_table_owner||'"."'||ad_zd_table.ev_view(x_table_name)||'"',
626 c_module);
627 end if;
628 return;
629 end if;
630
631 -- Construct SQL fragmnets that depend on TABLE COLUMNS
632 l_first := TRUE;
633 for crec in c_tab_cols(x_table_owner, x_table_name) loop
634 if l_first then
635 l_first := FALSE;
636 else
637 l_save_stmt := l_save_stmt||NL;
638 end if;
639 l_save_stmt := l_save_stmt||' l_data(idx).'||crec.column_name||' := :new.'||crec.column_name||';';
640 end loop;
641
642 -- Create trigger disabled on initial data copy
643 if x_copy_data then
644 l_disable := 'disable';
645 else
646 l_disable := '';
647 end if;
648
649 --
650 -- Create statement
651 -- Note: Inserts are converted to update, in the "updating" section of the trigger,
652 -- this is intentional.
653 -- The trigger is optimized for the initial data copy where a call to ad_zd_table.apply
654 -- is used to copy the data. In this case a fake update is issues to every row in the
655 -- table.
656 -- It is understood that this is not optimal for propagating ongoing changes, but
657 -- is is expected that very few updates will be issued to seed data in the RUN edition
658 --
659 l_stmt:='create or replace trigger '||ad_zd.apps_schema||'."'||l_trig_name||'"'
660 ||NL||' for insert or update or delete on '||x_table_owner||'.'||x_table_name
661 ||NL||' forward crossedition '||l_disable||' compound trigger '
662 ||NL||' type DATA_T is table of '||x_table_owner||'.'||x_table_name||'%ROWTYPE index by simple_integer;'
663 ||NL|| l_rec_key_stmt
664 ||NL||' type KEY_T is table of key_r index by simple_integer;'
665 ||NL||' l_data data_t;'
666 ||NL||' l_key key_t;'
667 ||NL||' idx simple_integer := 0;'
668 ||NL||' l_edition varchar2(30) := sys_context(''userenv'',''current_edition_name'');'
669 ||NL||' dml_errors EXCEPTION;'
670 ||NL||' pragma exception_init(dml_errors,-24381);'
671 ||NL
672 ||NL||'AFTER EACH ROW IS begin'
673 ||NL||' idx := idx + 1;'
674 ||NL||' if inserting then'
675 ||NL|| l_new_key_stmt
676 ||NL||' else'
677 ||NL|| l_old_key_stmt
678 ||NL||' end if;'
679 ||NL||' if inserting or updating then'
680 ||NL|| l_save_stmt
681 ||NL||' end if;'
682 ||NL||' l_data(idx).zd_edition_name := l_edition;'
683 ||NL||'end AFTER EACH ROW;'
684 ||NL
685 ||NL||'AFTER STATEMENT IS begin'
686 ||NL||' if inserting then'
687 ||NL||' for j IN 1..l_key.count loop'
688 ||NL||' begin '
689 ||NL||' insert into '||x_table_owner||'.'||x_table_name
690 ||NL||' values l_data(j);'
691 ||NL||' exception when dup_val_on_index then'
692 ||NL||' update '||x_table_owner||'.'||x_table_name||' set row = l_data(j)'
693 ||NL||' '||l_where_stmt||' and zd_edition_name=l_edition;'
694 ||NL||' end;'
695 ||NL||' end loop;'
696 ||NL||' elsif updating then'
697 ||NL||' declare '
698 ||NL||' l_chunk pls_integer := 65000;'
699 ||NL||' l_s_ind pls_integer := 1;'
700 ||NL||' l_e_ind pls_integer := least(l_chunk,l_key.count);'
701 ||NL||' l_done boolean := FALSE;'
702 ||NL||' begin '
703 ||NL||' while( not l_done ) loop'
704 ||NL||' begin '
708 ||NL||' exception when dml_errors then '
705 ||NL||' forall j in l_s_ind..l_e_ind save exceptions'
706 ||NL||' insert into '||x_table_owner||'.'||x_table_name
707 ||NL||' values l_data(j);'
709 ||NL||' declare'
710 ||NL||' j pls_integer:= 0;'
711 ||NL||' begin'
712 ||NL||' for i in 1..SQL%BULK_EXCEPTIONS.COUNT loop'
713 ||NL||' j:= SQL%BULK_EXCEPTIONS(i).ERROR_INDEX + l_s_ind -1;'
714 ||NL||' update '||x_table_owner||'.'||x_table_name||' set row = l_data(j)'
715 ||NL||' '||l_where_stmt||' and zd_edition_name=l_edition;'
716 ||NL||' end loop;'
717 ||NL||' end;'
718 ||NL||' end;'
719 ||NL||' if l_e_ind >= l_key.count then'
720 ||NL||' l_done := TRUE;'
721 ||NL||' else'
722 ||NL||' l_s_ind := l_s_ind + l_chunk;'
723 ||NL||' l_e_ind := least( (l_e_ind + l_chunk),l_key.count);'
724 ||NL||' end if;'
725 ||NL||' end loop;'
726 ||NL||' end;'
727 ||NL||' elsif deleting then '
728 ||NL||' forall j in 1..l_key.count'
729 ||NL||' delete from '||x_table_owner||'.'||x_table_name
730 ||NL|| l_where_stmt||' and zd_edition_name=l_edition;'
731 ||NL||' end if;'
732 ||NL||'end AFTER STATEMENT;'
733 ||NL||'end "'||l_trig_name||'";';
734
735 log(c_module, 'EVENT', 'Creating EDS Sync Trigger '||l_trig_name);
736 exec (l_stmt, c_module);
737
738 if x_copy_data then
739 ad_zd_table.apply(l_trig_name);
740 end if;
741
742 log(c_module, 'PROCEDURE', 'end');
743 end CREATE_SYNC;
744
745
746 /*
747 ** --------------------------------------------------------------------
748 ** Database Preparation APIs - Public
749 ** --------------------------------------------------------------------
750 */
751
752
753 /*
754 ** Upgrade seed data table to Editioned Data Storage
755 **
756 ** Table can only be upgraded
757 ** - Table must be Effectively Editioned (have an EV cover)
758 ** - Table must not contain a LONG column
759 ** - In the Patch Edition, Table must be new (not visible in the run edition)
760 **
761 ** Conversion process:
762 ** 1) Disable product team triggers
763 ** 2) Add ZD_EDITION_NAME column
764 ** 3) Add ZD_EDITION_NAME column to all Unique indexes
765 ** 4) Create Trigger to Populate ZD_EDITION_NAME
766 ** 5) Create VPD Policy and Function to strict access to a specfic PARTITION of the table
767 ** 6) Enable product team triggers
768 **
769 ** Note: X_MODE parameter is currently unused
770 */
771 procedure UPGRADE(
772 X_TABLE_NAME in varchar2,
773 X_MODE in varchar2 default 'CURRENT')
774 is
775 C_MODULE varchar2(80) := c_package||'upgrade';
776 L_EDITION varchar2(30);
777 L_TABLE_OWNER varchar2(30);
778 L_TABLE_NAME varchar2(30);
779 L_LOCK_HANDLE varchar2(128) := null;
780 L_SAVE_BODY varchar2(32000);
781 V_PARALLEL number;
782 L_EXISTS varchar2(1);
783 begin
784 log(c_module, 'PROCEDURE', 'begin: '||x_table_name);
785 translate_synonym(c_module, x_table_name, false, l_table_owner, l_table_name);
786
787 -- If database is not editioned, save DDL for later execution
788 if ad_zd.is_editions_enabled = 'N' then
789 -- get parallel servers info
790 select to_number(value) into v_parallel
791 from v$parameter where name='parallel_max_servers';
792
793 ad_zd_parallel_exec.load('UPGRADE_SEED',
794 'begin '||ad_zd.apps_schema||'.ad_zd_seed.upgrade('''||x_table_name||'''); end;',
795 true);
796 ad_zd_parallel_exec.load('COLLECT_STATS',
797 'begin '||ad_zd.apps_schema||'.fnd_stats.gather_table_stats('''||l_table_owner||''','''||x_table_name||''', 100, '||v_parallel||'); end;',
798 true);
799 commit;
800 log(c_module, 'PROCEDURE', 'end');
801 return;
802 end if;
803
804 l_edition := ad_zd.get_edition;
805
806 -- Table Must have EV
807 if ad_zd_table.ev_exists(l_table_owner, l_table_name) = 'N' then
808 error(c_module, 'Table must be upgraded for editioning first: '||x_table_name);
809 end if;
810
811 -- In Patch Edition, table must be new (not visible in Run Edition)
812 if ad_zd.get_edition_type = 'PATCH' then
813 begin
814 -- check if synonym exists in the run edition
815 select 'Y' into l_exists from dual
816 where exists
817 ( select syn.object_name from dba_objects_ae syn
818 where syn.owner = ad_zd.apps_schema
819 and syn.object_name = x_table_name
820 and syn.object_type = 'SYNONYM'
821 and syn.edition_name =
822 ( select max(ed.edition_name) from dba_objects_ae ed
823 where ed.owner = syn.owner
824 and ed.object_name = syn.object_name
825 and ed.edition_name < ad_zd.get_edition ) );
826 error(c_module, 'Cannot upgrade existing table from Patch Edition: '
827 ||x_table_name);
828 exception
829 when no_data_found then
830 null;
831 end;
832 end if;
833
834 -- LONG column not supported
835 begin
836 -- Check if long column exists
837 select 'Y' into l_exists from dual
838 where exists
839 ( select column_name
840 from dba_tab_columns
841 where owner = l_table_owner
842 and table_name = l_table_name
843 and data_type = 'LONG' );
844
848 null;
845 error(c_module, 'Table with long column not supported: '||x_table_name);
846 exception
847 when no_data_found then
849 end;
850
851 -- Cannot re-upgrade existing seed data table,
852 -- but ok to upgrade new table with only ZD_EDITION_NAME column
853 if (ad_zd_table.is_seed(l_table_owner, l_table_name) = 'Y') then
854 -- Check if guard trigger exists
855 begin
856 select 'Y' into l_exists
857 from user_triggers
858 where trigger_name = ad_zd_seed.eds_trigger(l_table_name)
859 and table_owner = l_table_owner
860 and table_name = ad_zd_table.ev_view(l_table_name);
861
862 log(c_module, 'WARNING', 'Cannot re-upgrade seed data table: '||x_table_name);
863 return;
864 exception
865 when no_data_found then
866 null;
867 end;
868 end if;
869
870 -- Get lock to ensure only one upgrade is running for this table
871 l_lock_handle := acquire_lock(c_module||'.'||x_table_name);
872
873 -- begin block to release lock on an excepton
874 begin
875 log(c_module, 'EVENT', 'Upgrade Table: '||l_table_owner||'.'||l_table_name);
876
877 alter_triggers(l_table_owner, l_table_name, 'DISABLE');
878 create_zd_column(l_table_owner, l_table_name, l_edition);
879 ad_zd_table.patch(l_table_owner, l_table_name);
880 fix_indexes(l_table_owner, l_table_name);
881 create_guard(l_table_owner, l_table_name, l_edition, l_save_body);
882 create_policy(l_table_owner, l_table_name, l_edition);
883 alter_triggers(l_table_owner, l_table_name, 'ENABLE');
884 commit;
885
886 exception when others then
887 -- release lock and rethrow exception
888 log(c_module, 'ERROR', 'TABLE: '||x_table_name||', CODE: '||sqlcode||', MESSAGE: "'||sqlerrm||'"');
889 log(c_module, 'ERROR', 'TABLE: '||x_table_name||', BACKTRACE: '||dbms_utility.format_error_backtrace);
890 -- TODO: reverse actions
891 release_lock(l_lock_handle);
892 raise;
893 end;
894
895 release_lock(l_lock_handle);
896 log(c_module, 'PROCEDURE', 'end');
897 end UPGRADE;
898
899
900
901 /*
902 ** Revert back the changes done for Editioned Data Storage
903 **
904 ** Conversion process:
905 ** 1) Remove the ZD_EDITION_NAME column from Unique indexes
906 ** 2) Drop the VPD Policy and Function
907 ** 3) Drop the FCET for data synchronization
908 ** 4) Drop the Trigger to Populate ZD_EDITION_NAME
909 ** 5) Mark the ZD_EDITION_NAME column unused
910 ** 6) Call ad_zd_table.upgrade to refresh EV and synonyms
911 ** 7) Drop SV
912 */
913 procedure DOWNGRADE(X_TABLE_NAME in varchar2)
914 is
915 C_MODULE varchar2(80) := c_package||'downgrade';
916 L_TABLE_OWNER varchar2(30);
917 L_TABLE_NAME varchar2(30);
918 L_STMT varchar2(32767);
919 L_FCET_NAME varchar2(30);
920 L_TRIG_NAME varchar2(30);
921 L_EDITIONS_ENABLED varchar2(1);
922 L_DDL_STMTS sys.ku$_ddls;
923
924 NO_POLICY_FOUND EXCEPTION;
925 PRAGMA EXCEPTION_INIT(NO_POLICY_FOUND, -28102);
926 NO_OBJECT_FOUND EXCEPTION;
927 PRAGMA EXCEPTION_INIT(NO_OBJECT_FOUND, -4043);
928 NO_TRIGGER_FOUND EXCEPTION;
929 PRAGMA EXCEPTION_INIT(NO_TRIGGER_FOUND, -4080);
930 NO_COLUMN_FOUND EXCEPTION;
931 PRAGMA EXCEPTION_INIT(NO_COLUMN_FOUND, -904);
932 DUPLICATE_INDEX EXCEPTION;
933 PRAGMA EXCEPTION_INIT(DUPLICATE_INDEX, -1408);
934
935 cursor C_IND_CUR (x_tab_owner varchar2, x_tab_name varchar2) is
936 select i.owner, i.index_name
937 from dba_indexes i
938 where i.table_owner = x_tab_owner
939 and i.table_name = x_tab_name
940 and i.uniqueness = 'UNIQUE'
941 and i.index_type <> 'LOB'
942 and exists ( select null
943 from dba_ind_columns c
944 where c.index_owner = i.owner
945 and c.index_name = i.index_name
946 and c.column_name = 'ZD_EDITION_NAME' );
947
948 cursor C_CONSTRAINT_CUR (x_tab_owner varchar2, x_tab_name varchar2) is
949 select constraint_name, constraint_type
950 from dba_constraints o
951 where owner = x_tab_owner
952 and table_name = x_tab_name
953 and constraint_type in ('U','P')
954 and exists ( select null
955 from dba_cons_columns i
956 where i.owner = o.owner
957 and i.constraint_name = o.constraint_name
958 and i.column_name = 'ZD_EDITION_NAME' );
959
960 cursor C_POLICY_CUR (x_tab_owner varchar2, x_tab_name varchar2) is
961 select object_owner, object_name, policy_name, function from dba_policies
962 where object_owner = x_tab_owner
963 and object_name in (x_tab_name, ad_zd_table.ev_view(x_tab_name))
967 log(c_module, 'PROCEDURE', 'begin: '||x_table_name);
964 and upper(policy_name) like '%ZD_SEED';
965
966 begin
968
969 l_editions_enabled := ad_zd.is_editions_enabled;
970 translate_synonym(c_module, x_table_name, false, l_table_owner, l_table_name);
971
972 log(c_module, 'EVENT', 'Downgrade Table: '||l_table_owner||'.'||l_table_name);
973
974 -- Cleanup data copies
975 if (l_editions_enabled = 'Y') then
976 if ad_zd_table.is_seed(l_table_owner, l_table_name) = 'Y' then
977 ad_zd_seed.cleanup(l_table_name);
978 end if;
979 end if;
980
981 -- Fix Constraints
982 for crec in c_constraint_cur(l_table_owner,l_table_name) loop
983 log(c_module, 'STATEMENT', 'Fixing Constraint: '||crec.constraint_name);
984 lock_table(l_table_owner, l_table_name, c_module);
985 l_ddl_stmts := fetch_multiple_ddls(l_table_owner, l_table_name, 'CONSTRAINT', l_table_owner, crec.constraint_name);
986
987 if crec.constraint_type = 'U' then
988 l_stmt := 'alter table "'||l_table_owner||'"."'||l_table_name||'" drop constraint '||crec.constraint_name||' drop index';
989 else
990 l_stmt := 'alter table "'||l_table_owner||'"."'||l_table_name||'" drop constraint '||crec.constraint_name||' cascade drop index';
991 end if;
992 exec(l_stmt, c_module, true);
993
994 for i in 1 .. l_ddl_stmts.count loop
995 l_stmt := dbms_lob.substr(l_ddl_stmts(i).ddltext);
996 if i=1 then
997 l_stmt := regexp_replace(l_stmt, '[, ]*"ZD_EDITION_NAME"[, ]*', '', 1, 1);
998 end if;
999 exec(l_stmt, c_module, true);
1000 end loop;
1001 end loop;
1002
1003 -- Fix Indexes
1004 for irec in c_ind_cur(l_table_owner,l_table_name) loop
1005 log(c_module, 'STATEMENT', 'Fixing Index: '||irec.owner||'.'||irec.index_name);
1006 lock_table(l_table_owner, l_table_name, c_module);
1007 l_ddl_stmts := fetch_multiple_ddls(l_table_owner, l_table_name, 'INDEX', irec.owner, irec.index_name);
1008
1009 l_stmt := 'drop index "'||irec.owner||'"."'||irec.index_name||'"';
1010 exec(l_stmt, c_module, true);
1011
1012 for i in 1 .. l_ddl_stmts.count loop
1013 begin
1014 l_stmt := dbms_lob.substr(l_ddl_stmts(i).ddltext);
1015 if i=1 then
1016 l_stmt := regexp_replace(l_stmt, '[, ]*"ZD_EDITION_NAME"[, ]*', '', 1, 1);
1017 end if;
1018 exec(l_stmt, c_module);
1019 exception
1020 when duplicate_index then
1021 log(c_module, 'STATEMENT', 'Ignored: Such column list already indexed: '||irec.index_name);
1022 when others then
1023 log(c_module, 'ERROR', 'Error fixing '||irec.index_name||': '||SQLERRM);
1024 end;
1025 end loop;
1026 end loop;
1027
1028 -- Drop EDS Policy
1029 for policyrec in c_policy_cur(l_table_owner, l_table_name) loop
1030 begin
1031 log(c_module, 'STATEMENT', 'Dropping EDS Policy: '
1032 ||policyrec.object_owner||','||policyrec.object_name||','||policyrec.policy_name);
1033 dbms_rls.drop_policy(
1034 object_schema => policyrec.object_owner,
1035 object_name => policyrec.object_name,
1036 policy_name => policyrec.policy_name);
1037 exception when no_policy_found then
1038 log(c_module, 'STATEMENT', 'Ignored: Policy not found');
1039 end;
1040 begin
1041 l_stmt := 'drop function '|| '"'||ad_zd.apps_schema||'"."'||policyrec.function||'"';
1042 exec(l_stmt, c_module, true);
1043 end;
1044 end loop;
1045
1046 -- Drop EDS Sync Trigger (old style)
1047 l_fcet_name := replace(l_table_name, '_','-');
1048 l_stmt := 'drop trigger '|| '"'||ad_zd.apps_schema||'"."'||l_fcet_name||'"';
1049 exec(l_stmt, c_module, true);
1050
1051 -- Drop EDS Sync Trigger (new style)
1052 l_fcet_name := ad_zd_seed.eds_fcet(x_table_name);
1053 l_stmt := 'drop trigger '|| '"'||ad_zd.apps_schema||'"."'||l_fcet_name||'"';
1054 exec(l_stmt, c_module, true);
1055
1056 -- Drop EDS Guard Trigger (old style)
1057 l_trig_name := lower(l_table_name);
1058 l_stmt := 'drop trigger '|| '"'||ad_zd.apps_schema||'"."'||l_trig_name||'"';
1059 exec(l_stmt, c_module, true);
1060
1061 -- Drop EDS Guard Trigger (new style)
1062 l_trig_name := ad_zd_seed.eds_trigger(x_table_name);
1063 l_stmt := 'drop trigger '|| '"'||ad_zd.apps_schema||'"."'||l_trig_name||'"';
1064 exec(l_stmt, c_module, true);
1065
1066 -- Mark EDS Striping Column unused
1067 l_stmt := 'alter table "'||l_table_owner||'"."'||l_table_name||'" set unused (ZD_EDITION_NAME)';
1068 exec(l_stmt, c_module, true);
1069
1070 -- Regenerate editioning view
1071 if (l_editions_enabled = 'Y') then
1072 ad_zd_table.patch(l_table_owner, l_table_name);
1073 end if;
1074
1075 -- Drop SV
1076 exec('drop view '||l_table_owner||'.'||substrb(x_table_name,1,29)||'$', c_module, true);
1077
1078 commit;
1079 log(c_module, 'PROCEDURE', 'end');
1080
1081 exception when others then
1082 log(c_module, 'ERROR', 'TABLE: '||x_table_name||', CODE: '||sqlcode||', MESSAGE: "'||sqlerrm||'"');
1083 log(c_module, 'ERROR', 'TABLE: '||x_table_name||', BACKTRACE: '||dbms_utility.format_error_backtrace);
1084 raise;
1085 end DOWNGRADE;
1086
1087
1088 /*
1089 ** --------------------------------------------------------------------
1090 ** Patch Event APIs - Public
1091 ** --------------------------------------------------------------------
1092 */
1093
1094
1095 /*
1096 ** Patch seed data table
1097 ** - regenerates sych trigger to new table structure
1098 */
1099 procedure PATCH(X_TABLE_OWNER in varchar2, X_TABLE_NAME in varchar2)
1100 is
1101 C_MODULE varchar2(80) := c_package||'patch';
1102 begin
1103 log(c_module, 'PROCEDURE', 'begin: '||x_table_owner||'.'||x_table_name);
1107 log(c_module, 'PROCEDURE', 'end-noop');
1104
1105 -- If not in patch edition, then do nothing
1106 if ad_zd.get_edition_type <> 'PATCH' then
1108 return;
1109 end if;
1110
1111 -- If not prepared then do nothing
1112 if not is_prepared(x_table_name) then
1113 log(c_module, 'PROCEDURE', 'end-noop');
1114 return;
1115 end if;
1116
1117 -- Recreate sync trigger, no copy
1118 create_sync(x_table_owner, x_table_name, false);
1119
1120 log(c_module, 'PROCEDURE', 'end');
1121 end PATCH;
1122
1123
1124 /*
1125 ** Prepare Table for Seed data patching
1126 ** Product teams need to call this API once for every
1127 ** seed data table a loader will insert/delete/update
1128 ** data in.
1129 **
1130 ** 1). Create new trigger to populate ZD_EDITION_NAME
1131 ** 2). Create a copy of the data
1132 ** 3). Create new VPD POLICY to restrict sql to that new data
1133 ** 4). Create synchronization FCET
1134 ** 5). Commit
1135 **
1136 */
1137 procedure PREPARE(X_TABLE_NAME in varchar2)
1138 is
1139 C_MODULE varchar2(80) := c_package||'prepare';
1140 L_TABLE_OWNER varchar2(30);
1141 L_TABLE_NAME varchar2(30);
1142 L_EDITION varchar2(30) := sys_context('userenv', 'current_edition_name');
1143 L_STMT varchar2(500);
1144 L_TEXT varchar2(4000);
1145 L_ZD_COL varchar2(30);
1146 L_LOCK_HANDLE varchar2(128) := null;
1147 L_SAVE_BODY varchar2(32000);
1148
1149 cursor C_FUNC_CUR(x_owner varchar2, x_function varchar2, x_edition varchar2) is
1150 select text
1151 from dba_source
1152 where owner = x_owner
1153 and name = x_function
1154 and type = 'FUNCTION'
1155 and instr(text,x_edition) <> 0;
1156
1157 begin
1158
1159 -- If not in patch edition, then do nothing
1160 if ad_zd.get_edition_type(l_edition) <> 'PATCH' then
1161 return;
1162 end if;
1163
1164 -- If prepare is repeated for the same table, then do nothing
1165 if is_prepared(x_table_name) then
1166 return;
1167 end if;
1168
1169 log(c_module, 'PROCEDURE', 'begin: '||x_table_name);
1170 translate_synonym(c_module, x_table_name, true, l_table_owner, l_table_name);
1171
1172 -- Get lock to ensure only one prepare is running for this table
1173 l_lock_handle := acquire_lock(c_module||'.'||x_table_name);
1174
1175 -- begin block to release lock on an exception
1176 begin
1177 -- If table is not yet prepared, then prepare it
1178 open c_func_cur(ad_zd.apps_schema, ad_zd_seed.eds_function(x_table_name), l_edition);
1179 fetch c_func_cur INTO l_text;
1180 if (c_func_cur%notfound) then
1181
1182 log(c_module, 'EVENT', 'Prepare Table: '||l_table_owner||'.'||l_table_name);
1183
1184 alter_triggers(l_table_owner, l_table_name, 'DISABLE');
1185 create_guard(l_table_owner, l_table_name, l_edition, l_save_body);
1186
1187 -- create sync trigger and copy table data, recover if failure
1188 begin
1189 create_sync(l_table_owner, l_table_name, true);
1190 exception when others then
1191 log(c_module, 'EVENT', 'Prepare Failure, reversing actions');
1192 create_guard(l_table_owner, l_table_name, NULL, l_save_body);
1193 exec('drop trigger '||ad_zd.apps_schema||'."'||ad_zd_seed.eds_fcet(l_table_name)||'"', c_module, TRUE);
1194 alter_triggers(l_table_owner,l_table_name,'ENABLE');
1195 raise;
1196 end;
1197
1198 create_policy(l_table_owner,l_table_name,l_edition);
1199 alter_triggers(l_table_owner,l_table_name,'ENABLE');
1200
1201 -- Trigger regen for affected MVs
1202 ad_zd_mview.patch(l_table_owner, l_table_name);
1203 commit; /* we are only going to commit if everything was created sucessfully */
1204 end if;
1205 close c_func_cur;
1206
1207 -- catch exception, release lock and rethrow exception
1208 exception when others then
1209 log(c_module, 'ERROR', 'TABLE: '||x_table_name||', CODE: '||sqlcode||', MESSAGE: "'||sqlerrm||'"');
1210 log(c_module, 'ERROR', 'TABLE: '||x_table_name||', BACKTRACE: '||dbms_utility.format_error_backtrace);
1211 release_lock(l_lock_handle);
1212 raise;
1213 end;
1214
1215 release_lock(l_lock_handle);
1216 log(c_module, 'PROCEDURE', 'end');
1217 end PREPARE;
1218
1219
1220 /*
1221 ** Cutover
1222 * NOTE: No action in current implementation, just here for consistancy
1223 */
1224 procedure CUTOVER
1225 is
1226 C_MODULE varchar2(80) := c_package||'cutover';
1227 begin
1228 null;
1229 end CUTOVER;
1230
1231
1232 /*
1233 ** Cleanup seed table (internal)
1234 */
1235 procedure CLEANUP_TABLE(X_TABLE_OWNER in varchar2, X_TABLE_NAME in varchar2) is
1236 C_MODULE varchar2(80) := c_package||'cleanup_table';
1237 L_SEED_EDITION varchar2(30);
1238 L_STMT varchar2(1000);
1239 begin
1240
1241 -- Get current edition of seed data
1242 begin
1243 l_stmt :=
1244 'select zd_edition_name'||
1245 ' from '||x_table_owner||'.'||ad_zd_table.ev_view(x_table_name)||
1246 ' where rownum=1';
1247 execute immediate l_stmt into l_seed_edition;
1248 exception
1249 when no_data_found then
1250 -- TODO only log this warning if the base table actually has rows
1251 log(c_module, 'WARNING', 'No seed data found in table '||x_table_name||', cleanup skipped');
1252 return;
1253 end;
1254
1255 -- Cleanup unused seed data editions
1256 log(c_module, 'EVENT', 'Cleanup unused seed data editions: '||x_table_owner||'.'||x_table_name);
1257 begin
1258 l_stmt :=
1259 'delete from '||x_table_owner||'.'||x_table_name||
1260 ' where zd_edition_name <> '''||l_seed_edition||'''';
1261 exec(l_stmt, c_module);
1262 exception when others then
1263 log(c_module, 'ERROR', 'TABLE: '||x_table_name||', CODE: '||sqlcode||', MESSAGE: "'||sqlerrm||'"');
1264 log(c_module, 'ERROR', 'TABLE: '||x_table_name||', BACKTRACE: '||dbms_utility.format_error_backtrace);
1265 raise;
1266 end;
1267 commit;
1268 end CLEANUP_TABLE;
1269
1270
1271 /*
1272 ** Cleanup - delete old seed data rows
1273 **
1274 ** NOTE: old triggers and policy functions will be handled by
1275 ** central edition manager cleanup
1276 */
1277 procedure CLEANUP(X_TABLE_NAME in varchar2 default NULL)
1278 is
1279 C_MODULE varchar2(80) := c_package||'cleanup';
1280 L_EDITION varchar2(30) := sys_context('userenv', 'current_edition_name');
1281 L_TABLE_OWNER varchar2(30);
1282 L_TABLE_NAME varchar2(30);
1283 type CHAR_TAB is table of varchar2(32) index by binary_integer;
1284 L_TABLE_NAME_TAB char_tab;
1285 L_TABLE_OWNER_TAB char_tab;
1286 L_IDX number;
1287
1288 -- select the list of tables that were patched
1289 -- in the current RUN EDITION
1290 cursor C_PATCHED_SEED_TABLES is
1291 select col.owner, col.table_name
1292 from
1293 dba_tab_columns col
1294 , user_objects obj
1295 where col.owner in
1296 ( select oracle_username from system.fnd_oracle_userid
1297 where read_only_flag in ('A','E') )
1298 and col.table_name not like '%#'
1299 and col.column_name = 'ZD_EDITION_NAME'
1300 and obj.object_name = ad_zd_seed.eds_function(col.table_name)
1301 and obj.object_type = 'FUNCTION'
1302 and obj.edition_name = sys_context('userenv', 'current_edition_name')
1303 and obj.edition_name <> 'ORA$BASE'
1304 and exists
1305 ( select src.line from user_source src
1306 where src.name = obj.object_name
1307 and src.type = obj.object_type
1308 and src.text like '%'||obj.edition_name||'%' );
1309
1310 begin
1311 log(c_module, 'PROCEDURE', 'begin: '|| nvl(X_TABLE_NAME, 'NULL'));
1312
1313 if ad_zd.get_edition('PATCH') is not null then
1314 error(c_module, 'Cannot cleanup while Patch Edition exists');
1315 end if;
1316
1317 if ad_zd.get_edition_type(l_edition) <> 'RUN' then
1318 error(c_module, 'Cleanup can only execute in the Run Edition');
1319 end if;
1320
1321 if x_table_name is not null then
1322 -- ignore missing synonym (table must have been dropped)
1323 begin
1324 select s.table_owner, s.table_name
1325 into l_table_owner, l_table_name
1326 from dba_synonyms s
1327 where s.owner = ad_zd.apps_schema
1328 and s.synonym_name = x_table_name;
1329 exception when no_data_found then
1330 log(c_module, 'STATEMENT', 'Ignored: Synonym does not exist: '
1331 ||x_table_name);
1332 return;
1333 end;
1334
1335 -- cleanup explicit table
1336 translate_synonym(c_module, x_table_name, true, l_table_owner, l_table_name);
1337 cleanup_table(l_table_owner, l_table_name);
1338 else
1339 -- cleanup all recently patched tables
1340
1341 l_table_name_tab.delete;
1342 l_table_owner_tab.delete;
1343 l_idx := 0;
1344 for trec in c_patched_seed_tables loop
1345 l_table_name_tab(l_idx) := trec.table_name;
1346 l_table_owner_tab(l_idx) := trec.owner;
1347 l_idx:=l_idx+1;
1348 end loop;
1349
1350 for i in 0 .. l_idx-1 loop
1351 cleanup_table(l_table_owner_tab(i), l_table_name_tab(i));
1352 end loop;
1353 end if;
1354
1355 log(c_module, 'PROCEDURE', 'end');
1356 end CLEANUP;
1357
1358
1359 /*
1360 ** Abort
1361 */
1362 procedure ABORT
1363 is
1364 C_MODULE varchar2(80) := c_package||'abort';
1365 L_STMT varchar2(1000);
1366
1367 cursor C_PREPARED_SEED_TABLES is
1368 select col.owner, col.table_name
1369 from
1370 dba_tab_columns col
1371 , user_objects_ae obj
1372 where col.owner in
1373 ( select oracle_username from system.fnd_oracle_userid
1374 where read_only_flag in ('A','E') )
1375 and col.table_name not like '%#'
1376 and col.column_name = 'ZD_EDITION_NAME'
1377 and obj.object_name = ad_zd_seed.eds_function(col.table_name)
1378 and obj.object_type = 'FUNCTION'
1379 and obj.edition_name > sys_context('userenv', 'current_edition_name')
1380 and exists
1381 ( select src.text from user_source_ae src
1382 where src.edition_name = obj.edition_name
1383 and src.name = obj.object_name
1384 and src.type = obj.object_type
1385 and src.text like '%'||obj.edition_name||'%' );
1386
1387 begin
1388 log(c_module, 'PROCEDURE', 'begin: no parameter(s)');
1389
1390 if ad_zd.get_edition_type <> 'RUN' then
1391 error(c_module, 'Abort can only execute in the Run Edition');
1392 end if;
1393
1394 for trec in c_prepared_seed_tables loop
1395 log(c_module, 'STATEMENT', 'Store cleanup action for seed data table: '||trec.table_name);
1396 l_stmt := 'begin ad_zd_seed.cleanup('''||trec.table_name||'''); end;';
1397 ad_zd.load_ddl('CLEANUP', l_stmt);
1398 end loop;
1399
1400 log(c_module, 'PROCEDURE', 'end');
1401 end ABORT;
1402
1403
1404 END AD_ZD_SEED;