1 PACKAGE BODY FND_OAM_DSCRAM_PLSQLS_PKG as
2 /* $Header: AFOAMDSPLSB.pls 120.2 2006/06/07 18:04:33 ilawler noship $ */
3
4 ----------------------------------------
5 -- Private Body Constants
6 ----------------------------------------
7 PKG_NAME CONSTANT VARCHAR2(20) := 'DSCRAM_PLSQLS_PKG.';
8
9 --exception used by INTERNAL_EXECUTE to do common cleanup code when failing an execute
10 EXECUTE_FAILED EXCEPTION;
11
12 ----------------------------------------
13 -- Private Body Types/Variables
14 ----------------------------------------
15 TYPE b_plsql_cache_entry IS RECORD
16 (
17 plsql_id NUMBER(15) := NULL,
18 cursor_id INTEGER := NULL,
19 arg_list FND_OAM_DSCRAM_ARGS_PKG.arg_list,
20 use_splitting BOOLEAN := FALSE, --not needed for plsqls, just for args of plsql
21 has_writable_args BOOLEAN := FALSE,
22 last_execute_ret_sts VARCHAR2(3) := NULL,
23 last_execute_ret_msg VARCHAR2(2048) := NULL
24 );
25 TYPE b_plsql_cache_type IS TABLE OF b_plsql_cache_entry INDEX BY BINARY_INTEGER;
26
27 -- Package cache of PLSQLs that have been prepared and parsed
28 -- into a dbms_sql cursor
29 b_plsql_cache b_plsql_cache_type;
30
31 -- PLSQL ID of the currently executing PLSQL
32 b_current_plsql_id NUMBER := NULL;
33
34 ----------------------------------------
35 -- Public/Private Procedures/Functions
36 ----------------------------------------
37
38 -- Public
39 FUNCTION GET_CURRENT_PLSQL_ID
40 RETURN NUMBER
41 IS
42 BEGIN
43 IF b_current_plsql_id IS NULL THEN
44 RAISE NO_DATA_FOUND;
45 END IF;
46
47 RETURN b_current_plsql_id;
48 END;
49
50 -- Public
51 PROCEDURE FETCH_PLSQL_IDS(p_unit_id IN NUMBER,
52 x_work_queue OUT NOCOPY FND_OAM_DSCRAM_UNITS_PKG.ordered_work_queue_type,
53 x_return_status OUT NOCOPY VARCHAR2,
54 x_return_msg OUT NOCOPY VARCHAR2)
55 IS
56 l_ctxt VARCHAR2(60) := PKG_NAME||'FETCH_PLSQL_IDS';
57
58 l_ids DBMS_SQL.NUMBER_TABLE;
59 l_priorities DBMS_SQL.NUMBER_TABLE;
60 l_weights DBMS_SQL.NUMBER_TABLE;
61
62 l_work_queue FND_OAM_DSCRAM_UNITS_PKG.ordered_work_queue_type := FND_OAM_DSCRAM_UNITS_PKG.ordered_work_queue_type();
63 k NUMBER;
64 BEGIN
65 fnd_oam_debug.log(2, l_ctxt, 'ENTER');
66 x_return_status := FND_API.G_RET_STS_ERROR;
67 x_return_msg := '';
68
69 -- bulk select all valid plsqls with work to do
70 SELECT plsql_id, priority, weight
71 BULK COLLECT INTO l_ids, l_priorities, l_weights
72 FROM fnd_oam_dscram_plsqls
73 WHERE unit_id = p_unit_id
74 AND finished_ret_sts IS NULL
75 ORDER BY priority ASC, weight DESC;
76
77 --allocate the work queue
78 l_work_queue.EXTEND(l_ids.COUNT);
79
80 --since we select them in the proper order, construct the work queue by traversing the arrays
81 k := l_ids.FIRST;
82 WHILE k IS NOT NULL LOOP
83 l_work_queue(k) := FND_OAM_DSCRAM_UNITS_PKG.CREATE_WORK_ITEM(l_priorities(k),
84 l_weights(k),
85 FND_OAM_DSCRAM_UTILS_PKG.G_TYPE_PLSQL,
86 l_ids(k));
87 k := l_ids.NEXT(k);
88 END LOOP;
89
90 --success
91 x_work_queue := l_work_queue;
92 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
93 x_return_status := FND_API.G_RET_STS_SUCCESS;
94 EXCEPTION
95 WHEN OTHERS THEN
96 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
97 x_return_msg := 'Unit ID ('||p_unit_id||') failed to fetch plsql_ids: (Code('||SQLCODE||'), Message("'||SQLERRM||'"))';
98 fnd_oam_debug.log(6, l_ctxt, x_return_msg);
99 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
100 END;
101
102 -- Public
103 PROCEDURE UPDATE_COMP_PLS_WRITABLE_ARGS(p_plsql_id IN NUMBER,
104 px_arg_context IN OUT NOCOPY FND_OAM_DSCRAM_ARGS_PKG.arg_context,
105 p_using_splitting IN BOOLEAN,
106 x_return_status OUT NOCOPY VARCHAR2,
107 x_return_msg OUT NOCOPY VARCHAR2)
108 IS
109 l_ctxt VARCHAR2(60) := PKG_NAME||'UPDATE_COMP_PLS_WRITABLE_ARGS';
110
111 l_arg_list FND_OAM_DSCRAM_ARGS_PKG.arg_list;
112 l_has_writable_args BOOLEAN;
113 l_plsql_cache_entry b_plsql_cache_entry;
114
115 l_return_status VARCHAR2(6);
116 l_return_msg VARCHAR2(2048);
117 BEGIN
118 fnd_oam_debug.log(2, l_ctxt, 'ENTER');
119 x_return_status := FND_API.G_RET_STS_ERROR;
120 x_return_msg := '';
121
122 --make sure the plsql's in the cache, if not we need to fetch its arg list now to update the writable args
123 --and place it in the cache if its possible that we will update an arg here.
124 IF NOT b_plsql_cache.EXISTS(p_plsql_id) THEN
125 --fetch the arg list first
126 FND_OAM_DSCRAM_ARGS_PKG.FETCH_ARG_LIST(FND_OAM_DSCRAM_UTILS_PKG.G_TYPE_PLSQL,
127 p_plsql_id,
128 l_arg_list,
129 l_has_writable_args,
130 l_return_status,
131 l_return_msg);
132 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
133 x_return_status := l_return_status;
134 x_return_msg := l_return_msg;
135 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
136 RETURN;
137 ELSIF l_return_status = FND_API.G_RET_STS_SUCCESS AND l_has_writable_args THEN
138 --to allow these args to be pushed into the context, we also need to make a plsql cache entry so that
139 --destroy_plsql_cache_entries can get the computed value
140 l_plsql_cache_entry.plsql_id := p_plsql_id;
141 l_plsql_cache_entry.arg_list := l_arg_list;
142 l_plsql_cache_entry.use_splitting := p_using_splitting;
143 l_plsql_cache_entry.has_writable_args := l_has_writable_args;
144 b_plsql_cache(p_plsql_id) := l_plsql_cache_entry;
145 END IF;
146 END IF;
147
148 --if the plsql made it into the cache and has writable args, update them
149 IF b_plsql_cache.EXISTS(p_plsql_id) AND b_plsql_cache(p_plsql_id).has_writable_args THEN
150 FND_OAM_DSCRAM_ARGS_PKG.UPDATE_WRITABLE_ARG_VALUES(b_plsql_cache(p_plsql_id).arg_list,
151 px_arg_context,
152 TRUE,
153 b_plsql_cache(p_plsql_id).use_splitting,
154 NULL,
155 NULL,
156 NULL,
157 l_return_status,
158 l_return_msg);
159
160 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
161 x_return_status := l_return_status;
162 x_return_msg := l_return_msg;
163 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
164 RETURN;
165 END IF;
166 END IF;
167
168 x_return_status := FND_API.G_RET_STS_SUCCESS;
169 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
170 EXCEPTION
171 WHEN OTHERS THEN
172 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
173 x_return_msg := 'Unhandled Exception: (Code('||SQLCODE||'), Message("'||SQLERRM||'"))';
174 fnd_oam_debug.log(6, l_ctxt, x_return_msg);
175 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
176 END;
177
178 -- Private helper to the destroy procedures to do the actual work of destroying a
179 -- cache entry, assumes p_plsql_id is in the cache.
180 PROCEDURE INTERNAL_DESTROY_CACHE_ENTRY(p_plsql_id IN NUMBER,
181 px_arg_context IN OUT NOCOPY FND_OAM_DSCRAM_ARGS_PKG.arg_context,
182 p_update_context IN BOOLEAN,
183 x_return_status OUT NOCOPY VARCHAR2,
184 x_return_msg OUT NOCOPY VARCHAR2)
185 IS
186 l_ctxt VARCHAR2(60) := PKG_NAME||'INTERNAL_DESTROY_CACHE_ENTRY';
187
188 l_return_status VARCHAR2(6);
189 l_return_msg VARCHAR2(2048);
190 BEGIN
191 x_return_status := FND_API.G_RET_STS_ERROR;
192 x_return_msg := '';
193
194 -- if requested, roll the arg list into the context
195 IF p_update_context THEN
196 FND_OAM_DSCRAM_ARGS_PKG.UPDATE_CONTEXT_USING_ARG_LIST(px_arg_context,
197 b_plsql_cache(p_plsql_id).arg_list,
198 b_plsql_cache(p_plsql_id).use_splitting);
199 END IF;
200
201 --first destroy the argument list
202 FND_OAM_DSCRAM_ARGS_PKG.DESTROY_ARG_LIST(b_plsql_cache(p_plsql_id).arg_list,
203 l_return_status,
204 l_return_msg);
205 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
206 x_return_status := l_return_status;
207 x_return_msg := l_return_msg;
208 RETURN;
209 END IF;
210
211 --now de-allocate the local cursor
212 IF b_plsql_cache(p_plsql_id).cursor_id IS NOT NULL AND DBMS_SQL.IS_OPEN(b_plsql_cache(p_plsql_id).cursor_id) THEN
213 DBMS_SQL.CLOSE_CURSOR(b_plsql_cache(p_plsql_id).cursor_id);
214 END IF;
215
216 --remove the plsql from the cache
217 b_plsql_cache.DELETE(p_plsql_id);
218
219 x_return_status := FND_API.G_RET_STS_SUCCESS;
220 EXCEPTION
221 WHEN OTHERS THEN
222 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
223 x_return_msg := 'Unhandled Exception: (Code('||SQLCODE||'), Message("'||SQLERRM||'"))';
224 fnd_oam_debug.log(6, l_ctxt, x_return_msg);
225 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
226 END;
227
228 -- Public
229 PROCEDURE DESTROY_PLSQL_CACHE_ENTRY(p_plsql_id IN NUMBER,
230 px_arg_context IN OUT NOCOPY FND_OAM_DSCRAM_ARGS_PKG.arg_context,
231 p_update_context IN BOOLEAN,
232 x_return_status OUT NOCOPY VARCHAR2,
233 x_return_msg OUT NOCOPY VARCHAR2)
234 IS
235 l_ctxt VARCHAR2(60) := PKG_NAME||'DESTROY_PLSQL_CACHE_ENTRY';
236
237 l_return_status VARCHAR2(6);
238 l_return_msg VARCHAR2(2048);
239 BEGIN
240 fnd_oam_debug.log(2, l_ctxt, 'ENTER');
241 x_return_status := FND_API.G_RET_STS_ERROR;
242 x_return_msg := '';
243
244 --make sure the plsql's in the cache and get it's entry
245 IF NOT b_plsql_cache.EXISTS(p_plsql_id) THEN
246 --not existing is fine here
247 x_return_status := FND_API.G_RET_STS_SUCCESS;
248 fnd_oam_debug.log(1, l_ctxt, 'PLSQL ID ('||p_plsql_id||') not found in cache.');
249 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
250 RETURN;
251 END IF;
252
253 --call the internal procedure to do the work and return the status of the operation
254 INTERNAL_DESTROY_CACHE_ENTRY(p_plsql_id,
255 px_arg_context,
256 p_update_context,
257 x_return_status,
258 x_return_msg);
259
260 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
261 EXCEPTION
262 WHEN OTHERS THEN
263 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
264 x_return_msg := 'Unhandled Exception: (Code('||SQLCODE||'), Message("'||SQLERRM||'"))';
265 fnd_oam_debug.log(6, l_ctxt, x_return_msg);
266 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
267 END;
268
269 -- Public
270 PROCEDURE DESTROY_PLSQL_CACHE(px_arg_context IN OUT NOCOPY FND_OAM_DSCRAM_ARGS_PKG.arg_context,
271 x_return_status OUT NOCOPY VARCHAR2,
272 x_return_msg OUT NOCOPY VARCHAR2)
273 IS
274 l_ctxt VARCHAR2(60) := PKG_NAME||'DESTROY_PLSQL_CACHE';
275
276 k NUMBER;
277 l_found_failure BOOLEAN := FALSE;
278
279 l_return_status VARCHAR2(6);
280 l_return_msg VARCHAR2(2048);
281 BEGIN
282 fnd_oam_debug.log(2, l_ctxt, 'ENTER');
283 x_return_status := FND_API.G_RET_STS_ERROR;
284 x_return_msg := '';
285
286 --loop through what's left in the cache, destroying each, ignore rolling the args into the context
287 k := b_plsql_cache.FIRST;
288 WHILE k IS NOT NULL LOOP
289 fnd_oam_debug.log(1, l_ctxt, 'PLSQL ID: '||k);
290 INTERNAL_DESTROY_CACHE_ENTRY(k,
291 px_arg_context,
292 FALSE,
293 l_return_status,
294 l_return_msg);
295 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
296 l_found_failure := TRUE;
297 --don't return, let it try to destroy the others first
298 x_return_status := l_return_status;
299 x_return_msg := l_return_msg;
300 END IF;
301
302 k := b_plsql_cache.NEXT(k);
303 END LOOP;
304
305 --delete all members of the cache even if some failed
306 b_plsql_cache.DELETE;
307
308 IF NOT l_found_failure THEN
309 x_return_status := FND_API.G_RET_STS_SUCCESS;
310 END IF;
311 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
312 EXCEPTION
313 WHEN OTHERS THEN
314 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
315 x_return_msg := 'Unhandled Exception: (Code('||SQLCODE||'), Message("'||SQLERRM||'"))';
316 fnd_oam_debug.log(6, l_ctxt, x_return_msg);
320 -- Public
317 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
318 END;
319
321 PROCEDURE COMPLETE_PLSQL(p_plsql_id IN NUMBER,
322 p_finished_ret_sts IN VARCHAR2,
323 p_message IN VARCHAR2,
324 x_return_status OUT NOCOPY VARCHAR2,
325 x_return_msg OUT NOCOPY VARCHAR2)
326 IS
327 l_ctxt VARCHAR2(60) := PKG_NAME||'COMPLETE_PLSQL';
328
329 l_message VARCHAR2(2048);
330 l_finished_ret_sts VARCHAR2(3);
331 l_finished_status VARCHAR2(30);
332
333 l_plsql_in_cache BOOLEAN;
334
335 l_return_status VARCHAR2(6);
336 l_return_msg VARCHAR2(2048);
337 BEGIN
338 fnd_oam_debug.log(2, l_ctxt, 'ENTER');
339 x_return_status := FND_API.G_RET_STS_ERROR;
340 x_return_msg := '';
341
342 --make sure the plsql's in the cache
343 IF b_plsql_cache.EXISTS(p_plsql_id) THEN
344 l_plsql_in_cache := TRUE;
345 ELSE
346 -- its possible for a split plsql to execute but not have any ad units. In this case,
347 -- the plsql will not make it into the cache since there's no point in parsing it. Instead
348 -- we just need to complete the record if workers_assigned = 1.
349 l_plsql_in_cache := FALSE;
350 fnd_oam_debug.log(1, l_ctxt, 'PLSQL id ('||p_plsql_id||') not in plsql cache. This happens.');
351 END IF;
352
353 --perform a blocking select for update on the PLSQL, shouldn't be contention since complete_plsql is called once
354 --and shouldn't already be finished.
355 SELECT finished_ret_sts
356 INTO l_finished_ret_sts
357 FROM fnd_oam_dscram_plsqls
358 WHERE plsql_id = p_plsql_id
359 FOR UPDATE;
360
361 --make sure it's not already finished.
362 IF l_finished_ret_sts IS NOT NULL THEN
363 x_return_msg := 'PLSQL id ('||p_plsql_id||') has finished already set. This should not happen.';
364 fnd_oam_debug.log(6, l_ctxt, x_return_msg);
365 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
366 --don't rollback, parent can do it
367 RETURN;
368 END IF;
369
370 --go ahead and always update the finished_ret_sts, invariant assumes this is only called once for a plsql.
371
372 --default the status, message to those of the last execute if not passed in explicitly
373 IF l_plsql_in_cache THEN
374 l_finished_ret_sts := NVL(p_finished_ret_sts, b_plsql_cache(p_plsql_id).last_execute_ret_sts);
375 l_message := NVL(p_message, b_plsql_cache(p_plsql_id).last_execute_ret_msg);
376 ELSE
377 l_finished_ret_sts := p_finished_ret_sts;
378 l_message := p_message;
379 END IF;
380 l_finished_status := FND_OAM_DSCRAM_UTILS_PKG.CONV_RET_STS_TO_COMPL_STATUS(l_finished_ret_sts);
381
382 fnd_oam_debug.log(1, l_ctxt, 'Finished PLSQL with Status: '||l_finished_status||'('||l_finished_ret_sts||')');
383
384 --update the plsql
385 UPDATE fnd_oam_dscram_plsqls
386 SET finished_ret_sts = l_finished_ret_sts,
387 last_updated_by = fnd_global.user_id,
388 last_update_login = fnd_global.user_id,
389 last_update_date = SYSDATE
390 WHERE plsql_id = p_plsql_id;
391
392 --dump a stats row as well
393 FND_OAM_DSCRAM_STATS_PKG.COMPLETE_ENTRY(p_source_object_type => FND_OAM_DSCRAM_UTILS_PKG.G_TYPE_PLSQL,
394 p_source_object_id => p_plsql_id,
395 p_end_time => SYSDATE,
396 p_end_message => l_message,
397 p_postend_status => l_finished_status);
398
399 --exit without committing, leave that to the parent
400
401 --success
402 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
403 x_return_status := FND_API.G_RET_STS_SUCCESS;
404 EXCEPTION
405 WHEN OTHERS THEN
406 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
407 x_return_msg := 'Unhandled Exception: (Code('||SQLCODE||'), Message("'||SQLERRM||'"))';
408 fnd_oam_debug.log(6, l_ctxt, x_return_msg);
409 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
410 END;
411
412 -- Private wrapper on COMPLETE_PLSQL to do it in an autonomous transaction. This is often used when
413 -- a piece of work has failed and we want to update the metadata even though the data will be
414 -- rolled back.
415 PROCEDURE COMPLETE_PLSQL_AUTONOMOUSLY(p_plsql_id IN NUMBER,
416 p_finished_ret_sts IN VARCHAR2,
417 p_message IN VARCHAR2,
418 x_return_status OUT NOCOPY VARCHAR2,
419 x_return_msg OUT NOCOPY VARCHAR2)
420 IS
421 PRAGMA AUTONOMOUS_TRANSACTION;
422 BEGIN
423 COMPLETE_PLSQL(p_plsql_id,
424 p_finished_ret_sts,
425 p_message,
426 x_return_status,
427 x_return_msg);
428 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
429 COMMIT;
430 ELSE
431 ROLLBACK;
432 END IF;
433 END;
434
435 -- Private: helper to ADD_PLSQL_TO_CACHE to prepare the final statement for a PLSQL. This is
436 -- autonomous to keep from commiting data on the transaction.
437 PROCEDURE GENERATE_FINAL_PLSQL_TEXT(p_plsql_id IN NUMBER,
438 px_arg_context IN OUT NOCOPY FND_OAM_DSCRAM_ARGS_PKG.arg_context,
442 IS
439 x_final_plsql_text OUT NOCOPY VARCHAR2,
440 x_return_status OUT NOCOPY VARCHAR2,
441 x_return_msg OUT NOCOPY VARCHAR2)
443 PRAGMA AUTONOMOUS_TRANSACTION;
444
445 l_ctxt VARCHAR2(60) := PKG_NAME||'GENERATE_FINAL_PLSQL_TEXT';
446
447 l_plsql_text VARCHAR2(3000);
448 l_final_plsql_text VARCHAR2(4000);
449
450 l_length NUMBER;
451 l_maxlen NUMBER := 4000;
452
453 l_return_status VARCHAR2(6);
454 l_return_msg VARCHAR2(2048);
455 BEGIN
456 fnd_oam_debug.log(2, l_ctxt, 'ENTER');
457 x_return_status := FND_API.G_RET_STS_ERROR;
458 x_return_msg := '';
459
460 --assume that the check already occured in add_plsql_to_cache so proceed right to the lock
461 SELECT plsql_text, plsql_final_text
462 INTO l_plsql_text, l_final_plsql_text
463 FROM fnd_oam_dscram_plsqls
464 WHERE plsql_id = p_plsql_id
465 FOR UPDATE;
466
467 --make sure the final text's still null
468 IF l_final_plsql_text IS NOT NULL THEN
469 x_final_plsql_text := l_final_plsql_text;
470 ROLLBACK;
471 x_return_status := FND_API.G_RET_STS_SUCCESS;
472 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
473 RETURN;
474 END IF;
475
476 --create the SQL statement by wrapping the plsql procedure text in an anonymous block
477 l_length := LENGTH(l_plsql_text) + LENGTH(FND_OAM_DSCRAM_UTILS_PKG.G_PLSQL_PREFIX) +
478 LENGTH(FND_OAM_DSCRAM_UTILS_PKG.G_PLSQL_SUFFIX) + 1;
479 IF l_length > l_maxlen THEN
480 x_return_msg := 'PLSQL Final text length would be '||l_length||', greater than max: '||l_maxlen;
481 fnd_oam_debug.log(6, l_ctxt, x_return_msg);
482 ROLLBACK;
483 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
484 RETURN;
485 END IF;
486 l_final_plsql_text := FND_OAM_DSCRAM_UTILS_PKG.G_PLSQL_PREFIX||l_plsql_text||';'||FND_OAM_DSCRAM_UTILS_PKG.G_PLSQL_SUFFIX;
487
488 --store this string as the final text
489 UPDATE fnd_oam_dscram_plsqls
490 SET plsql_final_text = l_final_plsql_text,
491 stats_finished = FND_API.G_FALSE,
492 last_updated_by = fnd_global.user_id,
493 last_update_login = fnd_global.user_id,
494 last_update_date = SYSDATE
495 WHERE plsql_id = p_plsql_id;
496
497 --commit the autonomous txn to release the lock
498 COMMIT;
499
500 --success
501 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
502 x_return_status := FND_API.G_RET_STS_SUCCESS;
503 x_final_plsql_text := l_final_plsql_text;
504 EXCEPTION
505 WHEN OTHERS THEN
506 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
507 x_return_msg := 'PLSQL ID ('||p_plsql_id||'), failed to generate final plsql text: (Code('||SQLCODE||'), Message("'||SQLERRM||'"))';
508 fnd_oam_debug.log(6, l_ctxt, x_return_msg);
509 ROLLBACK;
510 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
511 END;
512
513 -- Private helper to EXECUTE_PLSQL* to take a foreign plsql_id and prepare it by parsing it into
514 -- a cursor and fetching the arg list
515 PROCEDURE ADD_PLSQL_TO_CACHE(p_plsql_id IN NUMBER,
516 px_arg_context IN OUT NOCOPY FND_OAM_DSCRAM_ARGS_PKG.arg_context,
517 p_use_splitting IN BOOLEAN,
518 x_return_status OUT NOCOPY VARCHAR2,
519 x_return_msg OUT NOCOPY VARCHAR2)
520 IS
521 l_ctxt VARCHAR2(60) := PKG_NAME||'ADD_PLSQL_TO_CACHE';
522
523 l_final_plsql_text VARCHAR2(4000);
524
525 l_stat_id NUMBER;
526 l_cache_entry b_plsql_cache_entry;
527 l_cursor_id INTEGER;
528 l_arg_list FND_OAM_DSCRAM_ARGS_PKG.arg_list;
529 l_has_writable_args BOOLEAN;
530
531 l_return_status VARCHAR2(6);
532 l_return_msg VARCHAR2(4000);
533 BEGIN
534 fnd_oam_debug.log(2, l_ctxt, 'ENTER');
535 x_return_status := FND_API.G_RET_STS_ERROR;
536 x_return_msg := '';
537
538 --if the plsql isn't cached, there's a chance that its stats row hasn't been created yet, query and
539 --create if necessary. This is not mt-safe but extra calls to create_entry will be discarded due to
540 --the unique index on fnd_oam_dscram_stats.
541 IF NOT FND_OAM_DSCRAM_STATS_PKG.HAS_ENTRY(FND_OAM_DSCRAM_UTILS_PKG.G_TYPE_PLSQL,
542 p_plsql_id) THEN
543 --autonomously create the stats entry
544 FND_OAM_DSCRAM_STATS_PKG.CREATE_ENTRY_AUTONOMOUSLY(p_source_object_type => FND_OAM_DSCRAM_UTILS_PKG.G_TYPE_PLSQL,
545 p_source_object_id => p_plsql_id,
546 p_start_time => SYSDATE,
547 p_prestart_status => NULL,
548 p_dismiss_failure => FND_API.G_TRUE,
549 x_stat_id => l_stat_id);
550 END IF;
551
552 --query out the prepared plsql text
553 SELECT plsql_final_text
554 INTO l_final_plsql_text
555 FROM fnd_oam_dscram_plsqls
556 WHERE plsql_id = p_plsql_id;
557
558 --if it's not present, generate it
559 IF l_final_plsql_text IS NULL THEN
560 GENERATE_FINAL_PLSQL_TEXT(p_plsql_id,
564 l_return_msg);
561 px_arg_context,
562 l_final_plsql_text,
563 l_return_status,
565 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
566 x_return_status := l_return_status;
567 x_return_msg := l_return_msg;
568 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
569 RETURN;
570 END IF;
571 END IF;
572
573 --got a final stmt, put it into a cursor
574 l_cursor_id := DBMS_SQL.OPEN_CURSOR;
575 BEGIN
576 DBMS_SQL.PARSE(l_cursor_id,
577 l_final_plsql_text,
578 DBMS_SQL.NATIVE);
579 EXCEPTION
580 WHEN OTHERS THEN
581 DBMS_SQL.CLOSE_CURSOR(l_cursor_id);
582 x_return_msg := 'PLSQL_ID ('||p_plsql_id||'), failed to parse final stmt: (Code('||SQLCODE||'), Message("'||SQLERRM||'"))';
583 fnd_oam_debug.log(6, l_ctxt, x_return_msg);
584 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
585 RETURN;
586 END;
587
588 --now lets get the arg list for the plsql
589 FND_OAM_DSCRAM_ARGS_PKG.FETCH_ARG_LIST(FND_OAM_DSCRAM_UTILS_PKG.G_TYPE_PLSQL,
590 p_plsql_id,
591 l_arg_list,
592 l_has_writable_args,
593 l_return_status,
594 l_return_msg);
595 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
596 DBMS_SQL.CLOSE_CURSOR(l_cursor_id);
597 x_return_status := l_return_status;
598 x_return_msg := l_return_msg;
599 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
600 RETURN;
601 END IF;
602
603 --SUCCESS, update internal structures
604
605 --initialize the entry
606 l_cache_entry.plsql_id := p_plsql_id;
607 l_cache_entry.cursor_id := l_cursor_id;
608 l_cache_entry.arg_list := l_arg_list;
609 l_cache_entry.use_splitting := p_use_splitting;
610 l_cache_entry.has_writable_args := l_has_writable_args;
611 l_cache_entry.last_execute_ret_sts := NULL;
612 l_cache_entry.last_execute_ret_msg := NULL;
613
614 --store the entry in the cache
615 b_plsql_cache(p_plsql_id) := l_cache_entry;
616
617 --success
618 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
619 x_return_status := FND_API.G_RET_STS_SUCCESS;
620 EXCEPTION
621 WHEN OTHERS THEN
622 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
623 x_return_msg := 'PLSQL ID ('||p_plsql_id||'), unexpected error: (Code('||SQLCODE||'), Message("'||SQLERRM||'"))';
624 fnd_oam_debug.log(6, l_ctxt, x_return_msg);
625 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
626 END;
627
628 -- Private, helper to EXECUTE_PLSQL* procedures to do the grunt work. the call to complete the PLSQL
629 -- will be done by the caller. No commits or rollbacks are done here.
630 PROCEDURE INTERNAL_EXECUTE_PLSQL(p_plsql_id IN NUMBER,
631 px_arg_context IN OUT NOCOPY FND_OAM_DSCRAM_ARGS_PKG.arg_context,
632 p_use_splitting IN BOOLEAN,
633 p_rowid_lbound IN ROWID,
634 p_rowid_ubound IN ROWID,
635 x_return_status OUT NOCOPY VARCHAR2,
636 x_return_msg OUT NOCOPY VARCHAR2)
637 IS
638 l_ctxt VARCHAR2(60) := PKG_NAME||'INTERNAL_EXECUTE_PLSQL';
639
640 l_rows_processed NUMBER;
641
642 l_return_status VARCHAR2(6);
643 l_return_msg VARCHAR2(2048);
644 BEGIN
645 fnd_oam_debug.log(2, l_ctxt, 'ENTER');
646 x_return_status := FND_API.G_RET_STS_ERROR;
647 x_return_msg := '';
648
649 fnd_oam_debug.log(1, l_ctxt, 'PLSQL ID: '||p_plsql_id);
650
651 --set the current plsql id before binding args to allow for access of the plsql_id from a state argument
652 b_current_plsql_id := p_plsql_id;
653
654 --first see if the plsql's in the cache
655 IF b_plsql_cache.EXISTS(p_plsql_id) THEN
656 --make sure the cursor isn't configured for splitting
657 IF b_plsql_cache(p_plsql_id).use_splitting <> p_use_splitting THEN
658 x_return_msg := 'PLSQL ID ('||p_plsql_id||'), cached splitting enabled not equal to provided splitting enabled. This should not happen.';
659 fnd_oam_debug.log(6, l_ctxt, x_return_msg);
660 RAISE EXECUTE_FAILED;
661 END IF;
662 ELSE
663 --no cache entry, create one
664 ADD_PLSQL_TO_CACHE(p_plsql_id,
665 px_arg_context,
666 p_use_splitting,
667 l_return_status,
668 l_return_msg);
669 --react to the return status to skip execution if we failed
670 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
671 x_return_status := l_return_status;
672 x_return_msg := l_return_msg;
673 RAISE EXECUTE_FAILED;
674 END IF;
675 END IF;
676
677 --before every execution, re-bind the readable argument values. This may or may not change the
678 --value of the binding depending on the arg's write policy.
679 FND_OAM_DSCRAM_ARGS_PKG.BIND_ARG_LIST_TO_CURSOR(b_plsql_cache(p_plsql_id).arg_list,
680 px_arg_context,
681 b_plsql_cache(p_plsql_id).cursor_id,
685 l_return_status,
682 p_use_splitting,
683 p_rowid_lbound,
684 p_rowid_ubound,
686 l_return_msg);
687 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
688 x_return_status := l_return_status;
689 x_return_msg := l_return_msg;
690 RAISE EXECUTE_FAILED;
691 END IF;
692
693 --do not automatically bind the rowids if we're using splitting, it's up to the plsql to declare what args it requires
694 --independent of the context in which it's called
695
696 --skip the execute if we're in test-no-exec mode
697 IF FND_OAM_DSCRAM_RUNS_PKG.GET_RUN_MODE = FND_OAM_DSCRAM_UTILS_PKG.G_MODE_TEST_NO_EXEC THEN
698 fnd_oam_debug.log(1, l_ctxt, 'Skipping Executiong due to run mode.');
699 ELSE
700 --do the execute
701 fnd_oam_debug.log(1, l_ctxt, 'Executing cursor...');
702 BEGIN
703 l_rows_processed := DBMS_SQL.EXECUTE(b_plsql_cache(p_plsql_id).cursor_id);
704 EXCEPTION
705 WHEN OTHERS THEN
706 x_return_msg := 'SQL execute error: (Code('||SQLCODE||'), Message("'||SQLERRM||'"))';
707 fnd_oam_debug.log(6, l_ctxt, x_return_msg);
708 RAISE EXECUTE_FAILED;
709 END;
710 fnd_oam_debug.log(1, l_ctxt, '...Done.('||l_rows_processed||' rows)');
711 END IF;
712
713 -- If the plsql has any output variables, we should get values for them
714 IF b_plsql_cache(p_plsql_id).has_writable_args THEN
715 FND_OAM_DSCRAM_ARGS_PKG.UPDATE_WRITABLE_ARG_VALUES(b_plsql_cache(p_plsql_id).arg_list,
716 px_arg_context,
717 NOT p_use_splitting, --we're finished if we're not splitting
718 p_use_splitting,
719 p_rowid_lbound,
720 p_rowid_ubound,
721 b_plsql_cache(p_plsql_id).cursor_id,
722 l_return_status,
723 l_return_msg);
724 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
725 x_return_status := l_return_status;
726 x_return_msg := l_return_msg;
727 RAISE EXECUTE_FAILED;
728 END IF;
729 END IF;
730
731 -- Success
732 b_current_plsql_id := NULL;
733 x_return_status := FND_API.G_RET_STS_SUCCESS;
734 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
735 EXCEPTION
736 WHEN EXECUTE_FAILED THEN
737 b_current_plsql_id := NULL;
738 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
739 RETURN;
740 WHEN OTHERS THEN
741 b_current_plsql_id := NULL;
742 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
743 x_return_msg := 'Unhandled Exception: [Code('||SQLCODE||'), Message("'||SQLERRM||'")]';
744 fnd_oam_debug.log(6, l_ctxt, x_return_msg);
745 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
746 RETURN;
747 END;
748
749 -- Public
750 PROCEDURE EXECUTE_PLSQL(p_plsql_id IN NUMBER,
751 px_arg_context IN OUT NOCOPY FND_OAM_DSCRAM_ARGS_PKG.arg_context,
752 x_return_status OUT NOCOPY VARCHAR2,
753 x_return_msg OUT NOCOPY VARCHAR2)
754 IS
755 l_ctxt VARCHAR2(60) := PKG_NAME||'EXECUTE_PLSQL';
756
757 l_return_status VARCHAR2(6);
758 l_return_msg VARCHAR2(2048);
759 l_return_status2 VARCHAR2(6);
760 l_return_msg2 VARCHAR2(2048);
761 BEGIN
762 fnd_oam_debug.log(2, l_ctxt, 'ENTER');
763
764 --do the single-threaded execute
765 INTERNAL_EXECUTE_PLSQL(p_plsql_id,
766 px_arg_context,
767 FALSE,
768 NULL,
769 NULL,
770 l_return_status,
771 l_return_msg);
772
773 -- if successful, complete the plsql
774 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
775 --update the rows plsql cache's derived attributes
776 b_plsql_cache(p_plsql_id).last_execute_ret_sts := l_return_status;
777 b_plsql_cache(p_plsql_id).last_execute_ret_msg := l_return_msg;
778
779 --if in normal mode, complete normally, otherwise we need to complete autonomously for progress to be maintained
780 --the status of the complete is returned as the overall execute status so we have insight into whether the entire
781 --operation suceeded
782 IF FND_OAM_DSCRAM_UTILS_PKG.RUN_IS_NORMAL THEN
783 COMPLETE_PLSQL(p_plsql_id,
784 l_return_status,
785 l_return_msg,
786 x_return_status,
787 x_return_msg);
788 ELSE
789 COMPLETE_PLSQL_AUTONOMOUSLY(p_plsql_id,
790 l_return_status,
791 l_return_msg,
792 x_return_status,
793 x_return_msg);
794 END IF;
795 ELSE
796 --update the rows plsql cache's derived attributes
797 IF b_plsql_cache.EXISTS(p_plsql_id) THEN
798 b_plsql_cache(p_plsql_id).last_execute_ret_sts := l_return_status;
799 b_plsql_cache(p_plsql_id).last_execute_ret_msg := l_return_msg;
800 END IF;
801
802 --if failed, complete it autonomously since the data will be rolled back and errors override
803 --this does not need to be atomic with the unit update since individually executed PLSQLs are
804 --not queried concurrently and passing the data back to the unit to commit is extra unnecessary work.
805 COMPLETE_PLSQL_AUTONOMOUSLY(p_plsql_id,
806 l_return_status,
807 l_return_msg,
808 l_return_status2,
809 l_return_msg2);
810 --return status/msg of execute failure since we're more concerned about that
811 x_return_status := l_return_status;
812 x_return_msg := l_return_msg;
813 END IF;
814
815 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
816 EXCEPTION
817 WHEN OTHERS THEN
818 x_return_msg := 'Unhandled Exception: [Code('||SQLCODE||'), Message("'||SQLERRM||'")]';
819 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
820 fnd_oam_debug.log(6, l_ctxt, x_return_msg);
821 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
822 END;
823
824 -- Public
825 PROCEDURE EXECUTE_PLSQL_ON_RANGE(p_plsql_id IN NUMBER,
826 px_arg_context IN OUT NOCOPY FND_OAM_DSCRAM_ARGS_PKG.arg_context,
827 p_rowid_lbound IN ROWID,
828 p_rowid_ubound IN ROWID,
829 x_return_status OUT NOCOPY VARCHAR2,
830 x_return_msg OUT NOCOPY VARCHAR2)
831 IS
832 l_ctxt VARCHAR2(60) := PKG_NAME||'EXECUTE_PLSQL_ON_RANGE';
833
834 l_plsql_id NUMBER;
835
836 l_return_status VARCHAR2(6);
837 l_return_msg VARCHAR2(2048);
838 l_return_status2 VARCHAR2(6);
839 l_return_msg2 VARCHAR2(2048);
840 BEGIN
841 x_return_status := FND_API.G_RET_STS_ERROR;
842 x_return_msg := '';
843
844 fnd_oam_debug.log(2, l_ctxt, 'ENTER');
845 INTERNAL_EXECUTE_PLSQL(p_plsql_id,
846 px_arg_context,
847 TRUE,
848 p_rowid_lbound,
849 p_rowid_ubound,
850 l_return_status,
851 l_return_msg);
852 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
853 --update the rows plsql cache's derived attributes
854 b_plsql_cache(p_plsql_id).last_execute_ret_sts := l_return_status;
855 b_plsql_cache(p_plsql_id).last_execute_ret_msg := l_return_msg;
856 ELSE
857 --update the rows plsql cache's derived attributes
858 IF b_plsql_cache.EXISTS(p_plsql_id) THEN
859 b_plsql_cache(p_plsql_id).last_execute_ret_sts := l_return_status;
860 b_plsql_cache(p_plsql_id).last_execute_ret_msg := l_return_msg;
861 END IF;
862
863 --return status/msg of execute failure
864 x_return_status := l_return_status;
865 x_return_msg := l_return_msg;
866 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
867 RETURN;
868 END IF;
869
870 --success
871 x_return_status := FND_API.G_RET_STS_SUCCESS;
872 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
873 EXCEPTION
874 WHEN OTHERS THEN
875 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
876 x_return_msg := 'Unhandled Exception: [Code('||SQLCODE||'), Message("'||SQLERRM||'")]';
877 fnd_oam_debug.log(6, l_ctxt, x_return_msg);
878 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
879 END;
880
881 END FND_OAM_DSCRAM_PLSQLS_PKG;
[Home] [Help]