Sayantan's Blog On 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 NAME	DESCRIPTION
INVALID_PATH	File location is invalid
INVALID_MODE	The OPEN_MODE parameter of FOPEN is invalid
INVALID_FILEHANDLE	File handle is invalid
INVALID_OPERATION	File could not opened or operated on as requested
READ_ERROR	Operating system error occurred during read operation
WRITE_ERROR	Operating system error occurred during write operation
INTERNAL_ERROR	Unspecified PLSQL error
FILE_OPEN	Requested operation is failed as the file is opened
INVALID_MAXLINESIZE	The MAX_LINESIZE value for FOPEN() is invalid. It should be within range of 1 to 32767
INVALID_FILENAME	FILENAME parameter is invalid
ACCESS_DENIED	Permission to access the file location is denied
DELETE_FAILED	Requested delete operation is failed
RENAME_FAILED	Requested file rename operation is failed

IMPORTANT SUBPROGRAMS OF UTL_FILE:

• FOPEN FUNCTION
• IS_OPEN FUNCTION
• FCLOSE PROCEDURE
• FCLOSE_ALL PROCEDURE
• NEW_LINE PROCEDURE
• PUT PROCEDURE
• PUT_LINE PROCEDURE
• GET_LINE PROCEDURE
• FCOPY PROCEDURE
• FREMOVE PROCEDURE
• FRENAME PROCEDURE
• FFLUSH PROCEDURE
• FSEEK PROCEDURE
• FGETATTR PROCEDURE
• FGETPOS FUNCTION

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;

PARAMETER	DESCRIPTION
LOCATION	Directory location of file
FILENAME	Name of the file with extension
OPEN_MODE	Mode 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_LINESIZE	Maximum 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);

PARAMETER	DESCRIPTION
FILE	Active filehandle returned by FOPEN
LINES	No 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);

PARAMETER	DESCRIPTION
FILE	Active file handle. The file must be opened in write mode
BUFFER	Buffer 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);

PARAMETER	DESCRIPTION
FILE	Active file handle returned by an FOPEN call
BUFFER	Text buffer that contains the line to be written to the file
AUTOFLUSH	Flushes 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);

PARAMETER	DESCRIPTION
FILE	Active file handle return by an FOPEN call
BUFFER	Data buffer read a line from the file
LEN	The 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);

PARAMETER	DESCRIPTION
LOCATION	Directory location of the source file
FILENAME	Source file to be copied
DEST_DIR	Destination directory where the destination file is created
DEST_FILE	Destination file created from source file
START_LINE	Line no from which copying will be started
END_LINE	Line 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); 

PARAMETER	DESCRIPTION
LOCATION	Directory location of a file. DIRECTORY_NAME FROM ALL_DIRECTORIES.
FILENAME	Name 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);

PRARMETER	DESCRIPTION
LOCATION	Name of the directory location of the source file
FILENAME	Name of the source file to be renamed
DEST_DIR	Destination directory of the destination file
DEST_FILE	New name of the file
OVERRITE	The 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);

PARAMETER	DESCRIPTION
FID	The file ID
ABSOLUTE_OFFSET	Absolute location to which to seek
RELATIVE_OFFSET	The 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);

PARAMETER	DESCTIPTION
LOCATION	Directory location of the source file
FILENAME	Name of the file to be examined
EXISTS	Check whether the file is exists or not
FILE_LENGTH	Length of the file in byte. If its null then file does not exists.
BLOCKSIZE	The 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;

RELATED TOPICS:

• DBMS_OUTPUT
• DBMS_LOCK
• DBMS_UTILITY
• DBMS_SQL