1 package body FND_CACHE_VERSIONS_PKG as
2 /* $Header: AFCCHVRB.pls 120.4 2006/10/27 17:01:17 pdeluna noship $ */
3
4
5 /* Arrays for bulk collects */
6 TYPE NameTab IS TABLE OF FND_CACHE_VERSIONS.NAME%TYPE;
7 TYPE VersTab IS TABLE OF FND_CACHE_VERSIONS.VERSION%TYPE;
8 cacheName NameTab;
9 cacheVersion VersTab;
10
11 /*
12 * get_values
13 * Use this API to do a bulk collect on FND_CACHE_VERSIONS
14 */
15 procedure get_values is
16 begin
17 select NAME, VERSION
18 bulk collect into cacheName, cacheVersion
19 from FND_CACHE_VERSIONS;
20 end;
21
22 /*
23 * add_cache_name
24 * Use this API to add an entry in FND_CACHE_VERSIONS
25 *
26 * IN
27 * p_name - name of cache
28 *
29 */
30 procedure add_cache_name (p_name varchar2)
31 is
32 begin
33
34 -- Add a record into FND_CACHE_VERSIONS
35 insert into FND_CACHE_VERSIONS(
36 NAME,
37 VERSION
38 ) values (
39 p_name,
40 0);
41
42 get_values; -- Refresh the arrays.
43
44 end;
45
46 /*
47 * bump_version
48 * Use this API to increase the version by 1 in FND_CACHE_VERSIONS
49 *
50 * IN
51 * p_name - name of cache
52 *
53 */
54 procedure bump_version (p_name varchar2)
55 is
56 begin
57
58 -- Try to update FND_CACHE_VERSIONS given the name.
59 update FND_CACHE_VERSIONS
60 set VERSION = VERSION + 1
61 where NAME = p_name;
62
63 -- If name does not exist, insert the name using add_version.
64 if (sql%rowcount = 0) then
65 add_cache_name(p_name);
66 else
67 get_values; -- Refresh the arrays.
68 end if;
69
70 end;
71
72 /*
73 * get_version
74 * Use this API to get the current version in FND_CACHE_VERSIONS
75 *
76 * IN
77 * p_name - name of cache
78 *
79 * RETURN
80 * returns the current_version in FND_CACHE_VERSIONS given a name
81 *
82 * RAISES
83 * Never raises exceptions, returns -1 if name does not exist
84 */
85 function get_version (p_name varchar2)
86 return number
87 is
88 current_version number := -1; -- Bug 5629463:get_version returns NULL
89 begin
90
91 if cacheName is null then
92 get_values;
93
94 -- Please note that the previous fix to bug 4308360 has been moved
95 -- outside of this conditional block due to bug 5161071.
96
97 /* For bug 4308360. When there is no data, bulk collect option is
98 * not returning 'when_no_data_found' exception and the values for
99 * cachName.FIRST, cacheName.LAST are NULL, and this
100 * raises ORA-6502 exception at 'for i in cacheName.FIRST' line.
101 * Hence the following IF block is written.
102 *
103 if (cacheName.COUNT = 0) then
104 return -1;
105 end if;
106 */
107 end if;
108
109 /* Bug 5161071:4308360: ORA-06502 FND_CACHE_VERSIONS_PKG.get_version
110 * The previous fix for bug 4308360 did not completely resolve the issue
111 * since it was placed in a conditional block. As it happens, the
112 * condition is not satisfied in all cases and the failover is bypassed
113 * causing the ORA-6502 error to be raised.
114 *
115 * This, hopefully, more complete fix is to move the failover outside of
116 * the conditional block. This should catch all cases that the failover
117 * would be required.
118 */
119
120 -- Bug 5161071 shows a case where the 'cacheName is null' condition is not
121 -- met while FND_CACHE_VERSIONS table is empty. Hence, the
122 -- cacheName.COUNT = 0 condition should prevent the ORA-6502 error that
123 -- gets raised when the for loop executes even when cacheName.COUNT = 0.
124 if (cacheName.COUNT = 0) then
125 return -1;
126 else
127 for i in cacheName.FIRST..cacheName.LAST loop
128 if cacheName(i) = p_name then
129 current_version := cacheVersion(i);
130 end if;
131 end loop;
132 end if;
133
134 return current_version;
135 exception
136 when no_data_found then
137 return -1;
138 end;
139 /*
140 * check_version
141 * Use this API to get the current version in FND_CACHE_VERSIONS
142 *
143 * IN
144 * p_name - name of cache
145 *
146 * IN/OUT
147 * p_version - version, can be updated with current_version if applicable.
148 * If p_version is updated with current_version, then the RETURN
149 * value of the function is FALSE and the p_version value
150 * returned can be used to obtain the new value from cache.
151 *
152 * RETURN
153 * TRUE/FALSE - If TRUE, no need to retrieve value from cache (wherever
154 * cache is).
155 * If FALSE, retrieve the value from cache since a newer
156 * version exists.
157 *
158 * RAISES
159 * Never raises exceptions
160 */
161 function check_version (p_name IN varchar2,
162 p_version IN OUT nocopy number)
163 return boolean
164 is
165 current_version number;
166 begin
167 -- Get the current version for the cache name provided.
168 current_version := get_version(p_name);
169
170 -- If -1 is returned, then the cache_name does not exist yet. Still, set version to
171 -- current_version and return FALSE. This will be used as a flag to inidcate that the
172 -- cache_name does not exist yet.
173 if (current_version = -1) then
174 p_version := current_version;
175 return FALSE;
176
177 -- If version is less than current version, then set version to current
178 -- version and set flag to FALSE.
179 elsif (p_version < current_version) then
180 p_version := current_version;
181 return FALSE;
182
183 -- If version is greater than current version (which should NEVER
184 -- happen), then set an error message to the stack and return FALSE;
185 -- **FND_VERSION_GREATER_THAN_CURR message is to be created**.
186 elsif (p_version > current_version) then
187 fnd_message.set_name('FND', 'FND_VERSION_GREATER_THAN_CURR');
188 fnd_message.set_token('CACHE_NAME', p_name);
189 RETURN FALSE;
190
191 end if;
192
193 return TRUE;
194
195 end;
196
197 end FND_CACHE_VERSIONS_PKG;
[Home] [Help]