{"id":34767,"date":"2021-06-16T13:57:20","date_gmt":"2021-06-16T13:57:20","guid":{"rendered":"https:\/\/ittutorial.org\/?p=34767"},"modified":"2021-06-16T13:57:20","modified_gmt":"2021-06-16T13:57:20","slug":"utl_file-fopen-in-pl-sql","status":"publish","type":"post","link":"https:\/\/ittutorial.org\/utl_file-fopen-in-pl-sql\/","title":{"rendered":"UTL_FILE FOPEN in PL\/SQL"},"content":{"rendered":"

I will explain UTL_FILE FOPEN in PL\/SQL in this post.<\/p>\n

 <\/p>\n

UTL_FILE in Oracle<\/span><\/h2>\n

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<\/p>\n

 <\/p>\n\n

 <\/p>\n

 <\/p>\n

 <\/p>\n

FOPEN function of\u00a0UTL_FILE<\/span><\/h3>\n

You can open the operating system file in the database using fopen function, you can edit and manipulate the file content using FOPEN Function.<\/p>\n

 <\/p>\n

UTL_FILE.FOPEN Syntax<\/span><\/h3>\n
UTL_FILE.FOPEN (\r\n   location     IN VARCHAR2,\r\n   filename     IN VARCHAR2,\r\n   open_mode    IN VARCHAR2,\r\n   max_linesize IN BINARY_INTEGER) \r\n  RETURN file_type;\r\n\r\n\r\n\r\n<\/pre>\n\n\n\n\n\n\n\n\n
Parameter<\/th>\nDescription<\/th>\n<\/tr>\n<\/thead>\n
location<\/code><\/td>\nDirectory 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\u00a0UTL_FILE<\/code>\u00a0user to run\u00a0FOPEN<\/code>.<\/td>\n<\/tr>\n
filename<\/code><\/td>\nFile name, including extension (file type), without directory path. If a directory path is given as a part of the filename, it is ignored by\u00a0FOPEN<\/code>. On Unix, the filename cannot end with \/.<\/td>\n<\/tr>\n
open_mode<\/code><\/td>\nSpecifies how the file is opened. Modes include:<\/p>\n
r — read text<\/p>\n
w — write text<\/p>\n
a — append text<\/p>\n
rb — read byte mode<\/p>\n
wb — write byte mode<\/p>\n
ab — append byte mode<\/p>\n
If you try to open a file specifying ‘a<\/code>‘ or ‘ab<\/code>‘ for\u00a0open_mode<\/code>\u00a0but the file does not exist, the file is created in\u00a0write<\/code>\u00a0mode.<\/td>\n<\/tr>\n
max_linesize<\/code><\/td>\nMaximum 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.<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
\r\n\r\n\r\n\r\n\r\n<\/pre>\n
You can use the UTL_FILE and UTL_FILE.FOPEN function in the example as follows.<\/p>\n
 <\/p>\n
DECLARE \r\nV1 VARCHAR2(32767); \r\nF1 UTL_FILE.FILE_TYPE; \r\nBEGIN \r\n-- In this example MAX_LINESIZE is less than GET_LINE's length request \r\n-- so the number of bytes returned will be 256 or less if a line terminator is seen. \r\nF1 := UTL_FILE.FOPEN('MYDIR','MYFILE','R',256); \r\nUTL_FILE.GET_LINE(F1,V1,32767); \r\nUTL_FILE.FCLOSE(F1);\r\n\r\n-- In this example, FOPEN's MAX_LINESIZE is NULL and defaults to 1024, \r\n-- so the number of bytes returned will be 1024 or less if a line terminator is seen. \r\nF1 := UTL_FILE.FOPEN('MYDIR','MYFILE','R'); \r\nUTL_FILE.GET_LINE(F1,V1,32767); \r\nUTL_FILE.FCLOSE(F1);\r\n\r\n-- In this example, GET_LINE doesn't specify a number of bytes, so it defaults to \r\n-- the same value as FOPEN's MAX_LINESIZE which is NULL in this case and defaults to 1024. \r\n-- So the number of bytes returned will be 1024 or less if a line terminator is seen. \r\nF1 := UTL_FILE.FOPEN('MYDIR','MYFILE','R'); \r\nUTL_FILE.GET_LINE(F1,V1); \r\nUTL_FILE.FCLOSE(F1); \r\nEND;<\/pre>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
Do you want to learn Oracle Database Performance Tuning detailed, then Click this link.<\/span><\/a><\/h5>\n
Performance Tuning and SQL Tuning Tutorial in the Oracle Database<\/a><\/p><\/blockquote>\n