UTL_FILE PACKAGE In Oracle

UTL_FILE PACKAGE In Oracle:

UTL_FILE:

With this package, a plsql subprogram or block can read from or write to a operating system file.

SETUP:

UTL_FILE uses the oracle directories and not the OS directories. So a logical directory to be created by SYS user and read and write access of that directory to be granted to the user or to public.

CONN SYS AS SYSDBA/MANAGER123;
CREATE OR REPLACE DIRECTORY UTL_DIR AS 'D:\ORACLE\UTL_DIR';
GRANT READ, WRITE ON DIRECTORY UTL_DIR TO PUBLIC;
CONN HR/HR;
SELECT * FROM ALL_DIRECTORIES;

DOWNLOAD A FILE INTO A DIRECTORY:

DECLARE
CURSOR EMP_CUR
IS
SELECT EMPLOYEE_ID,
FIRST_NAME,
LAST_NAME,
SALARY,
TO_CHAR(HIRE_DATE, 'DD/MM/RRRR') HIRE_DATE
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 30
ORDER BY EMPLOYEE_ID;

V_FILE UTL_FILE.FILE_TYPE;
BEGIN
V_FILE := UTL_FILE.FOPEN('UTL_DIR', 'EMP_DETAILS.CSV', 'W', MAX_LINESIZE => 32767);
UTL_FILE.PUT_LINE(V_FILE, 'EMPLOYEE_ID'||','||'FIRST_NAME'||','||'LAST_NAME'||','||'SALARY'||','||'HIRE_DATE');
FOR I IN EMP_CUR
LOOP
UTL_FILE.PUT_LINE(V_FILE, I.EMPLOYEE_ID||','||I.FIRST_NAME||','||I.LAST_NAME||','||I.SALARY||','||I.HIRE_DATE);
END LOOP;
UTL_FILE.FCLOSE(V_FILE);
EXCEPTION
WHEN OTHERS THEN
UTL_FILE.FCLOSE(V_FILE);
RAISE;
END;
/

FILE DOWNLOAD:

The file generated on physical location:

File Data:

FILE DOWNLOAD & UPLOAD:

CREATE TABLE EMP_UPLOAD_DOWNLOAD
AS
SELECT EMPLOYEE_ID,
FIRST_NAME,
LAST_NAME,
SALARY,
TO_CHAR(HIRE_DATE, 'DD/MM/RRRR') HIRE_DATE,
DEPARTMENT_ID
FROM EMPLOYEES
WHERE 1 = 2;
DECLARE
CURSOR EMP_CUR
IS
SELECT EMPLOYEE_ID,
FIRST_NAME,
LAST_NAME,
SALARY,
TO_CHAR(HIRE_DATE, 'DD/MM/RRRR') HIRE_DATE,
DEPARTMENT_ID
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 50
ORDER BY EMPLOYEE_ID;

