1 package body FND_FILE as
2 /* $Header: AFCPPIOB.pls 120.8 2011/08/01 20:56:25 ckclark ship $ */
3
4
5 F_LOG utl_file.file_type;
6 F_OUT utl_file.file_type;
7
8 LOG_FNAME varchar2(255) := null;
9 OUT_FNAME varchar2(255) := null;
10 TEMP_DIR varchar2(255) := null;
11
12 LOG_OPEN boolean := FALSE;
13 OUT_OPEN boolean := FALSE;
14
15 /* bug8661315 */
16 /* implements utl_file.put_raw into fnd_file */
17 /* in PUT_RAW mode, use of termination characters */
18 /* every 32K is not required */
19
20 UTL_FILE_MODE varchar2(12) := 'TRADITIONAL';
21 RAW_TERM raw(3);
22
23 UTL_FILE_DELAY number := 0;
24
25 /* -------------------------------------------------------------------------
26 PRIVATE PROCEDURES
27 -------------------------------------------------------------------------
28 */
29
30 function get_exception (errcode in number,
31 errmsg in varchar2,
32 func in varchar2,
33 temp_file in varchar2) return varchar2 is
34
35 begin
36
37 case errcode
38 when UTL_FILE.INVALID_PATH_ERRCODE then
39 fnd_message.set_name('FND', 'CONC-TEMPFILE_INVALID_PATH');
40 fnd_message.set_module('fnd.plsql.fnd_file.' || func || '.invalid_path');
41
42 when UTL_FILE.INVALID_MODE_ERRCODE then
43 fnd_message.set_name('FND', 'CONC-TEMPFILE_INVALID_MODE');
44 fnd_message.set_token('FILE_MODE', 'w', FALSE);
45 fnd_message.set_module('fnd.plsql.fnd_file.' || func || '.invalid_mode');
46
47 when UTL_FILE.INVALID_OPERATION_ERRCODE then
48 fnd_message.set_name('FND', 'CONC-TEMPFILE_INVALID_OPERATN');
49 fnd_message.set_module('fnd.plsql.fnd_file.' || func || '.invalid_operation');
50
51 when UTL_FILE.INVALID_MAXLINESIZE_ERRCODE then
52 fnd_message.set_name('FND', 'CONC-TEMPFILE_INVALID_MAXLINE');
53 fnd_message.set_module('fnd.plsql.fnd_file.' || func || '.invalid_maxline');
54
55 when UTL_FILE.INVALID_FILEHANDLE_ERRCODE then
56 fnd_message.set_name('FND', 'CONC-TEMPFILE_INVALID_HANDLE');
57 fnd_message.set_module('fnd.plsql.fnd_file.' || func || '.invalid_handle');
58
59 when UTL_FILE.WRITE_ERROR_ERRCODE then
60 fnd_message.set_name('FND', 'CONC-TEMPFILE_WRITE_ERROR');
61 fnd_message.set_module('fnd.plsql.fnd_file.' || func || '.write_error');
62
63 when UTL_FILE.READ_ERROR_ERRCODE then
64 fnd_message.set_name('FND', 'CONC-TEMPFILE_READ_ERROR');
65 fnd_message.set_module('fnd.plsql.fnd_file.' || func || '.read_error');
66
67 when UTL_FILE.ACCESS_DENIED_ERRCODE then
68 fnd_message.set_name('FND', 'CONC-TEMPFILE_ACCESS_DENIED');
69 fnd_message.set_module('fnd.plsql.fnd_file.' || func || '.access_denied');
70
71 end case;
72
73
74 fnd_message.set_token('TEMP_DIR', TEMP_DIR, FALSE);
75 fnd_message.set_token('TEMP_FILE', temp_file, FALSE);
76
77 return substrb(fnd_message.get, 1, 255);
78
79 exception
80 when case_not_found then
81 return errmsg;
82
83
84 end get_exception;
85
86 /*
87 ** log_simple_msg - Logs a message to FND_LOG_MESSAGES based on current runtime level
88 **
89 */
90 procedure log_simple_msg (level in number, fn in varchar2, msg in varchar2) is
91
92 BEGIN
93
94 if (level >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
95 FND_LOG.STRING(level,'fnd.plsql.fnd_file.'||fn, 'sid:'||userenv('SESSIONID')||': '||msg);
96 end if;
97
98 end;
99
100 function GET_BASE return varchar2 is
101 base_orig varchar2(12);
102 BASE varchar2(12);
103 PRAGMA AUTONOMOUS_TRANSACTION;
104 begin
105 /* Bug 2446909: Delete corrupted filenames from fnd_temp_files */
106 select filename, lpad(filename, 7, '0')
107 into base_orig, BASE
108
109 from fnd_temp_files
110 where type = 'P' and rownum = 1
111 for update of filename;
112
113 delete from fnd_temp_files
114 where filename = base_orig;
115
116 delete from fnd_temp_files
117 where filename = BASE;
118
119 commit;
120
121 return BASE;
122
123 exception
124 when OTHERS then
125 if (Sql%NotFound) then
126 select lpad(to_char(fnd_temp_files_s.nextval), 7, '0')
127 into BASE
128 from sys.dual;
129 end if;
130
131 commit;
132 return BASE;
133
134 end GET_BASE;
135
136
137
138 procedure OPEN_FILES is
139 MAX_LINESIZE binary_integer := 32767;
140 temp_file varchar2(255); -- used for messages
141 user_error varchar2(255); -- to store translated file_error
142
143 function openit(fname in varchar2) return utl_file.file_type is
144 log_f utl_file.file_type;
145 begin
146 -- Open and close file to use the workaround for bug
147 log_f := utl_file.fopen(TEMP_DIR, fname, 'w');
148
149 BEGIN
150 utl_file.fclose(log_f);
151 EXCEPTION
152 when others then
153 NULL;
154 END;
155
156 if (UTL_FILE_MODE = 'TRADITIONAL') then
157 log_f := utl_file.fopen(TEMP_DIR, fname, 'w', MAX_LINESIZE);
158 else
159 log_f := utl_file.fopen(TEMP_DIR, fname, 'wb', MAX_LINESIZE);
160 end if;
161
162 return log_f;
163 end;
164
165 begin
166
167 if OUT_OPEN = FALSE then
168 temp_file := OUT_FNAME;
169 log_simple_msg(fnd_log.level_statement, 'open_files', 'open OUT_FNAME ='|| OUT_FNAME);
170 F_OUT := openit(OUT_FNAME);
171 OUT_OPEN := TRUE;
172 end if;
173
174 if LOG_OPEN = FALSE then
175 temp_file := LOG_FNAME;
176 log_simple_msg(fnd_log.level_statement, 'open_files', 'open LOG_FNAME ='|| LOG_FNAME);
177 F_LOG := openit(LOG_FNAME);
178 LOG_OPEN := TRUE;
179 end if;
180
181 exception
182 when others then
183 raise_application_error(-20100,
184 get_exception(SQLCODE, SQLERRM, 'open_files', temp_file));
185
186 end OPEN_FILES;
187
188
189
190
191 function PUT_NAMES_OS return boolean is
192 BASE varchar2(12);
193 TEMP_UTL varchar2(512);
194
195 begin
196
197 if LOG_FNAME is null AND
198 OUT_FNAME is null AND
199 TEMP_DIR is null then
200 -- use first entry of utl_file_dir as the TEMP_DIR
201 -- if there is no entry then do not even construct file names
202 select translate(ltrim(value),',',' ')
203 into TEMP_UTL
204 from v$parameter
205 where name = 'utl_file_dir';
206
207 if (instr(TEMP_UTL,' ') > 0 and TEMP_UTL is not null) then
208 select substrb(TEMP_UTL, 1, instr(TEMP_UTL,' ') - 1)
209 into TEMP_DIR
210 from dual ;
211 elsif (TEMP_UTL is not null) then
212 TEMP_DIR := TEMP_UTL;
213 end if;
214
215 if (TEMP_UTL is null or TEMP_DIR is null ) then
216 raise no_data_found;
217 end if;
218
219 -- We are now safe to call GET_BASE after making that as AUTONOMOUS
220 -- transaction.
221 -- Get the next sequence # from the db or reuse old one.
222
223 BASE := GET_BASE;
224
225 LOG_FNAME := 'l' || BASE || '.tmp';
226 OUT_FNAME := 'o' || BASE || '.tmp';
227
228 -- call fnd_file_private.put_names with these values
229 fnd_file_private.put_names(LOG_FNAME, OUT_FNAME, TEMP_DIR );
230
231 end if;
232
233 -- CAUTION: for NT, we can ignore this area for NT
234 -- log the temp file info in fnd_temp_files table
235
236 if UTL_FILE_DELAY > 0 then
237 if IS_OPEN('LOG') = 0 then
238 TEMP_UTL := TEMP_DIR || '/' || LOG_FNAME;
239 if ( fnd_conc_private_utils.check_temp_file_use(temp_utl,NULL,'F') <> 1) then
240 log_simple_msg (fnd_log.level_statement, 'put_names_os',
241 'call record_temp_file_use for: '|| TEMP_UTL);
242 fnd_conc_private_utils.record_temp_file_use(TEMP_UTL, NULL, 'F', NULL);
243 end if;
244 end if;
245
246 if IS_OPEN('OUT') = 0 then
247 TEMP_UTL := TEMP_DIR || '/' || OUT_FNAME;
248 if (fnd_conc_private_utils.check_temp_file_use(temp_utl,NULL,'F') <> 1) then
249 log_simple_msg (fnd_log.level_statement, 'put_names_os',
250 'call record_temp_file_use for: '|| TEMP_UTL);
251 fnd_conc_private_utils.record_temp_file_use(TEMP_UTL, NULL, 'F', NULL);
252 end if;
253 end if;
254 end if;
255
256 return TRUE;
257
258 exception
259 when no_data_found then
260 return FALSE;
261 when others then
262 return FALSE;
263 end PUT_NAMES_OS;
264
265
266
267 procedure WRITE_BUFF(WHICH in number, WTYPE in varchar2, BUFF in varchar2) is
268 temp_file varchar2(255); -- used for messages
269 log_f utl_file.file_type;
270 begin
271
272 -- if PUT_NAMES_OS is successfull then call OPEN_FILES
273 -- and write the buffer into temp file
274 if ( PUT_NAMES_OS ) then
275 OPEN_FILES;
276
277 if WHICH = FND_FILE.LOG then
278 temp_file := LOG_FNAME;
279 log_f := F_LOG;
280 else
281 temp_file := OUT_FNAME;
282 log_f := F_OUT;
283 end if;
284
285 if (UTL_FILE_MODE = 'TRADITIONAL') then
286
287 if ( WTYPE = 'PUT' ) then
288 utl_file.put(log_f, BUFF);
289 elsif (WTYPE = 'PUT_LINE' ) then
290 utl_file.put_line(log_f, BUFF);
291 elsif (WTYPE = 'NEW_LINE') then
292 utl_file.new_line(log_f, to_number(BUFF) );
293 end if;
294
295 else
296 if ( WTYPE = 'PUT' ) then
297 utl_file.put_raw(log_f, utl_raw.cast_to_raw(BUFF));
298 elsif (WTYPE = 'PUT_LINE' ) then
299 utl_file.put_raw(log_f, utl_raw.cast_to_raw(BUFF));
300 utl_file.put_raw(log_f, RAW_TERM);
301 elsif (WTYPE = 'NEW_LINE') then
302 for i in 1 .. to_number(BUFF) loop
303 utl_file.put_raw(log_f, RAW_TERM);
304 end loop;
305 end if;
306
307 end if;
308
309 utl_file.fflush(log_f);
310
311 if WHICH = FND_FILE.LOG then
312 log_simple_msg(fnd_log.level_event, 'write_buff.' || WTYPE, BUFF);
313 end if;
314
315 end if;
316
317 exception
318 when others then
319 raise_application_error(-20100,
320 get_exception(SQLCODE, SQLERRM, 'write_buff', temp_file));
321
322 end WRITE_BUFF;
323
324
325 procedure PUT_INTERNAL(WHICH in number,
326 BUFF in varchar2,
327 FUNC in varchar2) is
328
329 temp_file varchar2(255); -- used for messages
330 log_f utl_file.file_type;
331 begin
332
333 if WHICH = FND_FILE.LOG then
334 temp_file := LOG_FNAME;
335 log_f := F_LOG;
336 else
337 temp_file := OUT_FNAME;
338 log_f := F_OUT;
339 end if;
340
341 if (UTL_FILE_MODE = 'TRADITIONAL') then
342 if FUNC = 'PUT_LINE' then
343 utl_file.put_line(log_f, BUFF);
344 else
345 utl_file.put(log_f, BUFF);
346 end if;
347 else
348 utl_file.put_raw(log_f, utl_raw.cast_to_raw(BUFF));
349 if FUNC = 'PUT_LINE' then
350 utl_file.put_raw(log_f, RAW_TERM);
351 end if;
352 end if;
353
354 utl_file.fflush(log_f);
355
356 if which = FND_FILE.LOG then
357 log_simple_msg(fnd_log.level_event, FUNC, BUFF);
358 end if;
359
360 exception
361 when UTL_FILE.INVALID_FILEHANDLE then
362 -- first time this could be file not open case
366 when others then
363 -- try opening temp files and write
364 WRITE_BUFF(WHICH, FUNC, BUFF);
365
367 raise_application_error(-20100,
368 get_exception(SQLCODE, SQLERRM, FUNC, temp_file));
369
370 end PUT_INTERNAL;
371
372
373
374
375 /* -------------------------------------------------------------------------
376 PUBLIC PROCEDURES
377 -------------------------------------------------------------------------
378 */
379
380 /*
381 ** PUT - Put (write) text to file
382 **
383 ** IN
384 ** WHICH - Log or output file? Either FND_FILE.LOG or FND_FILE.OUTPUT
385 ** BUFF - Text to write
386 ** EXCEPTIONS
387 ** utl_file.invalid_path (*) - file location or name was invalid
388 ** utl_file.invalid_mode (*) - the open_mode string was invalid
389 ** utl_file.invalid_filehandle - file handle is invalid
390 ** utl_file.invalid_operation - file is not open for writing/appending
391 ** utl_file.write_error - OS error occured during write operation
392 */
393 procedure PUT(WHICH in number, BUFF in varchar2) is
394 temp_file varchar2(255); -- used for messages
395 log_f utl_file.file_type;
396 begin
397
398 PUT_INTERNAL(WHICH, BUFF, 'PUT');
399
400 end PUT;
401
402
403 /*
404 ** PUT_LINE - Put (write) a line of text to file
405 **
406 ** IN
407 ** WHICH - Log or output file? Either FND_FILE.LOG or FND_FILE.OUTPUT
408 ** BUFF - Text to write
409 ** EXCEPTIONS
410 ** utl_file.invalid_path - file location or name was invalid
411 ** utl_file.invalid_mode - the open_mode string was invalid
412 ** utl_file.invalid_filehandle - file handle is invalid
413 ** utl_file.invalid_operation - file is not open for writing/appending
414 ** utl_file.write_error - OS error occured during write operation
415 */
416 procedure PUT_LINE(WHICH in number, BUFF in varchar2) is
417 temp_file varchar2(255); -- used for messages
418 log_f utl_file.file_type;
419 begin
420
421 PUT_INTERNAL(WHICH, BUFF, 'PUT_LINE');
422
423 end PUT_LINE;
424
425
426 /*
427 ** NEW_LINE - Put (write) line terminators to file
428 **
429 ** IN
430 ** WHICH - Log or output file? Either FND_FILE.LOG or FND_FILE.OUTPUT
431 ** LINES - Number of line terminators to write
432 ** EXCEPTIONS
433 ** utl_file.invalid_path - file location or name was invalid
434 ** utl_file.invalid_mode - the open_mode string was invalid
435 ** utl_file.invalid_filehandle - file handle is invalid
436 ** utl_file.invalid_operation - file is not open for writing/appending
437 ** utl_file.write_error - OS error occured during write operation
438 */
439 procedure NEW_LINE(WHICH in number, LINES in natural := 1) is
440 temp_file varchar2(255); -- used for messages
441 log_f utl_file.file_type;
442 begin
443
444 if WHICH = FND_FILE.LOG then
445 temp_file := LOG_FNAME;
446 log_f := F_LOG;
447 else
448 temp_file := OUT_FNAME;
449 log_f := F_OUT;
450 end if;
451
452
453 if (UTL_FILE_MODE = 'TRADITIONAL') then
454 utl_file.new_line(log_f, LINES);
455 else
456
457 for i in 1 .. LINES loop
458 utl_file.put_raw(log_f, RAW_TERM);
459 end loop;
460
461 end if;
462
463 utl_file.fflush(log_f);
464
465 exception
466 when UTL_FILE.INVALID_FILEHANDLE then
467 -- first time this could be file not open case
468 -- try opening temp files and write
469 WRITE_BUFF(WHICH, 'NEW_LINE', to_char(LINES));
470
471 when others then
472 raise_application_error(-20100,
473 get_exception(SQLCODE, SQLERRM, 'new_line', temp_file));
474 end NEW_LINE;
475
476
477 /*
478 ** PUT_NAMES - Set the temp file names and directories
479 ** Has no effect when called from a concurrent program.
480 ** IN
481 ** P_LOG - Temporary logfile name
482 ** P_OUT - Temporary outfile name
483 ** P_DIR - Temporary directory name
484 **
485 */
486 procedure PUT_NAMES(P_LOG in varchar2, P_OUT in varchar2, P_DIR in varchar2)
487 is
488 begin
489
490 if LOG_FNAME is null AND
491 OUT_FNAME is null AND
492 TEMP_DIR is null then
493
494 LOG_FNAME := P_LOG;
495 OUT_FNAME := P_OUT;
496 TEMP_DIR := P_DIR;
497
498 end if;
499
500 end PUT_NAMES;
501
502
503 /*
504 ** PUT_NAMES - Set the temp file names and directories
505 ** Has no effect when called from a concurrent program.
506 ** IN
507 ** P_LOG - Temporary logfile name
508 ** P_OUT - Temporary outfile name
509 ** P_DIR - Temporary directory name
510 **
511 */
512 procedure RELEASE_NAMES(P_LOG in varchar2, P_OUT in varchar2) is
513 BASE varchar2(12);
514 begin
515 BASE := substr(P_LOG, 2, 7);
516 insert into fnd_temp_files (filename, type)
517 values (BASE, 'P');
518
519 end RELEASE_NAMES;
520
521
522 procedure GET_NAMES(P_LOG in out nocopy varchar2,
523 P_OUT in out nocopy varchar2) is
524 BASE varchar2(12);
525 begin
526
527 BASE := GET_BASE;
528 P_LOG := 'l' || BASE || '.tmp';
529 P_OUT := 'o' || BASE || '.tmp';
530
531 end GET_NAMES;
532
533
534 /*
535 ** CLOSE - Close open files.
539 log_recorded boolean := false;
536 ** Should not be called from a concurrent program.
537 */
538 procedure CLOSE is
540 out_recorded boolean := false;
541 file_exists boolean := false;
542 file_length number(15) := 0;
543 block_size number(15) := 0;
544 temp_utl varchar2(512) := null;
545
546 BEGIN
547
548 BEGIN
549 utl_file.fclose(F_LOG);
550 EXCEPTION
551 when others then
552 raise_application_error(-20100,
553 get_exception(SQLCODE, SQLERRM, 'close', LOG_FNAME));
554 END;
555
556 BEGIN
557 utl_file.fclose(F_OUT);
558 EXCEPTION
559 when others then
560 raise_application_error(-20100,
561 get_exception(SQLCODE, SQLERRM, 'close', OUT_FNAME));
562 END;
563
564 if UTL_FILE_DELAY > 0 then
565 temp_utl := TEMP_DIR || '/' || LOG_FNAME;
566 if (fnd_conc_private_utils.check_temp_file_use(temp_utl,NULL,'F') = 1) then
567 log_recorded := TRUE;
568 end if;
569
570 if log_recorded then
571 log_simple_msg(fnd_log.level_statement, 'close',
572 'temp file: '|| temp_utl ||' recorded');
573 fnd_conc_private_utils.erase_temp_file_use(temp_utl,NULL,'F');
574 else
575 log_simple_msg(fnd_log.level_statement, 'close',
576 'temp file: '|| temp_utl ||' not recorded');
577 end if;
578
579 temp_utl := TEMP_DIR || '/' || OUT_FNAME;
580 if (fnd_conc_private_utils.check_temp_file_use(temp_utl,NULL,'F') = 1) then
581 out_recorded := TRUE;
582 end if;
583 if out_recorded then
584 log_simple_msg(fnd_log.level_statement, 'close',
585 'temp file: '|| temp_utl ||' recorded');
586 fnd_conc_private_utils.erase_temp_file_use(temp_utl,NULL,'F');
587 else
588 log_simple_msg(fnd_log.level_statement, 'close',
589 'temp file: '|| temp_utl ||' not recorded');
590 end if;
591
592 if (log_recorded or out_recorded) then
593 dbms_lock.sleep(UTL_FILE_DELAY);
594 begin
595 utl_file.fgetattr(TEMP_DIR, LOG_FNAME, file_exists, file_length, block_size);
596 log_simple_msg(fnd_log.level_statement, 'close',
597 'utl_file.getattr (after '||to_char(UTL_FILE_DELAY)||' secs) log file: '||LOG_FNAME ||
598 ' is length: '||file_length||' and block_size: '||block_size);
599 exception
600 when others then
601 raise_application_error(-20100,
602 get_exception(SQLCODE, SQLERRM, 'close', LOG_FNAME));
603 end;
604 begin
605 utl_file.fgetattr(TEMP_DIR, OUT_FNAME, file_exists, file_length, block_size);
606 log_simple_msg(fnd_log.level_statement,'close',
607 'utl_file.getattr (after '||to_char(UTL_FILE_DELAY)||' secs) out file: '||OUT_FNAME ||
608 ' is length: '||file_length||' and block_size: '||block_size);
609 exception
610 when others then
611 raise_application_error(-20100,
612 get_exception(SQLCODE, SQLERRM, 'close', OUT_FNAME));
613 end;
614 end if;
615 end if;
616
617 LOG_OPEN := FALSE;
618 OUT_OPEN := FALSE;
619 end;
620
621 /*
622 ** IS_OPEN - Returns 1 if file is open, else 0.
623 **
624 */
625 function IS_OPEN (WHICH in varchar2) return number is
626 isopen number := 0;
627 BEGIN
628
629 if WHICH = 'LOG' then
630 BEGIN
631 if (utl_file.is_open(F_LOG)) then
632 isopen := 1;
633 end if;
634 EXCEPTION
635 when others then
636 raise_application_error(-20100,
637 get_exception(SQLCODE, SQLERRM, 'is_open', LOG_FNAME));
638 END;
639 end if;
640
641 if WHICH = 'OUT' then
642 BEGIN
643 if (utl_file.is_open(F_OUT)) then
644 isopen := 1;
645 end if;
646 EXCEPTION
647 when others then
648 raise_application_error(-20100,
649 get_exception(SQLCODE, SQLERRM, 'is_open', OUT_FNAME));
650 END;
651 end if;
652
653 return isopen;
654
655 end;
656
657
658
659 begin
660
661 -- sets ascii character for the newline. DO NOT edit the lines below!!
662 RAW_TERM:=utl_raw.cast_to_raw('
663 ');
664 -- sets ascii character for the newline. DO NOT edit the lines above!!
665
666
667 fnd_profile.get('UTL_FILE_MODE', UTL_FILE_MODE);
668 if (upper(UTL_FILE_MODE) = 'RAW') then
669 UTL_FILE_MODE := 'RAW';
670 else
671 UTL_FILE_MODE := 'TRADITIONAL';
672 end if;
673
674 if FND_PROFILE.defined('CONC_UTL_FILE_DELAY') then
675 fnd_profile.get('CONC_UTL_FILE_DELAY', UTL_FILE_DELAY);
676 log_simple_msg(fnd_log.level_statement, 'init', 'UTL_FILE_DELAY ='|| UTL_FILE_DELAY);
677 end if;
678
679 end fnd_file;