[Home] [Help]
PACKAGE BODY: APPS.FND_PLSQL_CACHE
Source
1 PACKAGE BODY fnd_plsql_cache AS
2 /* $Header: AFUTPSCB.pls 120.1.12010000.1 2008/07/25 14:23:34 appldev ship $ */
3
4 -- ----------------------------------------------------------------------
5 -- Error codes
6 --
7 ERROR_WHEN_OTHERS CONSTANT NUMBER := -20001;
8 ERROR_MAX_NUMOF_KEYS CONSTANT NUMBER := -20002;
9 ERROR_MAX_NUMOF_VALUES CONSTANT NUMBER := -20003;
10 ERROR_MAX_NUMOF_VALUES_PER_KEY CONSTANT NUMBER := -20004;
11
12 -- ----------------------------------------------------------------------
13 -- Implementation constants
14 --
15 -- CACHE_EXTENT_SIZE :
16 -- Every time we need more indexes to put values, then cache storage
17 -- size is extended by 128.
18 --
19 CACHE_EXTENT_SIZE CONSTANT NUMBER := 128;
20 g_newline VARCHAR2(100);
21
22 -- ----------------------------------------------------------------------
23 -- Global variables
24 --
25 g_error_code NUMBER;
26
27 -- ----------------------------------------------------------------------
28 PROCEDURE raise_error(p_error_code IN NUMBER,
29 p_error_text IN VARCHAR2)
30 IS
31 BEGIN
32 --
33 -- Record the actual error code
34 --
35 g_error_code := p_error_code;
36
37 raise_application_error(p_error_code, p_error_text);
38 END raise_error;
39
40 -- ----------------------------------------------------------------------
41 PROCEDURE raise_1to1_error(p_controller IN cache_1to1_controller_type,
42 p_error_code IN NUMBER,
43 p_error_text IN VARCHAR2)
44 IS
45 l_error_text VARCHAR2(32000);
46 l_newline_indent VARCHAR2(2000);
47 BEGIN
48 l_newline_indent := g_newline || Rpad(' ', 11, ' ');
49
50 l_error_text := p_error_text || l_newline_indent ||
51 'cache type : ' || p_controller.cache_type || ' 1to1' || l_newline_indent ||
52 'cache name : ' || p_controller.name || l_newline_indent ||
53 'max numof keys : ' || p_controller.max_numof_keys || l_newline_indent ||
54 'numof keys : ' || p_controller.numof_keys;
55
56 raise_error(p_error_code, l_error_text);
57 END raise_1to1_error;
58
59 -- ----------------------------------------------------------------------
60 PROCEDURE raise_1tom_error(p_controller IN cache_1tom_controller_type,
61 p_error_code IN NUMBER,
62 p_error_text IN VARCHAR2)
63 IS
64 l_error_text VARCHAR2(32000);
65 l_newline_indent VARCHAR2(2000);
66 BEGIN
67 l_newline_indent := g_newline || Rpad(' ', 11, ' ');
68
69 l_error_text := p_error_text || l_newline_indent ||
70 'cache type : ' || p_controller.cache_type || ' 1tom' || l_newline_indent ||
71 'cache name : ' || p_controller.name || l_newline_indent ||
72 'max numof keys : ' || p_controller.max_numof_keys || l_newline_indent ||
73 'numof keys : ' || p_controller.numof_keys || l_newline_indent ||
74 'numof values : ' || p_controller.numof_values || l_newline_indent ||
75 'maxof available indexes : ' || p_controller.maxof_available_indexes || l_newline_indent ||
76 'numof available indexes : ' || p_controller.numof_available_indexes;
77
78 raise_error(p_error_code, l_error_text);
79 END raise_1tom_error;
80
81 -- ----------------------------------------------------------------------
82 FUNCTION trunc_arg(p_arg IN VARCHAR2)
83 RETURN VARCHAR2
84 IS
85 l_arg VARCHAR2(100);
86 BEGIN
87 IF (Length(p_arg) > 20) THEN
88 l_arg := Substr(p_arg, 1, 17) || '...';
89 ELSE
90 l_arg := p_arg;
91 END IF;
92
93 l_arg := '''' || l_arg || '''';
94
95 RETURN l_arg;
96 END trunc_arg;
97
98 -- ----------------------------------------------------------------------
99 PROCEDURE raise_others_error(p_func_name IN VARCHAR2,
100 p_arg1 IN VARCHAR2,
101 p_arg2 IN VARCHAR2 DEFAULT NULL,
102 p_arg3 IN VARCHAR2 DEFAULT NULL)
103 IS
104 l_error VARCHAR2(32000);
105 BEGIN
106 l_error := 'UTPSC.' || p_func_name || '(' || trunc_arg(p_arg1);
107
108 IF (p_arg2 IS NOT NULL) THEN
109 l_error := l_error || ', ' || trunc_arg(p_arg2);
110
111 IF (p_arg3 IS NOT NULL) THEN
112 l_error := l_error || ', ' || trunc_arg(p_arg3);
113 END IF;
114 END IF;
115
116 l_error := l_error || ') failed';
117
118 l_error := (l_error || g_newline ||
119 dbms_utility.format_error_stack());
120
121 IF (l_error NOT LIKE '%PL/SQL Call Stack%') THEN
122 l_error := (l_error || g_newline ||
123 dbms_utility.format_call_stack());
124 END IF;
125
126 raise_application_error(ERROR_WHEN_OTHERS, l_error);
127
128 --
129 -- Don't catch the exception here.
130 --
131 END raise_others_error;
132
133 -- ----------------------------------------------------------------------
134 FUNCTION boolean_to_char(p_boolean IN BOOLEAN)
135 RETURN VARCHAR2
136 IS
137 BEGIN
138 IF (p_boolean) THEN
139 RETURN 'TRUE';
140 ELSIF (NOT p_boolean) THEN
141 RETURN 'FALSE';
142 ELSE
143 RETURN NULL;
144 END IF;
145 END boolean_to_char;
146
147 -- ----------------------------------------------------------------------
148 PROCEDURE debug_put_line(p_debug IN VARCHAR2)
149 IS
150 l_debug VARCHAR2(100);
151 BEGIN
152 IF (Lengthb(p_debug) > 77) THEN
153 l_debug := substrb(p_debug, 1, 74) || '...';
154 ELSE
155 l_debug := p_debug;
156 END IF;
157 execute immediate ('begin dbms_output.put_line(''|' ||
158 REPLACE(l_debug, '''', '''''') ||
159 ''');end;');
160 END debug_put_line;
161
162 -- ----------------------------------------------------------------------
163 PROCEDURE debug_append_attribute(px_debug_value IN OUT nocopy VARCHAR2,
164 p_value IN VARCHAR2)
165 IS
166 BEGIN
167 IF (p_value IS NOT NULL) THEN
168 IF (px_debug_value IS NOT NULL) THEN
169 px_debug_value := px_debug_value || '.';
170 END IF;
171 px_debug_value := px_debug_value || p_value;
172 END IF;
173 END debug_append_attribute;
174
175 -- ----------------------------------------------------------------------
176 FUNCTION debug_get_generic_value_concat(p_value IN generic_cache_value_type)
177 RETURN VARCHAR2
178 IS
179 l_debug_value VARCHAR2(32000);
180 BEGIN
181 l_debug_value := NULL;
182 debug_append_attribute(l_debug_value, p_value.varchar2_1);
183 debug_append_attribute(l_debug_value, p_value.varchar2_2);
184 debug_append_attribute(l_debug_value, p_value.varchar2_3);
185 debug_append_attribute(l_debug_value, p_value.varchar2_4);
186 debug_append_attribute(l_debug_value, p_value.varchar2_5);
187 debug_append_attribute(l_debug_value, p_value.varchar2_6);
188 debug_append_attribute(l_debug_value, p_value.varchar2_7);
189 debug_append_attribute(l_debug_value, p_value.varchar2_8);
190 debug_append_attribute(l_debug_value, p_value.varchar2_9);
191 debug_append_attribute(l_debug_value, p_value.varchar2_10);
192 debug_append_attribute(l_debug_value, p_value.varchar2_11);
193 debug_append_attribute(l_debug_value, p_value.varchar2_12);
194 debug_append_attribute(l_debug_value, p_value.varchar2_13);
195 debug_append_attribute(l_debug_value, p_value.varchar2_14);
196 debug_append_attribute(l_debug_value, p_value.varchar2_15);
197
198 debug_append_attribute(l_debug_value, To_char(p_value.number_1));
199 debug_append_attribute(l_debug_value, To_char(p_value.number_2));
200 debug_append_attribute(l_debug_value, To_char(p_value.number_3));
201 debug_append_attribute(l_debug_value, To_char(p_value.number_4));
202 debug_append_attribute(l_debug_value, To_char(p_value.number_5));
203
204 debug_append_attribute(l_debug_value, To_char(p_value.date_1, 'YYYY/MM/DD HH24:MI:SS'));
205 debug_append_attribute(l_debug_value, To_char(p_value.date_2, 'YYYY/MM/DD HH24:MI:SS'));
206 debug_append_attribute(l_debug_value, To_char(p_value.date_3, 'YYYY/MM/DD HH24:MI:SS'));
207
208 debug_append_attribute(l_debug_value, boolean_to_char(p_value.boolean_1));
209 debug_append_attribute(l_debug_value, boolean_to_char(p_value.boolean_2));
210 debug_append_attribute(l_debug_value, boolean_to_char(p_value.boolean_3));
211
212 RETURN(l_debug_value);
213 END debug_get_generic_value_concat;
214
215 -- ----------------------------------------------------------------------
216 PROCEDURE internal_1to1_debug
217 (px_controller IN cache_1to1_controller_type,
218 px_storage IN generic_cache_values_type,
219 p_debug_level IN VARCHAR2)
220 IS
221 l_debug_line VARCHAR2(32000);
222 BEGIN
223 debug_put_line(' ');
224 debug_put_line(Rpad('== ' || px_controller.cache_type || ' 1to1 Cache Debug ==', 77, '='));
225 debug_put_line('Name : ' || px_controller.name);
226 debug_put_line('Max Number Of Keys : ' || px_controller.max_numof_keys);
227 debug_put_line('Number Of Keys : ' || px_controller.numof_keys);
228 debug_put_line(' ');
229
230 IF (p_debug_level IN (CDL_SUMMARY_KEYS,
231 CDL_SUMMARY_KEYS_VALUES)) THEN
232 IF ((px_controller.cache_type = CACHE_TYPE_GENERIC) AND
233 (p_debug_level = CDL_SUMMARY_KEYS_VALUES)) THEN
234 --
235 -- Print Header for Keys/Values
236 --
237 debug_put_line('Index Key Value ');
238 debug_put_line('===== =================================== ===================================');
239 ELSE
240 --
241 -- Print Header for Keys
242 --
243 debug_put_line('Index Key ');
244 debug_put_line('===== =======================================================================');
245 END IF;
246
247 FOR i IN 1..px_controller.max_numof_keys LOOP
248 IF (px_controller.keys(i) IS NOT NULL) THEN
249
250 l_debug_line := Lpad(i, 5, ' ') || ' ';
251
252 IF ((px_controller.cache_type = CACHE_TYPE_GENERIC) AND
253 (p_debug_level = CDL_SUMMARY_KEYS_VALUES)) THEN
254
255 IF (Length(px_controller.keys(i)) > 35) THEN
256 l_debug_line := l_debug_line || Substr(px_controller.keys(i), 1, 32) || '...';
257 ELSE
258 l_debug_line := l_debug_line || Rpad(px_controller.keys(i), 35, ' ');
259 END IF;
260
261 l_debug_line := l_debug_line || ' ' || debug_get_generic_value_concat(px_storage(i));
262 ELSE
263 l_debug_line := l_debug_line || px_controller.keys(i);
264 END IF;
265
266 debug_put_line(l_debug_line);
267
268 END IF;
269 END LOOP;
270 END IF;
271
272 debug_put_line(Rpad('=', 77, '='));
273 EXCEPTION
274 WHEN OTHERS THEN
275 raise_others_error(p_func_name => 'internal_1to1_debug',
276 p_arg1 => px_controller.name,
277 p_arg2 => p_debug_level);
278 END internal_1to1_debug;
279
280 -- ----------------------------------------------------------------------
281 PROCEDURE internal_1tom_debug
282 (px_controller IN cache_1tom_controller_type,
283 px_storage IN generic_cache_values_type,
284 p_debug_level IN VARCHAR2)
285 IS
286 l_index NUMBER;
287 l_debug_line VARCHAR2(32000);
288 BEGIN
289 debug_put_line(' ');
290 debug_put_line(Rpad('== ' || px_controller.cache_type || ' 1toM Cache Debug ==', 77, '='));
291 debug_put_line('Name : ' || px_controller.name);
292 debug_put_line('Max Number Of Keys : ' || px_controller.max_numof_keys);
293 debug_put_line('Number Of Keys : ' || px_controller.numof_keys);
294 debug_put_line('Number Of Values : ' || px_controller.numof_values);
295 debug_put_line('Max Of Avail Indexes : ' || px_controller.maxof_available_indexes);
296 debug_put_line('Num Of Avail Indexes : ' || px_controller.numof_available_indexes);
297 debug_put_line('Available Indexes : ' || px_controller.available_indexes);
298 debug_put_line(' ');
299
300 IF (p_debug_level IN (CDL_SUMMARY_KEYS,
301 CDL_SUMMARY_KEYS_VALUES)) THEN
302 --
303 -- Print Header for Keys
304 --
305 debug_put_line('Index Key #Vals Value Indexes ');
306 debug_put_line('===== ==================================== ===== ============================');
307
308 FOR i IN 1..px_controller.max_numof_keys LOOP
309 IF (px_controller.keys(i) IS NOT NULL) THEN
310
311 l_debug_line := Lpad(i, 5, ' ') || ' ';
312
313 IF (Length(px_controller.keys(i)) > 36) THEN
314 l_debug_line := l_debug_line || Substr(px_controller.keys(i), 1, 33) || '...';
315 ELSE
316 l_debug_line := l_debug_line || Rpad(px_controller.keys(i), 36, ' ');
317 END IF;
318
319 l_debug_line := l_debug_line || (' ' || Rpad(px_controller.numof_indexes(i), 5, ' ') || ' ' ||
320 px_controller.value_indexes(i));
321
322 debug_put_line(l_debug_line);
323
324 IF ((px_controller.cache_type = CACHE_TYPE_GENERIC) AND
325 (p_debug_level = CDL_SUMMARY_KEYS_VALUES)) THEN
326 IF (px_controller.numof_indexes(i) > 0) THEN
327 --
328 -- Print Header for Values
329 --
330 debug_put_line(' Index Value ');
331 debug_put_line(' ===== =================================================================');
332 END IF;
333
334 FOR j IN 1..px_controller.numof_indexes(i) LOOP
335 l_index := To_number(Substr(px_controller.value_indexes(i),
336 (j - 1) * CACHE_NUMOF_DIGITS_PER_INDEX + 1,
337 CACHE_NUMOF_DIGITS_PER_INDEX));
338
339 debug_put_line(Lpad(' ', 6, ' ') || Lpad(l_index, 5, ' ') || ' ' ||
340 debug_get_generic_value_concat(px_storage(l_index)));
341 END LOOP;
342 debug_put_line(' ');
343 END IF;
344 END IF;
345 END LOOP;
346 END IF;
347
348 debug_put_line(Rpad('=', 77, '='));
349 EXCEPTION
350 WHEN OTHERS THEN
351 raise_others_error(p_func_name => 'internal_1tom_debug',
352 p_arg1 => px_controller.name,
353 p_arg2 => p_debug_level);
354 END internal_1tom_debug;
355
356 -- ----------------------------------------------------------------------
357 PROCEDURE generic_cache_new_value
358 (x_value OUT nocopy generic_cache_value_type,
359 p_varchar2_1 IN VARCHAR2 DEFAULT NULL,
360 p_varchar2_2 IN VARCHAR2 DEFAULT NULL,
361 p_varchar2_3 IN VARCHAR2 DEFAULT NULL,
362 p_varchar2_4 IN VARCHAR2 DEFAULT NULL,
363 p_varchar2_5 IN VARCHAR2 DEFAULT NULL,
364 p_varchar2_6 IN VARCHAR2 DEFAULT NULL,
365 p_varchar2_7 IN VARCHAR2 DEFAULT NULL,
366 p_varchar2_8 IN VARCHAR2 DEFAULT NULL,
367 p_varchar2_9 IN VARCHAR2 DEFAULT NULL,
368 p_varchar2_10 IN VARCHAR2 DEFAULT NULL,
369 p_varchar2_11 IN VARCHAR2 DEFAULT NULL,
370 p_varchar2_12 IN VARCHAR2 DEFAULT NULL,
374 p_number_1 IN NUMBER DEFAULT NULL,
371 p_varchar2_13 IN VARCHAR2 DEFAULT NULL,
372 p_varchar2_14 IN VARCHAR2 DEFAULT NULL,
373 p_varchar2_15 IN VARCHAR2 DEFAULT NULL,
375 p_number_2 IN NUMBER DEFAULT NULL,
376 p_number_3 IN NUMBER DEFAULT NULL,
377 p_number_4 IN NUMBER DEFAULT NULL,
378 p_number_5 IN NUMBER DEFAULT NULL,
379 p_date_1 IN DATE DEFAULT NULL,
380 p_date_2 IN DATE DEFAULT NULL,
381 p_date_3 IN DATE DEFAULT NULL,
382 p_boolean_1 IN BOOLEAN DEFAULT NULL,
383 p_boolean_2 IN BOOLEAN DEFAULT NULL,
384 p_boolean_3 IN BOOLEAN DEFAULT NULL)
385 IS
386 BEGIN
387 x_value.varchar2_1 := p_varchar2_1;
388 x_value.varchar2_2 := p_varchar2_2;
389 x_value.varchar2_3 := p_varchar2_3;
390 x_value.varchar2_4 := p_varchar2_4;
391 x_value.varchar2_5 := p_varchar2_5;
392 x_value.varchar2_6 := p_varchar2_6;
393 x_value.varchar2_7 := p_varchar2_7;
394 x_value.varchar2_8 := p_varchar2_8;
395 x_value.varchar2_9 := p_varchar2_9;
396 x_value.varchar2_10 := p_varchar2_10;
397 x_value.varchar2_11 := p_varchar2_11;
398 x_value.varchar2_12 := p_varchar2_12;
399 x_value.varchar2_13 := p_varchar2_13;
400 x_value.varchar2_14 := p_varchar2_14;
401 x_value.varchar2_15 := p_varchar2_15;
402 x_value.number_1 := p_number_1;
403 x_value.number_2 := p_number_2;
404 x_value.number_3 := p_number_3;
405 x_value.number_4 := p_number_4;
406 x_value.number_5 := p_number_5;
407 x_value.date_1 := p_date_1;
408 x_value.date_2 := p_date_2;
409 x_value.date_3 := p_date_3;
410 x_value.boolean_1 := p_boolean_1;
411 x_value.boolean_2 := p_boolean_2;
412 x_value.boolean_3 := p_boolean_3;
413 END generic_cache_new_value;
414
415 -- ----------------------------------------------------------------------
416 FUNCTION generic_cache_new_value
417 (p_varchar2_1 IN VARCHAR2 DEFAULT NULL,
418 p_varchar2_2 IN VARCHAR2 DEFAULT NULL,
419 p_varchar2_3 IN VARCHAR2 DEFAULT NULL,
420 p_varchar2_4 IN VARCHAR2 DEFAULT NULL,
421 p_varchar2_5 IN VARCHAR2 DEFAULT NULL,
422 p_varchar2_6 IN VARCHAR2 DEFAULT NULL,
423 p_varchar2_7 IN VARCHAR2 DEFAULT NULL,
424 p_varchar2_8 IN VARCHAR2 DEFAULT NULL,
425 p_varchar2_9 IN VARCHAR2 DEFAULT NULL,
426 p_varchar2_10 IN VARCHAR2 DEFAULT NULL,
427 p_varchar2_11 IN VARCHAR2 DEFAULT NULL,
428 p_varchar2_12 IN VARCHAR2 DEFAULT NULL,
429 p_varchar2_13 IN VARCHAR2 DEFAULT NULL,
430 p_varchar2_14 IN VARCHAR2 DEFAULT NULL,
431 p_varchar2_15 IN VARCHAR2 DEFAULT NULL,
432 p_number_1 IN NUMBER DEFAULT NULL,
433 p_number_2 IN NUMBER DEFAULT NULL,
434 p_number_3 IN NUMBER DEFAULT NULL,
435 p_number_4 IN NUMBER DEFAULT NULL,
436 p_number_5 IN NUMBER DEFAULT NULL,
437 p_date_1 IN DATE DEFAULT NULL,
438 p_date_2 IN DATE DEFAULT NULL,
439 p_date_3 IN DATE DEFAULT NULL,
440 p_boolean_1 IN BOOLEAN DEFAULT NULL,
441 p_boolean_2 IN BOOLEAN DEFAULT NULL,
442 p_boolean_3 IN BOOLEAN DEFAULT NULL)
443 RETURN generic_cache_value_type
444 IS
445 l_value generic_cache_value_type;
446 BEGIN
447 l_value.varchar2_1 := p_varchar2_1;
448 l_value.varchar2_2 := p_varchar2_2;
449 l_value.varchar2_3 := p_varchar2_3;
450 l_value.varchar2_4 := p_varchar2_4;
451 l_value.varchar2_5 := p_varchar2_5;
452 l_value.varchar2_6 := p_varchar2_6;
453 l_value.varchar2_7 := p_varchar2_7;
454 l_value.varchar2_8 := p_varchar2_8;
455 l_value.varchar2_9 := p_varchar2_9;
456 l_value.varchar2_10 := p_varchar2_10;
457 l_value.varchar2_11 := p_varchar2_11;
458 l_value.varchar2_12 := p_varchar2_12;
459 l_value.varchar2_13 := p_varchar2_13;
460 l_value.varchar2_14 := p_varchar2_14;
461 l_value.varchar2_15 := p_varchar2_15;
462 l_value.number_1 := p_number_1;
463 l_value.number_2 := p_number_2;
464 l_value.number_3 := p_number_3;
465 l_value.number_4 := p_number_4;
466 l_value.number_5 := p_number_5;
467 l_value.date_1 := p_date_1;
468 l_value.date_2 := p_date_2;
469 l_value.date_3 := p_date_3;
470 l_value.boolean_1 := p_boolean_1;
471 l_value.boolean_2 := p_boolean_2;
472 l_value.boolean_3 := p_boolean_3;
473
474 RETURN (l_value);
475 END generic_cache_new_value;
476
477 -- ======================================================================
478 -- Generic One Cache:
479 -- ======================================================================
480
481 -- ----------------------------------------------------------------------
482 PROCEDURE generic_1to1_init
483 (p_name IN VARCHAR2,
484 px_controller IN OUT nocopy cache_1to1_controller_type,
485 px_storage IN OUT nocopy generic_cache_values_type,
486 p_max_numof_keys IN NUMBER DEFAULT CACHE_MAX_NUMOF_KEYS)
487 IS
488 BEGIN
489 custom_1to1_init(p_name,
490 px_controller,
491 p_max_numof_keys);
492 px_controller.cache_type := CACHE_TYPE_GENERIC;
493 px_storage.DELETE;
494 EXCEPTION
495 WHEN OTHERS THEN
496 raise_others_error(p_func_name => 'generic_1to1_init',
497 p_arg1 => p_name,
501 -- ----------------------------------------------------------------------
498 p_arg2 => p_max_numof_keys);
499 END generic_1to1_init;
500
502 PROCEDURE generic_1to1_get_value
503 (px_controller IN OUT nocopy cache_1to1_controller_type,
504 px_storage IN OUT nocopy generic_cache_values_type,
505 p_key IN VARCHAR2,
506 x_value OUT nocopy generic_cache_value_type,
507 x_return_code OUT nocopy VARCHAR2)
508 IS
509 l_key VARCHAR2(2048) := fnd_global.session_context || '.' || p_key;
510 l_key_index NUMBER;
511 BEGIN
512 -- Similar logic exists in custom_1to1_get_get_index()
513 --
514 -- Get the key index. (1..px_controller.max_numof_keys)
515 --
516 l_key_index := dbms_utility.get_hash_value(l_key, 1,
517 px_controller.max_numof_keys);
518 --
519 -- Is it in cache?
520 --
521 IF (px_controller.keys(l_key_index) = l_key) THEN
522 x_value := px_storage(l_key_index);
523 x_return_code := CACHE_FOUND;
524 ELSE
525 x_return_code := CACHE_NOTFOUND;
526 END IF;
527 EXCEPTION
528 WHEN OTHERS THEN
529 raise_others_error(p_func_name => 'generic_1to1_get_value',
530 p_arg1 => px_controller.name,
531 p_arg2 => p_key);
532 END generic_1to1_get_value;
533
534 -- ----------------------------------------------------------------------
535 PROCEDURE generic_1to1_put_value
536 (px_controller IN OUT nocopy cache_1to1_controller_type,
537 px_storage IN OUT nocopy generic_cache_values_type,
538 p_key IN VARCHAR2,
539 p_value IN generic_cache_value_type)
540 IS
541 l_key VARCHAR2(2048) := fnd_global.session_context || '.' || p_key;
542 l_key_index NUMBER;
543 BEGIN
544 -- Similar logic exists in custom_1to1_get_put_index()
545 --
546 -- Get the key index. (1..px_controller.max_numof_keys)
547 --
548 l_key_index := dbms_utility.get_hash_value(l_key, 1,
549 px_controller.max_numof_keys);
550
551 IF (px_controller.keys(l_key_index) IS NULL) THEN
552 --
553 -- A new key is cached.
554 --
555 px_controller.numof_keys := px_controller.numof_keys + 1;
556 END IF;
557
558 --
559 -- Update the cache. Old entry is overwritten.
560 --
561 px_controller.keys(l_key_index) := l_key;
562 px_storage(l_key_index) := p_value;
563 EXCEPTION
564 WHEN OTHERS THEN
565 raise_others_error(p_func_name => 'generic_1to1_put_value',
566 p_arg1 => px_controller.name,
567 p_arg2 => p_key);
568 END generic_1to1_put_value;
569
570 -- ----------------------------------------------------------------------
571 PROCEDURE generic_1to1_remove_key
572 (px_controller IN OUT nocopy cache_1to1_controller_type,
573 p_key IN VARCHAR2)
574 IS
575 BEGIN
576 custom_1to1_remove_key(px_controller,
577 p_key);
578 EXCEPTION
579 WHEN OTHERS THEN
580 raise_others_error(p_func_name => 'generic_1to1_remove_key',
581 p_arg1 => px_controller.name,
582 p_arg2 => p_key);
583 END generic_1to1_remove_key;
584
585 -- ----------------------------------------------------------------------
586 PROCEDURE generic_1to1_clear
587 (px_controller IN OUT nocopy cache_1to1_controller_type,
588 px_storage IN OUT nocopy generic_cache_values_type)
589 IS
590 BEGIN
591 generic_1to1_init(px_controller.name,
592 px_controller,
593 px_storage,
594 px_controller.max_numof_keys);
595 EXCEPTION
596 WHEN OTHERS THEN
597 raise_others_error(p_func_name => 'generic_1to1_clear',
598 p_arg1 => px_controller.name);
599 END generic_1to1_clear;
600
601 -- ----------------------------------------------------------------------
602 PROCEDURE generic_1to1_debug
603 (px_controller IN cache_1to1_controller_type,
604 px_storage IN generic_cache_values_type,
605 p_debug_level IN VARCHAR2 DEFAULT CDL_SUMMARY_KEYS_VALUES)
606 IS
607 BEGIN
608 internal_1to1_debug(px_controller, px_storage, p_debug_level);
609 EXCEPTION
610 WHEN OTHERS THEN
611 raise_others_error(p_func_name => 'generic_1to1_debug',
612 p_arg1 => px_controller.name,
613 p_arg2 => p_debug_level);
614 END generic_1to1_debug;
615
616
617 -- ======================================================================
618 -- Generic Many Cache:
619 -- ======================================================================
620
621 -- ----------------------------------------------------------------------
622 PROCEDURE generic_1tom_init
623 (p_name IN VARCHAR2,
624 px_controller IN OUT nocopy cache_1tom_controller_type,
625 px_storage IN OUT nocopy generic_cache_values_type,
626 p_max_numof_keys IN NUMBER DEFAULT CACHE_MAX_NUMOF_KEYS)
627 IS
628 BEGIN
629 custom_1tom_init(p_name,
630 px_controller,
631 p_max_numof_keys);
635 WHEN OTHERS THEN
632 px_controller.cache_type := CACHE_TYPE_GENERIC;
633 px_storage.DELETE;
634 EXCEPTION
636 raise_others_error(p_func_name => 'generic_1tom_init',
637 p_arg1 => p_name,
638 p_arg2 => p_max_numof_keys);
639 END generic_1tom_init;
640
641 -- ----------------------------------------------------------------------
642 PROCEDURE generic_1tom_get_values
643 (px_controller IN OUT nocopy cache_1tom_controller_type,
644 px_storage IN OUT nocopy generic_cache_values_type,
645 p_key IN VARCHAR2,
646 x_numof_values OUT nocopy NUMBER,
647 x_values OUT nocopy generic_cache_values_type,
648 x_return_code OUT nocopy VARCHAR2)
649 IS
650 l_key VARCHAR2(2048) := fnd_global.session_context || '.' || p_key;
651 l_key_index NUMBER;
652 l_indexes_vc2 VARCHAR2(2048);
653 BEGIN
654 -- Similar logic exists in custom_1tom_get_get_indexes()
655 --
656 -- Get the key index. (1..px_controller.max_numof_keys)
657 --
658 l_key_index := dbms_utility.get_hash_value(l_key, 1,
659 px_controller.max_numof_keys);
660 --
661 -- Is it in cache?
662 --
663 IF (px_controller.keys(l_key_index) = l_key) THEN
664 --
665 -- Resolve the value indexes.
666 --
667 l_indexes_vc2 := px_controller.value_indexes(l_key_index);
668 --
669 -- Use indexes list to get values.
670 --
671 FOR i IN 1..px_controller.numof_indexes(l_key_index) LOOP
672 x_values(i) := px_storage(To_number(Substr(l_indexes_vc2, 1, CACHE_NUMOF_DIGITS_PER_INDEX)));
673 l_indexes_vc2 := Substr(l_indexes_vc2, CACHE_NUMOF_DIGITS_PER_INDEX + 1);
674 END LOOP;
675
676 x_numof_values := px_controller.numof_indexes(l_key_index);
677 x_return_code := CACHE_FOUND;
678 ELSE
679
680 x_return_code := CACHE_NOTFOUND;
681 END IF;
682 EXCEPTION
683 WHEN OTHERS THEN
684 raise_others_error(p_func_name => 'generic_1tom_get_values',
685 p_arg1 => px_controller.name,
686 p_arg2 => p_key);
687 END generic_1tom_get_values;
688
689 -- ----------------------------------------------------------------------
690 PROCEDURE generic_1tom_put_values
691 (px_controller IN OUT nocopy cache_1tom_controller_type,
692 px_storage IN OUT nocopy generic_cache_values_type,
693 p_key IN VARCHAR2,
694 p_numof_values IN NUMBER,
695 p_values IN generic_cache_values_type)
696 IS
697 l_indexes custom_cache_indexes_type;
698 l_return_code VARCHAR2(1);
699 BEGIN
700 --
701 -- Since the put logic in 1tom is complicated, it is not repeated here.
702 -- Hopefully put will not be called many times anyway.
703 --
704 custom_1tom_get_put_indexes(px_controller,
705 p_key,
706 p_numof_values,
707 l_indexes,
708 l_return_code);
709
710 IF (l_return_code = CACHE_PUT_IS_SUCCESSFUL) THEN
711 FOR i IN 1..p_numof_values LOOP
712 px_storage(l_indexes(i)) := p_values(i);
713 END LOOP;
714 END IF;
715
716 EXCEPTION
717 WHEN OTHERS THEN
718 raise_others_error(p_func_name => 'generic_1tom_put_values',
719 p_arg1 => px_controller.name,
720 p_arg2 => p_key,
721 p_arg3 => p_numof_values);
722 END generic_1tom_put_values;
723
724 -- ----------------------------------------------------------------------
725 PROCEDURE generic_1tom_remove_key
726 (px_controller IN OUT nocopy cache_1tom_controller_type,
727 p_key IN VARCHAR2)
728 IS
729 BEGIN
730 custom_1tom_remove_key(px_controller,
731 p_key);
732 EXCEPTION
733 WHEN OTHERS THEN
734 raise_others_error(p_func_name => 'generic_1tom_remove_key',
735 p_arg1 => px_controller.name,
736 p_arg2 => p_key);
737 END generic_1tom_remove_key;
738
739 -- ----------------------------------------------------------------------
740 PROCEDURE generic_1tom_clear
741 (px_controller IN OUT nocopy cache_1tom_controller_type,
742 px_storage IN OUT nocopy generic_cache_values_type)
743 IS
744 BEGIN
745 generic_1tom_init(px_controller.name,
746 px_controller,
747 px_storage,
748 px_controller.max_numof_keys);
749 EXCEPTION
750 WHEN OTHERS THEN
751 raise_others_error(p_func_name => 'generic_1tom_clear',
752 p_arg1 => px_controller.name);
753 END generic_1tom_clear;
754
755 -- ----------------------------------------------------------------------
756 PROCEDURE generic_1tom_debug
757 (px_controller IN cache_1tom_controller_type,
758 px_storage IN generic_cache_values_type,
759 p_debug_level IN VARCHAR2 DEFAULT CDL_SUMMARY_KEYS_VALUES)
760 IS
761 l_index NUMBER;
762 BEGIN
763 internal_1tom_debug(px_controller, px_storage, p_debug_level);
764 EXCEPTION
765 WHEN OTHERS THEN
769 END generic_1tom_debug;
766 raise_others_error(p_func_name => 'generic_1tom_debug',
767 p_arg1 => px_controller.name,
768 p_arg2 => p_debug_level);
770
771
772 -- ======================================================================
773 -- Custom One Cache:
774 -- ======================================================================
775
776 -- ----------------------------------------------------------------------
777 PROCEDURE custom_1to1_init
778 (p_name IN VARCHAR2,
779 px_controller IN OUT nocopy cache_1to1_controller_type,
780 p_max_numof_keys IN NUMBER DEFAULT CACHE_MAX_NUMOF_KEYS)
781 IS
782 BEGIN
783 px_controller.name := Nvl(p_name, 'NONAME Cache');
784 px_controller.cache_type := CACHE_TYPE_CUSTOM;
785 px_controller.max_numof_keys := p_max_numof_keys;
786 px_controller.numof_keys := 0;
787
788 --
789 -- 1 <= p_max_numof_keys <= CACHE_MAX_NUMOF_KEYS, 2^n = p_max_numof_keys
790 --
791 IF (NOT ((p_max_numof_keys >= 1) AND
792 (p_max_numof_keys <= CACHE_MAX_NUMOF_KEYS) AND
793 (p_max_numof_keys IN (1,2,4,8,16,32,64,128,256,512,1024)))) THEN
794 raise_1to1_error(px_controller, ERROR_MAX_NUMOF_KEYS,
795 'p_max_numof_keys (' || p_max_numof_keys ||
796 ') must be between 1 and ' ||
797 CACHE_MAX_NUMOF_KEYS || ', and must be power of 2.');
798 END IF;
799
800 px_controller.keys := cache_varchar2_varray_type();
801 px_controller.keys.extend(px_controller.max_numof_keys);
802 EXCEPTION
803 WHEN OTHERS THEN
804 raise_others_error(p_func_name => 'custom_1to1_init',
805 p_arg1 => p_name,
806 p_arg2 => p_max_numof_keys);
807 END custom_1to1_init;
808
809 -- ----------------------------------------------------------------------
810 PROCEDURE custom_1to1_get_get_index
811 (px_controller IN OUT nocopy cache_1to1_controller_type,
812 p_key IN VARCHAR2,
813 x_index OUT nocopy BINARY_INTEGER,
814 x_return_code OUT nocopy VARCHAR2)
815 IS
816 l_key VARCHAR2(2048) := fnd_global.session_context || '.' || p_key;
817 l_key_index NUMBER;
818 BEGIN
819 -- Similar logic exists in generic_1to1_get_value()
820 --
821 -- Get the key index. (1..px_controller.max_numof_keys)
822 --
823 l_key_index := dbms_utility.get_hash_value(l_key, 1,
824 px_controller.max_numof_keys);
825 --
826 -- Is it in cache?
827 --
828 IF (px_controller.keys(l_key_index) = l_key) THEN
829 x_index := l_key_index;
830 x_return_code := CACHE_FOUND;
831 ELSE
832 x_return_code := CACHE_NOTFOUND;
833 END IF;
834 EXCEPTION
835 WHEN OTHERS THEN
836 raise_others_error(p_func_name => 'custom_1to1_get_get_index',
837 p_arg1 => px_controller.name,
838 p_arg2 => p_key);
839 END custom_1to1_get_get_index;
840
841 -- ----------------------------------------------------------------------
842 PROCEDURE custom_1to1_get_put_index
843 (px_controller IN OUT nocopy cache_1to1_controller_type,
844 p_key IN VARCHAR2,
845 x_index OUT nocopy BINARY_INTEGER)
846 IS
847 l_key VARCHAR2(2048) := fnd_global.session_context || '.' || p_key;
848 l_key_index NUMBER;
849 BEGIN
850 -- Similar logic exists in generic_1to1_put_value()
851 --
852 -- Get the key index. (1..px_controller.max_numof_keys)
853 --
854 l_key_index := dbms_utility.get_hash_value(l_key, 1,
855 px_controller.max_numof_keys);
856
857 IF (px_controller.keys(l_key_index) IS NULL) THEN
858 --
859 -- A new key is cached.
860 --
861 px_controller.numof_keys := px_controller.numof_keys + 1;
862 END IF;
863
864 --
865 -- Update the cache. Old entry is overwritten.
866 --
867 px_controller.keys(l_key_index) := l_key;
868 x_index := l_key_index;
869 EXCEPTION
870 WHEN OTHERS THEN
871 raise_others_error(p_func_name => 'custom_1to1_get_put_index',
872 p_arg1 => px_controller.name,
873 p_arg2 => p_key);
874 END custom_1to1_get_put_index;
875
876 -- ----------------------------------------------------------------------
877 PROCEDURE custom_1to1_remove_key
878 (px_controller IN OUT nocopy cache_1to1_controller_type,
879 p_key IN VARCHAR2)
880 IS
881 l_key VARCHAR2(2048) := fnd_global.session_context || '.' || p_key;
882 l_key_index NUMBER;
883 BEGIN
884 --
885 -- Get the key index. (1..px_controller.max_numof_keys)
886 --
887 l_key_index := dbms_utility.get_hash_value(l_key, 1,
888 px_controller.max_numof_keys);
889
890 --
891 -- Is it in cache?
892 --
893 IF (px_controller.keys(l_key_index) = l_key) THEN
894 --
895 -- Remove it from the cache.
896 --
897 px_controller.numof_keys := px_controller.numof_keys - 1;
898 px_controller.keys(l_key_index) := NULL;
899 END IF;
900 EXCEPTION
901 WHEN OTHERS THEN
905 END custom_1to1_remove_key;
902 raise_others_error(p_func_name => 'custom_1to1_remove_key',
903 p_arg1 => px_controller.name,
904 p_arg2 => p_key);
906
907 -- ----------------------------------------------------------------------
908 PROCEDURE custom_1to1_clear
909 (px_controller IN OUT nocopy cache_1to1_controller_type)
910 IS
911 BEGIN
912 custom_1to1_init(px_controller.name,
913 px_controller,
914 px_controller.max_numof_keys);
915 EXCEPTION
916 WHEN OTHERS THEN
917 raise_others_error(p_func_name => 'custom_1to1_clear',
918 p_arg1 => px_controller.name);
919 END custom_1to1_clear;
920
921 -- ----------------------------------------------------------------------
922 PROCEDURE custom_1to1_debug
923 (px_controller IN cache_1to1_controller_type,
924 p_debug_level IN VARCHAR2 DEFAULT CDL_SUMMARY_KEYS_VALUES)
925 IS
926 l_dummy_storage generic_cache_values_type;
927 BEGIN
928 internal_1to1_debug(px_controller, l_dummy_storage, p_debug_level);
929 EXCEPTION
930 WHEN OTHERS THEN
931 raise_others_error(p_func_name => 'custom_1to1_debug',
932 p_arg1 => px_controller.name,
933 p_arg2 => p_debug_level);
934 END custom_1to1_debug;
935
936
937 -- ======================================================================
938 -- Custom Many Cache:
939 -- ======================================================================
940
941 -- ----------------------------------------------------------------------
942 PROCEDURE custom_1tom_remove_key_private
943 (px_controller IN OUT nocopy cache_1tom_controller_type,
944 p_key_index IN NUMBER)
945 IS
946 BEGIN
947 --
948 -- Return the indexes
949 --
950 px_controller.available_indexes := px_controller.value_indexes(p_key_index) || px_controller.available_indexes;
951 px_controller.numof_available_indexes := px_controller.numof_available_indexes + px_controller.numof_indexes(p_key_index);
952
953 --
954 -- Remove it from the cache.
955 --
956 px_controller.numof_keys := px_controller.numof_keys - 1;
957 px_controller.numof_values := px_controller.numof_values - px_controller.numof_indexes(p_key_index);
958
959 px_controller.keys(p_key_index) := NULL;
960 px_controller.value_indexes(p_key_index) := NULL;
961 px_controller.numof_indexes(p_key_index) := 0;
962 EXCEPTION
963 WHEN OTHERS THEN
964 raise_others_error(p_func_name => 'custom_1tom_remove_key_private',
965 p_arg1 => px_controller.name,
966 p_arg2 => p_key_index);
967 END custom_1tom_remove_key_private;
968
969 -- ----------------------------------------------------------------------
970 PROCEDURE custom_1tom_extend_private
971 (px_controller IN OUT nocopy cache_1tom_controller_type,
972 p_extent_size IN NUMBER)
973 IS
974 BEGIN
975 --
976 -- Make sure we are not gonna hit the CACHE_MAX_NUMOF_VALUES limit.
977 --
978 IF ((px_controller.maxof_available_indexes + p_extent_size) > CACHE_MAX_NUMOF_VALUES) THEN
979 raise_1tom_error(px_controller, ERROR_MAX_NUMOF_VALUES,
980 'Cache maximum size (' ||
981 CACHE_MAX_NUMOF_VALUES || ') is reached.');
982 END IF;
983
984 --
985 -- Append new indexes to the end of available list.
986 --
987 FOR i IN 1..p_extent_size LOOP
988 px_controller.available_indexes := px_controller.available_indexes ||
989 Rpad(px_controller.maxof_available_indexes + i, CACHE_NUMOF_DIGITS_PER_INDEX, ' ');
990 END LOOP;
991
992 px_controller.numof_available_indexes := px_controller.numof_available_indexes + p_extent_size;
993 px_controller.maxof_available_indexes := px_controller.maxof_available_indexes + p_extent_size;
994 EXCEPTION
995 WHEN OTHERS THEN
996 raise_others_error(p_func_name => 'custom_1tom_extend_private',
997 p_arg1 => px_controller.name,
998 p_arg2 => p_extent_size);
999 END custom_1tom_extend_private;
1000
1001 -- ----------------------------------------------------------------------
1002 PROCEDURE custom_1tom_init
1003 (p_name IN VARCHAR2,
1004 px_controller IN OUT nocopy cache_1tom_controller_type,
1005 p_max_numof_keys IN NUMBER DEFAULT CACHE_MAX_NUMOF_KEYS)
1006 IS
1007 BEGIN
1008 px_controller.name := Nvl(p_name, 'NONAME Cache');
1009 px_controller.cache_type := CACHE_TYPE_CUSTOM;
1010 px_controller.max_numof_keys := p_max_numof_keys;
1011 px_controller.numof_keys := 0;
1012 px_controller.numof_values := 0;
1013
1014 px_controller.available_indexes := NULL;
1015 px_controller.numof_available_indexes := 0;
1016 px_controller.maxof_available_indexes := 0;
1017
1018 custom_1tom_extend_private(px_controller, CACHE_EXTENT_SIZE);
1019
1020 --
1021 -- 1 <= p_max_numof_keys <= CACHE_MAX_NUMOF_KEYS, 2^n = p_max_numof_keys
1022 --
1023 IF (NOT ((p_max_numof_keys >= 1) AND
1024 (p_max_numof_keys <= CACHE_MAX_NUMOF_KEYS) AND
1025 (p_max_numof_keys IN (1,2,4,8,16,32,64,128,256,512,1024)))) THEN
1029 CACHE_MAX_NUMOF_KEYS || ', and must be power of 2.');
1026 raise_1tom_error(px_controller, ERROR_MAX_NUMOF_KEYS,
1027 'p_max_numof_keys (' || p_max_numof_keys ||
1028 ') must be between 1 and ' ||
1030 END IF;
1031
1032 px_controller.keys := cache_varchar2_varray_type();
1033 px_controller.keys.extend(px_controller.max_numof_keys);
1034
1035 px_controller.numof_indexes := cache_number_varray_type();
1036 px_controller.numof_indexes.extend(px_controller.max_numof_keys);
1037
1038 px_controller.value_indexes := cache_varchar2_varray_type();
1039 px_controller.value_indexes.extend(px_controller.max_numof_keys);
1040
1041 EXCEPTION
1042 WHEN OTHERS THEN
1043 raise_others_error(p_func_name => 'custom_1tom_init',
1044 p_arg1 => p_name,
1045 p_arg2 => p_max_numof_keys);
1046 END custom_1tom_init;
1047
1048 -- ----------------------------------------------------------------------
1049 PROCEDURE custom_1tom_get_get_indexes
1050 (px_controller IN OUT nocopy cache_1tom_controller_type,
1051 p_key IN VARCHAR2,
1052 x_numof_indexes OUT nocopy NUMBER,
1053 x_indexes OUT nocopy custom_cache_indexes_type,
1054 x_return_code OUT nocopy VARCHAR2)
1055 IS
1056 l_key VARCHAR2(2048) := fnd_global.session_context || '.' || p_key;
1057 l_key_index NUMBER;
1058 l_indexes_vc2 VARCHAR2(2048);
1059 BEGIN
1060 -- Similar logic exists in generic_1tom_get_values()
1061 --
1062 -- Get the key index. (1..px_controller.max_numof_keys)
1063 --
1064 l_key_index := dbms_utility.get_hash_value(l_key, 1,
1065 px_controller.max_numof_keys);
1066 --
1067 -- Is it in cache?
1068 --
1069 IF (px_controller.keys(l_key_index) = l_key) THEN
1070 --
1071 -- Resolve the value indexes.
1072 --
1073 l_indexes_vc2 := px_controller.value_indexes(l_key_index);
1074 --
1075 -- Use indexes list to get indexes.
1076 --
1077 FOR i IN 1..px_controller.numof_indexes(l_key_index) LOOP
1078 x_indexes(i) := To_number(Substr(l_indexes_vc2, 1, CACHE_NUMOF_DIGITS_PER_INDEX));
1079 l_indexes_vc2 := Substr(l_indexes_vc2, CACHE_NUMOF_DIGITS_PER_INDEX + 1);
1080 END LOOP;
1081
1082 x_numof_indexes := px_controller.numof_indexes(l_key_index);
1083 x_return_code := CACHE_FOUND;
1084 ELSE
1085
1086 x_return_code := CACHE_NOTFOUND;
1087 END IF;
1088 EXCEPTION
1089 WHEN OTHERS THEN
1090 raise_others_error(p_func_name => 'custom_1tom_get_get_indexes',
1091 p_arg1 => px_controller.name,
1092 p_arg2 => p_key);
1093 END custom_1tom_get_get_indexes;
1094
1095 -- ----------------------------------------------------------------------
1096 PROCEDURE custom_1tom_get_put_indexes
1097 (px_controller IN OUT nocopy cache_1tom_controller_type,
1098 p_key IN VARCHAR2,
1099 p_numof_indexes IN NUMBER,
1100 x_indexes OUT nocopy custom_cache_indexes_type,
1101 x_return_code OUT nocopy VARCHAR2)
1102 IS
1103 l_key VARCHAR2(2048) := fnd_global.session_context || '.' || p_key;
1104 l_key_index NUMBER;
1105 l_indexes_vc2 VARCHAR2(2048);
1106 BEGIN
1107 --
1108 -- 0 <= p_numof_indexes <= CACHE_MAX_NUMOF_VALUES_PER_KEY
1109 --
1110 IF (NOT ((p_numof_indexes >= 0) AND
1111 (p_numof_indexes <= CACHE_MAX_NUMOF_VALUES_PER_KEY))) THEN
1112 raise_1tom_error(px_controller, ERROR_MAX_NUMOF_VALUES_PER_KEY,
1113 'p_numof_indexes (' || p_numof_indexes ||
1114 ') must be between 0 and ' ||
1115 CACHE_MAX_NUMOF_VALUES_PER_KEY || '.');
1116 END IF;
1117
1118 --
1119 -- Get the key index. (1..px_controller.max_numof_keys)
1120 --
1121 l_key_index := dbms_utility.get_hash_value(l_key, 1,
1122 px_controller.max_numof_keys);
1123
1124 --
1125 -- Old entry is overwritten.
1126 --
1127 IF (px_controller.keys(l_key_index) IS NOT NULL) THEN
1128 custom_1tom_remove_key_private(px_controller, l_key_index);
1129 END IF;
1130
1131 --
1132 -- If there are not enough indexes then extend the cache.
1133 --
1134 WHILE (px_controller.numof_available_indexes <= p_numof_indexes) LOOP
1135 custom_1tom_extend_private(px_controller, CACHE_EXTENT_SIZE);
1136 END LOOP;
1137
1138 --
1139 -- Get the indexes.
1140 --
1141 IF (p_numof_indexes = 0) THEN
1142 l_indexes_vc2 := NULL;
1143 ELSE
1144 l_indexes_vc2 := Substr(px_controller.available_indexes, 1, CACHE_NUMOF_DIGITS_PER_INDEX * p_numof_indexes);
1145
1146 --
1147 -- Update the controller.
1148 --
1149 px_controller.available_indexes := Substr(px_controller.available_indexes, CACHE_NUMOF_DIGITS_PER_INDEX * p_numof_indexes + 1);
1150 px_controller.numof_available_indexes := px_controller.numof_available_indexes - p_numof_indexes;
1151 END IF;
1152
1153 px_controller.numof_keys := px_controller.numof_keys + 1;
1154 px_controller.numof_values := px_controller.numof_values + p_numof_indexes;
1155
1156 px_controller.keys(l_key_index) := l_key;
1160 --
1157 px_controller.numof_indexes(l_key_index) := p_numof_indexes;
1158 px_controller.value_indexes(l_key_index) := l_indexes_vc2;
1159
1161 -- Use indexes list to get indexes.
1162 --
1163 FOR i IN 1..p_numof_indexes LOOP
1164 x_indexes(i) := To_number(Substr(l_indexes_vc2, 1, CACHE_NUMOF_DIGITS_PER_INDEX));
1165 l_indexes_vc2 := Substr(l_indexes_vc2, CACHE_NUMOF_DIGITS_PER_INDEX + 1);
1166 END LOOP;
1167
1168 x_return_code := CACHE_PUT_IS_SUCCESSFUL;
1169 EXCEPTION
1170 WHEN OTHERS THEN
1171 IF (g_error_code = ERROR_MAX_NUMOF_VALUES_PER_KEY) THEN
1172 x_return_code := CACHE_TOO_MANY_VALUES_PER_KEY;
1173
1174 ELSIF (g_error_code = ERROR_MAX_NUMOF_VALUES) THEN
1175 x_return_code := CACHE_IS_FULL;
1176
1177 ELSE
1178 raise_others_error(p_func_name => 'custom_1tom_get_put_indexes',
1179 p_arg1 => px_controller.name,
1180 p_arg2 => p_key,
1181 p_arg3 => p_numof_indexes);
1182 END IF;
1183 END custom_1tom_get_put_indexes;
1184
1185 -- ----------------------------------------------------------------------
1186 PROCEDURE custom_1tom_remove_key
1187 (px_controller IN OUT nocopy cache_1tom_controller_type,
1188 p_key IN VARCHAR2)
1189 IS
1190 l_key VARCHAR2(2048) := fnd_global.session_context || '.' || p_key;
1191 l_key_index NUMBER;
1192 BEGIN
1193 --
1194 -- Get the key index. (1..px_controller.max_numof_keys)
1195 --
1196 l_key_index := dbms_utility.get_hash_value(l_key, 1,
1197 px_controller.max_numof_keys);
1198 --
1199 -- Is it in cache?
1200 --
1201 IF (px_controller.keys(l_key_index) = l_key) THEN
1202 custom_1tom_remove_key_private(px_controller, l_key_index);
1203 END IF;
1204 EXCEPTION
1205 WHEN OTHERS THEN
1206 raise_others_error(p_func_name => 'custom_1tom_remove_key',
1207 p_arg1 => px_controller.name,
1208 p_arg2 => p_key);
1209 END custom_1tom_remove_key;
1210
1211 -- ----------------------------------------------------------------------
1212 PROCEDURE custom_1tom_clear
1213 (px_controller IN OUT nocopy cache_1tom_controller_type)
1214 IS
1215 BEGIN
1216 custom_1tom_init(px_controller.name,
1217 px_controller,
1218 px_controller.max_numof_keys);
1219 EXCEPTION
1220 WHEN OTHERS THEN
1221 raise_others_error(p_func_name => 'custom_1tom_clear',
1222 p_arg1 => px_controller.name);
1223 END custom_1tom_clear;
1224
1225 -- ----------------------------------------------------------------------
1226 PROCEDURE custom_1tom_debug
1227 (px_controller IN cache_1tom_controller_type,
1228 p_debug_level IN VARCHAR2 DEFAULT CDL_SUMMARY_KEYS_VALUES)
1229 IS
1230 l_dummy_storage generic_cache_values_type;
1231 BEGIN
1232 internal_1tom_debug(px_controller, l_dummy_storage, p_debug_level);
1233 EXCEPTION
1234 WHEN OTHERS THEN
1235 raise_others_error(p_func_name => 'custom_1tom_debug',
1236 p_arg1 => px_controller.name,
1237 p_arg2 => p_debug_level);
1238 END custom_1tom_debug;
1239
1240
1241 -- ----------------------------------------------------------------------
1242 PROCEDURE test
1243 IS
1244 TYPE custom_cache_storage_type IS TABLE OF VARCHAR2(100) INDEX BY BINARY_INTEGER;
1245
1246 lg_1to1_controller fnd_plsql_cache.cache_1to1_controller_type;
1247 lg_1tom_controller fnd_plsql_cache.cache_1tom_controller_type;
1248
1249 lg_generic_storage fnd_plsql_cache.generic_cache_values_type;
1250 lg_custom_storage custom_cache_storage_type;
1251
1252 lg_return_code VARCHAR2(1);
1253
1254 lg_same_index_key VARCHAR2(2000);
1255 lg_debug VARCHAR2(32000);
1256
1257 -- ----------------------------------------------------------------------
1258 PROCEDURE test_generic_1to1_put(p_key IN VARCHAR2,
1259 p_value IN VARCHAR2)
1260 IS
1261 l_value fnd_plsql_cache.generic_cache_value_type;
1262 BEGIN
1263 generic_cache_new_value(l_value,
1264 p_varchar2_1 => p_value);
1265 generic_1to1_put_value(lg_1to1_controller, lg_generic_storage,
1266 p_key, l_value);
1267 debug_put_line('OK. (' || p_key || ', ' || p_value || ') is stored.');
1268 END test_generic_1to1_put;
1269
1270 -- ----------------------------------------------------------------------
1271 PROCEDURE test_generic_1to1_get(p_key IN VARCHAR2,
1272 p_is_found_expected IN BOOLEAN)
1273 IS
1274 l_value fnd_plsql_cache.generic_cache_value_type;
1275 BEGIN
1276 generic_1to1_get_value(lg_1to1_controller, lg_generic_storage,
1277 p_key, l_value, lg_return_code);
1278
1279 IF ((lg_return_code = CACHE_FOUND) AND
1280 (p_is_found_expected)) THEN
1281
1282 debug_put_line('OK. (' || p_key || ', ' || l_value.varchar2_1 ||
1283 ') is found.');
1284
1285 ELSIF ((lg_return_code = CACHE_FOUND) AND
1286 (NOT p_is_found_expected)) THEN
1287 debug_put_line('ERROR. (' || p_key || ') was not supposed to be found.');
1288 ELSIF ((lg_return_code = CACHE_NOTFOUND) AND
1292 (p_is_found_expected)) THEN
1289 (NOT p_is_found_expected)) THEN
1290 debug_put_line('OK. (' || p_key || ') is not found.');
1291 ELSIF ((lg_return_code = CACHE_NOTFOUND) AND
1293 debug_put_line('ERROR. (' || p_key || ') was supposed to be found.');
1294 END IF;
1295 END test_generic_1to1_get;
1296
1297 -- ----------------------------------------------------------------------
1298 PROCEDURE test_generic_1tom_put(p_key IN VARCHAR2,
1299 p_value1 IN VARCHAR2 DEFAULT NULL,
1300 p_value2 IN VARCHAR2 DEFAULT NULL,
1301 p_value3 IN VARCHAR2 DEFAULT NULL,
1302 p_value4 IN VARCHAR2 DEFAULT NULL,
1303 p_value5 IN VARCHAR2 DEFAULT NULL)
1304 IS
1305 l_value fnd_plsql_cache.generic_cache_value_type;
1306 l_values fnd_plsql_cache.generic_cache_values_type;
1307 l_numof_values NUMBER;
1308 BEGIN
1309 IF (p_value1 IS NOT NULL) THEN
1310 generic_cache_new_value(l_value,
1311 p_varchar2_1 => p_value1);
1312 l_values(1) := l_value;
1313 l_numof_values := 1;
1314
1315 IF (p_value2 IS NOT NULL) THEN
1316 generic_cache_new_value(l_value,
1317 p_varchar2_1 => p_value2);
1318 l_values(2) := l_value;
1319 l_numof_values := 2;
1320
1321 IF (p_value3 IS NOT NULL) THEN
1322 generic_cache_new_value(l_value,
1323 p_varchar2_1 => p_value3);
1324 l_values(3) := l_value;
1325 l_numof_values := 3;
1326
1327 IF (p_value4 IS NOT NULL) THEN
1328 generic_cache_new_value(l_value,
1329 p_varchar2_1 => p_value4);
1330 l_values(4) := l_value;
1331 l_numof_values := 4;
1332
1333 IF (p_value5 IS NOT NULL) THEN
1334 generic_cache_new_value(l_value,
1335 p_varchar2_1 => p_value5);
1336 l_values(5) := l_value;
1337 l_numof_values := 5;
1338
1339 END IF;
1340 END IF;
1341 END IF;
1342 END IF;
1343 END IF;
1344
1345 generic_1tom_put_values(lg_1tom_controller, lg_generic_storage,
1346 p_key, l_numof_values, l_values);
1347
1348 lg_debug := 'OK. (' || p_key || '; [';
1349 FOR i IN 1..l_numof_values LOOP
1350 IF (i < l_numof_values) THEN
1351 lg_debug := lg_debug || l_values(i).varchar2_1 || ',';
1352 ELSE
1353 lg_debug := lg_debug || l_values(i).varchar2_1 || ']';
1354 END IF;
1355 END LOOP;
1356 lg_debug := lg_debug || ') is stored.';
1357 debug_put_line(lg_debug);
1358
1359 END test_generic_1tom_put;
1360
1361 -- ----------------------------------------------------------------------
1362 PROCEDURE test_generic_1tom_get(p_key IN VARCHAR2,
1363 p_is_found_expected IN BOOLEAN)
1364 IS
1365 l_numof_values NUMBER;
1366 l_values fnd_plsql_cache.generic_cache_values_type;
1367 BEGIN
1368 generic_1tom_get_values(lg_1tom_controller, lg_generic_storage,
1369 p_key, l_numof_values, l_values,
1370 lg_return_code);
1371
1372 IF ((lg_return_code = CACHE_FOUND) AND
1373 (p_is_found_expected)) THEN
1374
1375 lg_debug := 'OK. (' || p_key || '; [';
1376 FOR i IN 1..l_numof_values LOOP
1377 IF (i < l_numof_values) THEN
1378 lg_debug := lg_debug || l_values(i).varchar2_1 || ',';
1379 ELSE
1380 lg_debug := lg_debug || l_values(i).varchar2_1 || ']';
1381 END IF;
1382 END LOOP;
1383 lg_debug := lg_debug || ') is found.';
1384 debug_put_line(lg_debug);
1385
1386 ELSIF ((lg_return_code = CACHE_FOUND) AND
1387 (NOT p_is_found_expected)) THEN
1388 debug_put_line('ERROR. (' || p_key || ') was not supposed to be found.');
1389 ELSIF ((lg_return_code = CACHE_NOTFOUND) AND
1390 (NOT p_is_found_expected)) THEN
1391 debug_put_line('OK. (' || p_key || ') is not found.');
1392 ELSIF ((lg_return_code = CACHE_NOTFOUND) AND
1393 (p_is_found_expected)) THEN
1394 debug_put_line('ERROR. (' || p_key || ') was supposed to be found.');
1395 END IF;
1396 END test_generic_1tom_get;
1397
1398 -- ----------------------------------------------------------------------
1399 PROCEDURE test_custom_1to1_put(p_key IN VARCHAR2,
1400 p_value IN VARCHAR2)
1401 IS
1402 l_index BINARY_INTEGER;
1403 BEGIN
1404 custom_1to1_get_put_index(lg_1to1_controller,
1405 p_key, l_index);
1406 lg_custom_storage(l_index) := p_value;
1407 debug_put_line('OK. (' || p_key || ', ' || p_value || ') is stored.');
1408 END test_custom_1to1_put;
1409
1410 -- ----------------------------------------------------------------------
1411 PROCEDURE test_custom_1to1_get(p_key IN VARCHAR2,
1412 p_is_found_expected IN BOOLEAN)
1413 IS
1414 l_index BINARY_INTEGER;
1415 BEGIN
1416 custom_1to1_get_get_index(lg_1to1_controller,
1417 p_key, l_index, lg_return_code);
1418
1419 IF ((lg_return_code = CACHE_FOUND) AND
1420 (p_is_found_expected)) THEN
1421
1422 debug_put_line('OK. (' || p_key || ', ' || lg_custom_storage(l_index) ||
1423 ') is found.');
1424
1425 ELSIF ((lg_return_code = CACHE_FOUND) AND
1426 (NOT p_is_found_expected)) THEN
1427 debug_put_line('ERROR. (' || p_key || ') was not supposed to be found.');
1428 ELSIF ((lg_return_code = CACHE_NOTFOUND) AND
1432 (p_is_found_expected)) THEN
1429 (NOT p_is_found_expected)) THEN
1430 debug_put_line('OK. (' || p_key || ') is not found.');
1431 ELSIF ((lg_return_code = CACHE_NOTFOUND) AND
1433 debug_put_line('ERROR. (' || p_key || ') was supposed to be found.');
1434 END IF;
1435 END test_custom_1to1_get;
1436
1437 -- ----------------------------------------------------------------------
1438 PROCEDURE test_custom_1tom_put(p_key IN VARCHAR2,
1439 p_value1 IN VARCHAR2 DEFAULT NULL,
1440 p_value2 IN VARCHAR2 DEFAULT NULL,
1441 p_value3 IN VARCHAR2 DEFAULT NULL,
1442 p_value4 IN VARCHAR2 DEFAULT NULL,
1443 p_value5 IN VARCHAR2 DEFAULT NULL)
1444 IS
1445 l_indexes fnd_plsql_cache.custom_cache_indexes_type;
1446 l_values custom_cache_storage_type;
1447 l_numof_indexes NUMBER;
1448 BEGIN
1449 IF (p_value1 IS NOT NULL) THEN
1450 l_values(1) := p_value1;
1451 l_numof_indexes := 1;
1452
1453 IF (p_value2 IS NOT NULL) THEN
1454 l_values(2) := p_value2;
1455 l_numof_indexes := 2;
1456
1457 IF (p_value3 IS NOT NULL) THEN
1458 l_values(3) := p_value3;
1459 l_numof_indexes := 3;
1460
1461 IF (p_value4 IS NOT NULL) THEN
1462 l_values(4) := p_value4;
1463 l_numof_indexes := 4;
1464
1465 IF (p_value5 IS NOT NULL) THEN
1466 l_values(5) := p_value5;
1467 l_numof_indexes := 5;
1468 END IF;
1469 END IF;
1470 END IF;
1471 END IF;
1472 END IF;
1473
1474 custom_1tom_get_put_indexes(lg_1tom_controller,
1475 p_key, l_numof_indexes,
1476 l_indexes, lg_return_code);
1477
1478 IF (lg_return_code = CACHE_PUT_IS_SUCCESSFUL) THEN
1479 FOR i IN 1..l_numof_indexes LOOP
1480 lg_custom_storage(l_indexes(i)) := l_values(i);
1481 END LOOP;
1482
1483 lg_debug := 'OK. (' || p_key || '; [';
1484 FOR i IN 1..l_numof_indexes LOOP
1485 IF (i < l_numof_indexes) THEN
1486 lg_debug := lg_debug || lg_custom_storage(l_indexes(i)) || ',';
1487 ELSE
1488 lg_debug := lg_debug || lg_custom_storage(l_indexes(i)) || ']';
1489 END IF;
1490 END LOOP;
1491 lg_debug := lg_debug || ') is stored.';
1492
1493 ELSE
1494 lg_debug := 'ERROR. Put failed. Return Code: ' || lg_return_code;
1495
1496 END IF;
1497
1498 debug_put_line(lg_debug);
1499
1500 END test_custom_1tom_put;
1501
1502 -- ----------------------------------------------------------------------
1503 PROCEDURE test_custom_1tom_get(p_key IN VARCHAR2,
1504 p_is_found_expected IN BOOLEAN)
1505 IS
1506 l_numof_indexes NUMBER;
1507 l_indexes fnd_plsql_cache.custom_cache_indexes_type;
1508 BEGIN
1509 custom_1tom_get_get_indexes(lg_1tom_controller,
1510 p_key, l_numof_indexes, l_indexes,
1511 lg_return_code);
1512
1513 IF ((lg_return_code = CACHE_FOUND) AND
1514 (p_is_found_expected)) THEN
1515
1516 lg_debug := 'OK. (' || p_key || '; [';
1517 FOR i IN 1..l_numof_indexes LOOP
1518 IF (i < l_numof_indexes) THEN
1519 lg_debug := lg_debug || lg_custom_storage(l_indexes(i)) || ',';
1520 ELSE
1521 lg_debug := lg_debug || lg_custom_storage(l_indexes(i)) || ']';
1522 END IF;
1523 END LOOP;
1524 lg_debug := lg_debug || ') is found.';
1525 debug_put_line(lg_debug);
1526
1527 ELSIF ((lg_return_code = CACHE_FOUND) AND
1528 (NOT p_is_found_expected)) THEN
1529 debug_put_line('ERROR. (' || p_key || ') was not supposed to be found.');
1530 ELSIF ((lg_return_code = CACHE_NOTFOUND) AND
1531 (NOT p_is_found_expected)) THEN
1532 debug_put_line('OK. (' || p_key || ') is not found.');
1533 ELSIF ((lg_return_code = CACHE_NOTFOUND) AND
1534 (p_is_found_expected)) THEN
1535 debug_put_line('ERROR. (' || p_key || ') was supposed to be found.');
1536 END IF;
1537 END test_custom_1tom_get;
1538
1539 -- ----------------------------------------------------------------------
1540 FUNCTION test_get_same_index_key(p_key IN VARCHAR2) RETURN VARCHAR2
1541 IS
1542 l_key VARCHAR2(2048) := fnd_global.session_context || '.' || p_key;
1543 l_key_index NUMBER;
1544 l_index NUMBER;
1545 i NUMBER;
1546 l_return VARCHAR2(2000);
1547 BEGIN
1548 l_key_index := dbms_utility.get_hash_value(l_key, 1,
1549 CACHE_MAX_NUMOF_KEYS);
1550 i := 1;
1551 LOOP
1552 l_index := dbms_utility.get_hash_value(l_key || i, 1,
1553 CACHE_MAX_NUMOF_KEYS);
1554 IF (l_index = l_key_index) THEN
1555 l_return := p_key || i;
1556 EXIT;
1557 END IF;
1558 i := i + 1;
1559 END LOOP;
1560
1561 RETURN (l_return);
1562 END test_get_same_index_key;
1563
1564 BEGIN
1565 execute immediate ('begin dbms_output.enable(1000000); end;');
1566
1567 debug_put_line('Testing Generic 1to1');
1568 debug_put_line('========================================');
1569 generic_1to1_init('GenericOne', lg_1to1_controller, lg_generic_storage);
1570
1571 test_generic_1to1_get('USA', FALSE);
1575
1572
1573 test_generic_1to1_put('USA', 'Washington DC');
1574 test_generic_1to1_get('USA', TRUE);
1576 lg_same_index_key := test_get_same_index_key('USA');
1577 test_generic_1to1_put(lg_same_index_key, 'Replaces USA');
1578 test_generic_1to1_get(lg_same_index_key, TRUE);
1579 test_generic_1to1_get('USA', FALSE);
1580
1581 test_generic_1to1_put('Turkey', 'Ankara');
1582 test_generic_1to1_get('Turkey', TRUE);
1583
1584 generic_1to1_remove_key(lg_1to1_controller, 'Turkey');
1585 test_generic_1to1_get('Turkey', FALSE);
1586 test_generic_1to1_put('Turkey', 'Ankara');
1587
1588 generic_1to1_debug(lg_1to1_controller, lg_generic_storage);
1589
1590 generic_1to1_clear(lg_1to1_controller, lg_generic_storage);
1591
1592 debug_put_line('');
1593
1594
1595 debug_put_line('Testing Generic 1toM');
1596 debug_put_line('========================================');
1597 generic_1tom_init('GenericMany', lg_1tom_controller, lg_generic_storage);
1598
1599 test_generic_1tom_get('Bill Gates', FALSE);
1600
1601 test_generic_1tom_put('Bill Gates', 'Math', 'C Prog', 'Chem');
1602 test_generic_1tom_get('Bill Gates', TRUE);
1603
1604 lg_same_index_key := test_get_same_index_key('Bill Gates');
1605 test_generic_1tom_put(lg_same_index_key, 'x1', 'x2', 'x3', 'x4', 'x5');
1606 test_generic_1tom_get(lg_same_index_key, TRUE);
1607 test_generic_1tom_get('Bill Gates', FALSE);
1608
1609 test_generic_1tom_put('Bill Clinton', 'Law', 'History', 'Economy');
1610 test_generic_1tom_get('Bill Clinton', TRUE);
1611
1612 generic_1tom_remove_key(lg_1tom_controller, 'Bill Clinton');
1613 test_generic_1tom_get('Bill Clinton', FALSE);
1614 test_generic_1tom_put('Bill Clinton', 'Law', 'History', 'Economy');
1615
1616 generic_1tom_debug(lg_1tom_controller, lg_generic_storage);
1617
1618 generic_1tom_clear(lg_1tom_controller, lg_generic_storage);
1619
1620 debug_put_line('');
1621
1622
1623 debug_put_line('Testing Custom 1to1');
1624 debug_put_line('========================================');
1625 custom_1to1_init('CustomOne', lg_1to1_controller);
1626
1627 test_custom_1to1_get('USA', FALSE);
1628
1629 test_custom_1to1_put('USA', 'Washington DC');
1630 test_custom_1to1_get('USA', TRUE);
1631
1632 lg_same_index_key := test_get_same_index_key('USA');
1633 test_custom_1to1_put(lg_same_index_key, 'Replaces USA');
1634 test_custom_1to1_get(lg_same_index_key, TRUE);
1635 test_custom_1to1_get('USA', FALSE);
1636
1637 test_custom_1to1_put('Turkey', 'Ankara');
1638 test_custom_1to1_get('Turkey', TRUE);
1639
1640 custom_1to1_remove_key(lg_1to1_controller, 'Turkey');
1641 test_generic_1to1_get('Turkey', FALSE);
1642 test_custom_1to1_put('Turkey', 'Ankara');
1643
1644 custom_1to1_debug(lg_1to1_controller);
1645
1646 custom_1to1_clear(lg_1to1_controller);
1647
1648 debug_put_line('');
1649
1650
1651 debug_put_line('Testing Custom 1toM');
1652 debug_put_line('========================================');
1653 custom_1tom_init('CustomMany', lg_1tom_controller);
1654
1655 test_custom_1tom_get('Bill Gates', FALSE);
1656
1657 test_custom_1tom_put('Bill Gates', 'Math', 'C Prog', 'Chem');
1658 test_custom_1tom_get('Bill Gates', TRUE);
1659
1660 lg_same_index_key := test_get_same_index_key('Bill Gates');
1661 test_custom_1tom_put(lg_same_index_key, 'x1', 'x2', 'x3', 'x4', 'x5');
1662 test_custom_1tom_get(lg_same_index_key, TRUE);
1663 test_custom_1tom_get('Bill Gates', FALSE);
1664
1665 test_custom_1tom_put('Bill Clinton', 'Law', 'History', 'Economy');
1666 test_custom_1tom_get('Bill Clinton', TRUE);
1667
1668 custom_1tom_remove_key(lg_1tom_controller, 'Bill Clinton');
1669 test_generic_1tom_get('Bill Clinton', FALSE);
1670 test_custom_1tom_put('Bill Clinton', 'Law', 'History', 'Economy');
1671
1672 custom_1tom_debug(lg_1tom_controller);
1673
1674 custom_1tom_clear(lg_1tom_controller);
1675
1676 debug_put_line('');
1677
1678 END test;
1679
1680 -- ----------------------------------------------------------------------
1681 PROCEDURE sample_package
1682 IS
1683 --
1684 -- Assume that this is your package declaration section
1685 --
1686 --
1687 -- Declare your data types
1688 --
1689 TYPE application_record_type IS RECORD
1690 (application_id fnd_application_vl.application_id%TYPE,
1691 application_short_name fnd_application_vl.application_short_name%TYPE,
1692 application_name fnd_application_vl.application_name%TYPE);
1693
1694 TYPE applications_array_type IS TABLE OF application_record_type
1695 INDEX BY BINARY_INTEGER;
1696
1697 TYPE responsibility_record_type IS RECORD
1698 (responsibility_id fnd_responsibility_vl.responsibility_id%TYPE,
1699 responsibility_key fnd_responsibility_vl.responsibility_key%TYPE,
1700 responsibility_name fnd_responsibility_vl.responsibility_name%TYPE);
1701
1702 TYPE responsibilities_array_type IS TABLE OF responsibility_record_type
1703 INDEX BY BINARY_INTEGER;
1704
1705 --
1706 -- Declare your package global cache variables.
1707 --
1708
1709 --
1710 -- Application cache, generic version. One value per key
1714
1711 --
1712 g_app_generic_1to1_controller fnd_plsql_cache.cache_1to1_controller_type;
1713 g_app_generic_1to1_storage fnd_plsql_cache.generic_cache_values_type;
1715 --
1716 -- Application cache, custom version. One value per key
1717 --
1718 g_app_custom_1to1_controller fnd_plsql_cache.cache_1to1_controller_type;
1719 g_app_custom_1to1_storage applications_array_type;
1720
1721 --
1722 -- Responsibility cache, generic version. Many values per key
1723 --
1724 g_rsp_generic_1tom_controller fnd_plsql_cache.cache_1tom_controller_type;
1725 g_rsp_generic_1tom_storage fnd_plsql_cache.generic_cache_values_type;
1726
1727 --
1728 -- Responsibility cache, custom version. Many values per key
1729 --
1730 g_rsp_custom_1tom_controller fnd_plsql_cache.cache_1tom_controller_type;
1731 g_rsp_custom_1tom_storage responsibilities_array_type;
1732
1733 --
1734 -- Implement your getter functions
1735 --
1736 ----------------------------------------------------------------------
1737 FUNCTION get_application_generic(p_application_short_name IN VARCHAR2)
1738 RETURN application_record_type
1739 IS
1740 l_application application_record_type;
1741 l_key VARCHAR2(2000);
1742 l_value fnd_plsql_cache.generic_cache_value_type;
1743 l_return_code VARCHAR2(1);
1744 BEGIN
1745 --
1746 -- Create the key. If you have a composite key then concatenate
1747 -- them with a delimiter. i.e. p_key1 || '.' || p_key2 || ...
1748 --
1749 l_key := p_application_short_name;
1750
1751 --
1752 -- First check the cache.
1753 --
1754 fnd_plsql_cache.generic_1to1_get_value(g_app_generic_1to1_controller,
1755 g_app_generic_1to1_storage,
1756 l_key,
1757 l_value,
1758 l_return_code);
1759
1760 IF (l_return_code = fnd_plsql_cache.CACHE_FOUND) THEN
1761 --
1762 -- Entity is in the cache, populate the return value.
1763 --
1764 l_application.application_id := l_value.number_1;
1765 l_application.application_short_name := l_value.varchar2_1;
1766 l_application.application_name := l_value.varchar2_2;
1767
1768 ELSE
1769 --
1770 -- Entity is not in the cache, get it from DB.
1771 --
1772 SELECT application_id, application_short_name, application_name
1773 INTO l_application
1774 FROM fnd_application_vl
1775 WHERE application_short_name = p_application_short_name;
1776
1777 --
1778 -- Create the cache value, and populate it with values came from DB.
1779 --
1780 fnd_plsql_cache.generic_cache_new_value
1781 (x_value => l_value,
1782 p_number_1 => l_application.application_id,
1783 p_varchar2_1 => l_application.application_short_name,
1784 p_varchar2_2 => l_application.application_name);
1785
1786 --
1787 -- Put the value in cache.
1788 --
1789 fnd_plsql_cache.generic_1to1_put_value(g_app_generic_1to1_controller,
1790 g_app_generic_1to1_storage,
1791 l_key,
1792 l_value);
1793 END IF;
1794
1795 --
1796 -- Return the output value.
1797 --
1798 RETURN l_application;
1799 END get_application_generic;
1800
1801 ----------------------------------------------------------------------
1802 FUNCTION get_application_custom(p_application_short_name IN VARCHAR2)
1803 RETURN application_record_type
1804 IS
1805 l_application application_record_type;
1806 l_key VARCHAR2(2000);
1807 l_index BINARY_INTEGER;
1808 l_return_code VARCHAR2(1);
1809 BEGIN
1810 --
1811 -- Create the key. If you have a composite key then concatenate
1812 -- them with a delimiter. i.e. p_key1 || '.' || p_key2 || ...
1813 --
1814 l_key := p_application_short_name;
1815
1816 --
1817 -- First check the cache.
1818 --
1819 fnd_plsql_cache.custom_1to1_get_get_index(g_app_custom_1to1_controller,
1820 l_key,
1821 l_index,
1822 l_return_code);
1823
1824 IF (l_return_code = fnd_plsql_cache.CACHE_FOUND) THEN
1825 --
1826 -- Entity is in the cache, populate the return value.
1827 --
1828 l_application := g_app_custom_1to1_storage(l_index);
1829
1830 ELSE
1831 --
1832 -- Entity is not in the cache, get it from DB.
1833 --
1834 SELECT application_id, application_short_name, application_name
1835 INTO l_application
1836 FROM fnd_application_vl
1837 WHERE application_short_name = p_application_short_name;
1838
1839 --
1840 -- Put the value in cache.
1841 --
1842 fnd_plsql_cache.custom_1to1_get_put_index(g_app_custom_1to1_controller,
1843 l_key,
1844 l_index);
1845 g_app_custom_1to1_storage(l_index) := l_application;
1846 END IF;
1847
1848 --
1849 -- Return the output value.
1850 --
1851 RETURN l_application;
1852 END get_application_custom;
1853
1854 ----------------------------------------------------------------------
1855 FUNCTION get_responsibilities_generic(p_application_short_name IN VARCHAR2)
1856 RETURN responsibilities_array_type
1857 IS
1858 CURSOR resp_cursor IS
1862 AND r.application_id = a.application_id;
1859 SELECT r.responsibility_id, r.responsibility_key, r.responsibility_name
1860 FROM fnd_responsibility_vl r, fnd_application a
1861 WHERE a.application_short_name = p_application_short_name
1863
1864 l_responsibilities responsibilities_array_type;
1865 l_key VARCHAR2(2000);
1866 l_values fnd_plsql_cache.generic_cache_values_type;
1867 l_numof_values NUMBER;
1868 l_return_code VARCHAR2(1);
1869 i NUMBER;
1870 BEGIN
1871 --
1872 -- Create the key. If you have a composite key then concatenate
1873 -- them with a delimiter. i.e. p_key1 || '.' || p_key2 || ...
1874 --
1875 l_key := p_application_short_name;
1876
1877 --
1878 -- First check the cache.
1879 --
1880 fnd_plsql_cache.generic_1tom_get_values(g_rsp_generic_1tom_controller,
1881 g_rsp_generic_1tom_storage,
1882 l_key,
1883 l_numof_values,
1884 l_values,
1885 l_return_code);
1886
1887 IF (l_return_code = fnd_plsql_cache.CACHE_FOUND) THEN
1888 --
1889 -- Entity is in the cache, populate the return values.
1890 --
1891 FOR i IN 1..l_numof_values LOOP
1892 l_responsibilities(i).responsibility_id := l_values(i).number_1;
1893 l_responsibilities(i).responsibility_key := l_values(i).varchar2_1;
1894 l_responsibilities(i).responsibility_name := l_values(i).varchar2_2;
1895 END LOOP;
1896
1897 ELSE
1898 i := 0;
1899 FOR r IN resp_cursor LOOP
1900 i := i + 1;
1901
1902 l_responsibilities(i) := r;
1903 --
1904 -- Create the cache value, and populate it with values came from DB.
1905 --
1906 fnd_plsql_cache.generic_cache_new_value
1907 (x_value => l_values(i),
1908 p_number_1 => r.responsibility_id,
1909 p_varchar2_1 => r.responsibility_key,
1910 p_varchar2_2 => r.responsibility_name);
1911
1912 END LOOP;
1913 l_numof_values := i;
1914 --
1915 -- Put the values in cache.
1916 --
1917 fnd_plsql_cache.generic_1tom_put_values(g_rsp_generic_1tom_controller,
1918 g_rsp_generic_1tom_storage,
1919 l_key,
1920 l_numof_values,
1921 l_values);
1922 END IF;
1923
1924 RETURN l_responsibilities;
1925 END get_responsibilities_generic;
1926
1927 ----------------------------------------------------------------------
1928 FUNCTION get_responsibilities_custom(p_application_short_name IN VARCHAR2)
1929 RETURN responsibilities_array_type
1930 IS
1931 CURSOR resp_cursor IS
1932 SELECT r.responsibility_id, r.responsibility_key, r.responsibility_name
1933 FROM fnd_responsibility_vl r, fnd_application a
1934 WHERE a.application_short_name = p_application_short_name
1935 AND r.application_id = a.application_id;
1936
1937 l_responsibilities responsibilities_array_type;
1938 l_key VARCHAR2(2000);
1939 l_numof_indexes NUMBER;
1940 l_indexes fnd_plsql_cache.custom_cache_indexes_type;
1941 l_return_code VARCHAR2(1);
1942 i NUMBER;
1943 BEGIN
1944 --
1945 -- Create the key. If you have a composite key then concatenate
1946 -- them with a delimiter. i.e. p_key1 || '.' || p_key2 || ...
1947 --
1948 l_key := p_application_short_name;
1949
1950 --
1951 -- First check the cache.
1952 --
1953 fnd_plsql_cache.custom_1tom_get_get_indexes(g_rsp_custom_1tom_controller,
1954 l_key,
1955 l_numof_indexes,
1956 l_indexes,
1957 l_return_code);
1958
1959 IF (l_return_code = fnd_plsql_cache.CACHE_FOUND) THEN
1960 --
1961 -- Entity is in the cache, populate the return values.
1962 --
1963 FOR i IN 1..l_numof_indexes LOOP
1964 l_responsibilities(i) := g_rsp_custom_1tom_storage(l_indexes(i));
1965 END LOOP;
1966
1967 ELSE
1968 i := 0;
1969 FOR r IN resp_cursor LOOP
1970 i := i + 1;
1971 l_responsibilities(i) := r;
1972 END LOOP;
1973 l_numof_indexes := i;
1974 --
1975 -- Put the values in cache.
1976 --
1977 fnd_plsql_cache.custom_1tom_get_put_indexes(g_rsp_generic_1tom_controller,
1978 l_key,
1979 l_numof_indexes,
1980 l_indexes,
1981 l_return_code);
1982
1983 IF (l_return_code = CACHE_PUT_IS_SUCCESSFUL) THEN
1984 FOR i IN 1..l_numof_indexes LOOP
1985 g_rsp_custom_1tom_storage(l_indexes(i)) := l_responsibilities(i);
1986 END LOOP;
1987 END IF;
1988 END IF;
1989
1990 RETURN l_responsibilities;
1991 END get_responsibilities_custom;
1992
1993 PROCEDURE any_procedure
1994 IS
1995 l_fnd_application application_record_type;
1996 l_fnd_responsibilities responsibilities_array_type;
1997 BEGIN
1998 --
1999 -- This will hit the DB
2000 --
2001 l_fnd_application := get_application_generic('FND');
2002 l_fnd_responsibilities := get_responsibilities_generic('FND');
2003
2004 --
2005 -- This will get it from cache.
2006 --
2007 l_fnd_application := get_application_generic('FND');
2008 l_fnd_responsibilities := get_responsibilities_generic('FND');
2009
2010 --
2011 -- This will hit the DB
2012 --
2013 l_fnd_application := get_application_custom('FND');
2014 l_fnd_responsibilities := get_responsibilities_custom('FND');
2015
2016 --
2017 -- This will get it from cache.
2018 --
2019 l_fnd_application := get_application_custom('FND');
2020 l_fnd_responsibilities := get_responsibilities_custom('FND');
2021
2022 END any_procedure;
2023
2024 BEGIN
2025 --
2026 -- Assume that this is your package initialization section.
2027 --
2028 --
2029 -- Application Generic 1to1 Cache.
2030 --
2031 fnd_plsql_cache.generic_1to1_init('Application Generic 1to1 Cache',
2032 g_app_generic_1to1_controller,
2033 g_app_generic_1to1_storage);
2034
2035 --
2036 -- Application Custom 1to1 Cache.
2037 --
2038 fnd_plsql_cache.custom_1to1_init('Application Custom 1to1 Cache',
2039 g_app_custom_1to1_controller);
2040 g_app_custom_1to1_storage.DELETE;
2041
2042 --
2043 -- Responsibilities Generic 1toM Cache
2044 --
2045 fnd_plsql_cache.generic_1tom_init('Responsibilities Generic 1toM Cache',
2046 g_rsp_generic_1tom_controller,
2047 g_rsp_generic_1tom_storage);
2048
2049 --
2050 -- Responsibilities Custom 1toM Cache
2051 --
2052 fnd_plsql_cache.custom_1tom_init('Responsibilities Custom 1toM Cache',
2053 g_rsp_custom_1tom_controller);
2054 g_rsp_custom_1tom_storage.DELETE;
2055 END sample_package;
2056
2057 BEGIN
2058 g_newline := fnd_global.newline();
2059 END fnd_plsql_cache;