· 8 years ago · Feb 14, 2018, 07:52 AM
1CREATE OR REPLACE PACKAGE pkg_dt_mkv IS
2
3 type tbl_nc_objects is table of nc_objects%rowtype;
4 type tbl_nc_params is table of nc_params%rowtype;
5 type tbl_nc_references is table of nc_references%rowtype;
6
7 type affected_rec_stat is record (
8 objs pls_integer,
9 params pls_integer,
10 refs pls_integer
11 );
12
13 gv_job_prefix CONSTANT VARCHAR2(100) := 'MKV_TRANSFORMATION_JOB';
14
15
16 -- Return value is current session chunk_id
17 FUNCTION get_chunk_id(p_force_new pls_integer default 0) RETURN NUMBER;
18
19
20 -- Lock for bulk migration; autonomous_transaction
21 -- for on-demand (when p_instance_object_id presented) return value = instance transformation status:
22 -- -2 - subscription not found in fullscope (object_id or source_sid)
23 -- -1 - unexpected error while locking subscription
24 -- 0 - ready for transformation
25 -- 1 - transformation is in progress
26 -- 2 - last transformed finished successfully
27 -- 3 - last transformation failed
28 -- 4 - last transformation finished with warnings
29 --
30 -- For bulk (p_instance_object_id is null) return value always 0
31 FUNCTION lock_for_transformation(p_instance_object_id IN NUMBER DEFAULT NULL) RETURN PLS_INTEGER;
32
33 -- Lock for BNG migration; autonomous_transaction
34 PROCEDURE lock_for_migration(p_instance_object_ids IN arrayofnumbers);
35
36 -- Launch transformation actions; autonomous_transaction
37 -- p_out_errcount - returns number of failed subscriptions
38 -- p_out_objects - returns objects affected by transformation DML
39 PROCEDURE transform(p_out_errcount OUT PLS_INTEGER, p_out_objects OUT arrayofnumbers, p_out_fallouts OUT arrayofstrings);
40
41
42 -- Setting fallout for list of subscriptions, autonomous_transaction
43 -- * put message for all listed subscriptions into nc_mkv_fallouts
44 -- * if p_major=1 then set status 3 (Failed) for listed subscription in nc_mkv_fullscope
45 -- * only root_object_id (subscriptiton instances id) existing in nc_mkv_curscope allowed
46 PROCEDURE set_fallout(p_message VARCHAR2, p_object_ids IN arrayofnumbers, p_major PLS_INTEGER DEFAULT 1, p_chunk_id IN NUMBER DEFAULT NULL);
47
48 -- the same for only one subscription as input instead of list
49 PROCEDURE set_fallout(p_message VARCHAR2, p_object_id IN NUMBER DEFAULT NULL, p_major PLS_INTEGER DEFAULT 1, p_chunk_id IN NUMBER DEFAULT NULL);
50
51
52 -- Procedure called by MKV transformation oracle jobs
53 PROCEDURE bulk_job_internals(p_job_number PLS_INTEGER);
54
55 -- Procedure called by MKV reset oracle job
56 PROCEDURE reset_job_internals;
57
58 -- FORALL insert routines with DML error logging
59 PROCEDURE bulk_insert_objs(p_objs tbl_nc_objects);
60 PROCEDURE bulk_insert_params(p_params tbl_nc_params);
61 PROCEDURE bulk_insert_refs(p_refs tbl_nc_references);
62
63 PROCEDURE create_chunk(p_chunk_id NUMBER DEFAULT NULL);
64END;
65/
66
67
68CREATE OR REPLACE PACKAGE BODY pkg_dt_mkv IS
69
70 type rec_locked_list is record (
71 object_id number(20),
72 object_type_id number(20),
73 source_sid number(20)
74 );
75
76 type t_locked_list is table of rec_locked_list index by pls_integer;
77
78 c_holder_ond varchar2(3) := 'OND';
79 c_holder_blk varchar2(3) := 'BLK';
80 c_holder_mig varchar2(3) := 'BNG';
81
82 gv_holder varchar2(3) := null; -- BLK/OND/BNG
83 gv_chunk_id number(20) := null; -- same as session_id, creating for both: bulk and on-demand
84 gv_err_list arrayofnumbers := arrayofnumbers();
85
86 /*
87 Current RC configurations
88 */
89
90 PROCEDURE external_fill_curscope_objs IS
91 v_cnt pls_integer;
92 BEGIN
93 -- if scope is empty then exit immediately
94 select count(*) into v_cnt from nc_mkv_curscope where rownum = 1;
95 if v_cnt = 0 then return; end if;
96
97 pkg_dt_mkv172.fill_curscope_objs;
98 END;
99
100 PROCEDURE external_validate_minor IS
101 v_cnt pls_integer;
102 BEGIN
103 -- if scope is empty then exit immediately
104 select count(*) into v_cnt from nc_mkv_curscope where rownum = 1;
105 if v_cnt = 0 then return; end if;
106
107 pkg_dt_mkv172.validate_minor;
108 END;
109
110 PROCEDURE external_validate_major IS
111 v_cnt pls_integer;
112 BEGIN
113 -- if scope is empty then exit immediately
114 select count(*) into v_cnt from nc_mkv_curscope where rownum = 1;
115 if v_cnt = 0 then return; end if;
116
117 pkg_dt_mkv172.validate_major;
118 END;
119
120 PROCEDURE external_transform(p_out_objects IN OUT arrayofnumbers, p_out_affected_stat IN OUT affected_rec_stat) IS
121 v_cnt pls_integer;
122 BEGIN
123 -- if scope is empty then exit immediately
124 select count(*) into v_cnt from nc_mkv_curscope where rownum = 1;
125 if v_cnt = 0 then return; end if;
126
127 -- transformation
128 pkg_dt_mkv172.transform(gv_chunk_id, p_out_objects, p_out_affected_stat);
129 END;
130 /*
131 End of Current RC configurations
132 */
133
134
135 /*
136 MKV Engine implementation
137 * Transaction control
138 * Chunks management
139 * Retry failed chunks
140 */
141
142 PROCEDURE bulk_insert_objs(p_objs tbl_nc_objects) IS
143 v_err_code number;
144 v_err_indx number;
145 v_err_str varchar2(1024);
146 BEGIN
147 forall i in 1..p_objs.count save exceptions insert into nc_objects values p_objs(i);
148 EXCEPTION
149 when others then
150 if sqlcode = -24381 /* error(s) in array DML */ then
151 for indx in 1..sql%bulk_exceptions.count loop
152 v_err_indx := sql%bulk_exceptions(indx).error_index;
153 v_err_code := sql%bulk_exceptions(indx).error_code;
154 v_err_str := sqlerrm(v_err_code);
155
156 insert into nc_mkv_dml_err_objs(ora_err_number$, ora_err_mesg$, ora_err_rowid$, ora_err_optyp$, ora_err_tag$, object_id, parent_id, object_type_id, object_class_id, project_id, picture_id, name, description, attr_schema_id, order_number, source_object_id, version)
157 values(v_err_code, v_err_str, null, 'I', 'Bulk insert error', p_objs(indx).object_id, p_objs(indx).parent_id, p_objs(indx).object_type_id, p_objs(indx).object_class_id, p_objs(indx).project_id, p_objs(indx).picture_id, p_objs(indx).name, p_objs(indx).description, p_objs(indx).attr_schema_id, p_objs(indx).order_number, p_objs(indx).source_object_id, p_objs(indx).version);
158 end loop;
159 else raise; end if;
160 END;
161
162 PROCEDURE bulk_insert_params(p_params tbl_nc_params) IS
163 v_err_code number;
164 v_err_indx number;
165 v_err_str varchar2(1024);
166 BEGIN
167 forall i in 1..p_params.count save exceptions insert into nc_params values p_params(i);
168 EXCEPTION
169 when others then
170 if sqlcode = -24381 /* error(s) in array DML */ then
171 for indx in 1..sql%bulk_exceptions.count loop
172 v_err_indx := sql%bulk_exceptions(indx).error_index;
173 v_err_code := sql%bulk_exceptions(indx).error_code;
174 v_err_str := sqlerrm(v_err_code);
175
176 insert into nc_mkv_dml_err_params(ora_err_number$, ora_err_mesg$, ora_err_rowid$, ora_err_optyp$, ora_err_tag$, attr_id, object_id, attr_access_type, value, list_value_id, show_order, date_value, priority)
177 values(v_err_code, v_err_str, null, 'I', 'Bulk insert error', p_params(indx).attr_id, p_params(indx).object_id, p_params(indx).attr_access_type, p_params(indx).value, p_params(indx).list_value_id, p_params(indx).show_order, p_params(indx).date_value, p_params(indx).priority);
178 end loop;
179 else raise; end if;
180 END;
181
182 PROCEDURE bulk_insert_refs(p_refs tbl_nc_references) IS
183 v_err_code number;
184 v_err_indx number;
185 v_err_str varchar2(1024);
186 BEGIN
187 forall i in 1..p_refs.count save exceptions insert into nc_references values p_refs(i);
188 EXCEPTION
189 when others then
190 if sqlcode = -24381 /* error(s) in array DML */ then
191 for indx in 1..sql%bulk_exceptions.count loop
192 v_err_indx := sql%bulk_exceptions(indx).error_index;
193 v_err_code := sql%bulk_exceptions(indx).error_code;
194 v_err_str := sqlerrm(v_err_code);
195
196 insert into nc_mkv_dml_err_refs(ora_err_number$, ora_err_mesg$, ora_err_rowid$, ora_err_optyp$, ora_err_tag$, attr_id, reference, object_id, show_order, priority, attr_access_type)
197 values(v_err_code, v_err_str, null, 'I', 'Bulk insert error', p_refs(indx).attr_id, p_refs(indx).reference, p_refs(indx).object_id, p_refs(indx).show_order, p_refs(indx).priority, p_refs(indx).attr_access_type);
198 end loop;
199 else raise; end if;
200 END;
201
202 PROCEDURE reset_job_internals IS
203 v_fake PLS_INTEGER;
204 ar arrayofnumbers;
205 BEGIN
206 select /*+ leading(f) index(f XIF01NC_MKV_FULLSCOPE) use_nl(o) use_nl(c) */
207 f.object_id bulk collect into ar
208 from nc_mkv_fullscope f
209 ,nc_mkv_chunks_objs o
210 ,nc_mkv_chunks c
211 where f.status = 1 /* In Progress */
212 and o.object_id = f.object_id
213 and c.chunk_id = o.chunk_id
214 and c.start_date < sysdate - 1; -- in progress longer than 24 hours
215
216 if ar.count > 0 then
217 select -1 into v_fake from nc_objects where object_id = 9146238644413342544 /* Lock object for MKV */ for update;
218
219 update /*+ index(f XPKNC_MKV_FULLSCOPE) */ nc_mkv_fullscope f
220 set f.status = 0 /* Ready for transformation */
221 where f.status = 1 -- double check
222 and f.object_id in (select column_value as object_id from table(ar));
223
224 commit;
225 end if;
226 END;
227
228 PROCEDURE bulk_job_internals(p_job_number PLS_INTEGER) IS
229 v_thread_count PLS_INTEGER;
230 v_lock_res PLS_INTEGER;
231 v_err_count PLS_INTEGER;
232 v_scope_count PLS_INTEGER := -1;
233 v_objects_affected arrayofnumbers := arrayofnumbers();
234 v_fallouts arrayofstrings := arrayofstrings();
235 BEGIN
236 if p_job_number is null then return; end if;
237 if p_job_number <= 0 or p_job_number > 16 then return; end if;
238
239 LOOP
240 begin
241 select nvl(param_value, 0) into v_thread_count from nc_mkv_params where param_type = 'AMOUNT' and param_name = 'ACTIVE_THREADS';
242 exception when no_data_found then v_thread_count := 0;
243 end;
244
245 if v_thread_count < p_job_number then
246 return; -- exiting till next scheduled start (hourly by default)
247 end if;
248
249 v_lock_res := lock_for_transformation;
250
251 if v_lock_res = -1 then -- some shit happend
252 select object_id bulk collect into v_objects_affected from nc_mkv_curscope;
253 set_fallout('Unhandled ecxeption while MKV bulk job tried to lock subscriptions. Probably oracle package was recompiled during running job.', v_objects_affected, -1 /* log only, no status changes */);
254 end if;
255
256 select count(*) into v_scope_count from nc_mkv_curscope where rownum = 1;
257
258 if v_scope_count > 0 then
259 transform(v_err_count, v_objects_affected, v_fallouts);
260 commit;
261 else
262 exit; -- leave cycle if nothing left to transform in fullscope
263 end if;
264
265 END LOOP;
266 END bulk_job_internals;
267
268 FUNCTION get_chunk_id(p_force_new pls_integer default 0) RETURN NUMBER IS
269 BEGIN
270 IF gv_chunk_id IS NULL OR p_force_new = 1 THEN
271 gv_chunk_id := pkgutils.getid;
272 END IF;
273
274 RETURN gv_chunk_id;
275 END;
276
277 PROCEDURE del_curscope(p_object_ids arrayofnumbers) IS
278 BEGIN
279 delete nc_mkv_curscope where object_id in (select column_value from table(p_object_ids));
280 delete nc_mkv_curscope_objs where root_object_id in (select column_value from table(p_object_ids));
281 END;
282
283 PROCEDURE set_fallout(p_message VARCHAR2, p_object_ids arrayofnumbers, p_major PLS_INTEGER DEFAULT 1, p_chunk_id IN NUMBER DEFAULT NULL) IS
284 PRAGMA AUTONOMOUS_TRANSACTION;
285 v_cnt pls_integer := 0;
286 v_object_ids arrayofnumbers;
287 v_temp arrayofnumbers;
288 BEGIN
289 if p_object_ids is null then return; end if;
290 if p_object_ids.count = 0 then return; end if;
291
292 v_object_ids := p_object_ids;
293
294 if p_major >= 0 then
295 select count(*) into v_cnt from table(v_object_ids) x
296 where not exists (select null from nc_mkv_curscope c where c.object_id = x.column_value);
297
298 /*+ Provider change */
299 select source_sid bulk collect into v_temp from nc_mkv_fullscope where source_sid is not null and object_id in (select column_value from table(v_object_ids));
300 v_object_ids := v_object_ids multiset union distinct v_temp;
301
302 select object_id bulk collect into v_temp from nc_mkv_fullscope where source_sid in (select column_value from table(v_object_ids));
303 v_object_ids := v_object_ids multiset union distinct v_temp;
304 /*- Provider change */
305
306 update nc_mkv_fullscope
307 set status = decode(p_major
308 ,0 /* Minor */, 4 /* Transformation finished with warnings */
309 ,1 /* Major */, 3 /* Transformation failed */)
310 where object_id in (select column_value from table(v_object_ids));
311
312 if p_major = 1 then
313 gv_err_list := gv_err_list multiset union distinct v_object_ids;
314 end if;
315 end if;
316
317 insert into nc_mkv_fallouts(server_time, object_id, chunk_id, fallout_message, major)
318 select systimestamp, column_value, nvl(p_chunk_id, gv_chunk_id), p_message, p_major from table(v_object_ids);
319
320 commit;
321 EXCEPTION
322 WHEN OTHERS THEN rollback; raise;
323 END;
324
325 PROCEDURE set_fallout(p_message VARCHAR2, p_object_id IN NUMBER, p_major PLS_INTEGER DEFAULT 1, p_chunk_id IN NUMBER DEFAULT NULL) IS
326 ar arrayofnumbers := arrayofnumbers();
327 BEGIN
328 if p_object_id is null then
329 raise_application_error(-20001, 'Can not set fallout for NULL subscription');
330 end if;
331
332 ar.extend(1);
333 ar(1) := p_object_id;
334
335 set_fallout(p_message, ar, p_major, p_chunk_id);
336 END;
337
338
339 PROCEDURE lock_subscriptions(p_instance_object_ids IN arrayofnumbers, p_out_locked_list OUT t_locked_list, p_allowed_statuses IN arrayofnumbers, p_sql_text IN varchar2 default null) IS
340 PRAGMA AUTONOMOUS_TRANSACTION;
341 v_fake pls_integer;
342 v_instance_object_ids arrayofnumbers := arrayofnumbers();
343 BEGIN
344 select -1 into v_fake from nc_objects where object_id = 9146238644413342544 /* Lock object for MKV */ for update;
345
346 if p_sql_text is null then
347 v_instance_object_ids := p_instance_object_ids;
348 else
349 execute immediate p_sql_text bulk collect into v_instance_object_ids;
350 end if;
351
352 update nc_mkv_fullscope f
353 set status = 1 /* In progress */
354 where status in (select column_value from table(p_allowed_statuses)) -- doublecheck
355 and object_id in (select column_value from table(v_instance_object_ids))
356 returning object_id, object_type_id, source_sid bulk collect into p_out_locked_list;
357
358 commit;
359 END;
360
361 PROCEDURE lock_for_mkv(p_instance_object_ids IN arrayofnumbers, p_allowed_statuses IN arrayofnumbers, p_sql_text IN varchar2 default null) IS
362 v_locked_list t_locked_list;
363 BEGIN
364 lock_subscriptions(p_instance_object_ids, v_locked_list, p_allowed_statuses, p_sql_text); -- autonomous transaction
365
366 delete nc_mkv_curscope; -- DTSAGATE-5196 clean refill
367 -- delete nc_mkv_curscope_objs; no need because of commit in the end of procedure (GTT on commit delete rows)
368
369 forall i in 1..v_locked_list.count
370 insert into nc_mkv_curscope values v_locked_list(i);
371
372 -- PW case
373 insert into nc_mkv_curscope(object_id, object_type_id)
374 select object_id, object_type_id
375 from nc_objects o
376 where object_id in (select subscription_typ as source_sid /* source_sid placed here right now */
377 from nc_mkv_curscope
378 where subscription_typ is not null)
379 and not exists (select null from nc_mkv_curscope cs where cs.object_id = o.object_id);
380
381 /*
382 we believe that we can behave here as child transaction because:
383 - REQUIRES_NEW in java
384 - no changes yet and "commit each" in sql-to-db module anyway (BNG Mig)
385 - no changes yet here during bulk
386
387 so we can commit here to save status of nc_mkv_curscope and save consistency for current logical MKV session after rollback inside transform()
388 */
389 commit;
390 END;
391
392 FUNCTION lock_for_transformation(p_instance_object_id IN NUMBER DEFAULT NULL) RETURN PLS_INTEGER IS
393 v_status PLS_INTEGER := -1;
394 v_bulksize NUMBER(20);
395 v_fullscope_row nc_mkv_fullscope%rowtype;
396 v_instances arrayofnumbers := arrayofnumbers();
397 v_allowed_statuses arrayofnumbers := arrayofnumbers();
398 v_sql_text VARCHAR2(1000);
399 BEGIN
400 gv_err_list.delete; -- protection from previous call in the same oracle session where transform() was not executed
401
402 if p_instance_object_id is null then
403 /* Bulk */
404 gv_holder := c_holder_blk;
405 v_status := 0;
406
407 begin
408 select param_value into v_bulksize
409 from nc_mkv_params p
410 where param_type = 'AMOUNT' and param_name = 'BULKSIZE';
411 exception
412 when no_data_found then v_bulksize := 200;
413 end;
414
415 v_sql_text := '
416 select object_id
417 from (select /*+ index(f XIF01NC_MKV_FULLSCOPE) */ object_id
418 from nc_mkv_fullscope f
419 where f.status = 0 /* Ready for transformation */
420 and nvl(f.blk_forbidden, 0) <> 1
421 order by f.status, f.kwt_priority)
422 where rownum <= ' || v_bulksize;
423
424 v_allowed_statuses.extend(1);
425 v_allowed_statuses(1) := 0 /* Ready for transformation */;
426
427 lock_for_mkv(null, v_allowed_statuses, v_sql_text);
428 else
429 /* On-Demand */
430 gv_holder := c_holder_ond;
431
432 begin
433 select * into v_fullscope_row from nc_mkv_fullscope where object_id = p_instance_object_id;
434 exception
435 when no_data_found then
436 begin
437 /* Source subscription as input? */
438 select * into v_fullscope_row from nc_mkv_fullscope where source_sid = p_instance_object_id;
439 exception
440 when no_data_found then
441 return -2 /* Subscription not found in fullscope */;
442 end;
443 end;
444
445 v_status := v_fullscope_row.status;
446 if v_status in (0 /* Ready for transformation */
447 ,3 /* Last transformation failed */)
448 then
449 v_instances.extend(1);
450 v_instances(1) := v_fullscope_row.object_id;
451
452 v_allowed_statuses.extend(2);
453 v_allowed_statuses(1) := 0 /* Ready for transformation */;
454 v_allowed_statuses(2) := 3 /* Last transformation failed */;
455
456 lock_for_mkv(v_instances, v_allowed_statuses);
457 end if;
458 end if;
459
460 return v_status;
461 END lock_for_transformation;
462
463 PROCEDURE lock_for_migration(p_instance_object_ids IN arrayofnumbers) IS
464 v_locked_list t_locked_list;
465 v_instances arrayofnumbers := arrayofnumbers();
466 v_allowed_statuses arrayofnumbers := arrayofnumbers();
467 BEGIN
468 if p_instance_object_ids is null then return; end if;
469 if p_instance_object_ids.count = 0 then return; end if;
470
471 delete nc_mkv_curscope;
472 delete nc_mkv_curscope_objs;
473 gv_holder := c_holder_mig;
474
475 select object_id bulk collect into v_instances
476 from (select column_value as object_id from table(p_instance_object_ids)
477 union all
478 select object_id from nc_mkv_fullscope where source_sid in (select column_value as object_id from table(p_instance_object_ids)));
479
480 v_allowed_statuses.extend(1);
481 v_allowed_statuses(1) := 0 /* Ready for transformation */;
482
483 lock_for_mkv(v_instances, v_allowed_statuses);
484 END lock_for_migration;
485
486 PROCEDURE check_variables_state IS
487 BEGIN
488 if gv_holder is null then
489 rollback;
490 raise_application_error(-20001, 'MKV function LOCK_FOR_TRANSFORMATION was not called in current session yet or existing state of packages has been discarded unexpectedly. Current MKV transformation session has been terminated');
491 end if;
492 END;
493
494 PROCEDURE create_chunk(p_chunk_id NUMBER DEFAULT NULL) IS
495 PRAGMA AUTONOMOUS_TRANSACTION;
496 BEGIN
497 -- creating chunk if not exists
498 gv_chunk_id := get_chunk_id;
499
500 begin
501 insert into nc_mkv_chunks(chunk_id, start_date, end_date, holder)
502 values(nvl(p_chunk_id, gv_chunk_id), sysdate /* Start Date */, sysdate /* End Date will be updated later, if not - some error happend and chunk closed immediately */, gv_holder);
503
504 insert into nc_mkv_chunks_objs(chunk_id, object_id)
505 select nvl(p_chunk_id, gv_chunk_id), object_id from nc_mkv_curscope;
506 exception
507 when others then if sqlcode = -1 /* constraint, already exists */ then null; else raise; end if;
508 end;
509
510 commit;
511 END;
512
513 PROCEDURE fill_fallouts_variable(p_out_fallouts IN OUT arrayofstrings) IS
514 BEGIN
515 select substr('[' || object_id || '] ' || fallout_message, 1, 4000)
516 bulk collect into p_out_fallouts
517 from nc_mkv_fallouts
518 where object_id in (select column_value from table(gv_err_list))
519 and chunk_id = gv_chunk_id
520 and major = 1;
521 END;
522
523 PROCEDURE transform(p_out_errcount OUT PLS_INTEGER, p_out_objects OUT arrayofnumbers, p_out_fallouts OUT arrayofstrings) IS
524 v_rows affected_rec_stat;
525 v_cnt pls_integer := 0;
526 v_time_before timestamp;
527 v_dml_err_list arrayofnumbers := arrayofnumbers();
528 v_tmp_err_list arrayofnumbers := arrayofnumbers();
529 v_mkv_stat nc_mkv_perfstat%rowtype;
530 BEGIN
531 p_out_errcount := 0;
532 p_out_objects := arrayofnumbers();
533 p_out_fallouts := arrayofstrings();
534 v_mkv_stat.retry_count := 0;
535
536 check_variables_state;
537 BEGIN
538 -- if scope is empty then exit immediately
539 select count(*) into v_cnt from nc_mkv_curscope where rownum = 1;
540 if v_cnt = 0 then return; end if;
541
542 create_chunk; -- autonomous transaction
543
544 v_mkv_stat.chunk_id := gv_chunk_id;
545 select count(*) into v_mkv_stat.chunk_size from nc_mkv_curscope;
546
547 check_variables_state;
548
549 -- business validations
550 v_time_before := systimestamp;
551 external_fill_curscope_objs;
552 v_mkv_stat.fill_scope_duration := systimestamp - v_time_before;
553
554 v_time_before := systimestamp;
555 external_validate_minor;
556 external_validate_major;
557 v_mkv_stat.validation_duration := systimestamp - v_time_before;
558
559 if gv_err_list.count > 0 then
560 del_curscope(gv_err_list);
561 end if;
562
563 -- transformation cycle
564 v_time_before := systimestamp;
565 loop
566 check_variables_state;
567
568 v_tmp_err_list := gv_err_list;
569
570 if gv_err_list.count > 0 then -- need refill if fallouts found during validations or rollback was executed because nc_mkv_curscope_objs is GTT on commit delete rows
571 external_fill_curscope_objs;
572 end if;
573 external_transform(p_out_objects, v_rows);
574
575 -- set "Specification Version Number" for all objects in current scope
576 merge into nc_params dst
577 using
578 ( select /*+ no_merge leading(rc cso) */ distinct cso.object_id, rc.version as value, 9126401290113841160 /* Specification Version Number */ as attr_id
579 from (with ins as
580 (select /*+ inline */ p_si_ot.object_id
581 ,to_number(p_si_ot.value) as object_type_id
582 ,p_ver.value as version
583 from nc_objects o_cr
584 ,nc_references r_cr_spec
585 ,nc_objects o_cr_templ
586 ,nc_params_ix p_cp_ot
587 ,nc_references r_tt
588 ,nc_params p_si_ot
589 ,nc_params p_ver
590 where o_cr.object_type_id = 9143881687113546145 /* Catalog Release */
591 and o_cr.project_id = 9144968841513665438 /* Catalog Releases */
592 and r_cr_spec.reference = o_cr.object_id
593 and r_cr_spec.attr_id = 9143881791313546283 /* Catalog Release */
594 and o_cr_templ.object_id = r_cr_spec.object_id
595 and p_cp_ot.attr_id = 4100137113013253246 /* ObjectTypeID */
596 and p_cp_ot.ix_key = to_char(o_cr_templ.object_type_id)
597 and r_tt.attr_id = 4093072468013253221 /* Template Type */
598 and r_tt.reference = p_cp_ot.object_id
599 and p_si_ot.object_id = r_tt.object_id
600 and p_si_ot.attr_id = 4100137113013253246 /* ObjectTypeID */
601 and p_ver.attr_id = 9126401290113841160 /* Specification Version Number */
602 and p_ver.object_id = o_cr_templ.object_id
603 and not exists (select null from nc_references r where r.object_id = o_cr.object_id) -- to find active release
604 ),
605 ord as
606 (select to_number(p_so_ot.value) as object_type_id, i.version
607 from ins i
608 ,nc_references r_tto2tti
609 ,nc_params p_so_ot
610 where r_tto2tti.attr_id = 4093072469013253226 /* Instance Type */
611 and r_tto2tti.reference = i.object_id
612 and p_so_ot.object_id = r_tto2tti.object_id
613 and p_so_ot.attr_id = 4100137113013253246 /* ObjectTypeID */
614 )
615 select object_type_id, version from ins union all select object_type_id, version from ord
616 ) rc
617 ,nc_mkv_curscope_objs cso
618 where cso.object_type_id = rc.object_type_id
619 and exists (select null from nc_objects where object_id = cso.object_id)
620 ) src
621 on (dst.object_id = src.object_id and dst.attr_id = src.attr_id)
622 when matched then
623 update set dst.value = src.value
624 when not matched then
625 insert (attr_id, object_id, attr_access_type, value, show_order, priority)
626 values (src.attr_id, src.object_id, 0, src.value, 0, 1)
627 log errors into nc_mkv_dml_err_params(gv_chunk_id || '|' || src.object_id) reject limit unlimited;
628
629 v_rows.params := v_rows.params + sql%rowcount;
630
631 select distinct root_object_id bulk collect into v_dml_err_list
632 from nc_mkv_curscope_objs
633 where object_id in (select substr(ORA_ERR_TAG$, instr(ORA_ERR_TAG$, '|') + 1) as object_id from nc_mkv_dml_err_refs
634 where substr(ORA_ERR_TAG$, 1, instr(ORA_ERR_TAG$, '|') - 1) = to_char(gv_chunk_id)
635 union all
636 select substr(ORA_ERR_TAG$, instr(ORA_ERR_TAG$, '|') + 1) as object_id from nc_mkv_dml_err_params
637 where substr(ORA_ERR_TAG$, 1, instr(ORA_ERR_TAG$, '|') - 1) = to_char(gv_chunk_id)
638 union all
639 select substr(ORA_ERR_TAG$, instr(ORA_ERR_TAG$, '|') + 1) as object_id from nc_mkv_dml_err_objs
640 where substr(ORA_ERR_TAG$, 1, instr(ORA_ERR_TAG$, '|') - 1) = to_char(gv_chunk_id));
641
642 set_fallout('DML errors while transforming subscription', v_dml_err_list); -- increases gv_err_list
643 v_tmp_err_list := gv_err_list multiset except v_tmp_err_list;
644 del_curscope(gv_err_list);
645
646 exit when v_tmp_err_list.count = 0; -- exiting if no new errors after last iteration
647
648 v_mkv_stat.retry_count := v_mkv_stat.retry_count + 1;
649 rollback; -- rolling back changes made by external transform
650 end loop;
651
652 v_mkv_stat.transform_duration := systimestamp - v_time_before;
653 v_mkv_stat.objects_affected := v_rows.objs;
654 v_mkv_stat.params_affected := v_rows.params;
655 v_mkv_stat.references_affected := v_rows.refs;
656
657 insert into nc_mkv_perfstat values v_mkv_stat;
658 update nc_mkv_chunks set end_date = sysdate where chunk_id = gv_chunk_id;
659 update nc_mkv_fullscope fs set status = 2 /* Transformed */ where status = 1 and object_id in (select object_id from nc_mkv_curscope);
660 update nc_objects set version = nvl(version, 0) + 1 where object_id in (select object_id from nc_mkv_curscope_objs);
661
662 if gv_holder = c_holder_ond then
663 fill_fallouts_variable(p_out_fallouts);
664 end if;
665
666 p_out_errcount := gv_err_list.count;
667 gv_err_list.delete;
668 gv_holder := null;
669 gv_chunk_id := null;
670 delete nc_mkv_curscope;
671 delete nc_mkv_curscope_objs;
672 EXCEPTION
673 WHEN OTHERS THEN raise;
674 select distinct object_id bulk collect into gv_err_list from nc_mkv_curscope;
675 set_fallout(sqlerrm, gv_err_list);
676 p_out_errcount := gv_err_list.count;
677
678 if gv_holder = c_holder_ond then
679 fill_fallouts_variable(p_out_fallouts);
680 end if;
681
682 gv_holder := null;
683 gv_chunk_id := null;
684 gv_err_list.delete;
685 delete nc_mkv_curscope;
686 delete nc_mkv_curscope_objs;
687 END;
688 END transform;
689END;
690/