1 package body wf_event_ojmstext_qh as
2 /* $Header: wfjmstxb.pls 120.2.12000000.2 2007/07/03 03:59:57 sstomar ship $ */
3
4 DATE_MASK constant varchar2(21) := 'YYYY/MM/DD HH24:MI:SS';
5 DEFAULT_PRIORITY constant int := 100;
6
7 -- reserved Business Event System parameter names must begin with 'BES_'
8
9 PRIORITY constant varchar2(30) := 'BES_PRIORITY';
10 SEND_DATE constant varchar2(30) := 'BES_SEND_DATE';
11 RECEIVE_DATE constant varchar2(30) := 'BES_RECEIVE_DATE';
12 CORRELATION_ID constant varchar2(30) := 'BES_CORRELATION_ID';
13 EVENT_NAME constant varchar2(30) := 'BES_EVENT_NAME';
14 EVENT_KEY constant varchar2(30) := 'BES_EVENT_KEY';
15 FROM_AGENT constant varchar2(30) := 'BES_FROM_AGENT';
16 TO_AGENT constant varchar2(30) := 'BES_TO_AGENT';
17 ERROR_SUBSCRIPTION constant varchar2(30) := 'BES_ERROR_SUBSCRIPTION';
18 ERROR_MESSAGE constant varchar2(30) := 'BES_ERROR_MESSAGE';
19 ERROR_STACK constant varchar2(30) := 'BES_ERROR_STACK';
20 PAYLOAD_OBJECT constant varchar2(30) := 'BES_PAYLOAD_OBJECT';
21
22 -- procedures to emulate the native aq$_jms_text_message methods available in RDBMS 9.2
23
24 procedure set_type(p_jms_text_message in out nocopy sys.aq$_jms_text_message,
25 type in varchar)
26 is
27 begin
28 if(p_jms_text_message.header is null) then
29 p_jms_text_message.header := sys.aq$_jms_header(null, null, null, null, null, 0, null);
30 end if;
31
32 p_jms_text_message.header.type := type;
33 end set_type;
34
35 procedure set_userid(p_jms_text_message in out nocopy sys.aq$_jms_text_message,
36 userid in varchar)
37 is
38 begin
39 if(p_jms_text_message.header is null) then
40 p_jms_text_message.header := sys.aq$_jms_header(null, null, null, null, null, 0, null);
41 end if;
42
43 p_jms_text_message.header.userid := userid;
44 end set_userid;
45
46 procedure set_appid(p_jms_text_message in out nocopy sys.aq$_jms_text_message,
47 appid in varchar)
48 is
49 begin
50 if(p_jms_text_message.header is null) then
51 p_jms_text_message.header := sys.aq$_jms_header(null, null, null, null, null, 0, null);
52 end if;
53
54 p_jms_text_message.header.appid := appid;
55 end set_appid;
56
57 procedure set_groupid(p_jms_text_message in out nocopy sys.aq$_jms_text_message,
58 groupid in varchar)
59 is
60 begin
61 if(p_jms_text_message.header is null) then
62 p_jms_text_message.header := sys.aq$_jms_header(null, null, null, null, null, 0, null);
63 end if;
64
65 p_jms_text_message.header.groupid := groupid;
66 end set_groupid;
67
68 procedure set_groupseq(p_jms_text_message in out nocopy sys.aq$_jms_text_message,
69 groupseq in int)
70 is
71 begin
72 if(p_jms_text_message.header is null) then
73 p_jms_text_message.header := sys.aq$_jms_header(null, null, null, null, null, 0, null);
74 end if;
75
76 p_jms_text_message.header.groupseq := groupseq;
77 end set_groupseq;
78
79 procedure set_replyto(p_jms_text_message in out nocopy sys.aq$_jms_text_message,
80 replyto in sys.aq$_agent)
81 is
82 begin
83 if(p_jms_text_message.header is null) then
84 p_jms_text_message.header := sys.aq$_jms_header(null, null, null, null, null, 0, null);
85 end if;
86
87 p_jms_text_message.header.replyto := replyto;
88 end set_replyto;
89
90 procedure lookup_property_name(properties in out nocopy sys.aq$_jms_userproparray,
91 new_property_name in varchar)
92 is
93 begin
94 if(new_property_name is null) then
95 -- dbms_sys_error.raise_system_error(-24192);
96
97 raise_application_error(-20192, 'property name is null');
98 null;
99 end if;
100
101 for i in properties.first .. properties.last loop
102 if(properties(i).name = new_property_name) then
103 -- dbms_sys_error.raise_system_error(-24191, new_property_name);
104
105 raise_application_error(-20191, 'property name already exists: ' || new_property_name);
106 end if;
107 end loop;
108 end lookup_property_name;
109
110 procedure set_int_property(p_jms_text_message in out nocopy sys.aq$_jms_text_message,
111 property_name in varchar,
112 property_value in int)
113 is
114 begin
115 if((property_value > 2147483647) or (property_value < -2147483647)) then
116 -- dbms_sys_error.raise_system_error(-24193, '-2147483647 to 2147483647');
117
118 raise_application_error(-20193, 'property value out of range [-2147483647, 2147483647]: ' ||
119 property_value);
120 end if;
121
122 if(p_jms_text_message.header.properties is null) then
123 p_jms_text_message.header.properties := sys.aq$_jms_userproparray(
124 sys.aq$_jms_userproperty(property_name, 200, null, property_value, 23));
125 else
126 lookup_property_name(p_jms_text_message.header.properties, property_name);
127
128 p_jms_text_message.header.properties.extend;
129
130 p_jms_text_message.header.properties(p_jms_text_message.header.properties.count) :=
131 sys.aq$_jms_userproperty(property_name, 200, null, property_value, 23);
132 end if;
133 end set_int_property;
134
135 procedure set_string_property(p_jms_text_message in out nocopy sys.aq$_jms_text_message,
136 property_name in varchar,
137 property_value in varchar)
138 is
139 l_property_value varchar2(2000);
140 begin
141 -- YOHUANG: JMS Property has 2000 characters limit while ERROR_MESSAGE and ERROR_STACK
142 -- can be 4000 characters long. Bug 3628473
143 l_property_value := substr(property_value, 1 , 2000);
144 if(p_jms_text_message.header.properties is null) then
145 p_jms_text_message.header.properties := sys.aq$_jms_userproparray(
146 sys.aq$_jms_userproperty(property_name, 100, l_property_value, null, 27));
147 else
148 lookup_property_name(p_jms_text_message.header.properties, property_name);
149
150 p_jms_text_message.header.properties.extend;
151
152 p_jms_text_message.header.properties(p_jms_text_message.header.properties.count) :=
153 sys.aq$_jms_userproperty(property_name, 100, l_property_value, null, 27);
154 end if;
155 end set_string_property;
156
157 function get_boolean_property(p_jms_text_message in out nocopy sys.aq$_jms_text_message,
158 property_name in varchar)
159 return boolean
160 is
161 begin
162 for i in p_jms_text_message.header.properties.first ..
163 p_jms_text_message.header.properties.last loop
164 if((p_jms_text_message.header.properties(i).name = property_name) and
165 (p_jms_text_message.header.properties(i).java_type = 20)) then
166 if(p_jms_text_message.header.properties(i).num_value is not null) then
167 if(p_jms_text_message.header.properties(i).num_value = 0) then
168 return false;
169 else
170 return true;
171 end if;
172 else
173 return null;
174 end if;
175 end if;
176 end loop;
177
178 return null;
179 end get_boolean_property;
180
181 function get_byte_property(p_jms_text_message in out nocopy sys.aq$_jms_text_message,
182 property_name in varchar)
183 return int
184 is
185 begin
186 for i in p_jms_text_message.header.properties.first ..
187 p_jms_text_message.header.properties.last loop
188 if((p_jms_text_message.header.properties(i).name = property_name) and
189 (p_jms_text_message.header.properties(i).java_type = 21)) then
190 return p_jms_text_message.header.properties(i).num_value;
191 end if;
192 end loop;
193
194 return null;
195 end get_byte_property;
196
197 function get_short_property(p_jms_text_message in out nocopy sys.aq$_jms_text_message,
198 property_name in varchar)
199 return int
200 is
201 begin
202 for i in p_jms_text_message.header.properties.first ..
203 p_jms_text_message.header.properties.last loop
204 if((p_jms_text_message.header.properties(i).name = property_name) and
205 (p_jms_text_message.header.properties(i).java_type = 22)) then
206 return p_jms_text_message.header.properties(i).num_value;
207 end if;
208 end loop;
209
210 return null;
211 end get_short_property;
212
213 function get_int_property(p_jms_text_message in out nocopy sys.aq$_jms_text_message,
214 property_name in varchar)
215 return int
216 is
217 begin
218 for i in p_jms_text_message.header.properties.first ..
219 p_jms_text_message.header.properties.last loop
220 if((p_jms_text_message.header.properties(i).name = property_name) and
221 (p_jms_text_message.header.properties(i).java_type = 23)) then
222 return p_jms_text_message.header.properties(i).num_value;
223 end if;
224 end loop;
225
226 return null;
227 end get_int_property;
228
229 function get_long_property(p_jms_text_message in out nocopy sys.aq$_jms_text_message,
230 property_name in varchar)
231 return number
232 is
233 begin
234 for i in p_jms_text_message.header.properties.first ..
235 p_jms_text_message.header.properties.last loop
236 if((p_jms_text_message.header.properties(i).name = property_name) and
237 (p_jms_text_message.header.properties(i).java_type = 24)) then
238 return p_jms_text_message.header.properties(i).num_value;
239 end if;
240 end loop;
241
242 return null;
243 end get_long_property;
244
245 function get_float_property(p_jms_text_message in out nocopy sys.aq$_jms_text_message,
246 property_name in varchar)
247 return float
248 is
249 begin
250 for i in p_jms_text_message.header.properties.first ..
251 p_jms_text_message.header.properties.last loop
252 if((p_jms_text_message.header.properties(i).name = property_name) and
253 (p_jms_text_message.header.properties(i).java_type = 25)) then
254 return p_jms_text_message.header.properties(i).num_value;
255 end if;
256 end loop;
257
258 return null;
259 end get_float_property;
260
261 function get_double_property(p_jms_text_message in out nocopy sys.aq$_jms_text_message,
262 property_name in varchar)
263 return double precision
264 is
265 begin
266 for i in p_jms_text_message.header.properties.first ..
267 p_jms_text_message.header.properties.last loop
268 if((p_jms_text_message.header.properties(i).name = property_name) and
269 (p_jms_text_message.header.properties(i).java_type = 26)) then
270 return p_jms_text_message.header.properties(i).num_value;
271 end if;
272 end loop;
273
274 return null;
275 end get_double_property;
276
277 function get_string_property(p_jms_text_message in out nocopy sys.aq$_jms_text_message,
278 property_name in varchar)
279 return varchar
280 is
281 begin
282 for i in p_jms_text_message.header.properties.first ..
283 p_jms_text_message.header.properties.last loop
284 if((p_jms_text_message.header.properties(i).name = property_name) and
285 (p_jms_text_message.header.properties(i).java_type = 27)) then
286 return p_jms_text_message.header.properties(i).str_value;
287 end if;
288 end loop;
289
290 return null;
291 end get_string_property;
292
293 procedure set_text(p_jms_text_message in out nocopy sys.aq$_jms_text_message,
294 payload in clob)
295 is
296 l_text varchar2(4000);
297 l_length number;
298
299 invalid_lob_locator exception;
300 pragma exception_init(invalid_lob_locator, -22275);
301 begin
302 if(payload is null) then
303 p_jms_text_message.text_len := 0;
304
305 p_jms_text_message.text_vc := null;
306 p_jms_text_message.text_lob := null;
307 else
308 begin
309 p_jms_text_message.text_len := dbms_lob.getLength(payload);
310
311 if(p_jms_text_message.text_len = 0) then
312 p_jms_text_message.text_vc := null;
313 p_jms_text_message.text_lob := null;
314 else
315 --Bug 2632448
316 l_length := lengthb(dbms_lob.substr(payload));
317
318 if(l_length <= 4000) then
319 --elsif(p_jms_text_message.text_len <= 4000) then
320 dbms_lob.read(lob_loc => payload,
321 amount => p_jms_text_message.text_len,
322 offset => 1,
323 buffer => l_text);
324
325 p_jms_text_message.text_vc := l_text;
326
327 -- ANKUNG
328 -- Because of bug 2676012, we need to set both the varchar
329 -- and the clob with the data
330 -- p_jms_text_message.text_lob := null;
331 p_jms_text_message.text_lob := payload;
332 else
333 p_jms_text_message.text_vc := null;
334 p_jms_text_message.text_lob := payload;
335 end if;
336 end if;
337 exception
338 when invalid_lob_locator then
339 -- when 'payload' is an empty_clob, dbms_lob.getLength(payload)
340 -- raises an 'ORA-22275: invalid LOB locator specified' error
341
342 p_jms_text_message.text_len := 0;
343
344 p_jms_text_message.text_vc := null;
345 p_jms_text_message.text_lob := null;
346 end;
347 end if;
348 end set_text;
349
350 procedure get_text(p_jms_text_message in out nocopy sys.aq$_jms_text_message,
351 payload out nocopy clob)
352 is
353 l_text_lob_length number;
354 begin
355
356 begin
357 if p_jms_text_message.text_lob is null then
358 l_text_lob_length := 0;
359 else
360 l_text_lob_length := dbms_lob.getlength(p_jms_text_message.text_lob);
361 end if;
362 exception
363 when others then
364 l_text_lob_length := 0;
365 end;
366
367 -- ANKUNG
368 -- Because of bug 2676012, we set both the varchar and the clob with the
369 -- data
370 --if(p_jms_text_message.text_vc is not null) then
371
372 if(p_jms_text_message.text_vc is not null and
373 l_text_lob_length = 0) then
374
375 dbms_lob.createTemporary(lob_loc => payload,
376 cache => true);
377
378 dbms_lob.write(lob_loc => payload,
379 amount => p_jms_text_message.text_len,
380 offset => 1,
381 buffer => p_jms_text_message.text_vc);
382 else
383 payload := p_jms_text_message.text_lob;
384 end if;
385 end get_text;
386
387 --------------------------------------------------------------------------------
388 -- Returns true iff the parameter name is a user parameter.
389 --
390 -- p_parameter_name - the parameter name
391 --
392 -- return: true if the parameter name is a user parameter; false otherwise
393 --------------------------------------------------------------------------------
394 function is_user_parameter(p_parameter_name in varchar2) return boolean
395 is
396 begin
397 if((instr(p_parameter_name, 'BES_') <> 1) or
398 p_parameter_name in ('BES_DATABASE_ID', 'BES_SECURITY_GROUP_ID', PAYLOAD_OBJECT)) then
399 -- the parameter name does not begin with 'BES_'
400 -- (or it is an exception) so it is a user parameter
401
402 return true;
403 else
404 -- the parameter name is not a user parameter
405
406 return false;
407 end if;
408 end is_user_parameter;
409
410 --------------------------------------------------------------------------------
411 -- Tranforms a business event into a JMS Text Message.
412 --
413 -- p_event - the business event to transform
414 -- p_jms_text_message - the JMS Text Message
415 --------------------------------------------------------------------------------
416 procedure serialize(p_event in wf_event_t,
417 p_jms_text_message out nocopy sys.aq$_jms_text_message)
418 is
419 l_replyto varchar2(2000);
420 l_priority int;
421 i1 integer;
422 i2 integer;
423 l_agent_name varchar2(30);
424 l_address varchar2(1024);
425 l_protocol number;
426 l_aq_agent sys.aq$_agent;
427 l_wf_agent wf_agent_t;
428 l_parameter_list wf_parameter_list_t;
429 l_correlation_id varchar2(240);
430 begin
431 p_jms_text_message := sys.aq$_jms_text_message(
432 sys.aq$_jms_header(null, null, null, null, null, 0, null), 0, null, null);
433
434 -- set the JMS properties
435
436 set_type(p_jms_text_message, p_event.getValueForParameter(JMS_TYPE));
437 set_userid(p_jms_text_message, p_event.getValueForParameter(JMS_USERID));
438 set_appid(p_jms_text_message, p_event.getValueForParameter(JMS_APPID));
439 set_groupid(p_jms_text_message, p_event.getValueForParameter(JMS_GROUPID));
440 set_groupseq(p_jms_text_message, p_event.getValueForParameter(JMS_GROUPSEQ));
441
442 set_string_property(p_jms_text_message, EVENT_NAME, p_event.getEventName());
443 set_string_property(p_jms_text_message, EVENT_KEY, p_event.getEventKey());
444
445 -- parse the replyto attribute which must be in the form
446 -- "name:address:protocol"
447
448 l_replyto := p_event.getValueforParameter(JMS_REPLYTO);
449
450 if(l_replyto is not null) then
451 i1 := instr(l_replyto, ':');
452 i2 := instr(l_replyto, ':', 1, 2);
453
454 l_agent_name := substr(l_replyto, 1, i1 - 1);
455 l_address := substr(l_replyto, i1 + 1, i2 - i1 - 1);
456 l_protocol := substr(l_replyto, i2 + 1);
457
458 l_aq_agent := sys.aq$_agent(l_agent_name, l_address, l_protocol);
459
460 set_replyto(p_jms_text_message, l_aq_agent);
461 end if;
462
463 -- set the priority
464
465 if(p_event.getPriority() is not null) then
466 -- get the priority from the event
467
468 l_priority := p_event.getPriority();
469 elsif(p_event.getValueForParameter(PRIORITY) is not null) then
470 -- get the priority from the event parameters
471
472 l_priority := p_event.getValueForParameter(PRIORITY);
473 else
474 -- use the default priority
475
476 l_priority := DEFAULT_PRIORITY;
477 end if;
478
479 set_int_property(p_jms_text_message, PRIORITY, l_priority);
480
481 -- set the send date
482
483 if(p_event.getSendDate() is not null) then
484 set_string_property(p_jms_text_message, SEND_DATE, to_char(p_event.getSendDate(), DATE_MASK));
485 end if;
486
487 -- set the receive date
488
489 if(p_event.getReceiveDate() is not null) then
490 set_string_property(p_jms_text_message, RECEIVE_DATE, to_char(p_event.getReceiveDate(), DATE_MASK));
491 end if;
492
493 -- set the correlation id
494
495 if(p_event.getCorrelationId() is not null) then
496 -- get the correlation id from the event
497
498 l_correlation_id := p_event.getCorrelationId();
499 elsif(p_event.getValueForParameter(CORRELATION_ID) is not null) then
500 -- get the correlation id from the event parameters
501
502 l_correlation_id := p_event.getValueForParameter(CORRELATION_ID);
503 else
504 l_correlation_id := null;
505 end if;
506
507 if(l_correlation_id is not null) then
508 set_string_property(p_jms_text_message, CORRELATION_ID, l_correlation_id);
509 end if;
510
511 -- set the from agent
512
513 l_wf_agent := p_event.getFromAgent();
514
515 if(l_wf_agent is not null) then
516 set_string_property(p_jms_text_message, FROM_AGENT, l_wf_agent.getName() || '@'
517 || l_wf_agent.getSystem());
518 end if;
519
520 -- set the to agent
521
522 l_wf_agent := p_event.getToAgent();
523
524 if(l_wf_agent is not null) then
525 set_string_property(p_jms_text_message, TO_AGENT, l_wf_agent.getName() || '@'
526 || l_wf_agent.getSystem());
527 end if;
528
529 -- set the error subscription
530
531 if(p_event.getErrorSubscription() is not null) then
532 set_string_property(p_jms_text_message, ERROR_SUBSCRIPTION, p_event.getErrorSubscription());
533 end if;
534
535 -- set the error message
536
537 if(p_event.getErrorMessage() is not null) then
538 set_string_property(p_jms_text_message, ERROR_MESSAGE, p_event.getErrorMessage());
539 end if;
540
541 -- set the error stack
542
543 if(p_event.getErrorStack() is not null) then
544 set_string_property(p_jms_text_message, ERROR_STACK, p_event.getErrorStack());
545 end if;
546
547 -- set the wf_event_t user-defined properties
548
549 l_parameter_list := p_event.getParameterList();
550
551 if(l_parameter_list is not null and
552 l_parameter_list.first is not null) then
553 for i in l_parameter_list.first .. l_parameter_list.last loop
554 if(is_user_parameter(l_parameter_list(i).getName())) then
555 set_string_property(p_jms_text_message, l_parameter_list(i).getName(),
556 l_parameter_list(i).getValue());
557 end if;
558 end loop;
559 end if;
560
561 -- set the text payload
562
563 set_text(p_jms_text_message, p_event.getEventData());
564
565 exception when others then
566 wf_core.context('WF_EVENT_OJMSTEXT_QH', 'serialize',
567 'SQL error is ' || substr(sqlerrm, 1, 200));
568 raise;
569 end serialize;
570
571 --------------------------------------------------------------------------------
572 -- Tranforms a JMS Text Message into a business event.
573 --
574 -- p_jms_text_message - the JMS Text Message
575 -- p_event - the business event
576 --------------------------------------------------------------------------------
577 procedure deserialize(p_jms_text_message in out nocopy sys.aq$_jms_text_message,
578 p_event out nocopy wf_event_t)
579 is
580 i1 integer;
581
582 l_jms_agent varchar2(2000);
583 l_agent_name varchar2(30);
584 l_system_name varchar2(30);
585 l_from_agent wf_agent_t;
586 l_to_agent wf_agent_t;
587
588 l_jms_user_properties sys.aq$_jms_userproparray;
589 l_jms_property_name varchar2(100);
590 l_jms_property_value varchar2(2000);
591 l_boolean_value boolean;
592
593 l_clob clob;
594 begin
595 p_event := wf_event_t(0, null, null, null, null, null, null, null, null,
596 null, null, null, null);
597
598 if(p_jms_text_message.header.properties.count > 0) then
599 -- set the wf_event properties
600
601 p_event.setEventName(get_string_property(p_jms_text_message, EVENT_NAME));
602 p_event.setEventKey(get_string_property(p_jms_text_message, EVENT_KEY));
603 p_event.setPriority(get_int_property(p_jms_text_message, PRIORITY));
604 p_event.setSendDate(to_date(get_string_property(p_jms_text_message, SEND_DATE), DATE_MASK));
605 p_event.setReceiveDate(to_date(get_string_property(p_jms_text_message, RECEIVE_DATE), DATE_MASK));
606 p_event.setCorrelationId(get_string_property(p_jms_text_message, CORRELATION_ID));
607
608 -- parse the from agent which must be in the form "name@system"
609
610 l_jms_agent := get_string_property(p_jms_text_message, FROM_AGENT);
611
612 if(l_jms_agent is not null) then
613 i1 := instr(l_jms_agent, '@');
614
615 l_agent_name := substr(l_jms_agent, 1, i1 - 1);
616 l_system_name := substr(l_jms_agent, i1 + 1);
617
618 l_from_agent := wf_agent_t(l_agent_name, l_system_name);
619
620 p_event.setFromAgent(l_from_agent);
621 end if;
622
623 -- parse the to agent which must be in the form "name@system"
624
625 l_jms_agent := get_string_property(p_jms_text_message, TO_AGENT);
626
627 if(l_jms_agent is not null) then
628 i1 := instr(l_jms_agent, '@');
629
630 l_agent_name := substr(l_jms_agent, 1, i1 - 1);
631 l_system_name := substr(l_jms_agent, i1 + 1);
632
633 l_to_agent := wf_agent_t(l_agent_name, l_system_name);
634
635 p_event.setToAgent(l_to_agent);
636 end if;
637
638 p_event.setErrorSubscription(get_string_property(p_jms_text_message, ERROR_SUBSCRIPTION));
639 p_event.setErrorMessage(get_string_property(p_jms_text_message, ERROR_MESSAGE));
640 p_event.setErrorStack(get_string_property(p_jms_text_message, ERROR_STACK));
641
642 -- set the wf_event user-defined properties
643
644 l_jms_user_properties := p_jms_text_message.header.properties;
645
646 if(l_jms_user_properties.count > 0) then
647 for i in l_jms_user_properties.first .. l_jms_user_properties.last loop
648 l_jms_property_name := l_jms_user_properties(i).name;
649
650 if(is_user_parameter(l_jms_property_name)) then
651 -- since we don't know the property type, try retrieving the value
652 -- as each possible type until we find it (get a non-null value)
653
654 -- get the property as a string
655
656 l_jms_property_value :=
657 get_string_property(p_jms_text_message, l_jms_property_name);
658
659 if(l_jms_property_value is not null) then
660 goto found;
661 end if;
662
663 -- get the property as an int
664
665 l_jms_property_value :=
666 get_int_property(p_jms_text_message, l_jms_property_name);
667
668 if(l_jms_property_value is not null) then
669 goto found;
670 end if;
671
672 -- get the property as a boolean
673
674 l_boolean_value :=
675 get_boolean_property(p_jms_text_message, l_jms_property_name);
676
677 if(l_boolean_value is not null) then
678 if(l_boolean_value) then
679 l_jms_property_value := 'true';
680 else
681 l_jms_property_value := 'false';
682 end if;
683
684 goto found;
685 end if;
686
687 -- get the property as a byte
688
689 l_jms_property_value :=
690 get_byte_property(p_jms_text_message, l_jms_property_name);
691
692 if(l_jms_property_value is not null) then
693 goto found;
694 end if;
695
696 -- get the property as a short
697
698 l_jms_property_value :=
699 get_short_property(p_jms_text_message, l_jms_property_name);
700
701 if(l_jms_property_value is not null) then
702 goto found;
703 end if;
704
705 -- get the property as a long
706
707 l_jms_property_value :=
708 get_long_property(p_jms_text_message, l_jms_property_name);
709
710 if(l_jms_property_value is not null) then
711 goto found;
712 end if;
713
714 -- get the property as a float
715
716 l_jms_property_value :=
717 get_float_property(p_jms_text_message, l_jms_property_name);
718
719 if(l_jms_property_value is not null) then
720 goto found;
721 end if;
722
723 -- get the property as a double
724
725 l_jms_property_value :=
726 get_double_property(p_jms_text_message, l_jms_property_name);
727
728 if(l_jms_property_value is not null) then
729 goto found;
730 end if;
731
732 <<found>> null;
733
734 -- At this point, if l_jms_property_value is null, that means that
735 -- the property value really is null. In that case, do not add the
736 -- property to the parameter list.
737
738 if(l_jms_property_value is not null) then
739 p_event.addParameterToList(l_jms_property_name, l_jms_property_value);
740 end if;
741 end if;
742 end loop;
743 end if;
744 end if;
745
746 -- set the event data
747
748 get_text(p_jms_text_message, l_clob);
749
750 p_event.setEventData(l_clob);
751 end deserialize;
752
753 --------------------------------------------------------------------------------
754 -- Enqueues a business event into a JMS queue.
755 --
756 -- p_event - the business event to enqueue
757 -- p_out_agent_override - the out agent override
758 --------------------------------------------------------------------------------
759 procedure enqueue(p_event in wf_event_t,
760 p_out_agent_override in wf_agent_t)
761 is
762 l_jms_text_message sys.aq$_jms_text_message;
763
764 l_out_agent_name varchar2(30);
765 l_out_system_name varchar2(30);
766 l_out_queue_name varchar2(80);
767 l_q_correlation_id varchar2(240);
768
769 l_to_agent_name varchar2(30);
770 l_to_system_name varchar2(30);
771 l_to_queue_name varchar2(80);
772 l_to_address varchar2(1024);
773 l_to_protocol varchar2(30);
774 l_to_protocol_number number;
775
776 l_delay number;
777 l_enqueue_options dbms_aq.enqueue_options_t;
778 l_message_properties dbms_aq.message_properties_t;
779 l_msgid raw(16);
780
781 i number :=1;
782 l_type varchar2(8);
783 begin
784 serialize(p_event, l_jms_text_message);
785
786 -- determine the out queue
787
788 if(p_out_agent_override is not null) then
789 l_out_agent_name := p_out_agent_override.getName();
790 l_out_system_name := p_out_agent_override.getSystem();
791 else
792 l_out_agent_name := p_event.getFromAgent().getName();
793 l_out_system_name := p_event.getFromAgent().getSystem();
794 end if;
795
796 -- get the out queue name
797
798 select wfa.queue_name into l_out_queue_name
799 from wf_agents wfa,
800 wf_systems wfs
801 where wfa.name = l_out_agent_name
802 and wfs.name = l_out_system_name
803 and wfs.guid = wfa.system_guid;
804
805 -- if there is a to queue, we need to set the recipient list address
806
807 if((p_event.getToAgent() is not null) and
808 (l_out_agent_name <> 'WF_DEFERRED')) then
809 WF_EVENT.Set_Recipient_List(p_event,
810 l_out_agent_name ,
811 l_out_system_name,
812 l_message_properties);
813 end if;
814
815 -- set the priority
816
817 l_message_properties.priority := get_int_property(l_jms_text_message,
818 PRIORITY);
819
820 -- set the delay if required; also used for deferred agent
821
822 if(p_event.getSendDate() > sysdate) then
823 if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then
824 wf_log_pkg.string(wf_log_pkg.level_statement,
825 'wf.plsql.WF_EVENT_OJMSTEXT_QH.enqueue.delay',
826 'Delay Detected');
827 end if;
828
829 l_delay := (p_event.getSendDate() - sysdate)*24*60*60;
830
831 if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then
832 wf_log_pkg.string(wf_log_pkg.level_statement,
833 'wf.plsql.WF_EVENT_OJMSTEXT_QH.enqueue.delay_time',
834 'delay = ' || to_char(l_delay));
835 end if;
836
837 if(l_delay > 1) then
838 -- message_properties.delay is BINARY_INTEGER so check if delay is
839 -- too big, and set the max delay to be 2**31 - 1
840
841 if(l_delay >= power(2, 31)) then
842 l_message_properties.delay := power(2, 31) - 1;
843 else
844 l_message_properties.delay := l_delay;
845 end if;
846 end if;
847
848 else -- senddate may not be set, or it could be less than sysdate
849
850 -- for Web Services, it is possible for the SOAP client to directly
851 -- set the #MSG_DELAY parameter in the p_event. In so doing, the SOAP
852 -- client indicates how far later shall a message be dequeued next time.
853 -- the SOAP client does not use the senddate to achieve that because, the
854 -- SOAP java midtier time may be inconsistant to the sysdate in DB.
855 l_delay := p_event.getValueForParameter ('#MSG_DELAY');
856
857 if (l_delay is not NULL) then
858
859 if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then
860 wf_log_pkg.string(wf_log_pkg.level_statement,
861 'wf.plsql.WF_EVENT_OJMSTEXT_QH.enqueue.delay',
862 'Delay Detected');
863 wf_log_pkg.string(wf_log_pkg.level_statement,
864 'wf.plsql.WF_EVENT_OJMSTEXT_QH.enqueue.delay_time',
865 'delay = ' || to_char(l_delay));
866 end if;
867
868
869 if(l_delay > 1) then
870
871 -- message_properties.delay is BINARY_INTEGER so check if delay is
872 -- too big, and set the max delay to be 2**31 - 1
873
874 if(l_delay >= power(2, 31)) then
875 l_message_properties.delay := power(2, 31) - 1;
876 else
877 l_message_properties.delay := l_delay;
878 end if;
879 end if;
880
881 end if; -- l_delay is not NULL
882
883 end if; -- p_event.getSendDate
884
885
886 -- if we are enqueuing for an internal agent, must set the account name
887 -- into the correlation id
888 if (l_out_agent_name like 'WF_%'
889 or l_to_agent_name like 'WF_%') then
890 if wf_event.account_name is null then
891 wf_event.SetAccountName;
892 end if;
893 l_message_properties.correlation := wf_event.account_name;
894 end if;
895
896 IF ((l_out_agent_name = 'WF_JAVA_DEFERRED') OR
897 (l_to_agent_name = 'WF_JAVA_DEFERRED') OR
898 (l_out_agent_name = 'WF_JAVA_ERROR') OR
899 (l_to_agent_name = 'WF_JAVA_ERROR')) THEN
900
901 l_q_correlation_id := p_event.event_name;
902 else
903 l_q_correlation_id := p_event.getValueForParameter('Q_CORRELATION_ID');
904 end if;
905
906 IF (l_q_correlation_id IS NOT NULL) THEN
907 -- If account name is set, append account name in front of correlation id.
908 if (l_message_properties.correlation is not null) then
909 l_message_properties.correlation := l_message_properties.correlation ||
910 ':' || l_q_correlation_id;
911 else
912 l_message_properties.correlation := l_q_correlation_id;
913 end if;
914 END IF;
915
916 if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then
917 wf_log_pkg.string(wf_log_pkg.level_statement,
918 'wf.plsql.WF_EVENT_OJMSTEXT_QH.enqueue.dbms_aq',
919 'Calling dbms_aq.enqueue');
920 end if;
921
922 dbms_aq.enqueue(queue_name => l_out_queue_name,
923 enqueue_options => l_enqueue_options,
924 message_properties => l_message_properties,
925 payload => l_jms_text_message,
926 msgid => l_msgid);
927
928 -- Storing the enqueue msgid, similar to that been done WF_EVENT_QH
929 WF_EVENT.g_msgid := l_msgid;
930
931 if (wf_log_pkg.level_procedure >= fnd_log.g_current_runtime_level) then
932 wf_log_pkg.string(wf_log_pkg.level_procedure,
933 'wf.plsql.WF_EVENT_OJMSTEXT_QH.enqueue.End',
934 'Finished calling dbms_aq.enqueue');
935 end if;
936
937 exception
938 when others then
939 wf_core.context('WF_EVENT_OJMSTEXT_QH', 'enqueue', l_out_queue_name,
940 'SQL error is ' || substr(sqlerrm, 1, 200));
941 raise;
942 end enqueue;
943
944 --------------------------------------------------------------------------------
945 -- Dequeues a business event from a JMS queue.
946 --
947 -- p_agent_guid - the agent GUID
948 -- p_event - the business event
949 -- p_wait - the number of seconds to wait to dequeue the event
950 --------------------------------------------------------------------------------
951
952 procedure dequeue(p_agent_guid in raw,
953 p_event out nocopy wf_event_t,
954 p_wait in binary_integer)
955 is
956 l_queue_name varchar2(80);
957 l_agent_name varchar2(30);
958 l_dequeue_options dbms_aq.dequeue_options_t;
959 l_message_properties dbms_aq.message_properties_t;
960 l_jms_text_message sys.aq$_jms_text_message;
961 l_msgid raw(16);
962
963 no_messages exception;
964 pragma exception_init(no_messages, -25228);
965 --Define the snapshot too old error
966 snap_too_old exception;
967 pragma exception_init(snap_too_old, -1555);
968 begin
969 -- get the agent name
970
971 select upper(queue_name),
972 upper(name)
973 into l_queue_name,
974 l_agent_name
975 from wf_agents
976 where guid = p_agent_guid;
977
978 if (wf_log_pkg.level_procedure >= fnd_log.g_current_runtime_level) then
979 wf_log_pkg.string(wf_log_pkg.level_procedure,
980 'wf.plsql.WF_EVENT_OJMSTEXT_QH.dequeue.Begin',
981 'Dequeuing '||l_queue_name||' on '||l_agent_name);
982 end if;
983
984 -- Set correlation if the g_correlation is not null
985 if (WF_EVENT.g_correlation is not null and WF_EVENT.g_correlation <> '%') then
986
987 -- If seeded agent, set the account name as the prefix of correlation.
988 if (l_agent_name like 'WF_%') then
989
990 if(wf_event.account_name is null) then
991 wf_event.setAccountName();
992 end if;
993
994 l_dequeue_options.correlation := wf_event.account_name || ':' ||WF_EVENT.g_correlation;
995
996 else
997 l_dequeue_options.correlation := WF_EVENT.g_correlation;
998 end if;
999
1000 if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then
1001 wf_log_pkg.string(wf_log_pkg.level_statement,
1002 'wf.plsql.WF_EVENT_OJMSTEXT_QH.dequeue.corr',
1003 'Setting correlation: ' || l_dequeue_options.correlation);
1004 end if;
1005
1006 END IF;
1007 -- set the dequeue options
1008
1009 l_dequeue_options.consumer_name := l_agent_name;
1010 l_dequeue_options.wait := p_wait;
1011 l_dequeue_options.navigation := wf_event.navigation;
1012 -- l_dequeue_options.navigation := dbms_aq.FIRST_MESSAGE;
1013
1014 begin
1015 dbms_aq.dequeue(queue_name => l_queue_name,
1016 dequeue_options => l_dequeue_options,
1017 message_properties => l_message_properties, -- out
1018 payload => l_jms_text_message, -- out
1019 msgid => l_msgid); -- out
1020
1021 wf_event.navigation := dbms_aq.next_message;
1022 exception
1023 when no_messages then
1024 if (wf_log_pkg.level_event >= fnd_log.g_current_runtime_level) then
1025 wf_log_pkg.string(wf_log_pkg.level_event,
1026 'wf.plsql.WF_EVENT_OJMSTEXT_QH.dequeue.queue_empty',
1027 'No more messages in dequeue.');
1028 end if;
1029
1030 wf_event.navigation := dbms_aq.first_message;
1031 p_event := null;
1032
1033 return;
1034 --Capture the snapshot too old error
1035 when snap_too_old then
1036 --Workaround for AQ when receiving ORA-01555 using NEXT_MESSAGE as
1037 --navigation. We will try to set to FIRST_MESSAGE and dequeue to
1038 --silently handle this exception.
1039 if (wf_event.navigation = dbms_aq.FIRST_MESSAGE) then
1040 raise;
1041 else
1042 wf_event.navigation := dbms_aq.FIRST_MESSAGE;
1043 l_dequeue_options.navigation := wf_event.navigation;
1044 dbms_aq.dequeue(queue_name => l_queue_name,
1045 dequeue_options => l_dequeue_options,
1046 message_properties => l_message_properties, -- out
1047 payload => l_jms_text_message, -- out
1048 msgid => l_msgid); -- out
1049
1050 --Set the navigation now to the next message
1051 wf_event.navigation := dbms_aq.next_message;
1052 end if;
1053 when others then
1054 wf_event.navigation := dbms_aq.FIRST_MESSAGE;
1055 raise;
1056 end;
1057
1058 deserialize(l_jms_text_message, p_event);
1059
1060 -- Set the number of dequeue attempts made for this message
1061 p_event.addParameterToList('#MSG_DQ_ATTEMPTS',
1062 to_char(l_message_properties.attempts));
1063 -- set the receive date
1064
1065 p_event.setReceiveDate(sysdate);
1066
1067 -- set the msgid to the event
1068 p_event.addparametertolist('#MSG_ID', l_msgid);
1069
1070 if (wf_log_pkg.level_procedure >= fnd_log.g_current_runtime_level) then
1071 wf_log_pkg.string(wf_log_pkg.level_procedure,
1072 'wf.plsql.WF_EVENT_OJMSTEXT_QH.dequeue.End',
1073 'Finished');
1074 end if;
1075
1076 exception
1077 when others then
1078 wf_core.context('WF_EVENT_OJMSTEXT_QH', 'Dequeue', l_queue_name,
1079 'SQL error is ' || substr(sqlerrm, 1, 200));
1080 raise;
1081 end dequeue;
1082
1083 end wf_event_ojmstext_qh;
[Home] [Help]