UTL_FILE PACKAGE in PL/SQL:

“You’ve gotta dance like there’s nobody watching, love like you’ll never be hurt, sing like there’s nobody listening, and live like it’s heaven on earth.”

UTL_FILE is a built-in package of Oracle which is basically use for to perform operation over text files in different modes like read, write.

It provides restricted version of standard OS stream file input\output (I\O).

In PLSQL file input and output operations are available on both the sides i.e., client side and server side too. Normally it is same as file read, write operations but in case of server it can have some administrative privileges.

Let’s learn about UTIL_FILE.FOPEN() function:

With the help of FOPEN function you can open the operating system text file and edit, modify, and manipulate its contents.

Syntax:

UTL_FILE.FOPEN (    location           IN VARCHAR2,    filename          IN VARCHAR2,    open_mode    IN VARCHAR2,    max_linesize   IN BINARY_INTEGER DEFAULT 1024 ) RETURN FILE_TYPE;

Let’s learn about UTIL_FILE.FOPEN_NCHAR () function:

FOPEN_NCHAR function opens the file in the national character set and manipulates it in Unicode instead of database character set.

Syntax:

UTL_FILE.FOPEN_NCHAR (    location           IN VARCHAR2,    filename          IN VARCHAR2,    open_mode    IN VARCHAR2,    max_linesize   IN BINARY_INTEGER DEFAULT 1024 ) RETURN FILE_TYPE;

Below are the FOPEN function parameter details and detailed descriptions

Parameters	Description
Location	location of file
Filename	file name with file extension
open_mode	mode of operations on file:
	r -read
	w -write
	a -append
	rb -read byte
	wb -write byte
	ab -append byte
max_linesize	File can allow up to 32767 char which includes new line char also. Oracle file is having 1024 char size by default.

Example:

DECLARE V1 VARCHAR2(10000); V_file UTL_FILE.FILE_TYPE; BEGIN V_file := UTL_FILE.FOPEN(‘FIRSTFILE’, ‘FIRSTFILE’,’R’,256); UTL_FILE.GET_LINE(V_file,V1,10000); UTL_FILE.FCLOSE(V_file); V_file := UTL_FILE.FOPEN(‘FIRSTFILE’,’FIRSTFILE’,’R’); UTL_FILE.GET_LINE(V_file,V1,10000); UTL_FILE.FCLOSE(V_file); V_file := UTL_FILE.FOPEN(‘FIRSTFILE’,’FIRSTFILE’,’R’); UTL_FILE.GET_LINE(V_file,V1); UTL_FILE.FCLOSE(V_file); END;