V_FILE UTL_FILE.FILE_TYPE;
V_HDR VARCHAR2(32767);
V_DTL VARCHAR2(32767);
BEGIN
--FILE DOWNLOAD PART STARTED--
BEGIN
V_FILE := UTL_FILE.FOPEN('UTL_DIR', 'EMP_DETAILS.CSV', 'W', MAX_LINESIZE => 32767);
UTL_FILE.PUT_LINE(V_FILE, 'EMPLOYEE_ID'||','||'FIRST_NAME'||','||'LAST_NAME'||','||'SALARY'||','||'HIRE_DATE'||','||'DEPARTMENT_ID');
FOR I IN EMP_CUR
LOOP
UTL_FILE.PUT_LINE(V_FILE, I.EMPLOYEE_ID||','||''''||I.FIRST_NAME||''''||','||''''||I.LAST_NAME||''''||','||I.SALARY||','||''''||I.HIRE_DATE||''''||','||I.DEPARTMENT_ID);
END LOOP;
UTL_FILE.FCLOSE(V_FILE);
EXCEPTION
WHEN OTHERS THEN
UTL_FILE.FCLOSE(V_FILE);
RAISE;
END;
--FILE UPLOAD PART STARTED--
BEGIN
EXECUTE IMMEDIATE 'TRUNCATE TABLE EMP_UPLOAD_DOWNLOAD';
V_FILE := UTL_FILE.FOPEN('UTL_DIR', 'EMP_DETAILS.CSV', 'R', 32767);
UTL_FILE.GET_LINE(V_FILE, V_HDR, 32767);
LOOP
BEGIN
UTL_FILE.GET_LINE(V_FILE, V_DTL, 32767);
IF V_DTL NOT LIKE 'EMPLOYEE_ID%' THEN
EXECUTE IMMEDIATE 'INSERT INTO EMP_UPLOAD_DOWNLOAD('||V_HDR||') VALUES('||V_DTL||')';
COMMIT;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
EXIT;
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('ERROR = '||SQLERRM);
CONTINUE;
END;
END LOOP;
END;
END;
/
SELECT * FROM EMP_UPLOAD_DOWNLOAD;

UTL_FILE PACKAGE EXCEPTIONS:

EXCEPTION NAMEDESCRIPTION
INVALID_PATHFile location is invalid
INVALID_MODEThe OPEN_MODE parameter of FOPEN
is invalid
INVALID_FILEHANDLEFile handle is invalid
INVALID_OPERATIONFile could not opened or operated on as requested
READ_ERROROperating system error occurred during read operation
WRITE_ERROROperating system error occurred during write operation
INTERNAL_ERRORUnspecified PLSQL error
FILE_OPENRequested operation is failed as the file is opened
INVALID_MAXLINESIZEThe MAX_LINESIZE value for FOPEN() is invalid. It should be within range of 1 to 32767
INVALID_FILENAMEFILENAME parameter is invalid
ACCESS_DENIEDPermission to access the file location is denied
DELETE_FAILEDRequested delete operation is failed
RENAME_FAILEDRequested file rename operation is failed

IMPORTANT SUBPROGRAMS OF UTL_FILE:

FOPEN FUNCTION:
  • This function opens a file
  • You can specify the maximum line size
  • A maximum of 50 files can be opened simultaneously.
SYNTAX:
UTL_FILE.FOPEN(LOCATION          VARCHAR2,
FILENAME VARCHAR2,
OPEN_MODE VARCHAR2,
MAX_LINESIZE BINARY_INTEGER
) RETURN FILE_TYPE;
PARAMETERDESCRIPTION
LOCATIONDirectory location of file
FILENAMEName of the file with extension
OPEN_MODEMode in which file is to be opened
R -> Read
W -> Write
A -> Append
RB -> Read byte mode
WB -> Write byte mode
AB -> Append byte mode
In A or AB mode if file does not exists then the file will be created in write mode
MAX_LINESIZEMaximum no of characters in a single line.
Min vlaue is 1 & Max value is 32767. If not specified then default value is 1024
ASSOCIATED EXCEPTIONS:
  • INVALID_PATH
  • INVALID_MODE
  • INVALID_OPERATION
  • INVLAID_MAXLINESIZE
SELECT * FROM ALL_DIRECTORIES
WHERE DIRECTORY_NAME = 'UTL_DIR_TEST';
DECLARE
V_FILE UTL_FILE.FILE_TYPE;
BEGIN
V_FILE := UTL_FILE.FOPEN('UTL_DIR_TEST', 'EMP_DETAILS.CSV', 'W', MAX_LINESIZE => 32767);
END;
/
DECLARE
V_FILE UTL_FILE.FILE_TYPE;
BEGIN
V_FILE := UTL_FILE.FOPEN('UTL_DIR', 'EMP_DETAILS.CSV', 'W1', MAX_LINESIZE => 32767);
END;
/
DECLARE
V_FILE UTL_FILE.FILE_TYPE;
BEGIN
V_FILE := UTL_FILE.FOPEN('UTL_DIR', 'EMP_DETAILS123.CSV', 'R', MAX_LINESIZE => 32767);
END;
/
DECLARE
V_FILE UTL_FILE.FILE_TYPE;
BEGIN
V_FILE := UTL_FILE.FOPEN('UTL_DIR', 'EMP_DETAILS123.CSV', 'R', MAX_LINESIZE => 32768);
END;
/
IS_OPEN FUNCTION:
  • This function tests a file handle to check whether it represents a already open file.
  • It returns true if the file is already opened and not yet closed.
SYNTAX:
UTL_FILE.IS_OPEN(FILE FILE_TYPE) RETURN BOOLEAN;
FCLOSE PROCEDURE:

It closes a already opened file identified by file handle.

UTL_FILE.FCLOSE(FILE IN OUT FILE_TYPE);
ASSOCIATED EXCEPTIONS:
  • WRITE_ERROR
  • INVALID_FILEHANDLE
DECLARE
V_FILE UTL_FILE.FILE_TYPE;
BEGIN
V_FILE := UTL_FILE.FOPEN('UTL_DIR', 'EMP_DETAILS.CSV', 'W', MAX_LINESIZE => 5);
UTL_FILE.PUT_LINE(V_FILE,'FIRST LINE');
UTL_FILE.FCLOSE(V_FILE);
END;
/
DECLARE
V_FILE UTL_FILE.FILE_TYPE;
BEGIN
UTL_FILE.PUT_LINE(V_FILE,'FIRST LINE');
V_FILE := UTL_FILE.FOPEN('UTL_DIR', 'EMP_DETAILS.CSV', 'W', MAX_LINESIZE => 32767);
UTL_FILE.FCLOSE(V_FILE);
EXCEPTION
WHEN UTL_FILE.INVALID_FILEHANDLE THEN
DBMS_OUTPUT.PUT_LINE('ERROR FOR FILE HANDLE -> '||SQLERRM);
END;
/
FCLOSE_ALL PROCEDURE:
  • This procedure is used to close all the file handle in the session
  • This should be used in an emergency situation like exit from the session due an exception.
SYNTAX:
UTL_FILE.FCLOSE_ALL;
ASSOCIATED EXCEPTION:

WRITE_ERROR

NEW_LINE PROCEDURE:

This procedure writes one or more line terminator to the file identified by filehandle.

SYNTAX:
UTL_FILE.NEW_LINE(FILE      FILE_TYPE,
LINES NATURAL := 1);
PARAMETERDESCRIPTION
FILEActive filehandle returned by FOPEN
LINESNo of line terminator to be added in the file
ASSOCIATED EXCEPTIONS:
  • INVALID_FILEHANDLE
  • INVALID_OPERATION
  • WRITE_ERROR
PUT PROCEDURE:
  • PUT writes a string in the buffer parameter to the file handle by the filehandler.
  • File must be opened in write mode
  • Use NEW_LINE to terminate the line or use PUT_LINE to add a complete line with line terminator
SYNTAX:
UTL_FILE.PUT(FILE      FILE_TYPE,
BUFFER VARCHAR2);
PARAMETERDESCRIPTION
FILEActive file handle.
The file must be opened in write mode
BUFFERBuffer that contains the text to be written in the file
ASSOCIATED EXCEPTIONS:
  • INVALID_FILEHANDLE
  • INVALID_OPERATION
  • WRITE_ERROR
PUT_LINE PROCEDURE:
  • This procedure write the text stored in the buffer to the open file identified by filehandle.
  • The file must be opened in write mode.
  • PUT_LINE always terminates the line.
SYNTAX:
UTL_FILE.PUT_LINE(FILE         FILE_TYPE,
BUFFER VARCHAR2,
AUTOFLUSH BOOLEAN DEFAULT NULL);
PARAMETERDESCRIPTION
FILEActive file handle returned by an FOPEN call
BUFFERText buffer that contains the line to be written to the file
AUTOFLUSHFlushes the buffer to disk after write
ASSOCIATED EXCEPTIONS:
  • INVALID_FILEHANDLE
  • INVALID_OPERATION
  • WRITE_ERROR
GET_LINE PROCEDURE:
  • This procedure reads the text from the open file identified by file handler and places the text to output buffer parameter.
  • The text does not including the line terminator
  • The length of the buffer cannot exceed the max_linesize parameter
SYNTAX:
UTL_FILE.GET_LINE(FILE         FILE_TYPE,
BUFFER OUT VARCHAR2,
LEN PLS_INTEGER DEFAULT NULL);
PARAMETERDESCRIPTION
FILEActive file handle return by an FOPEN call
BUFFERData buffer read a line from the file
LENThe no of bytes read from the file.
ASSOCIATED EXCEPTIONS:
  • INVALID_FILEHANDLE
  • INVALID_OPERATION
  • READ_ERROR
  • NO_DATA_FOUND
  • VALUE_ERROR
DECLARE
V_FILE UTL_FILE.FILE_TYPE;
V_TEXT VARCHAR2(100);
BEGIN
V_FILE := UTL_FILE.FOPEN('UTL_DIR', 'EMP_DETAILS.CSV', 'R', MAX_LINESIZE => 5);
UTL_FILE.GET_LINE(V_FILE, V_TEXT, 10);
UTL_FILE.FCLOSE(V_FILE);
END;
/
DECLARE
V_FILE UTL_FILE.FILE_TYPE;
V_TEXT VARCHAR2(100);
BEGIN
V_FILE := UTL_FILE.FOPEN('UTL_DIR', 'EMP_DETAILS123.CSV', 'R', MAX_LINESIZE => 32767);
UTL_FILE.GET_LINE(V_FILE, V_TEXT, 32767);
UTL_FILE.FCLOSE(V_FILE);
END;
/
SELECT LENGTH('HJKHGJGHJGJGJHGHJGFJTYRYYJLJKFLJKSAGDASFAJFLJFLJALFJLASJFLAJLDFALFJDLFJLAJFLAJLDFJALDFJLAJFLDAJLFDJALFJALSJDFKKHJHKHKHKJHK') STR FROM DUAL;
DECLARE
V_FILE UTL_FILE.FILE_TYPE;
V_TEXT VARCHAR2(100);
BEGIN
V_FILE := UTL_FILE.FOPEN('UTL_DIR', 'EMP_DETAILS123.CSV', 'W', MAX_LINESIZE => 1022);
UTL_FILE.PUT_LINE(V_FILE, 'HJKHGJGHJGJGJHGHJGFJTYRYYJLJKFLJKSAGDASFAJFLJFLJALFJLASJFLAJLDFALFJDLFJLAJFLAJLDFJALDFJLAJFLDAJLFDJALFJALSJDFKKHJHKHKHKJHK');
UTL_FILE.FCLOSE(V_FILE);
V_FILE := UTL_FILE.FOPEN('UTL_DIR', 'EMP_DETAILS123.CSV', 'R', MAX_LINESIZE => 1024);
UTL_FILE.GET_LINE(V_FILE, V_TEXT, 1024);
UTL_FILE.FCLOSE(V_FILE);
END;
/
FCOPY PROCEDURE:
  • This procedure copies a contiguous portion of the file to a newly created file.
  • The source file opened in read mode
  • The destination file opened in write mode
  • A starting and end line nos are optionally specified to copy a portion of the file
SYNTAX:
UTL_FILE.FCOPY(LOCATION         VARCHAR2,
FILENAME VARCHAR2,
DEST_DIR VARCHAR2,
DEST_FILE VARCHAR2,
START_LINE PLS_INTEGER DEFAULT 1,
END_LINE PLS_INTEGER DEFAULT NULL);
PARAMETERDESCRIPTION
LOCATIONDirectory location of the source file
FILENAMESource file to be copied
DEST_DIRDestination directory where the destination file is created
DEST_FILEDestination file created from source file
START_LINELine no from which copying will be started
END_LINELine no post which copying will be stopper
FREMOVE PROCEDURE:

If you have sufficient privileges then with this procedure you can delete a disk file.

SYNTAX:
UTL_FILE.FREMOVE(LOCATION       VARCHAR2,
FILENAME VARCHAR2);
PARAMETERDESCRIPTION
LOCATIONDirectory location of a file. DIRECTORY_NAME FROM ALL_DIRECTORIES.
FILENAMEName of the file to be deleted.
FRENAME PROCEDURE:

This procedure rename an existing file to a new filename.

SYNTAX:
UTL_FILE.RENAME(LOCATION        VARCHAR2,
FILENAME VARCHAR2,
DEST_DIR VARCHAR2,
DEST_FILE VARCHAR2,
OVERRITE BOOLEAN DEFAULT FALSE);
PRARMETERDESCRIPTION
LOCATIONName of the directory location of the source file
FILENAMEName of the source file to be renamed
DEST_DIRDestination directory of the destination file
DEST_FILENew name of the file
OVERRITEThe default is FALSE
FFLUSH PROCEDURE:
  • This file physically writes the pending data into the file identified by the file handle.
  • Normally data being copied to file are buffered.
  • The data must be terminated with a new line character.
SYNTAX:
UTL_FILE.FFLUSH(FILE FILE_TYPE),
INVALID_MAXLINESIZE EXCEPTION;
ASSOCIATED EXCEPTIONS:
  • INVALID_FILEHANDLE
  • INVALID_OPERATION
  • WRITE_ERROR
FSEEK PROCEDURE:

This procedure adjust the file pointer forward and backward within the file by the no of bytes specified.

SYNTAX:
UTL_FILE(FID               UTL_FILE.FILE_TYPE,
ABSOLUTE_OFFSET PLS_INTEGER DEFAULT NULL,
RELATIVE_OFFSET PLS_INTEGER DEFAULT NULL);
PARAMETERDESCRIPTION
FIDThe file ID
ABSOLUTE_OFFSETAbsolute location to which to seek
RELATIVE_OFFSETThe no of bytes to seek forward or backward.
Possible values are:
Positive -> Forward
Negative -> Backward
Zero -> Current Position
NULL -> Default
FGETATTR PROCEDURE:

This procedure reads and returns the attributes of a disk file.

SYNTAX:
UTL_FILE.FGETATTR(LOCATION          VARCHAR2,
FILENAME VARCHAR2,
EXISTS OUT BOOLEAN,
FILE_LENGTH OUT NUMBER,
BLOCKSIZE OUT NUMBER);

PARAMETERDESCTIPTION
LOCATIONDirectory location of the source file
FILENAMEName of the file to be examined
EXISTSCheck whether the file is exists or not
FILE_LENGTHLength of the file in byte. If its null then file does not exists.
BLOCKSIZEThe file blocksize in bytes. If its null then the file does not exists.
FGETPOS FUNCTION OF UTL_FILE PACKAGE:

This function returns the current relative offset position within a file in bytes.

SYNTAX:
UTL_FILE.GETOPS(FILEID FILE_TYPE) RETURN PLS_INTEGER;