Section 21.2.  Reading and Writing Files

21.2. Reading and Writing Files The UTL_FILE package allows PL/SQL programs to both read from and write to any operating-system files that are accessible from the server on which your database instance is running. You can load data from files directly into database tables while applying the full power and flexibility of PL/SQL programming. You can generate reports directly from within PL/SQL without worrying about the maximum buffer restrictions of DBMS_OUTPUT that existed prior to Oracle Database 10g Release 2. UTL_FILE lets you read and write files accessible from the server on which your database is running. So you could theoretically use UTL_FILE to write right over your tablespace datafiles, control files, and so on. That is of course a very bad idea. Server security requires the ability to place restrictions on where you can read and write your files. UTL_FILE implements this security by limiting access to files in one of two ways: It only reads/writes files in directories that are specified by the UTL_FILE_DIR parameter in the database initialization file. With Oracle Database 9i Release 2 and above, UTL_FILE reads/writes files in locations specified by database Directory objects. The next two sections explain how to use these two approaches; we will then examine the specific capabilities of the UTL_FILE package . Many of the UTL_FILE programs are demonstrated in a handy encapsulation package found in the fileio.pkg file on the book's web site. 21.2.1. The UTL_FILE_DIR Parameter When you call FOPEN to open a file, you must specify both the location and the name of the file in separate arguments. This file location is then checked against the list of accessible directories. Here's the format of the parameter for file access in the database initialization file: UTL_FILE_DIR = directory Include a parameter for UTL_FILE_DIR for each directory you want to make accessible for UTL_FILE operations. The following entries, for example, enable four different directories in Unix: UTL_FILE_DIR = /tmp UTL_FILE_DIR = /ora_apps/hr/time_reporting UTL_FILE_DIR = /ora_apps/hr/time_reporting/log UTL_FILE_DIR = /users/test_area To bypass server security and allow read/write access to all directories, you can use this special syntax: UTL_FILE_DIR = * You should not use this option on production systems. In a development system, this entry certainly makes it easier for developers to get up and running on UTL_FILE and test their code. However, you should allow access to only a few specific directories when you move the application to production. 21.2.1.1 Setting up directories Here are some observations on working with and setting up accessible directories with UTL_FILE: Access is not recursive through subdirectories. Suppose that the following lines were in your database initialization file, for example: UTL_FILE_DIR = c:\group\dev1 UTL_FILE_DIR = c:\group\prod\oe UTL_FILE_DIR = c:\group\prod\ar You would not be able to open a file in the c:\group\prod\oe\reports subdirectory. Do not include the following entry in Unix systems: UTL_FILE_DIR = . This allows you to read/write on the current directory in the operating system. Do not enclose the directory names within single or double quotes. In the Unix environment, a file created by FOPEN has as its owner the shadow process running the Oracle instance. This is usually the "oracle" owner. If you try to access these files outside of UTL_FILE, you will need the correct privileges (or be logged in as "oracle") to access or change these files. You should not end your directory name with a delimiter, such as the forward slash in Unix. The following specification of a directory will result in problems when trying to read from or write to the directory: UTL_FILE_DIR = /tmp/orafiles/ 21.2.1.2 Specifying file locations The location of the file is an operating system-specific string that specifies the directory or area in which to open the file. The location you provide must have been listed as an accessible directory in the INIT.ORA file for the database instance. The INIT.ORA location is a valid directory or area specification, as shown in these examples: In Windows 'k:\common\debug' In Unix '/usr/od2000/admin' Notice that in Windows, the backslash character (\) is used as a delimiter. In Unix, the forward slash (/) is the delimiter. When you pass the location in the call to UTL_FILE.FOPEN, you provide the location specification as it appears in the INIT.ORA file (unless you just provided * for all directories in the initialization file). And remember that in case-sensitive operating systems, the case of the location specification in the initialization file must match that used in the call to UTL_ FILE.FOPEN. Here are some examples: In Windows file_id := UTL_FILE.FOPEN ('k:\common\debug', 'trace.lis', 'R'); In Unix file_id := UTL_FILE.FOPEN ('/usr/od2000/admin', 'trace.lis', 'W'); Your location must be an explicit, complete path to the file. You cannot use operating system-specific parameters such as environment variables in Unix to specify file locations. 21.2.2. Work with Oracle Directories Prior to Oracle9i Database Release 2, whenever you opened a file, you needed to specify the location of the file, as in: BEGIN file_id := UTL_FILE.fopen ( '/accts/data', 'trans.dat', 'R'); Such a hardcoding of values is always to be avoided, however. What if the location of the accounts data changes? How many programs will I have to go fix to make sure everyone is looking in the right place? How many times will I have to make such changes? A much better approach is to declare a variable or constant and assign it the value of the location. If you do this in a package, the constant can be referenced by any program in a schema with the EXECUTE privilege on that package. Here is an example, followed by a recoding of the earlier FOPEN call: CREATE PACKAGE accts_pkg IS c_data_location CONSTANT VARCHAR2(30) := '/accts/data'; ... END accts_pkg; /   BEGIN file_id := UTL_FILE.fopen ( accts_pkg.c_data_location, 'trans.dat', 'R'); That's great. But even better is to use a schema-level object that you can define in the database: a directory. This particular type of object is also used when working with BFILEs, so you can in effect "consolidate" file location references in both DBMS_LOB and UTL_FILE by shifting to directories. To create a directory, you will need the CREATE ANY DIRECTORY privilege . You then define a new directory as shown in this example: CREATE OR REPLACE DIRECTORY DEVELOPMENT_DIR AS '/dev/source'; CREATE OR REPLACE DIRECTORY TEST_DIR AS '/test/source'; Here are some things to keep in mind about directories and UTL_FILE: Oracle does not validate the location you specify when you specify the name of a directory. It simply associates that string with the named database object. When you specify the name of a directory in a call to, say, UTL_FILE.FOPEN, it is treated as a case-sensitive string. In other words, if you do not specify the name in uppercase, the operation will fail. Once created, you can grant permissions to specific users to work with that directory as follows: GRANT READ ON DIRECTORY DEVELOPMENT_DIR to senior_developer; Finally, you can query the contents of ALL_DIRECTORIES to determine which directories are available in the currently connected schema. You can also leverage this view to build some useful utilities, which you will find in the fileio.pkg package. Here is one example: generate a UTL_FILE_DIR entry for each directory defined in the database: PROCEDURE fileIO.gen_utl_file_dir_entries IS BEGIN FOR rec IN (SELECT * FROM all_directories) LOOP DBMS_OUTPUT.put_line ('UTL_FILE_DIR = ' || rec.directory_path); END LOOP; END gen_utl_file_dir_entries; One advantage of building utilities like those found in fileIO is that you can easily add sophisticated handling of the case of the directory to avoid "formatting errors," such as forgetting to specify the directory name in uppercase. 21.2.3. Open Files Before you can read or write a file, you must open it. The UTL_FILE.FOPEN function opens the specified file and returns a file handle you can then use to manipulate the file. Here's the header for the function: FUNCTION UTL_FILE.fopen ( location IN VARCHAR2 , filename IN VARCHAR2 , open_mode IN VARCHAR2 , max_linesize IN BINARY_INTEGER DEFAULT NULL) RETURN file_type; Parameters are summarized in the following table: Parameter Description location Location of the file (directory in UTL_FILE_DIR or a database Directory). filename Name of the file. openmode Mode in which the file is to be opened (see the following modes). max_linesize The maximum number of characters per line, including the newline character, for this file. Minimum is 1; maximum is 32767. The default of NULL means that UTL_FILE determines an appropriate value from the operating system (the value has historically been around 1,024 bytes). You can open the file in one of three modes: R Opens the file read-only. If you use this mode, use UTL_FILE's GET_LINE procedure to read from the file. W Opens the file to read and write in replace mode. When you open in replace mode, all existing lines in the file are removed. If you use this mode, you can use any of the following UTL_FILE programs to modify the file: PUT, PUT_LINE, NEW_LINE, PUTF, and FFLUSH. A Opens the file to read and write in append mode. When you open in append mode, all existing lines in the file are kept intact. New lines will be appended after the last line in the file. If you use this mode, you can use any of the following UTL_FILE programs to modify the file: PUT, PUT_LINE, NEW_LINE, PUTF, and FFLUSH. Keep the following points in mind as you attempt to open files: The file location and the filename joined together must represent a legal filename on your operating system. The file location specified must be accessible and must already exist; FOPEN will not create a directory or subdirectory for you in order to write a new file, for example. If you want to open a file for read access, the file must already exist. If you want to open a file for write access, the file will either be created if it does not exist or emptied of all its contents if it does exist. If you try to open with append, the file must already exist. UTL_FILE will not treat your append request like a write access request. If the file is not present, UTL_FILE will raise the INVALID_OPERATION exception. The following example shows how to declare a file handle and then open a configuration file for that handle in read-only mode: DECLARE config_file UTL_FILE.FILE_TYPE; BEGIN config_file := UTL_FILE.FOPEN ( '/maint/admin', 'config.txt', 'R'); Notice that I did not provide a maximum line size when I opened this file. That parameter is, in fact, optional. If you do not provide it, the maximum length of a line you can read from or write to the file is approximately 1,024. Given this limitation, you should always include the max_linesize argument as shown below: DECLARE config_file UTL_FILE.FILE_TYPE; BEGIN config_file := UTL_FILE.FOPEN ( '/maint/admin', 'config.txt', 'R', max_linesize => 32767); 21.2.4. Is the File Already Open? The IS_OPEN function returns TRUE if the specified handle points to a file that is already open. Otherwise, it returns false. The header for the function is, FUNCTION UTL_FILE.IS_OPEN (file IN UTL_FILE.FILE_TYPE) RETURN BOOLEAN; where file is the file to be checked. Within the context of UTL_FILE, it is important to know what this means. The IS_ OPEN function does not perform any operating system checks on the status of the file. In actuality, it merely checks to see if the id field of the file handle record is not NULL. If you don't play around with these records and their contents, this id field is set to a non-NULL value only when you call FOPEN. It is set back to NULL when you call FCLOSE. 21.2.5. Close Files Use the UTL_FILE.FCLOSE and UTL_FILE.FCLOSE_ALL procedures to close a specific file and all open files in your session, respectively. Use FCLOSE to close an open file. The header for this procedure is: PROCEDURE UTL_FILE.FCLOSE (file IN OUT FILE_TYPE); where file is the file handle. Notice that the argument to UTL_FILE.FCLOSE is an IN OUT parameter because the procedure sets the id field of the record to NULL after the file is closed. If there is buffered data that has not yet been written to the file when you try to close it, UTL_FILE will raise the WRITE_ERROR exception. FCLOSE_ALL closes all the opened files. The header for this procedure follows: PROCEDURE UTL_FILE.FCLOSE_ALL; This procedure will come in handy when you have opened a variety of files and want to make sure that none of them are left open when your program terminates. In programs in which files have been opened, you may wish to call FCLOSE_ALL in the exception handlers of those programs. If there is an abnormal termination of the program, files will then still be closed. EXCEPTION WHEN OTHERS THEN UTL_FILE.FCLOSE_ALL; ... other clean up activities ... END; When you close your files with the FCLOSE_ALL procedure, none of your file handles will be marked as closed (the id field, in other words, will still be non-NULL). The result is that any calls to IS_OPEN for those file handles will still return TRUE. You will not, however, be able to perform any read or write operations on those files (unless you reopen them). 21.2.6. Read from Files The UTL_FILE.GET_LINE procedure reads a line of data from the specified file, if it is open, into the provided line buffer. Here's the header for the procedure: PROCEDURE UTL_FILE.GET_LINE (file IN UTL_FILE.FILE_TYPE, buffer OUT VARCHAR2); Parameters are summarized in the following table: Parameter Description file The file handle returned by a call to FOPEN buffer The buffer into which the line of data is read The variable specified for the buffer parameter must be large enough to hold all the data up to the next carriage return or end-of-file condition in the file. If not, PL/SQL will raise the VALUE_ERROR exception. The line terminator character is not included in the string passed into the buffer. Oracle offers additional GET programs to read NVARCHAR2 data (GET_LINE_NCHAR) and raw data (GET_RAW). Here is an example that uses GET_LINE: DECLARE strbuffer VARCHAR2(32767); mynum NUMBER; BEGIN fileID := UTL_FILE.FOPEN ( 'TEMP_DIR', 'numlist.txt', 'R', max_linesize => 32767); UTL_FILE.GET_LINE (fileID, strbuffer); mynum := TO_NUMBER (strbuffer); END; / Because GET_LINE reads data only into a string variable, you will have to perform your own conversions to local variables of the appropriate datatype if your file holds numbers or dates. 21.2.6.1 GET_LINE exceptions When GET_LINE attempts to read past the end of the file, the NO_DATA_FOUND exception is raised. This is the same exception that is raised when you: Execute an implicit (SELECT INTO) cursor that returns no rows Reference an undefined row of a PL/SQL collection Read past the end of a BFILE (binary file) with DBMS_LOB If you are performing more than one of these operations in the same PL/SQL block, you may need to add extra logic to distinguish between the different sources of this error. See the who_did_that.sql file on the book's web site for a demonstration of this technique. 21.2.6.2 Handy encapsulation for GET_LINE The GET_LINE procedure is simple and straightforward. It gets the next line from the file. If the pointer to the file is already located at the last line of the file, UTL_ FILE.GET_LINE does not return any kind of flag but instead raises the NO_DATA_FOUND exception. This design leads to poorly structured code; you might consider using an encapsulation on top of GET_LINE to improve that design. A demonstration will make the point clear. Here is a program that reads each line from a file and then processes that line: DECLARE l_file UTL_FILE.file_type; l_line VARCHAR2 (32767); BEGIN l_file := UTL_FILE.fopen ('TEMP', 'names.txt', 'R');   LOOP UTL_FILE.get_line (l_file, l_line); process_line (l_line); END LOOP; EXCEPTION WHEN NO_DATA_FOUND THEN UTL_FILE.fclose (names_file); END; Notice that the simple loop does not contain any explicit EXIT statement. The loop terminates implicitly and with an exception, as soon as UTL_FILE reads past the end of the file. In a small block like this one, the logic is clear. But imagine if your program is hundreds of lines long and much more complex. Suppose further that reading the contents of the file is just one step in the overall algorithm. If an exception terminates my block, I will then need to put the rest of my business logic in the exception section (bad idea) or put an anonymous BEGIN-END block wrapper around my read-file logic. I am not comfortable with this approach. I don't like to code infinite loops without an EXIT statement; the termination condition is not structured into the loop itself. Furthermore, the end-of-file condition is not really an exception; every file, after all, must end at some point. Why must we be forced into the exception section simply because we want to read a file in its entirety? I believe that a better approach to handling the end-of-file condition is to build a layer of code around GET_LINE that immediately checks for end-of-file and returns a Boolean value (TRUE or FALSE). Theget_nextline procedure shown below demonstrates this approach: /* File on web: getnext.sp */ PROCEDURE get_nextline ( file_in IN UTL_FILE.FILE_TYPE , line_out OUT VARCHAR2 , eof_out OUT BOOLEAN) IS BEGIN UTL_FILE.GET_LINE (file_in, line_out); eof_out := FALSE; EXCEPTION WHEN NO_DATA_FOUND THEN line_out := NULL; eof_out := TRUE; END; The get_nextline procedure accepts an already assigned file handle and returns two pieces of information: the line of text (if there is one) and a Boolean flag (set to TRUE if the end-of-file is reached, FALSE otherwise). Using get_nextline, I can now read through a file with a loop that has an EXIT statement: DECLARE l_file UTL_FILE.file_type; l_line VARCHAR2 (32767); l_eof BOOLEAN; BEGIN l_file := UTL_FILE.fopen ('TEMP', 'names.txt', 'R');   LOOP get_nextline (l_file, l_line, l_eof); EXIT WHEN l_eof; process_line (l_line); END LOOP; UTL_FILE.fclose (l_file); END; With get_nextline, I no longer treat end-of-file as an exception. I read a line from the file until I am done, and then I close the file and exit. This is, I believe, a more straightforward and easily understood program. 21.2.7. Write to Files In contrast to the simplicity of reading from a file, UTL_FILE offers a number of different procedures you can use to write to a file: UTL_FILE.PUT Adds the data to the current line in the opened file but does not append a line terminator. You must use the NEW_LINE procedure to terminate the current line or use PUT_LINE to write out a complete line with a line termination character. UTL_FILE.NEW_LINE Inserts one or more newline characters (default is 1) into the file at the current position. UTL_FILE.PUT_LINE Puts a string into a file, followed by a platform-specific line termination character. This is the program you are most likely to be using with UTL_FILE. UTL_FILE.PUTF Puts up to five strings out to the file in a format based on a template string, similar to the printf function in C. UTL_FILE.FFLUSH Makes sure that all pending data for the specified file is written physically out to a file. You can use these procedures only if you have opened your file with modes Wor A; if you opened the file for read-only, the runtime engine raises the UTL_ FILE.INVALID_OPERATION exception. Oracle offers additional PUT programs to write NVARCHAR2 data (PUT_LINE_NCHAR, PUT_NCHAR, PUTF_NCHAR) and raw data (PUT_RAW). Let's take a closer look at UTL_FILE.PUT_LINE. This procedure writes data to a file and then immediately appends a newline character after the text. Here's the header for PUT_LINE: PROCEDURE UTL_FILE.PUT_LINE ( file IN UTL_FILE.FILE_TYPE, ,buffer IN VARCHAR2, ,autoflush IN BOOLEAN DEFAULT FALSE) Parameters are summarized in the following table: Parameter Description file The file handle returned by a call to FOPEN buffer Text to be written to the file; maximum size allowed is 32K for 8.0. 3 and above; for earlier versions, it is 1,023 bytes autoflush Pass TRUE if you want this line to be flushed out to the operating system immediately Before you can call UTL_FILE.PUT_LINE, you must have already opened the file. Here is an example that uses PUT_LINE to dump the names of all our employees to a file: PROCEDURE names_to_file IS fileid UTL_FILE.file_type; BEGIN fileid := UTL_FILE.fopen ('TEMP', 'names.dat', 'W');   FOR emprec IN (SELECT * FROM employee) LOOP UTL_FILE.put_line ( fileid , emprec.first_name || ' ' || emprec.last_name); END LOOP;   UTL_FILE.fclose (fileid); END names_to_file; A call to PUT_LINE is equivalent to a call to PUT followed by a call to NEW_LINE. It is also equivalent to a call to PUTF with a format string of "%s\n" (see the description of PUTF in the next section). 21.2.7.1 Writing formatted text to file Like PUT, PUTF puts data into a file, but it uses a message format (hence, the "F" in "PUTF") to interpret the different elements to be placed in the file. You can pass between one and five different items of data to PUTF. Here's the specification: PROCEDURE UTL_FILE.putf (file IN FILE_TYPE ,format IN VARCHAR2 ,arg1 IN VARCHAR2 DEFAULT NULL ,arg2 IN VARCHAR2 DEFAULT NULL ,arg3 IN VARCHAR2 DEFAULT NULL ,arg4 IN VARCHAR2 DEFAULT NULL ,arg5 IN VARCHAR2 DEFAULT NULL); Parameters are summarized in the following table: Parameter Description file The file handle returned by a call to FOPEN format The string that determines the format of the items in the file; see the following options argN An optional argument string; up to five may be specified The format string allows you to substitute the argN values directly into the text written to the file. In addition to "boilerplate" or literal text, the format string may contain the following patterns: %s Directs PUTF to put the corresponding item in the file. You can have up to five %s patterns in the format string because PUTF will take up to five items. \n Directs PUTF to put a newline character in the file. There is no limit to the number of \n patterns you may include in a format string. The %s formatters are replaced by the argument strings in the order provided. If you do not pass in enough values to replace all of the formatters, then the %s is simply removed from the string before writing it to the file. The following example illustrates how to use the format string. Suppose you want the contents of the file to look like this: Employee: Steven Feuerstein Soc Sec #: 123-45-5678 Salary: $1000 This single call to PUTF will accomplish the task: UTL_FILE.putf (file_handle, 'Employee: %s\nSoc Sec #: %s\nSalary: %s', 'Steven Feuerstein', '123-45-5678', TO_CHAR (:employee.salary, '$9999')); If you need to write out more than five items of data, you can simply call PUTF twice consecutively to finish the job. 21.2.8. Copy Files UTL_FILE.FCOPY lets you easily copy the contents of one source file to another destination file. The following snippet, for example, uses UTL_FILE.FCOPY to perform a backup by copying a single file from the development directory to the archive directory: DECLARE file_suffix VARCHAR2 (100) := TO_CHAR (SYSDATE, 'YYYYMMDDHHMISS'); BEGIN -- Copy the entire file... UTL_FILE.fcopy ( src_location => 'DEVELOPMENT_DIR', src_filename => 'archive.zip', dest_location => 'ARCHIVE_DIR', dest_filename => 'archive' || file_suffix || '.zip' ); END; You can also use FCOPY to copy just a portion of a file. The program offers two additional parameters that allow you to specify the starting and ending line numbers you want to copy from the file. Suppose that I have a text file containing the names of the winners of a monthly PL/SQL quiz that started in January 2000. I would like to transfer all the names in 2001 to another file. I can do that by taking advantage of the fifth and sixth arguments of the FCOPY procedure as shown below: DECLARE c_start_year PLS_INTEGER := 2000; c_year_of_interest PLS_INTEGER := 2001; l_start PLS_INTEGER; l_end PLS_INTEGER; BEGIN l_start := (c_year_of_interest - c_start_year)*12 + 1; l_end := l_start + 11;   UTL_FILE.fcopy ( src_location => 'WINNERS_DIR', src_filename => 'names.txt', dest_location => 'WINNERS_DIR', dest_filename => 'names2001.txt', start_line => l_start, end_line => l_end ); END; A useful encapsulation to UTL_FILE.FCOPY allows me to specify start and end strings instead of line numbers. We will leave the implementation of such a utility as an exercise for the reader (see the infile.sf file on the book's web site for an implementation of an "INSTR for files" that might give you some ideas on implementation). 21.2.9. Delete Files Beginning with Oracle9i Database Release 2, you can remove files using UTL_FILE.FREMOVE. The header for this program is: PROCEDURE UTL_FILE.fremove ( location IN VARCHAR2, filename IN VARCHAR2); That's simple enough. You provide the location and name of the file, and UTL_FILE attempts to delete it. What if UTL_FILE encounters a problem? You might see one of the following exceptions then raised: Exception name Meaning UTL_FILE.invalid_path Not a valid file handle UTL_FILE.invalid_filename File not found or filename NULL UTL_FILE.file_open File already open for writing/appending UTL_FILE.access_denied Access to the directory object is denied UTL_FILE.remove_failed Failed to delete file In other words, UTL_FILE will raise an exception if you try to remove a file that doesn't exist or if you do not have the privileges needed to remove the file. Many file-removal programs in other languages (for example, File.delete in Java) return a status code to inform you of the outcome of the removal attempt. If you prefer this approach, you can use (or copy) the fileIO.fremove program found in the fileio.pkg file on the book's web site. 21.2.10. Rename and Move Files I can combine copy and remove operations into a single step by calling the UTL_FILE.RENAME procedure. This handy utility allows me to either rename a file in the same directory or to rename a file to another name and location (in effect, moving that file). Here is the header for FRENAME: PROCEDURE UTL_FILE.frename ( src_location IN VARCHAR2, src_filename IN VARCHAR2, dest_location IN VARCHAR2, dest_filename IN VARCHAR2, overwrite IN BOOLEAN DEFAULT FALSE); This program may raise one of the following exceptions: Exception name Meaning UTL_FILE.invalid_path Not a valid file handle UTL_FILE.invalid_filename File not found or filename NULL UTL_FILE.rename_failed Unable to perform the rename as requested UTL_FILE.access_denied Insufficient privileges to access directory object You will find an interesting application of FRENAME in the fileIO packagethe chgext procedure. This program changes the extension of the specified file. 21.2.11. Retrieve File Attributes Sometimes we need to get information about the file in question: How big is this file? Does a file even exist? What is the block size of my file? Such questions are not mysteries that can only be solved with the help of an operating system command (or, in the case of the file length, the DBMS_LOB package), as they were in early Oracle releases. UTL_FILE.FGETATTR provides us with that information in a single native procedure call. Here is the header for FGETATTR: PROCEDURE UTL_FILE.fgetattr ( location IN VARCHAR2, filename IN VARCHAR2, fexists OUT BOOLEAN, file_length OUT NUMBER, block_size OUT BINARY_INTEGER); Thus, to use this program, we must declare three different variables to hold the Boolean flag (does the file exist?), the length of the file, and the block size. Here is a sample usage: DECLARE l_fexists BOOLEAN; l_file_length PLS_INTEGER; _block_size PLS_INTEGER; BEGIN UTL_FILE.fgetattr ( location => DEVELOPMENT_DIR, filename => 'bigpkg.pkg', fexists => l_fexists, file_length => l_file_length, block_size => l_block_size ); ... END; This interface is a bit awkward. Suppose we just want to find out the length of this file? We still have to declare all those variables, obtain the length, and then work with that value. Perhaps the best way to take advantage of FGETATTR is to build some of your own functions on top of this built-in that answer a single question, such as: FUNCTION fileIO.flength ( location_in IN VARCHAR2, file_in IN VARCHAR2 ) RETURN PLS_INTEGER; or: FUNCTION fileIO.fexists ( location_in IN VARCHAR2, file_in IN VARCHAR2 ) RETURN BOOLEAN; As a result, you do not have to declare unneeded variables, and you can write simpler, cleaner code.