UTL_FILE FOPEN in PL/SQL

I will explain UTL_FILE FOPEN in PL/SQL in this post.

 

UTL_FILE in Oracle

UTL_FILE package is used to restricted version of operating system stream file I/O in PL/SQL Programs that can read and write operating system text files

 

 

 

 

FOPEN function of UTL_FILE

You can open the operating system file in the database using fopen function, you can edit and manipulate the file content using FOPEN Function.

 

UTL_FILE.FOPEN Syntax

UTL_FILE.FOPEN (
   location     IN VARCHAR2,
   filename     IN VARCHAR2,
   open_mode    IN VARCHAR2,
   max_linesize IN BINARY_INTEGER) 
  RETURN file_type;

Parameter	Description
location	Directory location of file. This string is a directory object name and is case sensitive. The default is uppercase. Read privileges must be granted on this directory object for the UTL_FILE user to run FOPEN.
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. On Unix, the filename cannot end with /.
open_mode	Specifies how the file is opened. Modes include: r — read text w — write text a — append text rb — read byte mode wb — write byte mode ab — append byte mode If you try to open a file specifying ‘a‘ or ‘ab‘ for open_mode but the file does not exist, the file is created in write mode.
max_linesize	Maximum number of characters for each line, including the newline character, for this file (minimum value 1, maximum value 32767). If unspecified, Oracle supplies a default value of 1024.

You can use the UTL_FILE and UTL_FILE.FOPEN function in the example as follows.

 

DECLARE 
V1 VARCHAR2(32767); 
F1 UTL_FILE.FILE_TYPE; 
BEGIN 
-- In this example MAX_LINESIZE is less than GET_LINE's length request 
-- so the number of bytes returned will be 256 or less if a line terminator is seen. 
F1 := UTL_FILE.FOPEN('MYDIR','MYFILE','R',256); 
UTL_FILE.GET_LINE(F1,V1,32767); 
UTL_FILE.FCLOSE(F1);

-- In this example, FOPEN's MAX_LINESIZE is NULL and defaults to 1024, 
-- so the number of bytes returned will be 1024 or less if a line terminator is seen. 
F1 := UTL_FILE.FOPEN('MYDIR','MYFILE','R'); 
UTL_FILE.GET_LINE(F1,V1,32767); 
UTL_FILE.FCLOSE(F1);

-- In this example, GET_LINE doesn't specify a number of bytes, so it defaults to 
-- the same value as FOPEN's MAX_LINESIZE which is NULL in this case and defaults to 1024. 
-- So the number of bytes returned will be 1024 or less if a line terminator is seen. 
F1 := UTL_FILE.FOPEN('MYDIR','MYFILE','R'); 
UTL_FILE.GET_LINE(F1,V1); 
UTL_FILE.FCLOSE(F1); 
END;

 

 

 

 

 

Do you want to learn Oracle Database Performance Tuning detailed, then Click this link.

Performance Tuning and SQL Tuning Tutorial in the Oracle Database