The Oracle supplied PL/SQL package UTL_FILE used to read and write operating system files that are located on the database server.

UTL_FILE

The Oracle Directory should be created as follows:

CONN SYS/SYS_PWORD AS SYSDBA 
CREATE OR REPLACE DIRECTORY ext_tab_dir AS 'C:\External_Tables';  
GRANT READ,WRITE ON DIRECTORY ext_tab_dir TO scott; 

Setting the init.ora Parameters:

utl_file_dir=C:\External_Tables 

UTL_FILE Commands

  1. UTL_FILE.FILE_TYPE : The datatype that can handle UTL File type variable.

  2. UTL_FILE.FOPEN : Function to open a file for read or write operations. FOPEN function accepts 4 arguments-
    • file_location[ ext_tab_dir ]
    • file_name[ emp.csv ]
    • open_mode [ i.e. 'R' or 'W' ]
    • max_linesize [ Optional field, accepts BINARY_INTEGER defining the linesize of read or write DEFAULT is NULL ]

  3. UTL_FILE.FOPEN_NCHAR : Function to open a multi byte character file for read or write operations. Same as FOPEN.

  4. UTL_FILE.FCLOSE: Close a file. FCLOSE accepts 1 argument-
    • file [ utl_type file variable ]

  5. UTL_FILE.FCLOSE_ALL: Closes all files.

  6. UTL_FILE.GET_LINE : Reads a Line from a file. GET_LINE function accepts 2 arguments-
    • file [ utl_type file variable ]
    • len [ String variable to store the line read ]

  7. UTL_FILE.GETLINE_NCHAR : Reads a Line from a multi-byte character file. Same as GET_LINE.

  8. UTL_FILE.PUT : Writes a string to a file. PUT function accepts 3 arguments-
    • file [ utl_type file variable ]
    • str [ String variable to write to file ]
    • autoflush [ BOOLEAN variable DEFAULT is FALSE ]

  9. UTL_FILE.PUT_NCHAR : Writes a unicode string to a file. Same as PUT.

  10. UTL_FILE.PUT_LINE : Writes a line to a file and appends a newline character. PUT_LINE function accepts 3 arguments-
    • file [ utl_type file variable ]
    • str [ String variable to write to file ]

  11. UTL_FILE.PUT_LINE_NCHAR : Writes a unicode line to a file and appends a newline character

  12. UTL_FILE.NEW_LINE : Writes one or more new line character to a file. NEW_LINE function accepts 2 arguments-
    • file [ utl_type file variable ]
    • lines [ Number of new line characters ].

  13. UTL_FILE.IS_OPEN: Returns True if the file is Open Otherwise False. IS_OPEN accepts 1 argument-
    • file [utl_type file variable].

  14. UTL_FILE.FFLUSH : Writes pending data to the file. FFLUSH accepts 1 argument-
    • file [utl_type file variable].

UTL_FILE Exceptions

  • utl_file.invalid_filename
  • utl_file.access_denied
  • utl_file.file_open
  • utl_file.invalid_path
  • utl_file.invalid_mode
  • utl_file.invalid_filehandle
  • utl_file.invalid_operation
  • utl_file.read_error
  • utl_file.write_error


Have a question on this subject?

Ask questions to our expert community members and clear your doubts. Asking question or engaging in technical discussion is both easy and rewarding.

Are you on Twitter?

Start following us. This way we will always keep you updated with what's happening in Data Analytics community. We won't spam you. Promise.

  • Database Performance Tuning

    This article tries to comprehensively list down many things one needs to know for Oracle Database Performance Tuning. The ultimate goal of this document is to provide a generic and comprehensive guideline to Tune Oracle Databases from both...

  • Oracle Analytic Functions

    Oracle Analytic Functions compute an aggregate value based on a group of rows. It opens up a whole new way of looking at the data. This article explains how we can unleash the full potential of this.

  • Different Methods to move data from Oracle to External File

    How to push data from Oracle Table to external files? Well, external tables in Oracle provides a way to move data in as well as out of the database with the help of SQL*Loader and Data Pump functionality.

  • Understanding Oracle QUERY PLAN - A 10 minutes guide

    Confused about how to understand Oracle Query Execution Plan? This 10 minutes step by step primer is the first of a two part article that will teach you exactly the things you must know about Query Plan.

  • Oracle External Tables

    The Oracle external tables feature allows us to access data in external sources as if it is a table in the database. This is a very convenient and fast method to retrieve data from flat files outside Oracle database.

  • How to find out Which User is Running what SQL Query in Oracle database?

    Do you wonder how to get information on all the active query in the Oracle database? Do you want to know what query is executed by which user and how long is it running? Here is how to do it!

  • Oracle Spool

    Oracle Spool for SQLplus command line utility, can be used in conjunction with OS to export data from Oracle to flat file.

  • Oracle AUTOTRACE Explained - A 10 Minute Guide

    AUTOTRACE is a beautiful utility in Oracle that can help you gather vital performance statistics for a SQL Query. You need to understand and use it for SQL Query Tuning. Here is how!

  • Understanding Oracle QUERY PLAN - Part2 (Exploring SQL Joins)

    This is the second part of the article Understanding Oracle Query Plan. In this part we will deal with SQL Joins.

  • Oracle UTL_FILE

    The Oracle supplied PL/SQL package UTL_FILE used to read and write operating system files that are located on the database server.