1 package body FND_CACHE_VERSIONS_PKG as
2 /* $Header: AFCCHVRB.pls 120.4.12020000.2 2012/08/03 15:32:28 rarmaly ship $ */
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 -- Bug 13785535 - Use autonomous transaction
57 -- to solved cache-lock issue with high number
58 -- of call ie. usually during heavy validation-procedures
59 -- like wf-custome events etc.
60 PRAGMA AUTONOMOUS_TRANSACTION;
61
62 begin
63
64 -- Try to update FND_CACHE_VERSIONS given the name.
65 update FND_CACHE_VERSIONS
66 set VERSION = VERSION + 1
67 where NAME = p_name;
68
69 -- If name does not exist, insert the name using add_version.
70 if (sql%rowcount = 0) then
71 add_cache_name(p_name);
72 else
73 get_values; -- Refresh the arrays.
74 end if;
75
76 -- Bug 13785535 - Since now it is an autonomous transaction
77 -- we can use a commit here.
78 commit;
79
80 end;
81
82 /*
83 * get_version
84 * Use this API to get the current version in FND_CACHE_VERSIONS
85 *
86 * IN
87 * p_name - name of cache
88 *
89 * RETURN
90 * returns the current_version in FND_CACHE_VERSIONS given a name
91 *
92 * RAISES
93 * Never raises exceptions, returns -1 if name does not exist
94 */
95 function get_version (p_name varchar2)
96 return number
97 is
98 current_version number := -1; -- Bug 5629463:get_version returns NULL
99 begin
100
101 if cacheName is null then
102 get_values;
103
104 -- Please note that the previous fix to bug 4308360 has been moved
105 -- outside of this conditional block due to bug 5161071.
106
107 /* For bug 4308360. When there is no data, bulk collect option is
108 * not returning 'when_no_data_found' exception and the values for
109 * cachName.FIRST, cacheName.LAST are NULL, and this
110 * raises ORA-6502 exception at 'for i in cacheName.FIRST' line.
111 * Hence the following IF block is written.
112 *
113 if (cacheName.COUNT = 0) then
114 return -1;
115 end if;
116 */
117 end if;
118
119 /* Bug 5161071:4308360: ORA-06502 FND_CACHE_VERSIONS_PKG.get_version
120 * The previous fix for bug 4308360 did not completely resolve the issue
121 * since it was placed in a conditional block. As it happens, the
122 * condition is not satisfied in all cases and the failover is bypassed
123 * causing the ORA-6502 error to be raised.
124 *
125 * This, hopefully, more complete fix is to move the failover outside of
126 * the conditional block. This should catch all cases that the failover
127 * would be required.
128 */
129
130 -- Bug 5161071 shows a case where the 'cacheName is null' condition is not
131 -- met while FND_CACHE_VERSIONS table is empty. Hence, the
132 -- cacheName.COUNT = 0 condition should prevent the ORA-6502 error that
133 -- gets raised when the for loop executes even when cacheName.COUNT = 0.
134 if (cacheName.COUNT = 0) then
135 return -1;
136 else
137 for i in cacheName.FIRST..cacheName.LAST loop
138 if cacheName(i) = p_name then
139 current_version := cacheVersion(i);
140 end if;
141 end loop;
142 end if;
143
144 return current_version;
145 exception
146 when no_data_found then
147 return -1;
148 end;
149 /*
150 * check_version
151 * Use this API to get the current version in FND_CACHE_VERSIONS
152 *
153 * IN
154 * p_name - name of cache
155 *
156 * IN/OUT
157 * p_version - version, can be updated with current_version if applicable.
158 * If p_version is updated with current_version, then the RETURN
159 * value of the function is FALSE and the p_version value
160 * returned can be used to obtain the new value from cache.
161 *
162 * RETURN
163 * TRUE/FALSE - If TRUE, no need to retrieve value from cache (wherever
164 * cache is).
165 * If FALSE, retrieve the value from cache since a newer
166 * version exists.
167 *
168 * RAISES
169 * Never raises exceptions
170 */
171 function check_version (p_name IN varchar2,
172 p_version IN OUT nocopy number)
173 return boolean
174 is
175 current_version number;
176 begin
177 -- Get the current version for the cache name provided.
178 current_version := get_version(p_name);
179
180 -- If -1 is returned, then the cache_name does not exist yet. Still, set version to
181 -- current_version and return FALSE. This will be used as a flag to inidcate that the
182 -- cache_name does not exist yet.
183 if (current_version = -1) then
184 p_version := current_version;
185 return FALSE;
186
187 -- If version is less than current version, then set version to current
188 -- version and set flag to FALSE.
189 elsif (p_version < current_version) then
190 p_version := current_version;
191 return FALSE;
192
193 -- If version is greater than current version (which should NEVER
194 -- happen), then set an error message to the stack and return FALSE;
195 -- **FND_VERSION_GREATER_THAN_CURR message is to be created**.
196 elsif (p_version > current_version) then
197 fnd_message.set_name('FND', 'FND_VERSION_GREATER_THAN_CURR');
198 fnd_message.set_token('CACHE_NAME', p_name);
199 RETURN FALSE;
200
201 end if;
202
203 return TRUE;
204
205 end;
206
207 end FND_CACHE_VERSIONS_PKG;
[Home] [Help]