12.5 How do I…Read and write operating system files with UTL_FILE?
Problem
I need a method to read and write operating system files from within PL/SQL. I want to read and write files from within stored procedures and functions. How do I read and write operating system files?
Technique
The UTL_FILE built-in package is available with PL/SQL 2.3 and higher. The package enables you to both read and write operating system files. UTL_FILE modules can be called from within stored modules or client-side modules. Table 12.8 shows the modules that make up the UTL_FILE database package.
Name Type Description FOPEN Function Opens the specified file. IS_OPEN Function Returns TRUE if the file is currently open. GET_LINE Procedure Gets the next line from the file. PUT_LINE Procedure Writes a line to the file after appending the line terminator. PUT Procedure Writes a line to the file without appending the line terminator. PUTF Procedure Writes a formatted line to the file without appending the line terminator. NEW_LINE Procedure Terminates current line and begins a new line. FFLUSH Procedure Writes all data from the buffer to the file. FCLOSE Procedure Closes the specified file. FCLOSE_ALL Procedure Closes all open files. In order to read a file you first have to declare a file handle, which is used to reference the file in the other procedures. Next, you must open the file with a call to FOPEN. The file can be opened to read, write, or append. The PUT_LINE, PUT, and PUTF procedures are used to write data to the file, which is closed using the FCLOSE procedure. The GET_LINE procedure is used to read a line of data and places it in a VARCHAR2 variable.
The locations of directories accessible to UTL_FILE must be defined using the utl_file_dir parameter in the INIT<SID>.ORA initialization file. Only exact directories listed in the INIT<SID>.ORA file are accessible. Subdirectories of accessible directories are not automatically accessible unless you specify utl_file_dir = * in your INIT<SID>.ORA file; then all directories are accessible to UTL_FILE.
After changing the INIT<SID>.ORA file, the Oracle instance must be brought down and back up for the changes to be effective. Ensure that the Oracle owner has read and write permissions on these directories.
Steps
1. Run SQL*Plus and connect as the WAITE user account. Run the CHP12_18.SQL file, as shown in Figure 12.21. The file contains PL/SQL code to open a file and write two lines to it.
Line 2 declares a file handle variable, the datatype of the file handle defined in the UTL_FILE package. Line 4 uses the FOPEN function to open the file CHP12.TXT in the C:\TEMP directory. The file handle returned by FOPEN is used in all subsequent calls. The W parameter specifies that the file is opened in write mode. If A is specified, the file is opened in append mode; R opens the file in a read mode.
Line 5 calls PUT_LINE to write a line of text to the file and line 6 calls PUTF to write a formatted line of text to the file. The maximum size for the buffer in any PUT_LINE procedure is 1,023 bytes. If an error occurred while writing to the file, the exception handler in lines 9 to 11 is raised. If the file was already opened for read by another process in the system, the exception handler in lines 12 to 14 is raised.
Line 7 closes the file using the FCLOSE procedure. Check the C:\TEMP directory to view the contents of the CHP12.TXT file. On UNIX, file permissions on a file created using the UTL_FILE package are rw-r--r--. If the UTL_FIL.INVALID_PATH EXCEPTION is raised, verify that you do not have a slash (\ or /) at the end of the directory path in either the INIT<SID>.ORA file or your code.
2. Run the CHP12_19.SQL file in SQL*Plus. The PL/SQL block in the file reads the line of data written in the previous step. Figure 12.22 shows the results of the operation in SQL*Plus.
Line 2 declares a VARCHAR2 variable which receives the data when read from the file. Line 3 declares the file handle controlling access to the file. Line 5 opens the file for reading by using the FOPEN function and passing ‘R’ as the second parameter. Lines 6 and 8 use the GET_LINE procedure to read a line from the file. The maximum size for the buffer in any GET_LINE procedure is 1,022 bytes. If an error occurred while reading the file, the exception handler in lines 12 to 14 is raised. If the file is already opened for write by another process in the system, the exception handler in lines 15 to 17 is raised. Lines 7 and 9 use the PUT_LINE procedure from the DBMS_OUTPUT package to display the line read from the file. Line 10 closes the file by executing the FCLOSE procedure.
How It Works
The UTL_FILE package is used to read and write operating system files. The directories specified by the utl_file_dir parameter are valid for reading and writing for all users of the UTL_FILE package. It should be noted that this can override operating system permissions.
The FOPEN function is used to open a file, and the PUT_LINE, PUT, and PUTF procedures are used to write data to a file. The GET_LINE procedure is used to read a line from a file. Finally, the file handle is closed by calling FCLOSE to free any resources associated with the file. Step 1 opens an operating system file and writes a single line to it. Step 2 opens the same file and reads the line from it and displays it in SQL*Plus. If an error occurred while calling a function or procedure in the UTL_FILE package an exception is raised. Exceptions defined in the UTL_FILE package are listed in Table 12.9. In addition, the NO_DATA_FOUND or VALUE_ERROR predefined exceptions can also be raised by the UTL_FILE package.
Table 12.9 UTL_FILE package exceptions
Exception Description INVALID_PATH Invalid filename or directory. INVALID_MODE Invalid OPEN_MODE parameter in FOPEN call. INVALID_FILEHANDLE Invalid file handle. INVALID_OPERATION File could not be opened or operated on. READ_ERROR Read operation failed. WRITE_ERROR Write operation failed. INTERNAL_ERROR Unspecified internal error. The FLCOSE_ALL procedure does not change the state of open file handles, even though it closes all open file handles. The IS_OPEN function returns TRUE for a file handle that was closed by calling FCLOSE_ALL. Discretion is advised.
Comments
Reading and writing operating system files is very useful and expands the capabilities of PL/SQL. The UTL_FILE package runs on the server side and is only available in PL/SQL 2.3 and above. The client-side TEXT_IO package provided with the Oracle Procedure Builder is similar to the UTL_FILE package.