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;



ParameterDescription
locationDirectory 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.
filenameFile 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_modeSpecifies 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_linesizeMaximum 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

 15,159 views last month,  113 views today

About Mehmet Salih Deveci

I am Founder of SysDBASoft IT and IT Tutorial and Certified Expert about Oracle & SQL Server database, Goldengate, Exadata Machine, Oracle Database Appliance administrator with 10+years experience.I have OCA, OCP, OCE RAC Expert Certificates I have worked 100+ Banking, Insurance, Finance, Telco and etc. clients as a Consultant, Insource or Outsource.I have done 200+ Operations in this clients such as Exadata Installation & PoC & Migration & Upgrade, Oracle & SQL Server Database Upgrade, Oracle RAC Installation, SQL Server AlwaysOn Installation, Database Migration, Disaster Recovery, Backup Restore, Performance Tuning, Periodic Healthchecks.I have done 2000+ Table replication with Goldengate or SQL Server Replication tool for DWH Databases in many clients.If you need Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS Consultancy and Training you can send my email adress [email protected].-                                                                                                                                                                                                                                                 -Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS ve linux Danışmanlık ve Eğitim için  [email protected] a mail atabilirsiniz.

Leave a Reply