1 package body WF_CLONE as
2 /* $Header: wfcloneb.pls 120.1 2005/07/02 03:42:45 appldev noship $ */
3
4 procedure UpdateAttrValues(WEB_HOST in varchar2,
5 DOMAIN in varchar2,
6 WEB_PORT in varchar2,
7 SID in varchar2,
8 URL_PROTO in varchar2 )
9 is
10 source_web_agent varchar2(2000);
11 target_web_agent varchar2(2000);
12 l_url_proto varchar2(20);
13 begin
14
15 if URL_PROTO IS NULL then
16 l_url_proto := 'http';
17 else
18 l_url_proto := URL_PROTO;
19 end if;
20
21 target_web_agent := l_url_proto||'://'||WEB_HOST||'.'||DOMAIN||':'||WEB_PORT||'/pls/'||SID||'/';
22
23 --Select source agent
24 select text
25 into source_web_agent
26 from wf_resources
27 where name = 'WF_WEB_AGENT'
28 and language = 'US';
29
30 --Check if there is a trailing '/' in the web_agent
31 --If not add the trailing '/'
32 if (instr(substr(source_web_agent,length(source_web_agent)),'/') = 0) then
33 source_web_agent := source_web_agent||'/';
34 end if;
35
36
37 --For item attributes values
38 update WF_ITEM_ATTRIBUTE_VALUES wiav
39 set wiav.text_value =
40 replace(wiav.text_value,source_web_agent,target_web_agent)
41 where (wiav.item_type, wiav.name) =
42 (select wia.item_type, wia.name
43 from WF_ITEM_ATTRIBUTES wia
44 where wia.type = 'URL'
45 and wia.item_type = wiav.item_type
46 and wia.name = wiav.name)
47 and wiav.text_value is not null
48 and instr(wiav.text_value,source_web_agent) > 0 ;
49
50
51 --For default item attribute values
52 update WF_ITEM_ATTRIBUTES
53 set text_default = replace(text_default,source_web_agent,target_web_agent)
54 where type ='URL'
55 and text_default is not null
56 and instr(text_default,source_web_agent) > 0 ;
57
58
59 --Default activity attribute
60 update wf_activity_attributes
61 set text_default = replace(text_default,source_web_agent,target_web_agent)
62 where type ='URL'
63 and text_default is not null
64 and instr(text_default,source_web_agent)> 0;
65
66 --Activity attribute value
67 update wf_activity_attr_values waav
68 set waav.text_value = replace(waav.text_value,source_web_agent,target_web_agent)
69 where (waav.process_activity_id,waav.name) =(
70 select wpa.instance_id ,waa.name
71 from wf_process_activities wpa,wf_activity_attributes waa
72 where waa.activity_item_type = wpa.activity_item_type
73 and waa.activity_name = wpa.activity_name
74 and wpa.instance_id = waav.process_activity_id
75 and waa.name = waav.name
76 and waa.activity_version = wpa.process_version
77 and waa.type = 'URL')
78 and waav.text_value is not null
79 and waav.value_type = 'CONSTANT'
80 and instr(text_value,source_web_agent) > 0;
81
82
83 --Notification attributes
84 update wf_notification_attributes
85 set TEXT_VALUE = replace(text_value,source_web_agent,target_web_agent)
86 where instr(text_value,source_web_agent)> 0;
87
88 --Message attributes
89 update wf_message_attributes
90 set text_default = replace(text_default,source_web_agent,target_web_agent)
91 where type='URL'
92 and value_type = 'CONSTANT'
93 and text_default is not null
94 and instr(text_default,source_web_agent) > 0;
95
96 --Reset the cache
97 begin
98 --The execute immediate is used so that it doesn't fail
99 --when wf_cache pkg does not exist.
100 execute immediate 'begin WF_CACHE.Reset(); end;';
101 exception
102 when others then
103 null;
104 end;
105
106 exception
107 when others then
108 raise_application_error(-20000, 'Error : WF_CLONE.UpdateAttrValues -:Oracle Error = '||to_char(sqlcode)||' -'||sqlerrm);
109 end;
110
111
112
113 procedure UpdateSysGuid as
114 source_guid raw(16);
115 target_guid raw(16);
116 source_name VARCHAR2(30);
117 target_name VARCHAR2(30);
118 begin
119
120 savepoint wf_update_guid;
121
122 select text
123 into source_guid
124 from wf_resources
125 where name = 'WF_SYSTEM_GUID'
126 and language = 'US';
127
128 --Get a global unique identifier here
129 target_guid := sys_guid();
130
131 --Now set this value for the sysguid in wf_resources
132 update wf_resources
133 set text = target_guid
134 where name = 'WF_SYSTEM_GUID';
135
136 --Get the global name of source to replace
137 --the address in wf_agents
138 select name
139 into source_name
140 from wf_systems
141 where guid = source_guid;
142
143 --Get the global_name of target
144 select global_name
145 into target_name
146 from global_name;
147
148 --Now replace the agent address with the
149 --target global name.
150
151 update wf_agents
152 set address = substr(address,1,instr(address,'@',1))||target_name
153 where address = substr(address,1,instr(address,'@',1))||source_name;
154
155 --Update system guid references in wf_agents
156 update wf_agents
157 set system_guid = target_guid
158 where system_guid = source_guid;
159
160 --Update system guid references in event subscription tables
161 update wf_event_subscriptions
162 set SYSTEM_GUID = target_guid
163 where SYSTEM_GUID = source_guid;
164
165 --Update wf_system table
166 update wf_systems
167 set name = target_name
168 where name = source_name;
169
170 --Update system guid
171 update wf_systems
172 set guid = target_guid
173 where guid = source_guid;
174
175 exception
176 when others then
177 --Rollback any exception
178 rollback to wf_update_guid;
179 raise_application_error(-20000, 'Error : WF_CLONE.UpdateSysGuid -: Oracle Error = '||to_char(sqlcode)||' -'||sqlerrm);
180 end;
181
182 procedure UpdateMailer(WEB_HOST in varchar2,
183 DOMAIN in varchar2,
184 WEB_PORT in varchar2,
185 SID in varchar2,
186 URL_PROTO in varchar2 )
187 is
188 source_web_agent varchar2(2000);
189 l_url_proto varchar2(20);
190 begin
191
192 --Select source agent
193 select text
194 into source_web_agent
195 from wf_resources
196 where name = 'WF_WEB_AGENT'
197 and language = 'US';
198
199
200 if URL_PROTO IS NULL then
201 l_url_proto := 'http';
202 else
203 l_url_proto := URL_PROTO;
204 end if;
205 --The mailer parameter should be the WEB_AGENT name
206 --Update HTML agent
207 update wf_mailer_parameters
208 set VALUE = l_url_proto||'://'||WEB_HOST||'.'||DOMAIN||':'||WEB_PORT||'/pls/'||SID
209 where parameter = 'HTMLAGENT';
210
211 --Update replyto with new host name
212 update wf_mailer_parameters
213 set VALUE = substr(VALUE,1,instr(VALUE,'@'))||DOMAIN
214 where parameter = 'REPLYTO';
215
216 exception
217 when others then
218 raise_application_error(-20000, 'Error : WF_CLONE.UpdateMailer -: Oracle Error = '||to_char(sqlcode)||' -'||sqlerrm);
219 end;
220
221
222 --This would be called only last in the template as we
223 --do not have the source data stored anywhere else.
224 procedure UpdateResource(WEB_HOST in varchar2,
225 DOMAIN in varchar2,
226 WEB_PORT in varchar2,
227 SID in varchar2,
228 URL_PROTO in varchar2 )
229 is
230 target_web_agent varchar2(2000);
231 l_url_proto varchar2(20);
232 begin
233
234 if URL_PROTO IS NULL then
235 l_url_proto := 'http';
236 else
237 l_url_proto := URL_PROTO;
238 end if;
239
240 target_web_agent := l_url_proto||'://'||WEB_HOST||'.'||DOMAIN||':'||WEB_PORT||'/pls/'||SID;
241
242 --Update the target webagent.
243 update wf_resources
244 set text = target_web_agent
245 where name = 'WF_WEB_AGENT';
246
247 exception
248 when others then
249 raise_application_error(-20000, 'Error : WF_CLONE.UpdateResource -:Oracle Error = '||to_char(sqlcode)||' -'||sqlerrm);
250 end;
251
252 Function DetermineClone(WEB_HOST in varchar2,
253 DOMAIN in varchar2,
254 WEB_PORT in varchar2,
255 SID in varchar2,
256 URL_PROTO in varchar2 )
257 return boolean is
258 target_agent varchar2(2000);
259 source_agent varchar2(2000);
260 l_url_proto varchar2(20);
261 begin
262
263 begin
264 select text
265 into source_agent
266 from wf_resources
267 where name = 'WF_WEB_AGENT'
268 and language = 'US';
269 exception
270 when no_data_found then
271 --Install has not seeded the token WF_WEB_AGENT,
272 --return false here
273 return false;
274 end;
275
276 --Get the target agent
277 if URL_PROTO IS NULL then
278 l_url_proto := 'http';
279 else
280 l_url_proto := URL_PROTO;
281 end if;
282
283
284 target_agent := l_url_proto||'://'||WEB_HOST||'.'||DOMAIN||':'||WEB_PORT||'/pls/'||SID;
285
286 --Now check if source = target . If so skip calling the clone
287 --APIs and exit off
288 if ((source_agent = target_agent) OR
289 (rtrim(source_agent,'/') = target_agent)) then
290 return false;
291 else
292 return true;
293 end if;
294
295 end ;
296
297 --Procedure Clone
298 --This API calls all the cloning related APIs
299 --This will be invoked by the concurrent program
300 Procedure WFClone(P_WEB_HOST in varchar2,
301 P_DOMAIN in varchar2,
302 P_WEB_PORT in varchar2,
303 P_SID in varchar2,
304 P_URL_PROTO in varchar2)
305 is
306 begin
307 wf_clone.UpdateAttrValues(p_web_host,p_domain, p_web_port,p_sid,p_url_proto);
308 wf_clone.UpdateSysGuid;
309 wf_clone.UpdateMailer(p_web_host, p_domain, p_web_port,p_sid,p_url_proto );
310 wf_clone.UpdateResource(p_web_host,p_domain, p_web_port,p_sid,p_url_proto );
311 exception
312 when others then
313 raise;
314 end;
315
316 procedure purgedata
317 is
318 l_owner varchar2(30);
319 cursor queue_curs is
320 select queue_name , name
321 from wf_agents
322 where type ='AGENT';
323
324 begin
325 --WF Tables we depend are created in the schema given by the token
326 --wf_schema
327 l_owner := wf_core.translate('WF_SCHEMA');
328
329 --Truncate run-time data tables
330 TruncateTable('wf_notifications',l_owner);
331 TruncateTable('WF_ATTRIBUTE_CACHE',l_owner);
332 TruncateTable('WF_ITEM_ACTIVITY_STATUSES',l_owner);
333 TruncateTable('WF_ITEM_ACTIVITY_STATUSES_H',l_owner);
334 TruncateTable('WF_ITEM_ATTRIBUTE_VALUES' ,l_owner);
335 TruncateTable('WF_NOTIFICATION_ATTRIBUTES',l_owner);
336 TruncateTable('WF_ITEMS',l_owner);
337
338 --Clear Cache off
339 begin
340 --The execute immediate is used so that it doesn't fail
341 --when wf_cache pkg does not exist.
342 execute immediate 'begin wf_cache.clear; end;';
343 exception
344 when others then
345 null;
346 end;
347
348 for q_curs in queue_curs loop
349 begin
350 wf_clone.QDequeue(substr(q_curs.queue_name,instr(q_curs.queue_name,'.')+1),substr(q_curs.queue_name,1,instr(q_curs.queue_name,'.')-1),q_curs.name,true);
351 exception
352 when others then
353 null;
354 end;
355 end loop;
356
357 --Now clear the background queues
358 wf_clone.QDequeue('WF_DEFERRED_QUEUE_M',l_owner);
359 wf_clone.QDequeue('WF_INBOUND_QUEUE',l_owner);
360 wf_clone.QDequeue('WF_OUTBOUND_QUEUE',l_owner);
361
365 end;
362 exception
363 when others then
364 raise_application_error(-20000, 'Error : WF_CLONE.PurgeData -:Oracle Error = '||to_char(sqlcode)||' -'||sqlerrm);
366
367
368 --Generic API to trucate runtime data tables
369 --A seperate API will allow more flexibility
370 PROCEDURE TruncateTable (TableName IN VARCHAR2,
371 Owner IN VARCHAR2,
372 raise_error IN BOOLEAN ) is
373
374 tableNotFound EXCEPTION;
375 pragma exception_init(tableNotFound, -942);
376 BEGIN
377 execute IMMEDIATE 'truncate table '||Owner||'.'||TableName;
378
379 EXCEPTION
380 when tableNotFound then
381 if (raise_error) then
382 null;
383 else
384 raise_application_error(-20000, 'Error : WF_CLONE.TruncateTable -:Oracle Error = '||to_char(sqlcode)||' -'||sqlerrm);
385 end if;
386 when OTHERS then
387 raise_application_error(-20000, 'Error : WF_CLONE.TruncateTable -:Oracle Error = '||to_char(sqlcode)||' -'||sqlerrm);
388 end;
389
390 --Truncate Queue Table
391 PROCEDURE QTableTruncate(QName IN VARCHAR2,
392 raise_error IN BOOLEAN )
393 is
394 l_owner varchar2(30);
395 l_queue_tab varchar2(30);
396 tableNotFound EXCEPTION;
397 pragma exception_init(tableNotFound, -942);
398
399 begin
400 /* This code is not used
401 so commenting it off for bug #3548589
402
403 select que.queue_table , que.owner
404 into l_queue_tab , l_owner
405 from all_queues que
406 where que.name = QTableTruncate.QName ;
407
408 execute IMMEDIATE 'truncate table '||l_Owner||'.'||l_queue_tab;
409 */
410 --Any day AQ allows truncation this will be faster than
411 --dequeue.
412 null;
413
414 exception
415 when tableNotFound then
416 if (raise_error) then
417 null;
418 else
419 raise_application_error(-20000, 'Error : WF_CLONE.TruncateTable -:Oracle Error = '||to_char(sqlcode)||' -'||sqlerrm);
420 end if;
421 when OTHERS then
422 raise_application_error(-20000, 'Error : WF_CLONE.TruncateTable -:Oracle Error = '||to_char(sqlcode)||' -'||sqlerrm);
423 end;
424
425 PROCEDURE CLONE(errbuf out NOCOPY varchar2,
426 retcode out NOCOPY varchar2,
427 P_WEB_HOST in varchar2,
428 P_DOMAIN in varchar2,
429 P_WEB_PORT in varchar2,
430 P_SID in varchar2,
431 P_URL_PROTO in varchar2 )
432 is
433 l_clonable boolean;
434 begin
435 --Lets call DetermineClone to avoid unnecessary update
436 l_clonable := determineClone(P_WEB_HOST,P_DOMAIN,P_WEB_PORT,P_SID,P_URL_PROTO);
437 if l_clonable then
438 wf_clone.WFClone(P_WEB_HOST,P_DOMAIN,P_WEB_PORT,P_SID,P_URL_PROTO);
439 end if;
440
441 retcode := '0'; -- (successful completion)
442 errbuf := '';
443
444 exception
445 when others then
446 retcode := '2'; -- (error)
447 errbuf := sqlerrm;
448 end;
449
450 PROCEDURE QDequeue(QName IN VARCHAR2,
451 owner in VARCHAR2,
452 AgtName IN VARCHAR2,
453 raise_error IN BOOLEAN default FALSE )
454 is
455 dequeue_timeout exception;
456 pragma EXCEPTION_INIT(dequeue_timeout, -25228);
457
458 dequeue_disabled exception;
459 pragma EXCEPTION_INIT(dequeue_disabled, -25226);
460
461 dequeue_outofseq exception;
462 pragma EXCEPTION_INIT(dequeue_outofseq, -25237);
463
464 no_queue exception;
465 pragma EXCEPTION_INIT(no_queue, -24010);
466
467 multiconsumer_q exception;
468 pragma EXCEPTION_INIT(multiconsumer_q, -25231);
469
470 l_commit_level integer := 500; --commit frequency default to 500
471 l_timeout integer;
472 l_queue_name varchar2(200); --queue name
473
474 l_deq integer; -- dequeue count
475 l_xcount integer; -- commit frequency
476
477 --Message Properties
478 l_dequeue_options dbms_aq.dequeue_options_t;
479 l_message_properties dbms_aq.message_properties_t;
480 l_message_handle RAW(16) := NULL;
481 l_payload wf_event_t;
482 l_consumer varchar2(100);
483 l_msgid RAW(16);
484 type wait_message is ref cursor;
485 wait_msg wait_message ;
486 l_sql varchar2(4000);
487 l_qTable varchar2(30);
488
489 begin
490
491 l_timeout := 0;
492 l_deq := 0;
493 l_xcount := 0;
494
495 --Since we are not planning any processing of the
496 --payload data take in the remove_nodata mode
497 --This avoids overhead of payload reterival
498 l_dequeue_options.dequeue_mode := dbms_aq.REMOVE_NODATA;
499 l_dequeue_options.wait := dbms_aq.NO_WAIT;
500
501 l_dequeue_options.navigation := dbms_aq.FIRST_MESSAGE;
502
503 select qtab.RECIPIENTS ,qtab.queue_table
504 into l_consumer , l_qTable
505 from dba_queue_tables qtab , dba_queues aq
506 where aq.name = QDequeue.Qname
507 and aq.owner = QDequeue.owner
508 and qtab.queue_table = aq.queue_table
509 and qtab.owner = aq.owner ;
510
511 if (l_consumer = 'MULTIPLE') then
512 --Set the consumer name
513 if AgtName is null then
514 --In this case try setting the account name as consumer
515 --Do not put APIs to minimise dependencies
516 select sys_context('USERENV', 'CURRENT_SCHEMA')
517 into l_dequeue_options.consumer_name
518 from sys.dual;
522 end if;
519 else
520 l_dequeue_options.consumer_name := AgtName ;
521 end if;
523
524 --Dequeue waiting messages
525 -- Owner and Qname were verified in sql earlier and l_qTable was from
526 -- dba_queue_tables.
527 -- BINDVAR_SCAN_IGNORE
528 l_sql := 'select msgid from '||QDequeue.owner||'.'||l_qTable||' where q_name ='||''''||QDequeue.Qname||''''||' and state=1';
529
530 open wait_msg for l_sql ;
531 loop
532 fetch wait_msg into l_msgid;
533 exit when wait_msg%NOTFOUND;
534 l_dequeue_options.correlation := null;
535 l_dequeue_options.msgid := l_msgid;
536 begin
537 dbms_aq.dequeue
538 (
539 queue_name => QDequeue.owner||'.'||QDequeue.Qname,
540 dequeue_options => l_dequeue_options,
541 message_properties => l_message_properties,
542 payload => l_payload,
543 msgid => l_msgid
544 );
545 exception
546 when others then
547 --Move ahead assuming success
548 null;
549
550 end;
551 end loop;
552 close wait_msg;
553
554 l_dequeue_options.msgid := null;
555 while (l_timeout = 0) loop
556 begin
557 dbms_aq.Dequeue(queue_name => QDequeue.owner||'.'||QDequeue.Qname,
558 dequeue_options => l_dequeue_options,
559 message_properties => l_message_properties,
560 payload => l_payload,
561 msgid => l_message_handle);
562 l_deq := l_deq + 1;
563 l_xcount := l_xcount + 1;
564 l_timeout := 0;
565 exception
566 when dequeue_disabled then
567 --Incase dequeue has been disabled on the queue
568 --Enable the same and re-try the operation.
569 dbms_aqadm.start_queue(
570 queue_name =>QDequeue.Qname,
571 enqueue =>FALSE,
572 dequeue =>TRUE);
573 dbms_aq.Dequeue(queue_name => QDequeue.Qname,
574 dequeue_options => l_dequeue_options,
575 message_properties => l_message_properties,
576 payload => l_payload,
577 msgid => l_message_handle);
578 l_deq := l_deq + 1;
579 l_xcount := l_xcount + 1;
580 l_timeout := 0;
581 when dequeue_timeout then
582 l_timeout := 1;
583 when others then
584 if (raise_error) then
585 raise_application_error(-20000, 'Oracle Error = '||
586 to_char(sqlcode)||' - '||sqlerrm);
587 else
588 null;
589 end if;
590 end;
591
592 --Move to next message
593 l_dequeue_options.navigation := dbms_aq.NEXT_MESSAGE;
594
595 --Commit if commit frequency
596 if l_xcount >= l_commit_level then
597 commit;
598 l_xcount := 0;
599 end if;
600
601 end loop; --End of while loop
602 commit;
603 exception
604 when others then
605 if (raise_error) then
606 raise_application_error(-20000, 'Oracle Error = '||to_char(sqlcode)||' - '||sqlerrm);
607 else
608 null;
609 end if;
610 end;
611
612 --#2. PURGE - Where u do a complete purge of transaction/
613 -- runtime data.
614 PROCEDURE PURGE(errbuf out NOCOPY varchar2,
615 retcode out NOCOPY varchar2)
616 is
617 begin
618 wf_clone.purgedata;
619 retcode := '0'; -- (successful completion)
620 errbuf := '';
621 exception
622 when others then
623 retcode := '2'; -- (error)
624 errbuf := sqlerrm;
625 WF_CORE.Clear;
626 end;
627
628
629
630 end wf_clone;
631
[Home] [Help]