In this post, we will discuss FND_FILE in oracle apps, how it works, troubleshooting the FND_FILE issues
What is FND_FILE
- FND_FILE is the standard API provided by Oracle Apps. Package FND_FILE in Oracle apps contains routines that allow as concurrent programs to write to the request log and output files, stored under */log and */out.
- The FND_FILE.PUT_LINE() calls use the UTL_FILE package to write the line to a temporary file in the $APPLPTMP directory.
- Text written by the stored procedures is first kept in temporary files on the database server, and after the request is completed, is copied to the log and out files by the manager running the request. Opening and closing files are handled behind the scenes by the concurrent manager. Every read and write to the temporary files is implicitly flushed to minimize the risk of data loss.
- The actual temporary files used can be found with the following query (directory, output temporary file, log temporary file):
select cp.plsql_dir, cp.plsql_out, cp.plsql_log from fnd_concurrent_requests cr, fnd_concurrent_processes cp where cr.request_id =< running request id> and cp.concurrent_process_id = cr.controlling_manager;
- Once the concurrent request completes, the data in the temporary files are copied to the concurrent request output and log files (for example $APPLCSF/out//o.out and $APPLCSF/log//l.log)
- The concurrent managers maintain a shared pool of temporary files; when a manager starts up, it attempts to use filenames from the pool. If no filenames exist, the manager creates new temporary log and output files. These two files are cleared after each concurrent request and then reused for the next request. As a result, no more temporary files are created than necessary.
- The temporary files are named as follows, where the x’s indicate a sequence number, padded to 7 digits:
l<seq no>.req o<seq no>.req
- The directory for temporary files must be set in the environment variable APPLPTMP when the managers are started. This directory must also be listed in the UTL_FILE_DIR parameter in init. ora.
- To write to these log and output files, simply call the necessary procedures. Opening and closing the files is handled by the concurrent managers. Procedure arguments and exceptions are detailed below.
- There are several limitations to these procedures. The temporary files cannot be deleted, but are reduced to 0-length. Deleting them must be handled by the system administrator. This package is not designed for generic PL/SQL text I/O. It is only used for writing to request log and output files.
- To facilitate debugging and testing from SQLPlus, you can use the procedure FND_FILE.PUT_NAMES(LOG, OUT, DIR). This function sets the temporary log and out filenames and the temporary directory to the user-specified values. DIR must be a directory to which the database can write. FND_FILE.PUT_NAMES should be called before calling any other FND_FILE function. If this function is not called when using SQLPlus, FND_FILE will choose a filename from the pool, as described above. FND_FILE.PUT_NAMES works only once per session, and it does nothing if called from a concurrent program. Procedure FND_FILE.CLOSE will close the files in a command-line session. FND_FILE.CLOSE should not be called from a concurrent program; the concurrent manager will handle closing files.
FND_FILE API Procedure
FND_FILE.PUT
Syntax
FND_FILE.PUT (which IN NUMBER, buff IN VARCHAR2);
Arguments
which: A log file or output file. Use either FND_FILE.LOG or FND_FILE.OUTPUT.
buff: Text to write.
Description
Use this procedure to write text to a file (without a newline character).Multiple calls to FND_FILE.PUT will produce concatenated text. Typically used with FND_FILE.NEW_LINE.
FND_FILE.PUT_LINE
Syntax
procedure FND_FILE.PUT_LINE (which IN NUMBER, buff IN VARCHAR2);
Arguments
which: A log file or output file. Use either FND_FILE.LOG or FND_FILE.OUTPUT.
buff: Text to write.
Description
Use this procedure to write a line of text to a file (followed by a new line character).
You will use this utility most often.
FND_FILE.NEW_LINE
Syntax
FND_FILE.NEW_LINE(which IN NUMBER, LINES IN NATURAL:= 1);
Arguments
which: A log file or output file. Use either FND_FILE.LOG or FND_FILE.OUTPUT.
lines: Number of line terminators to write.
Description
Use this procedure to write line terminators (newline characters) to a file.
FND_FILE.PUT_NAMES
Syntax
procedure FND_FILE.PUT_NAMES
(p_log IN VARCHAR2,
p_out IN VARCHAR2,
(p_dir IN VARCHAR2);
Arguments
p_log :Temporary log filename.
p_out :Temporary output filename.
p_dir: Temporary directory name.
Description
As explained earlier, this Sets the temporary log and out filenames and the temp directory to the user-specified values. DIR must be a directory to which the database can write. FND_FILE.PUT_NAMES is meant for testing and debugging from SQL*Plus; it does nothing if called from a concurrent program.
Example
BEGIN
fnd_file.put_names('test.log', 'test.out',
'/u01/temp/');
fnd_file.put_line(fnd_file.output,'this is test');
fnd_file.close;
END;
FND_FILE.CLOSE
Syntax
procedure FND_FILE.CLOSE;
Description
Use this procedure to close open files.
Errors in FND_FILE processing
ORA-20100: File o0397099.tmp creation for FND_FILE failed. or ORA-20100: Error: FND_FILE failure. Unable to create file, nnnnnn.tmp in the directory, /usr/tmp. You will find more information in the request log. ORA-06512: at "APPS.FND_FILE", line 417 ORA-06512: at "APPS.FND_FILE", line 456 ORA-06512: at line 1
Solution
- Please check the value of $APPLPTMP variable and make sure this path is listed in UTL_FILE_DIR parameter in the Database.
- Please make sure that is writable/readable by the oracle user on the database server
- Sometimes the issue is caused by the parameter utl_file_dir in the pfile as being taken as a complete string. The utl_file_dir is currently defined in the following manner
utl_file_dir=/usr/tmp,/oraapp/inst_top/apps/directory_top/appltmp,/oraapp/inst_top/apps/directory_top/appltmp
The correct format is
utl_file_dir='/usr/tmp','/oraapp/inst_top/apps/directory_top/appltmp','/oraapp/inst_top/apps/directory_top/appltmp'
I hope you like this post on FND_FILE in oracle apps. Please do provide the feedback
Related Articles
How to change apps password in R12.2
FNDLOAD Command: FNDLOAD command/loader is a general-purpose utility that moves structured data between a text file and a database in an EBS environment.
ORA-29280: invalid directory path
ORA-29285: file write error