On more than fewer occasions, while developing PL/SQL programs, we are required to read / write data from text files that reside on our operating systems. UTL_FILE package lets us to read / write files from our PL/SQL programs.
In this post, I shall try to explain some of the procedures that are primarily used during this process and a sample code is provided at the end of the post. The objective of the sample code would be “read” the data from a csv file and then to “write” data into another file placed on the server.
Some of the primary procedures used to read and write using UTL_FILE are:
UTL_FILE.fgetattr: This procedure is used to check if the file exists or not in the specified directory location. It also returns the attributes (properties) of a file.
UTL_FILE.FGETATTR(location IN VARCHAR2,
filename IN VARCHAR2,
fexists OUT BOOLEAN,
file_length OUT NUMBER,
block_size OUT BINARY_INTEGER);
The input and output parameters are:
- location: Directory location of the source file,
DIRECTORY_NAMEfromALL_DIRECTORIESview (case sensitive). - filename: Name of the file to be processed.
- fexists: The procedure returns TRUE value if the file exists in the directory.
- file_length: Length of the file in bytes.
- block_size: File system block size in bytes.
UTL_FILE.fopen: This function opens the file that needs to be processed (read or write).
UTL_FILE.FOPEN ( location IN VARCHAR2, filename IN VARCHAR2, open_mode IN VARCHAR2, max_linesize IN BINARY_INTEGER DEFAULT 1024) RETURN FILE_TYPE;
The input parameters are:
- location: Directory location of file. This string is a directory object name and must be specified in upper case.
- filename: File name, including extension (file type), without directory path. If a directory path is given as a part of the filename, it is ignored by
FOPEN. - open_mode: The mode in which the file has to be opened in. The mostly used attributes are: r -> read and w -> write.
- UTL_FILE.get_line: Once the file is opened, the get_line procedure reads the text from the open file places the text in the output buffer parameter. If the len parameter is null – then the oracle provides the max_linesize value to that parameter.
UTL_FILE.GET_LINE ( file IN FILE_TYPE, buffer OUT VARCHAR2, len IN PLS_INTEGER DEFAULT NULL);
The parameters are:
- file: Active file handle returned by an FOPEN call.
- buffer: Data buffer to receive the line read from the file.
- UTL_FILE.put_line: The put_line procedure writes the text string stored in the buffer parameter to the open file identified by file handle.
UTL_FILE.PUT_LINE ( file IN FILE_TYPE, buffer IN VARCHAR2, autoflush IN BOOLEAN DEFAULT FALSE);
The parameters are:
- file: The file that is being processed and is called using the active file handle.
- buffer: Text buffer that contains the text lines that need to be written to the file.
- UTL_FILE.fclose: This fclose procedure closes the file that is opened earlier. This is a mandatory statement during the UTL_File process. Not closing the file would keep the file open and make it unable to move or delete the file at a later stage.
UTL_FILE.fclose (l_file_handler);
The Input read file – Test_UTL_Read_File.csv has the following content:
In order to explain UTL_FILE, below is the sample code that uses some of the procedures explained above. The objective of this code is to:
- Check to see if the file exists.
- Open the file.
- Read the data from the file line by line.
- Write the data into another file on the same server.
- Close the file.
declare
l_file_handler UTL_FILE.file_type;
l_file_handler1 UTL_FILE.file_type;
l_file_handler2 UTL_FILE.file_type;
l_file_exists BOOLEAN := FALSE;
l_length NUMBER;
l_blocksize NUMBER;
l_dir CONSTANT VARCHAR2 (100) DEFAULT 'XXCMNDIR';
l_new_line VARCHAR2 (500);
l_first_comma NUMBER;
l_second_comma NUMBER;
l_delim VARCHAR2 (1) := ',';
l_var1 VARCHAR2 (1000) := 'INSERT INTO XX_TEST_UTL_TBL (ID, STUDENT_NUM, SUBJECT) VALUES (';
l_var2 VARCHAR2 (1) := ')';
l_student_id VARCHAR2 (50);
l_student_num VARCHAR2 (50);
l_subject VARCHAR2 (50);
l_count NUMBER;
l_insert_stmnt VARCHAR2 (1000);
begin
/* Check to see if the file exists using the UTL_FILE.fgetattr function.*/
UTL_FILE.fgetattr (l_dir,
'Test_UTL_Read_File.csv',
l_file_exists,
l_length,
l_blocksize);
dbms_output.put_line ('l_length is: '||l_length);
dbms_output.put_line ('l_blocksize is: '||l_blocksize);
/* Opening the File.
It is mandatory to open the file before starting any operations on the files.
The File is opened using the UTL_FILE.fopen function. It has three parameters.
Directory_name, file_name, read (r) or write (w) mode. */
BEGIN
l_file_handler := UTL_FILE.fopen (l_dir,'Test_UTL_Read_File.csv','r');
l_file_handler1 := UTL_FILE.fopen (l_dir,'Test_UTL_Write_File.csv','w');
l_file_handler2 := UTL_FILE.fopen (l_dir,'Test_UTL_Error_File.csv','w');
dbms_output.put_line ('File is successfully readable...');
EXCEPTION
--Below are the different UTL_FILE Exceptions
WHEN UTL_FILE.invalid_path
THEN
UTL_FILE.put_line (l_file_handler2, 'File location is invalid: '||SUBSTR (SQLERRM, 1, 200));
WHEN UTL_FILE.invalid_mode
THEN
UTL_FILE.put_line (l_file_handler2,
'The open_mode parameter in FOPEN is invalid. '||SUBSTR (SQLERRM, 1, 200));
WHEN UTL_FILE.invalid_filehandle
THEN
UTL_FILE.put_line (l_file_handler2,
'File handle is invalid: '||SUBSTR (SQLERRM, 1, 200));
WHEN UTL_FILE.invalid_operation
THEN
UTL_FILE.put_line (l_file_handler2,
'File could not be opened or operated on as requested: '
||SUBSTR (SQLERRM, 1,200));
WHEN UTL_FILE.read_error
THEN
UTL_FILE.put_line (l_file_handler2,
'Destination buffer too small, or operating system error
occurred during the read operation: '||SUBSTR (SQLERRM, 1, 200));
WHEN UTL_FILE.write_error
THEN
UTL_FILE.put_line (l_file_handler2,
'Operating system error occurred during the write operation: '
||SUBSTR (SQLERRM,1,200));
WHEN OTHERS
THEN
UTL_FILE.put_line (l_file_handler2, 'Other Error: '||SQLERRM);
END;
/*Now once the file is opened - read the contents of the file
The UTL_FILE.GET_LINE procedure reads the text from the open files
identified by the file handle and places the text
in the output buffer paramter.*/
l_count := 0;
LOOP
l_count := l_count+1;
BEGIN
UTL_FILE.GET_LINE (l_file_handler, l_new_line);
EXCEPTION
WHEN NO_DATA_FOUND
THEN
EXIT;
END;
l_first_comma := INSTR (l_new_line, ',', 1, 1);
l_second_comma := INSTR (l_new_line, ',', 1, 2);
l_student_id := SUBSTR (l_new_line, 1, l_first_comma-1);
l_student_num := SUBSTR (l_new_line, l_first_comma+1, l_second_comma - l_first_comma - 1 );
l_subject := SUBSTR (l_new_line, l_second_comma + 1);
l_subject := REGEXP_REPLACE(l_subject,'(^[[:space:]]*|[[:space:]]*$)');
l_insert_stmnt := l_var1
||l_student_id ||l_delim
||l_student_num ||l_delim
||l_subject ||l_var2;
dbms_output.put_line (l_insert_stmnt);
UTL_FILE.put_line (l_file_handler1,l_insert_stmnt);
END LOOP;
UTL_FILE.fclose (l_file_handler);
UTL_FILE.fclose (l_file_handler1);
UTL_FILE.fclose (l_file_handler2);
exception when others
then
dbms_output.put_line ('Exception occurred in main block: '||SQLERRM);
end;
After the UTL_File completes the Write process into a file Test_UTL_Write_File.csv, then the final output is as below:
