1 PACKAGE BODY FEM_Database_Util_Pkg AS
2 -- $Header: fem_db_utl.plb 120.6 2007/02/20 03:10:48 gcheng ship $
3
4 /***************************************************************************
5 Copyright (c) 2005 Oracle Corporation
6 Redwood Shores, CA, USA
7 All rights reserved.
8 ***************************************************************************
9 FILENAME
10 fem_db_utl.plb
11
12 DESCRIPTION
13 FEM Database Utilities Package
14
15 HISTORY
16 Tim Moore 14-Oct-2003 Original script
17 Tim Moore 31-Aug-2004 Added Validate_Table_Columns
18 Greg Hall 23-May-2005 Bug# 4301983: Added procedures for managing
19 temporary tables, indexes, and views.
20 Greg Hall 21-Jun-2005 Bug# 4445212: Added calls to Get_PB_Param_Value
21 for data-driven tablespace and storage parameters.
22 Greg Hall 13-Jul-2005 Bug# 4491889: Fixed bug in the Exec procedure.
23 Greg Hall 19-Jul-2006 Bug# 5146586: Fixed bug that prevented logging
24 of the SQL error message to FEM_DDL_LOG for
25 failed SQL statements.
26 Greg Hall 19-Jul-2006 Bug# 5212287: for all four temp object
27 procedures, added Oracle error message into
28 the error message that is returned on the message
29 stack when a dynamic DDL statement fails.
30 Gordon Cheng 19-Feb-2007 Bug 5873766: Added p_pb_object_id
31 v120.6 parameter to the following procedures:
32 Create_Temp_Table
33 Create_Temp_Index
34 Create_Temp_View
35 Drop_Temp_DB_Objects
36 **************************************************************************/
37
38 ---------------------------------------
39 -- Declare Private Package Variables --
40 ---------------------------------------
41
42 c_log_level_1 CONSTANT NUMBER := fnd_log.level_statement;
43 c_log_level_2 CONSTANT NUMBER := fnd_log.level_procedure;
44 c_log_level_3 CONSTANT NUMBER := fnd_log.level_event;
45 c_log_level_4 CONSTANT NUMBER := fnd_log.level_exception;
46 c_log_level_5 CONSTANT NUMBER := fnd_log.level_error;
47 c_log_level_6 CONSTANT NUMBER := fnd_log.level_unexpected;
48
49 c_user_id CONSTANT NUMBER := FND_GLOBAL.User_ID;
50 c_login_id CONSTANT NUMBER := FND_GLOBAL.Login_Id;
51
52
53 /***************************************************************************
54 ===========================================================================
55 Private Procedures
56 ===========================================================================
57 ***************************************************************************/
58
59
60 /****************************************************************************/
61 PROCEDURE Validate_OA_Params (p_api_version IN NUMBER,
62 p_init_msg_list IN VARCHAR2,
63 p_commit IN VARCHAR2,
64 p_encoded IN VARCHAR2,
65 x_return_status OUT NOCOPY VARCHAR2) IS
66 -- ==========================================================================
67 -- DESCRIPTION
68 -- Validates the OA input parameters for other procedures in this package.
69 -- Parameters:
70 -- See description of other OA-compliant procedures having these same
71 -- parameters for a description of the IN parameters.
72 -- x_return_status:
73 -- Returns the value from FND_API.G_RET_STS_ERROR ('E') if there are
74 -- any parameter validation errors.
75 -- HISTORY
76 -- Greg Hall 23-May-2005 Bug# 4301983: copied from
77 -- FEM_DIMENSION_UTIL_PKG.
78 -- ==========================================================================
79
80 e_bad_p_api_ver EXCEPTION;
81 e_bad_p_init_msg_list EXCEPTION;
82 e_bad_p_commit EXCEPTION;
83 e_bad_p_encoded EXCEPTION;
84
85 BEGIN
86
87 x_return_status := c_success;
88
89 CASE p_api_version
90 WHEN c_api_version THEN NULL;
91 ELSE RAISE e_bad_p_api_ver;
92 END CASE;
93
94 CASE p_init_msg_list
95 WHEN c_false THEN NULL;
96 WHEN c_true THEN
97 FND_MSG_PUB.Initialize;
98 ELSE RAISE e_bad_p_init_msg_list;
99 END CASE;
100
101 CASE p_encoded
102 WHEN c_false THEN NULL;
103 WHEN c_true THEN NULL;
104 ELSE RAISE e_bad_p_encoded;
105 END CASE;
106
107 CASE p_commit
108 WHEN c_false THEN NULL;
109 WHEN c_true THEN NULL;
110 ELSE RAISE e_bad_p_commit;
111 END CASE;
112
113 EXCEPTION
114 WHEN e_bad_p_api_ver THEN
115 FEM_ENGINES_PKG.Put_Message(
116 p_app_name => 'FEM',
117 p_msg_name => 'FEM_BAD_P_API_VER_ERR',
118 p_token1 => 'VALUE',
119 p_value1 => p_api_version);
120 x_return_status := c_error;
121
122 WHEN e_bad_p_init_msg_list THEN
123 FEM_ENGINES_PKG.Put_Message(
124 p_app_name => 'FEM',
125 p_msg_name => 'FEM_BAD_P_INIT_MSG_LIST_ERR');
126 x_return_status := c_error;
127
128 WHEN e_bad_p_encoded THEN
129 FEM_ENGINES_PKG.Put_Message(
130 p_app_name => 'FEM',
131 p_msg_name => 'FEM_BAD_P_ENCODED_ERR');
132 x_return_status := c_error;
133
134 WHEN e_bad_p_commit THEN
135 FEM_ENGINES_PKG.Put_Message(
136 p_app_name => 'FEM',
137 p_msg_name => 'FEM_BAD_P_COMMIT_ERR');
138 x_return_status := c_error;
139
140 END Validate_OA_Params;
141
142
143 /****************************************************************************/
144 PROCEDURE exec (p_request_id IN NUMBER,
145 p_object_id IN NUMBER,
146 p_proc_name IN VARCHAR2,
147 p_command IN VARCHAR2,
148 p_ddl_logging IN VARCHAR2 DEFAULT 'OFF') IS
149 -- ==========================================================================
150 -- DESCRIPTION
151 -- Executes the DDL SQL statement passed to it, using EXECUTE IMMEDIATE.
152 -- It logs the DDL operation in the Apps debug log.
153 -- If the FEM Process Behavior parameter setting that is in effect for
154 -- p_object_id = 'ON' then tt logs the DDL operation into FEM_DDL_LOG.
155 -- Note that since DDL statements perform an implicit COMMIT, there is no
156 -- point in trying to honor the p_commit parameter received by any of the
157 -- calling procedures, so this procedure doesn't even bother taking it,
158 -- and all inserts into FEM_DDL_LOG are commited.
159 -- If the DDL statement fails, the error is re-raised, to be trapped by the
160 -- calling program.
161 -- This procedure should only be used for executing DDL or other SQL that
162 -- must be built "on-the-fly". All other SQL statements should be executed
163 -- directly or in a declared cursor.
164 -- PARAMETERS
165 -- The SQL statement in p_command is executed. All other parameters are
166 -- used for logging the DDL operation in FEM_DDL_LOG.
167 -- HISTORY
168 -- Greg Hall 23-May-2005 Bug# 4301983: created.
169 -- Greg Hall 13-Jul-2005 Bug# 4491889: moved SUBSTR function outside
170 -- of the INSERT commands, into a separate
171 -- PL/SQL assignment statement, to avoid an
172 -- error when p_command > 4000 char. Also
173 -- moved Debug log calls ahead of DDL log
174 -- inserts, in case the latter fails it won't
175 -- prevent the former, and added error handlers
176 -- for DDL log insert statements.
177 -- Greg Hall 19-Jul-2006 Bug# 5146586: fixed bug that prevented logging
178 -- of the SQL error message to FEM_DDL_LOG for
179 -- failed SQL statements.
180 -- ==========================================================================
181
182 v_command_short VARCHAR2(4000);
183 v_sqlerrm VARCHAR2(255);
184
185 BEGIN
186
187 v_command_short := SUBSTR(p_command, 1, 3980);
188
189 EXECUTE IMMEDIATE p_command;
190
191 -- Log to Debug Log
192
193 FEM_ENGINES_PKG.TECH_MESSAGE(
194 p_severity => c_log_level_1,
195 p_module => 'fem.plsql.fem_database_util_pkg.' || lower(p_proc_name) || '.exec',
196 p_msg_text => 'SUCCESSFUL DDL: ' || v_command_short );
197
198 COMMIT;
199
200 -- Log to DDL Log
201
202 IF p_ddl_logging = 'ON' THEN
203
204 BEGIN
205
206 INSERT into fem_ddl_log
207 (request_id,
208 object_id,
209 exec_seq,
210 timestamp,
211 procedure_name,
212 status,
213 sql_error_msg,
214 sql_statement,
215 created_by,
216 creation_date,
217 last_updated_by,
218 last_update_date,
219 last_update_login)
220 VALUES
221 (p_request_id,
222 p_object_id,
223 fem_ddl_log_s.nextval,
224 TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS'),
225 p_proc_name,
226 'S',
227 NULL,
228 v_command_short,
229 c_user_id,
230 SYSDATE,
231 c_user_id,
232 SYSDATE,
233 c_login_id);
234
235 EXCEPTION
236 WHEN OTHERS THEN
237
238 v_sqlerrm := SUBSTR(SQLERRM, 1, 255);
239
240 -- Log to Debug Log
241
242 FEM_ENGINES_PKG.TECH_MESSAGE(
243 p_severity => c_log_level_5,
244 p_module => 'fem.plsql.fem_database_util_pkg.' || lower(p_proc_name) || '.exec',
245 p_msg_text => v_sqlerrm );
246
247 END;
248
249 COMMIT;
250
251 END IF;
252
253 EXCEPTION
254 WHEN OTHERS THEN
255
256 v_sqlerrm := SUBSTR(SQLERRM, 1, 255);
257
258 -- Log to Debug Log
259
260 FEM_ENGINES_PKG.TECH_MESSAGE(
261 p_severity => c_log_level_5,
262 p_module => 'fem.plsql.fem_database_util_pkg.' || lower(p_proc_name) || '.exec',
263 p_msg_text => 'FAILED DDL: ' || v_command_short );
264
265 FEM_ENGINES_PKG.TECH_MESSAGE(
266 p_severity => c_log_level_5,
267 p_module => 'fem.plsql.fem_database_util_pkg.' || lower(p_proc_name) || '.exec',
268 p_msg_text => v_sqlerrm );
269
270 COMMIT;
271
272 -- Log to DDL Log
273
274 IF p_ddl_logging = 'ON' THEN
275
276 BEGIN
277
278 INSERT into fem_ddl_log
279 (request_id,
280 object_id,
281 exec_seq,
282 timestamp,
283 procedure_name,
284 status,
285 sql_error_msg,
286 sql_statement,
287 created_by,
288 creation_date,
289 last_updated_by,
290 last_update_date,
291 last_update_login)
292 VALUES
293 (p_request_id,
294 p_object_id,
295 fem_ddl_log_s.nextval,
296 TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS'),
297 p_proc_name,
298 'F',
299 v_sqlerrm,
300 v_command_short,
301 c_user_id,
302 SYSDATE,
303 c_user_id,
304 SYSDATE,
305 c_login_id);
306
307 EXCEPTION
308 WHEN OTHERS THEN
309
310 v_sqlerrm := SUBSTR(SQLERRM, 1, 255);
311
312 -- Log to Debug Log
313
314 FEM_ENGINES_PKG.TECH_MESSAGE(
315 p_severity => c_log_level_5,
316 p_module => 'fem.plsql.fem_database_util_pkg.' || lower(p_proc_name) || '.exec',
317 p_msg_text => v_sqlerrm );
318
319 END;
320
321 COMMIT;
322
323 END IF;
324
325 RAISE;
326
327 END exec;
328
329
330 /***************************************************************************
331 ===========================================================================
332 Public Procedures
333 ===========================================================================
334 ***************************************************************************/
335
336
337 /***************************************************************************/
338 PROCEDURE Get_Table_Owner (
339 p_api_version IN NUMBER DEFAULT c_api_version,
340 p_init_msg_list IN VARCHAR2 DEFAULT c_false,
341 p_commit IN VARCHAR2 DEFAULT c_false,
342 p_encoded IN VARCHAR2 DEFAULT c_true,
343 x_return_status OUT NOCOPY VARCHAR2,
344 x_msg_count OUT NOCOPY NUMBER,
345 x_msg_data OUT NOCOPY VARCHAR2,
346 p_syn_name IN VARCHAR2,
347 x_tab_name OUT NOCOPY VARCHAR2,
348 x_tab_owner OUT NOCOPY VARCHAR2 ) IS
349 -- =========================================================================
350 -- Returns the table name and table owner for a specified synonym.
351 -- =========================================================================
352
353 BEGIN
354
355 x_return_status := c_success;
356
357 ---------------------------------
358 -- Get table name and table owner
359 ---------------------------------
360
361 SELECT table_name,table_owner
362 INTO x_tab_name,x_tab_owner
363 FROM user_synonyms
364 WHERE synonym_name = p_syn_name;
365
366 EXCEPTION
367
368 WHEN no_data_found THEN
369 FEM_ENGINES_PKG.Put_Message(
370 p_app_name => 'FEM',
371 p_msg_name => 'FEM_DB_BAD_SYNONYM_ERR',
372 p_token1 => 'SYN_NAME',
373 p_value1 => p_syn_name);
374
375 FND_MSG_PUB.Count_and_Get(
376 p_encoded => p_encoded,
377 p_count => x_msg_count,
378 p_data => x_msg_data);
379
380 x_return_status := c_error;
381
382 END Get_Table_Owner;
383
384
385 /**************************************************************************/
386 PROCEDURE Get_Unique_Temp_Name (
387 p_api_version IN NUMBER DEFAULT c_api_version,
388 p_init_msg_list IN VARCHAR2 DEFAULT c_false,
389 p_commit IN VARCHAR2 DEFAULT c_false,
390 p_encoded IN VARCHAR2 DEFAULT c_true,
391 x_return_status OUT NOCOPY VARCHAR2,
392 x_msg_count OUT NOCOPY NUMBER,
393 x_msg_data OUT NOCOPY VARCHAR2,
394 p_temp_type IN VARCHAR2,
395 p_request_id IN NUMBER,
396 p_object_id IN NUMBER,
397 p_table_seq IN NUMBER DEFAULT NULL,
398 p_index_seq IN NUMBER DEFAULT NULL,
399 x_temp_name OUT NOCOPY VARCHAR2) IS
400 -- ===========================================================================
401 -- DESCRIPTION
402 -- Returns a table name, view name, or index
403 -- name that is guaranteed to be unique within the FEM schema, for
404 -- creating a temporary object. The object name returned is of the form:
405 -- FEM_{p_request_id}_{p_object_id}_[p_table_seq]_{T|V}_[p_index_seq]
406 -- Parameters:
407 -- p_api_version:
408 -- Optional OA-compliance parameter.
409 -- Default is 1.0 (and this is currently the only acceptable value).
410 -- p_init_msg_list
411 -- Optional OA-compliance flag parameter.
412 -- Tells whether or not to initialize the FND_MSG_PUB message stack by
413 -- calling FND_MSG_PUB.Initialize.
414 -- Valid values are 'T' and 'F'; default is 'F'.
415 -- p_commit:
416 -- Optional OA-compliance flag parameter. Valid values are 'T' and 'F'.
417 -- Note that this procedure is read-only, there is nothing to commit,
418 -- so this parameter is not used.
419 -- p_encoded:
420 -- Optional OA-compliance flag parameter.
421 -- Passed to FND_MSG_PUB.Count_and_Get to determine the format of any
422 -- message passed back in x_msg_data.
423 -- Valid values are 'T' and 'F'; default is 'T'.
424 -- x_return_status:
425 -- OA-compliance OUT parameter. This procedure returns 'S' for
426 -- success, 'E' for error in any of the values passed to the optional
427 -- OA parameters.
428 -- x_msg_count:
429 -- OA-compliance OUT parameter. Tells how many messages are waiting
430 -- on the FND_MSG_PUB message stack. If x_msg_count = 1, the message
431 -- has already been fetched from the stack and is found in x_msg_data.
432 -- x_msg_data:
433 -- OA-compliance OUT parameter. If x_msg_count = 1, the message
434 -- has already been fetched from the stack and is found in x_msg_data.
435 -- p_temp_type:
436 -- Required. Specifies the type of temporary database object for which a
437 -- unique name is requested. Valid values are 'TABLE', 'VIEW', 'INDEX'.
438 -- p_request_id:
439 -- The concurrent manager REQUEST_ID of the calling
440 -- process. Required for all values of p_temp_type.
441 -- p_object_id:
442 -- The FEM OBJECT_ID identifying the executable rule
443 -- for which the temporary table, view, or index
444 -- will be created. Required for all values of
445 -- p_temp_type.
446 -- p_table_seq:
447 -- Provides uniqueness for creation of up to 100
448 -- tables per REQUEST_ID/OBJECT_ID combination.
449 -- Required (for all values of p_temp_type) if multiple
450 -- tables or views are being created for the same
451 -- REQUEST_ID/OBJECT_ID combination. Valid values
452 -- are 0-99.
453 -- p_index_seq:
454 -- Provides uniqueness for creation of up to 10
455 -- indexes per table. Required when p_temp_type =
456 -- 'INDEX'. Valid values are 0-9.
457 -- x_temp_name:
458 -- Returns the temporary DB object name built by the procedure.
459 -- HISTORY
460 -- Greg Hall 23-May-2005 Bug# 4301983: created.
461 -- ===========================================================================
462
463 v_request_id VARCHAR2(38);
464 v_object_id VARCHAR2(38);
465 v_table_seq VARCHAR2(38);
466 v_index_seq VARCHAR2(38);
467 v_length NUMBER(2);
468 v_start NUMBER(2);
472
469 v_unique_name VARCHAR2(30);
470
471 BEGIN
473 -- Validate OA parameters
474
475 x_return_status := c_success;
476
477 Validate_OA_Params (
478 p_api_version => p_api_version,
479 p_init_msg_list => p_init_msg_list,
480 p_commit => p_commit,
481 p_encoded => p_encoded,
482 x_return_status => x_return_status);
483
484 IF (x_return_status <> c_success)
485 THEN
486 FND_MSG_PUB.Count_and_Get(
487 p_encoded => c_false,
488 p_count => x_msg_count,
489 p_data => x_msg_data);
490 RETURN;
491 END IF;
492
493 -- Build Request ID string; limit it to the 10 least significant digits
494
495 v_request_id := LTRIM(TO_CHAR(p_request_id), ' -');
496 v_length := LENGTH(v_request_id);
497
498 IF v_length > 11 THEN
499 v_start := v_length - 9;
500 v_request_id := SUBSTR(v_request_id, v_start);
501 END IF;
502
503 v_request_id := v_request_id || '_';
504
505 -- Build Object ID string; limit it to the 8 least significant digits
506
507 v_object_id := LTRIM(TO_CHAR(p_object_id), ' -');
508 v_length := LENGTH(v_object_id);
509
510 IF v_length > 8 THEN
511 v_start := v_length - 7;
512 v_object_id := SUBSTR(v_object_id, v_start);
513 END IF;
514
515 v_object_id := v_object_id || '_';
516
517 -- Build Table Sequence string; limit it to the 2 least significant digits
518
519 IF p_table_seq IS NOT NULL THEN
520
521 v_table_seq := LTRIM(TO_CHAR(p_table_seq), ' -');
522 v_length := LENGTH(v_table_seq);
523
524 IF v_length > 2 THEN
525 v_start := v_length - 1;
526 v_table_seq := SUBSTR(v_table_seq, v_start);
527 END IF;
528
529 v_table_seq := v_table_seq || '_';
530
531 END IF;
532
533 -- Build Index Sequence string; limit it to the 1 least significant digit
534
535 IF p_index_seq IS NOT NULL THEN
536
537 v_index_seq := LTRIM(TO_CHAR(p_index_seq), ' -');
538 v_length := LENGTH(v_index_seq);
539
540 IF v_length > 1 THEN
541 v_start := v_length;
542 v_index_seq := SUBSTR(v_index_seq, v_start);
543 END IF;
544
545 v_index_seq := '_' || v_index_seq;
546
547 END IF;
548
549 -- Build unique name
550
551 v_unique_name := 'FEM_' || v_request_id || v_object_id || v_table_seq;
552
553 IF p_temp_type in ('TABLE', 'INDEX') THEN
554
555 v_unique_name := v_unique_name || 'T';
556
557 IF p_temp_type = 'INDEX' THEN
558 v_unique_name := v_unique_name || v_index_seq;
559 END IF;
560
561 ELSIF p_temp_type = 'VIEW' THEN
562 v_unique_name := v_unique_name || 'V';
563 ELSE
564 NULL; -- Log Invalid p_temp_type debug warning HERE
565 END IF;
566
567 x_temp_name := v_unique_name;
568
569 FND_MSG_PUB.Count_and_Get(
570 p_encoded => p_encoded,
571 p_count => x_msg_count,
572 p_data => x_msg_data);
573
574 END Get_Unique_Temp_Name;
575
576
577 /**************************************************************************/
578 PROCEDURE Create_Temp_Table (
579 p_api_version IN NUMBER DEFAULT c_api_version,
580 p_init_msg_list IN VARCHAR2 DEFAULT c_false,
581 p_commit IN VARCHAR2 DEFAULT c_true,
582 p_encoded IN VARCHAR2 DEFAULT c_true,
583 x_return_status OUT NOCOPY VARCHAR2,
584 x_msg_count OUT NOCOPY NUMBER,
585 x_msg_data OUT NOCOPY VARCHAR2,
586 p_request_id IN NUMBER,
587 p_object_id IN NUMBER,
588 p_pb_object_id IN NUMBER DEFAULT NULL,
589 p_table_name IN VARCHAR2,
590 p_table_def IN VARCHAR2,
591 p_step_name IN VARCHAR2 DEFAULT 'ALL') IS
592 -- ===========================================================================
593 -- DESCRIPTION
594 -- Creates a conventional table in the FEM schema, according to the
595 -- definition passed in p_table_def.
596 -- TABLESPACE, INITIAL_EXTENT, and NEXT_EXTENT are queried by the procedure
597 -- from the process parameters 'TEMP_TABLE_TABLESPACE', 'TEMP_TABLE_INIT_EXTENT'
598 -- and 'TEMP_TABLE_NEXT_EXTENT', as set in the Admin=>Tuning Options UI.
599 -- Tables created by this procedure are expected to be temporary, i.e.
600 -- to be dropped by a call to Drop_Temp_DB_Objects before the end of the
601 -- concurrent process.
602 -- A synonym for the table is created in the APPS schema. The table is
603 -- logged by REQUEST_ID and OBJECT_ID in FEM_PL_TEMP_OBJECTS, so that it
604 -- can be undone by the Undo engine in case of failure. All DDL, including
605 -- synonym creation, is logged in FEM_DDL_LOG. This logging can be toggled
606 -- on/off by the process parameter 'DDL_LOGGING' in Admin=>Tuning Options.
607 -- Parameters:
608 -- p_api_version:
609 -- Optional OA-compliance parameter.
610 -- Default is 1.0 (and this is currently the only acceptable value).
611 -- p_init_msg_list
615 -- Valid values are 'T' and 'F'; default is 'F'.
612 -- Optional OA-compliance flag parameter.
613 -- Tells whether or not to initialize the FND_MSG_PUB message stack by
614 -- calling FND_MSG_PUB.Initialize.
616 -- p_commit:
617 -- Optional OA-compliance flag parameter. Valid values are 'T' and 'F'.
618 -- Note that since DDL operations, such as creating a table, index,
619 -- or view, always do an implicit COMMIT, this parameter cannot be
620 -- honored by this procedure: it will always commit.
621 -- p_encoded:
622 -- Optional OA-compliance flag parameter.
623 -- Passed to FND_MSG_PUB.Count_and_Get to determine the format of any
624 -- message passed back in x_msg_data.
625 -- Valid values are 'T' and 'F'; default is 'T'.
626 -- x_return_status:
627 -- OA-compliance OUT parameter. This procedure returns 'S' for
628 -- success, 'E' for error in any of the values passed to the optional
629 -- OA parameters, and 'U' for unexpected errors, e.g. if the DDL
630 -- statement fails.
631 -- x_msg_count:
632 -- OA-compliance OUT parameter. Tells how many messages are waiting
633 -- on the FND_MSG_PUB message stack. If x_msg_count = 1, the message
634 -- has already been fetched from the stack and is found in x_msg_data.
635 -- x_msg_data:
636 -- OA-compliance OUT parameter. If x_msg_count = 1, the message
637 -- has already been fetched from the stack and is found in x_msg_data.
638 -- p_request_id:
639 -- The concurrent manager REQUEST_ID of the calling process.
640 -- p_object_id:
641 -- The FEM OBJECT_ID identifying the executable rule for which the
642 -- temporary table is being created.
643 -- p_pb_object_id:
644 -- Optional parameter that specifies the FEM OBJECT_ID that this
645 -- procedure will use to determine the Process Behavior parameters.
646 -- If this parameter is NULL, this procedure will rely on
647 -- "p_object_id" parameter to determine the PB params.
648 -- p_table_name:
649 -- The name of the table. Use the Get_Unique_Temp_Name procedure to get
650 -- a unique table name.
651 -- p_table_def:
652 -- This is the columns definition of the table. It is the DDL statement
653 -- for creating the table, minus the CREATE TABLE key words, the table
654 -- name, and the physical properties. It can be specified either in
655 -- column definition format, or in AS SELECT format, as illustrated:
656 -- Column Definiton format example:
657 -- (COL1 NUMBER NOT NULL, COL2 VARCHAR2(30), COL3 DATE)
658 -- AS SELECT format example:
659 -- AS SELECT col1,col2,col3 FROM copy_table WHERE col2='ABC'
660 -- The SELECT statement for the AS SELECT format can be a complex
661 -- statement including joins, subqueries, etc.
662 -- This parameter can be up to 32000 characters long.
663 -- p_step_name:
664 -- Optional parameter for specifying the engine step, used in looking up
665 -- process behavior parameters for DDL Logging, tablespaces, and initial
666 -- and next extents. Default value is 'ALL'.
667 -- HISTORY
668 -- Greg Hall 23-May-2005 Bug# 4301983: created.
669 -- ===========================================================================
670
671 v_pb_object_type VARCHAR2(30);
672 v_pb_object_id FEM_OBJECT_CATALOG_B.object_id%TYPE;
673 v_param_data_type VARCHAR2(6);
674 v_ddl_logging VARCHAR2(3);
675 v_initial_extent VARCHAR2(30);
676 v_next_extent VARCHAR2(30);
677 v_tablespace VARCHAR2(30);
678 v_physical_clause VARCHAR2(300);
679 v_sql_statement VARCHAR2(32767);
680 v_sqlerrm VARCHAR2(255);
681
682 v_fnd_schema VARCHAR2(30); -- the schema name returned by the
683 -- FND_INSTALLATION.Get_App_Info function.
684 -- Also required for the FND_INSTALLATION.Get_App_Info function, but the return
685 -- values are not actually used.
686 v_fnd_status VARCHAR2(100);
687 v_fnd_industry VARCHAR2(100);
688
689 BEGIN
690
691 -- Validate OA parameters
692
693 x_return_status := c_success;
694
695 Validate_OA_Params (
696 p_api_version => p_api_version,
697 p_init_msg_list => p_init_msg_list,
698 p_commit => p_commit,
699 p_encoded => p_encoded,
700 x_return_status => x_return_status);
701
702 IF (x_return_status <> c_success)
703 THEN
704 FND_MSG_PUB.Count_and_Get(
705 p_encoded => c_false,
706 p_count => x_msg_count,
707 p_data => x_msg_data);
708 RETURN;
709 END IF;
710
711 -- Get DDL_LOGGING parameter value
712
713 -- Set Process Behavior object id to "p_object_id" if
714 -- "p_pb_object_id" was not provided.
715 v_pb_object_id := nvl(p_pb_object_id, p_object_id);
716
717 SELECT object_type_code
718 INTO v_pb_object_type
719 FROM fem_object_catalog_b
720 WHERE object_id = v_pb_object_id;
721
722 FEM_ENGINES_PKG.Get_PB_Param_Value
723 (p_api_version => p_api_version,
724 p_init_msg_list => p_init_msg_list,
725 p_commit => p_commit,
726 p_encoded => p_encoded,
727 x_return_status => x_return_status,
731 p_object_type_code => v_pb_object_type,
728 x_msg_count => x_msg_count,
729 x_msg_data => x_msg_data,
730 p_parameter_name => 'DDL_LOGGING',
732 p_step_name => p_step_name,
733 p_object_id => v_pb_object_id,
734 x_pb_param_data_type => v_param_data_type,
735 x_pb_param_value => v_ddl_logging);
736
737 IF v_ddl_logging IS NULL THEN
738 v_ddl_logging := 'OFF';
739 END IF;
740
741 -- Get tablespace, initial extent, and next extent parameter values
742
743 FEM_ENGINES_PKG.Get_PB_Param_Value
744 (p_api_version => p_api_version,
745 p_init_msg_list => p_init_msg_list,
746 p_commit => p_commit,
747 p_encoded => p_encoded,
748 x_return_status => x_return_status,
749 x_msg_count => x_msg_count,
750 x_msg_data => x_msg_data,
751 p_parameter_name => 'TEMP_TABLE_TABLESPACE',
752 p_object_type_code => v_pb_object_type,
753 p_step_name => p_step_name,
754 p_object_id => v_pb_object_id,
755 x_pb_param_data_type => v_param_data_type,
756 x_pb_param_value => v_tablespace);
757
758 IF v_tablespace IS NULL THEN
759 SELECT default_tablespace
760 INTO v_tablespace
761 FROM user_users
762 WHERE username = USER;
763 END IF;
764
765 FEM_ENGINES_PKG.Get_PB_Param_Value
766 (p_api_version => p_api_version,
767 p_init_msg_list => p_init_msg_list,
768 p_commit => p_commit,
769 p_encoded => p_encoded,
770 x_return_status => x_return_status,
771 x_msg_count => x_msg_count,
772 x_msg_data => x_msg_data,
773 p_parameter_name => 'TEMP_TABLE_INIT_EXTENT',
774 p_object_type_code => v_pb_object_type,
775 p_step_name => p_step_name,
776 p_object_id => v_pb_object_id,
777 x_pb_param_data_type => v_param_data_type,
778 x_pb_param_value => v_initial_extent);
779
780 IF v_initial_extent IS NULL THEN
781 SELECT initial_extent
782 INTO v_initial_extent
783 FROM user_tablespaces
784 WHERE tablespace_name = v_tablespace;
785 END IF;
786
787 FEM_ENGINES_PKG.Get_PB_Param_Value
788 (p_api_version => p_api_version,
789 p_init_msg_list => p_init_msg_list,
790 p_commit => p_commit,
791 p_encoded => p_encoded,
792 x_return_status => x_return_status,
793 x_msg_count => x_msg_count,
794 x_msg_data => x_msg_data,
795 p_parameter_name => 'TEMP_TABLE_NEXT_EXTENT',
796 p_object_type_code => v_pb_object_type,
797 p_step_name => p_step_name,
798 p_object_id => v_pb_object_id,
799 x_pb_param_data_type => v_param_data_type,
800 x_pb_param_value => v_next_extent);
801
802 IF v_next_extent IS NULL THEN
803 SELECT next_extent
804 INTO v_next_extent
805 FROM user_tablespaces
806 WHERE tablespace_name = v_tablespace;
807 END IF;
808
809 -- Complete the storage parameters clause later
810 v_physical_clause := ' TABLESPACE ' || v_tablespace ||
811 ' STORAGE ' ||
812 '(INITIAL ' || v_initial_extent ||
813 ' NEXT ' || v_next_extent ||
814 ' MINEXTENTS 1 MAXEXTENTS UNLIMITED' ||
815 ' PCTINCREASE 0)' ||
816 ' INITRANS 10 MAXTRANS 255 PCTFREE 10 ';
817
818 -- Get the schema name for the FEM application
819 IF FND_INSTALLATION.Get_App_Info (
820 APPLICATION_SHORT_NAME => 'FEM',
821 STATUS => v_fnd_status,
822 INDUSTRY => v_fnd_industry,
823 ORACLE_SCHEMA => v_fnd_schema) THEN
824 NULL;
825 ELSE
826 v_fnd_schema := 'FEM';
827 END IF;
828
829 IF INSTR(UPPER(p_table_def), 'AS SELECT') > 0 THEN
830
831 -- p_table_def is specified using the AS SELECT format
832 v_sql_statement := 'CREATE TABLE ' || v_fnd_schema || '.' ||
833 p_table_name ||
834 v_physical_clause || p_table_def;
835 ELSE
836
837 -- p_table_def is specified using the column definition format
838 v_sql_statement := 'CREATE TABLE ' || v_fnd_schema || '.' ||
839 p_table_name || ' ' ||
840 p_table_def || v_physical_clause;
841 END IF;
842
843 BEGIN
844 exec(p_request_id, p_object_id, 'CREATE_TEMP_TABLE', v_sql_statement, v_ddl_logging);
845 EXCEPTION
846 WHEN OTHERS THEN
847 -- Put user error message on the FND_MSG_PUB stack
848 -- "Failed to create temporary table: TABLE_NAME"
849
850 v_sqlerrm := SUBSTR(SQLERRM, 1, 255);
851
852 FEM_ENGINES_PKG.Put_Message(
853 p_app_name => 'FEM',
854 p_msg_name => 'FEM_CREATE_TEMP_TABLE_FAILURE',
855 p_token1 => 'ORACLE_ERROR_MSG',
856 p_value1 => v_sqlerrm,
857 p_token2 => 'TABLE_NAME',
858 p_value2 => p_table_name);
859 RAISE;
860 END;
861
862 -- Log table in FEM_PL_TEMP_OBJECTS
863
864 BEGIN
868 object_type,
865 INSERT INTO fem_pl_temp_objects
866 (request_id,
867 object_id,
869 object_name,
870 created_by,
871 creation_date,
872 last_updated_by,
873 last_update_date,
874 last_update_login)
875 VALUES
876 (p_request_id,
877 p_object_id,
878 'TABLE',
879 p_table_name,
880 c_user_id,
881 SYSDATE,
882 c_user_id,
883 SYSDATE,
884 c_login_id);
885 EXCEPTION
886 WHEN DUP_VAL_ON_INDEX THEN
887 UPDATE fem_pl_temp_objects
888 SET created_by = c_user_id,
889 creation_date = SYSDATE,
890 last_updated_by = c_user_id,
891 last_update_date = SYSDATE,
892 last_update_login = c_login_id
893 WHERE request_id = p_request_id
894 AND object_id = p_object_id
895 AND object_type = 'TABLE'
896 AND object_name = p_table_name;
897 END;
898
899 COMMIT;
900
901 -- Create APPS synonym for the new FEM table.
902
903 v_sql_statement := 'CREATE SYNONYM ' || p_table_name || ' FOR ' ||
904 v_fnd_schema || '.' || p_table_name;
905
906 BEGIN
907 exec(p_request_id, p_object_id, 'CREATE_TEMP_TABLE', v_sql_statement, v_ddl_logging);
908 EXCEPTION
909 WHEN OTHERS THEN
910 -- Put user error message on the FND_MSG_PUB stack
911 -- "Failed to create synonym for temporary table: TABLE_NAME"
912
913 v_sqlerrm := SUBSTR(SQLERRM, 1, 255);
914
915 FEM_ENGINES_PKG.Put_Message(
916 p_app_name => 'FEM',
917 p_msg_name => 'FEM_CREATE_TEMP_SYN_FAILURE',
918 p_token1 => 'ORACLE_ERROR_MSG',
919 p_value1 => v_sqlerrm,
920 p_token2 => 'TABLE_NAME',
921 p_value2 => p_table_name);
922 RAISE;
923 END;
924
925 FND_MSG_PUB.Count_and_Get(
926 p_encoded => p_encoded,
927 p_count => x_msg_count,
928 p_data => x_msg_data);
929
930 EXCEPTION
931 WHEN OTHERS THEN
932 FND_MSG_PUB.Count_and_Get(
933 p_encoded => p_encoded,
934 p_count => x_msg_count,
935 p_data => x_msg_data);
936 x_return_status := c_unexp;
937
938 END Create_Temp_Table;
939
940
941 /**************************************************************************/
942 PROCEDURE Create_Temp_Index (
943 p_api_version IN NUMBER DEFAULT c_api_version,
944 p_init_msg_list IN VARCHAR2 DEFAULT c_false,
945 p_commit IN VARCHAR2 DEFAULT c_true,
946 p_encoded IN VARCHAR2 DEFAULT c_true,
947 x_return_status OUT NOCOPY VARCHAR2,
948 x_msg_count OUT NOCOPY NUMBER,
949 x_msg_data OUT NOCOPY VARCHAR2,
950 p_request_id IN NUMBER,
951 p_object_id IN NUMBER,
952 p_pb_object_id IN NUMBER DEFAULT NULL,
953 p_table_name IN VARCHAR2,
954 p_index_name IN VARCHAR2,
955 p_index_columns IN VARCHAR2,
956 p_unique_flag IN VARCHAR2,
957 p_step_name IN VARCHAR2 DEFAULT 'ALL') IS
958 -- ===========================================================================
959 -- DESCRIPTION
960 -- Creates an index in the FEM schema, on the table specified in
961 -- p_table_name, on the columns specified in p_index_columns.
962 -- Tablespace, INITIAL_EXTENT, and NEXT_EXTENT are queried by the procedure
963 -- from the process parameters 'TEMP_INDEX_TABLESPACE', 'TEMP_INDEX_INIT_EXTENT'
964 -- and 'TEMP_INDEX_NEXT_EXTENT', as set in the Admin=>Tuning Options UI.
965 -- Indexes created by this procedure are expected to be temporary, i.e.
966 -- to be dropped by a call to Drop_Temp_DB_Objects before the end of the
967 -- concurrent process.
968 -- The index is logged by REQUEST_ID and OBJECT_ID in FEM_PL_TEMP_OBJECTS,
969 -- so that it can be undone by the Undo engine in case of failure. All DDL
970 -- is logged in FEM_DDL_LOG. This logging can be toggled on/off by the
971 -- process parameter 'DDL_LOGGING' in Admin=>Tuning Options.
972 -- Parameters:
973 -- p_api_version:
974 -- Optional OA-compliance parameter.
975 -- Default is 1.0 (and this is currently the only acceptable value).
976 -- p_init_msg_list
977 -- Optional OA-compliance flag parameter.
978 -- Tells whether or not to initialize the FND_MSG_PUB message stack by
979 -- calling FND_MSG_PUB.Initialize.
980 -- Valid values are 'T' and 'F'; default is 'F'.
981 -- p_commit:
982 -- Optional OA-compliance flag parameter. Valid values are 'T' and 'F'.
983 -- Note that since DDL operations, such as creating a table, index,
984 -- or view, always do an implicit COMMIT, this parameter cannot be
985 -- honored by this procedure: it will always commit.
986 -- p_encoded:
987 -- Optional OA-compliance flag parameter.
988 -- Passed to FND_MSG_PUB.Count_and_Get to determine the format of any
989 -- message passed back in x_msg_data.
990 -- Valid values are 'T' and 'F'; default is 'T'.
994 -- OA parameters, and 'U' for unexpected errors, e.g. if the DDL
991 -- x_return_status:
992 -- OA-compliance OUT parameter. This procedure returns 'S' for
993 -- success, 'E' for error in any of the values passed to the optional
995 -- statement fails.
996 -- x_msg_count:
997 -- OA-compliance OUT parameter. Tells how many messages are waiting
998 -- on the FND_MSG_PUB message stack. If x_msg_count = 1, the message
999 -- has already been fetched from the stack and is found in x_msg_data.
1000 -- x_msg_data:
1001 -- OA-compliance OUT parameter. If x_msg_count = 1, the message
1002 -- has already been fetched from the stack and is found in x_msg_data.
1003 -- p_request_id:
1004 -- The concurrent manager REQUEST_ID of the calling process.
1005 -- p_object_id:
1006 -- The FEM OBJECT_ID identifying the executable rule for which the
1007 -- temporary index is being created.
1008 -- p_pb_object_id:
1009 -- Optional parameter that specifies the FEM OBJECT_ID that this
1010 -- procedure will use to determine the Process Behavior parameters.
1011 -- If this parameter is NULL, this procedure will rely on
1012 -- "p_object_id" parameter to determine the PB params.
1013 -- p_table_name:
1014 -- The name of the table to create the index on.
1015 -- p_index_name:
1016 -- The name of the index. Use the Get_Unique_Temp_Name procedure to get
1017 -- a unique index name that is based on the name of the table that the
1018 -- index is for.
1019 -- p_index_columns:
1020 -- A comma-separated list of the columns to be indexed.
1021 -- p_unique_flag:
1022 -- 'Y' means create a unique index; 'N' means create a non-unique index.
1023 -- p_step_name:
1024 -- Optional parameter for specifying the engine step, used in looking up
1025 -- process behavior parameters for DDL Logging, tablespaces, and initial
1026 -- and next extents. Default value is 'ALL'.
1027 -- HISTORY
1028 -- Greg Hall 27-May-2005 Bug# 4301983: created.
1029 -- ===========================================================================
1030
1031 v_pb_object_type VARCHAR2(30);
1032 v_pb_object_id FEM_OBJECT_CATALOG_B.object_id%TYPE;
1033 v_param_data_type VARCHAR2(6);
1034 v_ddl_logging VARCHAR2(3);
1035 v_initial_extent VARCHAR2(30);
1036 v_next_extent VARCHAR2(30);
1037 v_tablespace VARCHAR2(30);
1038 v_unique VARCHAR2(7);
1039 v_physical_clause VARCHAR2(300);
1040 v_sql_statement VARCHAR2(32767);
1041 v_sqlerrm VARCHAR2(255);
1042
1043 v_fnd_schema VARCHAR2(30); -- the schema name returned by the
1044 -- FND_INSTALLATION.Get_App_Info function.
1045 -- Also required for the FND_INSTALLATION.Get_App_Info function, but the return
1046 -- values are not actually used.
1047 v_fnd_status VARCHAR2(100);
1048 v_fnd_industry VARCHAR2(100);
1049
1050 BEGIN
1051
1052 -- Validate OA parameters
1053
1054 x_return_status := c_success;
1055
1056 Validate_OA_Params (
1057 p_api_version => p_api_version,
1058 p_init_msg_list => p_init_msg_list,
1059 p_commit => p_commit,
1060 p_encoded => p_encoded,
1061 x_return_status => x_return_status);
1062
1063 IF (x_return_status <> c_success)
1064 THEN
1065 FND_MSG_PUB.Count_and_Get(
1066 p_encoded => c_false,
1067 p_count => x_msg_count,
1068 p_data => x_msg_data);
1069 RETURN;
1070 END IF;
1071
1072 -- Set Process Behavior object id to "p_object_id" if
1073 -- "p_pb_object_id" was not provided.
1074 v_pb_object_id := nvl(p_pb_object_id, p_object_id);
1075
1076 SELECT object_type_code
1077 INTO v_pb_object_type
1078 FROM fem_object_catalog_b
1079 WHERE object_id = v_pb_object_id;
1080
1081 FEM_ENGINES_PKG.Get_PB_Param_Value
1082 (p_api_version => p_api_version,
1083 p_init_msg_list => p_init_msg_list,
1084 p_commit => p_commit,
1085 p_encoded => p_encoded,
1086 x_return_status => x_return_status,
1087 x_msg_count => x_msg_count,
1088 x_msg_data => x_msg_data,
1089 p_parameter_name => 'DDL_LOGGING',
1090 p_object_type_code => v_pb_object_type,
1091 p_step_name => p_step_name,
1092 p_object_id => v_pb_object_id,
1093 x_pb_param_data_type => v_param_data_type,
1094 x_pb_param_value => v_ddl_logging);
1095
1096 IF v_ddl_logging IS NULL THEN
1097 v_ddl_logging := 'OFF';
1098 END IF;
1099
1100 -- Get tablespace, initial extent, and next extent parameter values
1101
1102 FEM_ENGINES_PKG.Get_PB_Param_Value
1103 (p_api_version => p_api_version,
1104 p_init_msg_list => p_init_msg_list,
1105 p_commit => p_commit,
1106 p_encoded => p_encoded,
1107 x_return_status => x_return_status,
1108 x_msg_count => x_msg_count,
1109 x_msg_data => x_msg_data,
1110 p_parameter_name => 'TEMP_INDEX_TABLESPACE',
1111 p_object_type_code => v_pb_object_type,
1112 p_step_name => p_step_name,
1113 p_object_id => v_pb_object_id,
1114 x_pb_param_data_type => v_param_data_type,
1118 SELECT default_tablespace
1115 x_pb_param_value => v_tablespace);
1116
1117 IF v_tablespace IS NULL THEN
1119 INTO v_tablespace
1120 FROM user_users
1121 WHERE username = USER;
1122 END IF;
1123
1124 FEM_ENGINES_PKG.Get_PB_Param_Value
1125 (p_api_version => p_api_version,
1126 p_init_msg_list => p_init_msg_list,
1127 p_commit => p_commit,
1128 p_encoded => p_encoded,
1129 x_return_status => x_return_status,
1130 x_msg_count => x_msg_count,
1131 x_msg_data => x_msg_data,
1132 p_parameter_name => 'TEMP_INDEX_INIT_EXTENT',
1133 p_object_type_code => v_pb_object_type,
1134 p_step_name => p_step_name,
1135 p_object_id => v_pb_object_id,
1136 x_pb_param_data_type => v_param_data_type,
1137 x_pb_param_value => v_initial_extent);
1138
1139 IF v_initial_extent IS NULL THEN
1140 SELECT initial_extent
1141 INTO v_initial_extent
1142 FROM user_tablespaces
1143 WHERE tablespace_name = v_tablespace;
1144 END IF;
1145
1146 FEM_ENGINES_PKG.Get_PB_Param_Value
1147 (p_api_version => p_api_version,
1148 p_init_msg_list => p_init_msg_list,
1149 p_commit => p_commit,
1150 p_encoded => p_encoded,
1151 x_return_status => x_return_status,
1152 x_msg_count => x_msg_count,
1153 x_msg_data => x_msg_data,
1154 p_parameter_name => 'TEMP_INDEX_NEXT_EXTENT',
1155 p_object_type_code => v_pb_object_type,
1156 p_step_name => p_step_name,
1157 p_object_id => v_pb_object_id,
1158 x_pb_param_data_type => v_param_data_type,
1159 x_pb_param_value => v_next_extent);
1160
1161 IF v_next_extent IS NULL THEN
1162 SELECT next_extent
1163 INTO v_next_extent
1164 FROM user_tablespaces
1165 WHERE tablespace_name = v_tablespace;
1166 END IF;
1167
1168 -- Complete the storage parameters clause
1169 v_physical_clause := ' TABLESPACE ' || v_tablespace ||
1170 ' STORAGE ' ||
1171 '(INITIAL ' || v_initial_extent ||
1172 ' NEXT ' || v_next_extent ||
1173 ' MINEXTENTS 1 MAXEXTENTS UNLIMITED' ||
1174 ' PCTINCREASE 0)' ||
1175 ' INITRANS 10 MAXTRANS 255 PCTFREE 10 ';
1176
1177 IF p_unique_flag = 'Y' THEN
1178 v_unique := 'UNIQUE ';
1179 ELSE
1180 v_unique := '';
1181 END IF;
1182
1183 -- Get the schema name for the FEM application
1184 IF FND_INSTALLATION.Get_App_Info (
1185 APPLICATION_SHORT_NAME => 'FEM',
1186 STATUS => v_fnd_status,
1187 INDUSTRY => v_fnd_industry,
1188 ORACLE_SCHEMA => v_fnd_schema) THEN
1189 NULL;
1190 ELSE
1191 v_fnd_schema := 'FEM';
1192 END IF;
1193
1194 v_sql_statement := 'CREATE ' || v_unique || 'INDEX ' || v_fnd_schema || '.' ||
1195 p_index_name || ' ON ' || p_table_name ||
1196 ' (' || p_index_columns || ') ' ||
1197 v_physical_clause;
1198
1199 BEGIN
1200 exec(p_request_id, p_object_id, 'CREATE_TEMP_INDEX', v_sql_statement, v_ddl_logging);
1201 EXCEPTION
1202 WHEN OTHERS THEN
1203 -- Put user error message on the FND_MSG_PUB stack
1204 -- "Failed to create temporary index: INDEX_NAME on table: TABLE_NAME"
1205
1206 v_sqlerrm := SUBSTR(SQLERRM, 1, 255);
1207
1208 FEM_ENGINES_PKG.Put_Message(
1209 p_app_name => 'FEM',
1210 p_msg_name => 'FEM_CREATE_TEMP_INDEX_FAILURE',
1211 p_token1 => 'ORACLE_ERROR_MSG',
1212 p_value1 => v_sqlerrm,
1213 p_token2 => 'INDEX_NAME',
1214 p_value2 => p_index_name,
1215 p_token3 => 'TABLE_NAME',
1216 p_value3 => p_table_name);
1217 RAISE;
1218 END;
1219
1220 -- Log index in FEM_PL_TEMP_OBJECTS
1221
1222 BEGIN
1223 INSERT INTO fem_pl_temp_objects
1224 (request_id,
1225 object_id,
1226 object_type,
1227 object_name,
1228 created_by,
1229 creation_date,
1230 last_updated_by,
1231 last_update_date,
1232 last_update_login)
1233 VALUES
1234 (p_request_id,
1235 p_object_id,
1236 'INDEX',
1237 p_index_name,
1238 c_user_id,
1239 SYSDATE,
1240 c_user_id,
1241 SYSDATE,
1242 c_login_id);
1243 EXCEPTION
1244 WHEN DUP_VAL_ON_INDEX THEN
1245 UPDATE fem_pl_temp_objects
1246 SET created_by = c_user_id,
1247 creation_date = SYSDATE,
1248 last_updated_by = c_user_id,
1249 last_update_date = SYSDATE,
1250 last_update_login = c_login_id
1251 WHERE request_id = p_request_id
1252 AND object_id = p_object_id
1253 AND object_type = 'INDEX'
1254 AND object_name = p_index_name;
1255 END;
1256
1257 COMMIT;
1258
1259 FND_MSG_PUB.Count_and_Get(
1260 p_encoded => p_encoded,
1264 EXCEPTION
1261 p_count => x_msg_count,
1262 p_data => x_msg_data);
1263
1265 WHEN OTHERS THEN
1266 FND_MSG_PUB.Count_and_Get(
1267 p_encoded => p_encoded,
1268 p_count => x_msg_count,
1269 p_data => x_msg_data);
1270 x_return_status := c_unexp;
1271
1272 END Create_Temp_Index;
1273
1274
1275 /**************************************************************************/
1276 PROCEDURE Create_Temp_View (
1277 p_api_version IN NUMBER DEFAULT c_api_version,
1278 p_init_msg_list IN VARCHAR2 DEFAULT c_false,
1279 p_commit IN VARCHAR2 DEFAULT c_true,
1280 p_encoded IN VARCHAR2 DEFAULT c_true,
1281 x_return_status OUT NOCOPY VARCHAR2,
1282 x_msg_count OUT NOCOPY NUMBER,
1283 x_msg_data OUT NOCOPY VARCHAR2,
1284 p_request_id IN NUMBER,
1285 p_object_id IN NUMBER,
1286 p_pb_object_id IN NUMBER DEFAULT NULL,
1287 p_view_name IN VARCHAR2,
1288 p_view_def IN VARCHAR2,
1289 p_step_name IN VARCHAR2 DEFAULT 'ALL') IS
1290 -- ===========================================================================
1291 -- DESCRIPTION
1292 -- Creates a view in the APPS schema, according to the view definition in
1293 -- p_view_def. Views created by this procedure are expected to be temporary,
1294 -- i.e. to be dropped by a call to Drop_Temp_DB_Objects before the end of the
1295 -- concurrent process. The view is logged by REQUEST_ID and OBJECT_ID in
1296 -- FEM_PL_TEMP_OBJECTS, so that it can be undone by the Undo engine in case
1297 -- of failure. All DDL is logged in FEM_DDL_LOG. This logging can be toggled
1298 -- on/off by the process parameter 'DDL_LOGGING' in Admin=>Tuning Options.
1299 -- Parameters:
1300 -- p_api_version:
1301 -- Optional OA-compliance parameter.
1302 -- Default is 1.0 (and this is currently the only acceptable value).
1303 -- p_init_msg_list
1304 -- Optional OA-compliance flag parameter.
1305 -- Tells whether or not to initialize the FND_MSG_PUB message stack by
1306 -- calling FND_MSG_PUB.Initialize.
1307 -- Valid values are 'T' and 'F'; default is 'F'.
1308 -- p_commit:
1309 -- Optional OA-compliance flag parameter. Valid values are 'T' and 'F'.
1310 -- Note that since DDL operations, such as creating a table, index,
1311 -- or view, always do an implicit COMMIT, this parameter cannot be
1312 -- honored by this procedure: it will always commit.
1313 -- p_encoded:
1314 -- Optional OA-compliance flag parameter.
1315 -- Passed to FND_MSG_PUB.Count_and_Get to determine the format of any
1316 -- message passed back in x_msg_data.
1317 -- Valid values are 'T' and 'F'; default is 'T'.
1318 -- x_return_status:
1319 -- OA-compliance OUT parameter. This procedure returns 'S' for
1320 -- success, 'E' for error in any of the values passed to the optional
1321 -- OA parameters, and 'U' for unexpected errors, e.g. if the DDL
1322 -- statement fails.
1323 -- x_msg_count:
1324 -- OA-compliance OUT parameter. Tells how many messages are waiting
1325 -- on the FND_MSG_PUB message stack. If x_msg_count = 1, the message
1326 -- has already been fetched from the stack and is found in x_msg_data.
1327 -- x_msg_data:
1328 -- OA-compliance OUT parameter. If x_msg_count = 1, the message
1329 -- has already been fetched from the stack and is found in x_msg_data.
1330 -- p_request_id:
1331 -- The concurrent manager REQUEST_ID of the calling process.
1332 -- p_object_id:
1333 -- The FEM OBJECT_ID identifying the executable rule for which the
1334 -- temporary view is being created.
1335 -- p_pb_object_id:
1336 -- Optional parameter that specifies the FEM OBJECT_ID that this
1337 -- procedure will use to determine the Process Behavior parameters.
1338 -- If this parameter is NULL, this procedure will rely on
1339 -- "p_object_id" parameter to determine the PB params.
1340 -- p_view_name:
1341 -- The name of the view. Use the Get_Unique_Temp_Name function to get
1342 -- a unique view name.
1343 -- p_view_def:
1344 -- This is the definition of the view. It is the DDL statement
1345 -- for creating the view, without the "CREATE VIEW view_name AS" part,
1346 -- which is prepended by the procedure.
1347 -- This parameter can be up to 32700 characters long.
1348 -- p_step_name:
1349 -- Optional parameter for specifying the engine step, used in looking up
1350 -- process behavior parameters for DDL Logging. Default value is 'ALL'.
1351 -- HISTORY
1352 -- Greg Hall 27-May-2005 Bug# 4301983: created.
1353 -- ===========================================================================
1354
1355 v_pb_object_type VARCHAR2(30);
1356 v_param_data_type VARCHAR2(6);
1357 v_ddl_logging VARCHAR2(3);
1358 v_pb_object_id FEM_OBJECT_CATALOG_B.object_id%TYPE;
1359
1360 v_sql_statement VARCHAR2(32767);
1361 v_sqlerrm VARCHAR2(255);
1362
1363 BEGIN
1364
1365 -- Validate OA parameters
1366
1367 x_return_status := c_success;
1368
1369 Validate_OA_Params (
1370 p_api_version => p_api_version,
1371 p_init_msg_list => p_init_msg_list,
1372 p_commit => p_commit,
1373 p_encoded => p_encoded,
1374 x_return_status => x_return_status);
1375
1376 IF (x_return_status <> c_success)
1377 THEN
1378 FND_MSG_PUB.Count_and_Get(
1379 p_encoded => c_false,
1380 p_count => x_msg_count,
1381 p_data => x_msg_data);
1382 RETURN;
1383 END IF;
1384
1385 -- Set Process Behavior object id to "p_object_id" if
1386 -- "p_pb_object_id" was not provided.
1387 v_pb_object_id := nvl(p_pb_object_id, p_object_id);
1388
1389 SELECT object_type_code
1390 INTO v_pb_object_type
1391 FROM fem_object_catalog_b
1392 WHERE object_id = v_pb_object_id;
1393
1394 FEM_ENGINES_PKG.Get_PB_Param_Value
1395 (p_api_version => p_api_version,
1396 p_init_msg_list => p_init_msg_list,
1397 p_commit => p_commit,
1398 p_encoded => p_encoded,
1399 x_return_status => x_return_status,
1400 x_msg_count => x_msg_count,
1401 x_msg_data => x_msg_data,
1402 p_parameter_name => 'DDL_LOGGING',
1403 p_object_type_code => v_pb_object_type,
1404 p_step_name => p_step_name,
1405 p_object_id => v_pb_object_id,
1406 x_pb_param_data_type => v_param_data_type,
1407 x_pb_param_value => v_ddl_logging);
1408
1409 IF v_ddl_logging IS NULL THEN
1410 v_ddl_logging := 'OFF';
1411 END IF;
1412
1413 -- Create the view
1414
1415 v_sql_statement := 'CREATE OR REPLACE VIEW ' ||
1416 p_view_name || ' AS ' || p_view_def;
1417
1418 BEGIN
1419 exec(p_request_id, p_object_id, 'CREATE_TEMP_VIEW', v_sql_statement, v_ddl_logging);
1420 EXCEPTION
1421 WHEN OTHERS THEN
1422 -- Put user error message on the FND_MSG_PUB stack
1423 -- "Failed to create temporary view: VIEW_NAME"
1424
1425 v_sqlerrm := SUBSTR(SQLERRM, 1, 255);
1426
1427 FEM_ENGINES_PKG.Put_Message(
1428 p_app_name => 'FEM',
1429 p_msg_name => 'FEM_CREATE_TEMP_VIEW_FAILURE',
1430 p_token1 => 'ORACLE_ERROR_MSG',
1431 p_value1 => v_sqlerrm,
1432 p_token2 => 'VIEW_NAME',
1433 p_value2 => p_view_name);
1434 RAISE;
1435 END;
1436
1437 -- Log view in FEM_PL_TEMP_OBJECTS
1438
1439 BEGIN
1440 INSERT INTO fem_pl_temp_objects
1441 (request_id,
1442 object_id,
1443 object_type,
1444 object_name,
1445 created_by,
1446 creation_date,
1447 last_updated_by,
1448 last_update_date,
1449 last_update_login)
1450 VALUES
1451 (p_request_id,
1452 p_object_id,
1453 'VIEW',
1454 p_view_name,
1455 c_user_id,
1456 SYSDATE,
1457 c_user_id,
1458 SYSDATE,
1459 c_login_id);
1460 EXCEPTION
1461 WHEN DUP_VAL_ON_INDEX THEN
1462 UPDATE fem_pl_temp_objects
1463 SET created_by = c_user_id,
1464 creation_date = SYSDATE,
1465 last_updated_by = c_user_id,
1466 last_update_date = SYSDATE,
1467 last_update_login = c_login_id
1468 WHERE request_id = p_request_id
1469 AND object_id = p_object_id
1470 AND object_type = 'VIEW'
1471 AND object_name = p_view_name;
1472 END;
1473
1474 COMMIT;
1475
1476 FND_MSG_PUB.Count_and_Get(
1477 p_encoded => p_encoded,
1478 p_count => x_msg_count,
1479 p_data => x_msg_data);
1480
1481 EXCEPTION
1482 WHEN OTHERS THEN
1483
1484 FND_MSG_PUB.Count_and_Get(
1485 p_encoded => p_encoded,
1486 p_count => x_msg_count,
1487 p_data => x_msg_data);
1488 x_return_status := c_unexp;
1489
1490 END Create_Temp_View;
1491
1492
1496 p_init_msg_list IN VARCHAR2 DEFAULT c_false,
1493 /**************************************************************************/
1494 PROCEDURE Drop_Temp_DB_Objects (
1495 p_api_version IN NUMBER DEFAULT c_api_version,
1497 p_commit IN VARCHAR2 DEFAULT c_true,
1498 p_encoded IN VARCHAR2 DEFAULT c_true,
1499 x_return_status OUT NOCOPY VARCHAR2,
1500 x_msg_count OUT NOCOPY NUMBER,
1501 x_msg_data OUT NOCOPY VARCHAR2,
1502 p_request_id IN NUMBER,
1503 p_object_id IN NUMBER,
1504 p_pb_object_id IN NUMBER DEFAULT NULL,
1505 p_step_name IN VARCHAR2 DEFAULT 'ALL') IS
1506 -- ===========================================================================
1507 -- DESCRIPTION
1508 -- Drops all temporary tables and synonyms, views, and indexes that are
1509 -- logged in FEM_PL_TEMP_OBJECTS for the given Request ID and Object ID.
1510 -- Parameters:
1511 -- p_api_version:
1512 -- Optional OA-compliance parameter.
1513 -- Default is 1.0 (and this is currently the only acceptable value).
1514 -- p_init_msg_list
1515 -- Optional OA-compliance flag parameter.
1516 -- Tells whether or not to initialize the FND_MSG_PUB message stack by
1517 -- calling FND_MSG_PUB.Initialize.
1518 -- Valid values are 'T' and 'F'; default is 'F'.
1519 -- p_commit:
1520 -- Optional OA-compliance flag parameter. Valid values are 'T' and 'F'.
1521 -- Note that since DDL operations, such as dropping a table, index,
1522 -- or view, always do an implicit COMMIT, this parameter cannot be
1523 -- honored by this procedure: it will always commit.
1524 -- p_encoded:
1525 -- Optional OA-compliance flag parameter.
1526 -- Passed to FND_MSG_PUB.Count_and_Get to determine the format of any
1527 -- message passed back in x_msg_data.
1528 -- Valid values are 'T' and 'F'; default is 'T'.
1529 -- x_return_status:
1530 -- OA-compliance OUT parameter.
1531 -- For this procedure, success means that all temp objects logged in
1532 -- FEM_PL_TEMP_OBJECTS for the given Request ID and Object ID have
1533 -- been dropped or could not be dropped because they already did not
1534 -- exist, and x_return_status = 'S'. If an object exists, but cannot
1535 -- be dropped for some reason, his procedure returns 'E' for error in
1536 -- x_return_status, but it continues to try to drop all other temp DB
1537 -- objects for the given Request ID and Object ID. 'U' is returned for
1538 -- any other type of unexpected error.
1539 -- x_msg_count:
1540 -- OA-compliance OUT parameter. Tells how many messages are waiting
1541 -- on the FND_MSG_PUB message stack. If x_msg_count = 1, the message
1542 -- has already been fetched from the stack and is found in x_msg_data.
1543 -- x_msg_data:
1544 -- OA-compliance OUT parameter. If x_msg_count = 1, the message
1545 -- has already been fetched from the stack and is found in x_msg_data.
1546 -- p_request_id:
1547 -- The concurrent manager REQUEST_ID for which the temporary database
1548 -- objects to be dropped were created.
1549 -- p_object_id:
1550 -- The FEM OBJECT_ID identifying the executable rule for which the
1551 -- temporary database objects to be dropped were created.
1552 -- p_pb_object_id:
1553 -- Optional parameter that specifies the FEM OBJECT_ID that this
1554 -- procedure will use to determine the Process Behavior parameters.
1555 -- If this parameter is NULL, this procedure will rely on
1556 -- "p_object_id" parameter to determine the PB params.
1557 -- p_step_name:
1558 -- Optional parameter for specifying the engine step, used in looking up
1559 -- process behavior parameter for DDL Logging. Default value is 'ALL'.
1560 -- HISTORY
1561 -- Greg Hall 27-May-2005 Bug# 4301983: created.
1562 -- ===========================================================================
1563
1564 v_pb_object_type VARCHAR2(30);
1565 v_param_data_type VARCHAR2(6);
1566 v_ddl_logging VARCHAR2(3);
1567 v_pb_object_id FEM_OBJECT_CATALOG_B.object_id%TYPE;
1568
1569 v_sql_statement VARCHAR2(100);
1570 v_sqlerrm VARCHAR2(255);
1571
1572 v_fnd_schema VARCHAR2(30); -- the schema name returned by the
1573 -- FND_INSTALLATION.Get_App_Info function.
1574 -- Also required for the FND_INSTALLATION.Get_App_Info function, but the return
1575 -- values are not actually used.
1576 v_fnd_status VARCHAR2(100);
1577 v_fnd_industry VARCHAR2(100);
1578
1579 CURSOR c1(cp_obj_type IN VARCHAR2) IS
1580 SELECT object_type, object_name
1581 FROM fem_pl_temp_objects
1582 WHERE request_id = p_request_id
1583 AND object_id = p_object_id
1584 AND object_type = cp_obj_type
1585 ORDER BY object_name;
1586
1587 table_or_view_not_exist EXCEPTION;
1588 PRAGMA EXCEPTION_INIT(table_or_view_not_exist, -942);
1589
1590 index_not_exist EXCEPTION;
1591 PRAGMA EXCEPTION_INIT(index_not_exist, -1418);
1592
1593 synonym_not_exist EXCEPTION;
1594 PRAGMA EXCEPTION_INIT(synonym_not_exist, -1434);
1595
1596 BEGIN
1597
1598 -- Validate OA parameters
1599
1600 x_return_status := c_success;
1601
1602 Validate_OA_Params (
1603 p_api_version => p_api_version,
1604 p_init_msg_list => p_init_msg_list,
1605 p_commit => p_commit,
1606 p_encoded => p_encoded,
1607 x_return_status => x_return_status);
1608
1609 IF (x_return_status <> c_success)
1610 THEN
1611 FND_MSG_PUB.Count_and_Get(
1612 p_encoded => c_false,
1613 p_count => x_msg_count,
1614 p_data => x_msg_data);
1615 RETURN;
1616 END IF;
1617
1618 -- Set Process Behavior object id to "p_object_id" if
1619 -- "p_pb_object_id" was not provided.
1620 v_pb_object_id := nvl(p_pb_object_id, p_object_id);
1621
1622 SELECT object_type_code
1623 INTO v_pb_object_type
1624 FROM fem_object_catalog_b
1625 WHERE object_id = v_pb_object_id;
1626
1627 FEM_ENGINES_PKG.Get_PB_Param_Value
1628 (p_api_version => p_api_version,
1629 p_init_msg_list => p_init_msg_list,
1630 p_commit => p_commit,
1631 p_encoded => p_encoded,
1632 x_return_status => x_return_status,
1633 x_msg_count => x_msg_count,
1634 x_msg_data => x_msg_data,
1635 p_parameter_name => 'DDL_LOGGING',
1636 p_object_type_code => v_pb_object_type,
1637 p_step_name => p_step_name,
1638 p_object_id => v_pb_object_id,
1639 x_pb_param_data_type => v_param_data_type,
1640 x_pb_param_value => v_ddl_logging);
1641
1642 IF v_ddl_logging IS NULL THEN
1643 v_ddl_logging := 'OFF';
1644 END IF;
1645
1646 -- Get the schema name for the FEM application
1647 IF FND_INSTALLATION.Get_App_Info (
1648 APPLICATION_SHORT_NAME => 'FEM',
1649 STATUS => v_fnd_status,
1650 INDUSTRY => v_fnd_industry,
1651 ORACLE_SCHEMA => v_fnd_schema) THEN
1652 NULL;
1653 ELSE
1654 v_fnd_schema := 'FEM';
1655 END IF;
1656
1657 -- Drop views
1658
1659 FOR obj IN c1('VIEW') LOOP
1660
1661 BEGIN
1662
1663 v_sql_statement := 'DROP VIEW ' || obj.object_name;
1664
1665 exec(p_request_id, p_object_id, 'DROP_TEMP_DB_OBJECTS', v_sql_statement, v_ddl_logging);
1666
1667 EXCEPTION
1668
1669 WHEN table_or_view_not_exist THEN
1670 NULL;
1671 WHEN OTHERS THEN
1672 -- Put user error message on the FND_MSG_PUB stack
1673 -- "Failed to drop temporary database object. Object Type: VIEW. Object Name: DB_OBJECT_NAME"
1674
1675 v_sqlerrm := SUBSTR(SQLERRM, 1, 255);
1676
1677 FEM_ENGINES_PKG.Put_Message(
1678 p_app_name => 'FEM',
1679 p_msg_name => 'FEM_DROP_TEMP_DB_OBJ_FAILURE',
1680 p_token1 => 'ORACLE_ERROR_MSG',
1681 p_value1 => v_sqlerrm,
1682 p_token2 => 'DB_OBJECT_TYPE',
1683 p_value2 => 'VIEW',
1684 p_token3 => 'DB_OBJECT_NAME',
1685 p_value3 => obj.object_name);
1686 x_return_status := c_error;
1687 END;
1688
1689 END LOOP;
1690
1691 DELETE FROM fem_pl_temp_objects tobj
1692 WHERE request_id = p_request_id
1693 AND object_id = p_object_id
1694 AND object_type = 'VIEW'
1695 AND NOT EXISTS
1696 (SELECT NULL
1697 FROM user_views
1698 WHERE view_name = tobj.object_name);
1699
1700 COMMIT;
1701
1702 -- Drop indexes
1703
1704 FOR obj IN c1('INDEX') LOOP
1705
1706 BEGIN
1707
1708 v_sql_statement := 'DROP INDEX ' || v_fnd_schema || '.' || obj.object_name;
1709
1710 exec(p_request_id, p_object_id, 'DROP_TEMP_DB_OBJECTS', v_sql_statement, v_ddl_logging);
1711
1712 EXCEPTION
1713
1714 WHEN index_not_exist THEN
1715 NULL;
1716 WHEN OTHERS THEN
1717 -- Put user error message on the FND_MSG_PUB stack
1718 -- "Failed to drop temporary database object: OBJECT_NAME. Object Type: INDEX"
1719
1720 v_sqlerrm := SUBSTR(SQLERRM, 1, 255);
1721
1722 FEM_ENGINES_PKG.Put_Message(
1723 p_app_name => 'FEM',
1724 p_msg_name => 'FEM_DROP_TEMP_DB_OBJ_FAILURE',
1725 p_token1 => 'ORACLE_ERROR_MSG',
1726 p_value1 => v_sqlerrm,
1727 p_token2 => 'DB_OBJECT_NAME',
1728 p_value2 => obj.object_name,
1729 p_token3 => 'DB_OBJECT_TYPE',
1730 p_value3 => 'INDEX');
1731 x_return_status := c_error;
1732 END;
1733
1734 END LOOP;
1735
1736 DELETE FROM fem_pl_temp_objects tobj
1737 WHERE request_id = p_request_id
1738 AND object_id = p_object_id
1739 AND object_type = 'INDEX'
1740 AND NOT EXISTS
1741 (SELECT NULL
1742 FROM user_indexes
1743 WHERE index_name = tobj.object_name);
1744
1745 COMMIT;
1746
1747 -- Drop tables
1748
1749 FOR obj IN c1('TABLE') LOOP
1750
1751 BEGIN
1752
1753 v_sql_statement := 'DROP SYNONYM ' || obj.object_name;
1754
1755 exec(p_request_id, p_object_id, 'DROP_TEMP_DB_OBJECTS', v_sql_statement, v_ddl_logging);
1756
1757 EXCEPTION
1758
1759 WHEN synonym_not_exist THEN
1760 NULL;
1761 WHEN OTHERS THEN
1762 -- Put user error message on the FND_MSG_PUB stack
1763 -- "Failed to drop temporary database object. Object Type: SYNONYM. Object Name: OBJECT_NAME"
1764
1765 v_sqlerrm := SUBSTR(SQLERRM, 1, 255);
1766
1767 FEM_ENGINES_PKG.Put_Message(
1768 p_app_name => 'FEM',
1769 p_msg_name => 'FEM_DROP_TEMP_DB_OBJ_FAILURE',
1770 p_token1 => 'ORACLE_ERROR_MSG',
1771 p_value1 => v_sqlerrm,
1772 p_token2 => 'DB_OBJECT_TYPE',
1773 p_value2 => 'SYNONYM',
1774 p_token3 => 'DB_OBJECT_NAME',
1775 p_value3 => obj.object_name);
1776 x_return_status := c_error;
1777 END;
1778
1779 BEGIN
1780
1781 v_sql_statement := 'DROP TABLE ' || v_fnd_schema || '.' || obj.object_name;
1782
1783 exec(p_request_id, p_object_id, 'DROP_TEMP_DB_OBJECTS', v_sql_statement, v_ddl_logging);
1784
1785 EXCEPTION
1786
1787 WHEN table_or_view_not_exist THEN
1788 NULL;
1789 WHEN OTHERS THEN
1790 -- Put user error message on the FND_MSG_PUB stack
1791 -- "Failed to drop temporary database object. Object Type: TABLE. Object Name: OBJECT_NAME"
1792
1793 v_sqlerrm := SUBSTR(SQLERRM, 1, 255);
1794
1795 FEM_ENGINES_PKG.Put_Message(
1796 p_app_name => 'FEM',
1797 p_msg_name => 'FEM_DROP_TEMP_DB_OBJ_FAILURE',
1798 p_token1 => 'ORACLE_ERROR_MSG',
1799 p_value1 => v_sqlerrm,
1800 p_token2 => 'DB_OBJECT_TYPE',
1801 p_value2 => 'TABLE',
1802 p_token3 => 'DB_OBJECT_NAME',
1803 p_value3 => obj.object_name);
1804 x_return_status := c_error;
1805 END;
1806
1807 END LOOP;
1808
1809 DELETE FROM fem_pl_temp_objects tobj
1810 WHERE request_id = p_request_id
1811 AND object_id = p_object_id
1812 AND object_type = 'TABLE'
1813 AND NOT EXISTS
1814 (SELECT NULL
1815 FROM user_tables
1816 WHERE table_name = tobj.object_name);
1817
1818 COMMIT;
1819
1820 FND_MSG_PUB.Count_and_Get(
1821 p_encoded => p_encoded,
1822 p_count => x_msg_count,
1823 p_data => x_msg_data);
1824
1825 EXCEPTION
1826 WHEN OTHERS THEN
1827 FEM_ENGINES_PKG.TECH_MESSAGE(
1828 p_severity => c_log_level_6,
1829 p_module => 'fem.plsql.fem_database_util_pkg.drop_temp_db_objects',
1830 p_msg_text => SUBSTR(SQLERRM, 1, 255) );
1831
1832 FND_MSG_PUB.Count_and_Get(
1833 p_encoded => p_encoded,
1834 p_count => x_msg_count,
1835 p_data => x_msg_data);
1836 x_return_status := c_unexp;
1837
1838 END Drop_Temp_DB_Objects;
1839
1840
1841 END FEM_Database_Util_Pkg;
[Home] [Help]