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 ( location IN VARCHAR2, filename IN VARCHAR2, open_mode IN VARCHAR2, max_linesize IN BINARY_INTEGER) RETURN file_type;
|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 |
|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 |
|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 ‘
|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;
161,146 views last month, 57 views today