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,
ALL_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
- 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.
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: