Input/output operation using PL/SQL:

PL/SQL like any other programming language can be used can be used for file I/O. Server as well as client side I/O is possible using PL/SQL. You can use UTL_FILE package of PL/SQL for doing file I/O. It is a standard package that is used for performing file I/O in PL/SQL.

Basic steps that are carried out for performing file I/O are :
Open file for I/O operation.
Read file.
Write content to the file.
Close the file.

UTL_FILE package provides standard functions and procedures that are used to carry out these operations. We will look how what are the standard modules avaliable to carry out these operations.

1>> Open a file for I/O operation: To open a file for carrying out tasks FOPEN. This function takes in some parameters like file directory, file name, mode to open a file and returns a file handle which is used in subsequent methods to carry out operations. FOPEN takes following parameters:

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

Here,

location : is the oracle virtual directory which points to a server side operating system directory.
filename : is the filename of the file on which to do the operation.
open_mode : is the mode in which to open the file; it can be
r : to open a file in read text mode
w : to open a file in write text mode
a : to open a file in append text mode. If the file specified by filename is not present then a new file is created and opened in write text mode.
rb : to open a file in read byte mode.
wb : to open a file in write byte mode.
ab : to open a file in append byte mode.

max_linesize : Maximum number of characters for each line including a newline character. If unspecified oracle default value of 1024 is used.

Ex:
UTL_FILE.FOPEN('VIR_DIR','my_first_file.txt','r',1024);

2>> Read file: Reading a file opened in PL/SQL is done using GET_LINE function of UTL_FILE. Following are the parameters used for GET_LINE function:

UTL_FILE.GET_LINE (
file IN FILE_TYPE,
buffer OUT VARCHAR2,
len IN PLS_INTEGER DEFAULT NULL);

file : is the file handle of pl/sql type file_type. This is the file handle returned from FOPEN function used to open a file.
buffer : is the buffer used to store the data read from the file.
len : is the length of characters to be read. If nothing is supplied then default value of null is considered. In this case max_linesize is used.

GET_LINE reads data from a file. No. of characters read is the lesser of max_linesize, len or the line upto the line terminator in the file being read excluding the terminator. Maximum buffer size of 32767.

3>> Write content to the file: PUT or PUT_LINE function of UTL_FILE package can be used to write content to a file. PUT writes no. of characters to file without appending a newline char to the text. While PUT_LINE is used to write text with a newline character to a file. Following are the parameters used in PUT function:

UTL_FILE.PUT (
file IN FILE_TYPE,
buffer IN VARCHAR2);

file : is the file handle used for performing operation.
buffer : is the string to write to a file.

<<>> If the file is not opened in w or a mode, then INVALID_OPERATION is raised.

<<>> One cannot write to a buffer more than 32767 characters. We have to flush the buffer using fflush to write more data to file.

4>> Close the file : A file is closed in PL/SQL using FCLOSE function. FCLOSE function takes a file handle and closes it if it is open otherwise raises an exception.