Translate

ORACLE UTL_FILE

How UTL_FILE PACKAGE used for File I/O Operations?

In Oracle PL/SQL, UTL_FILE is an Oracle supplied package which is used for file operations (read and write). UTL_FILE works for both server and client machine systems. A directory has to be created on the server, which points to the target file. For the files located on the server machine, the actual path can be given while creating the directory. For the files, which are located on the client machines, however, the relative path is required along with the client machine name.

In addition, the relative file path must be in shared mode with read and write access for the required users. A DBA must create the directory and then grant Read/Write access to the required users.

Process Flow:

In order to write to a file, you will (in most cases) perform the following steps:
  • Declare a file handle. This handle serves as a pointer to the file for subsequent calls to modules in the UTL_FILE package to manipulate the contents of this file.
  • Open the file with a call to FOPEN, which returns a file handle to the file. You can open a file to read, replace, or append text. 
  • Write data to the file using the PUT, PUTF, or PUT_LINE procedures.
  • Close the file with a call to FCLOSE. This releases resources associated with the file.
In order to read data from a file you will (in most cases) perform the following steps:
  •  Declare a file handle.
  •  Declare a VARCHAR2 string buffer that will receive the line of data from the file.
  •  Open the file using FOPEN in read mode.
  •   Use the GET_LINE procedure to read data from the file and into the buffer. To read all the     lines from a file, you would execute GET_LINE in a loop.
  •  Close the file with a call to FCLOSE.

The UTL_FILE package contains all of the modules and a special datatype to create file handles (UTL_FILE.FILE_TYPE).

UTL_FILE subprograms are listed as below.
  • FOPEN - Opens a file for input or output. FOPEN takes the following parameters:
    the File Location, Filename, OPEN_MODE and the Max_Linesize.
  • FCLOSE - Closes a file. 
  • FCLOSE_ALL - Closes all open file handles
  • FCOPY - Copies a contiguous portion of a file to a newly created file. Takes the following parameters:
    src_location, src_filename, dest_location, dest_filename, start_line, and end_line. 
  • FFLUSH - Physically writes all pending output to a file.
  • FGETATTR - Reads and returns the attributes of a disk file. Returns the following items about the file:
    location, filename, fexists (a boolean), file_length (in bytes), and block_size.
    The location must be either an existing directory on the server AND be in the utl_file_dir parameter, or it may be a directory. 
  • FGETPOS - Returns the current relative offset position within a file, in bytes as a binary_integer.
  • FOPEN_NCHAR - Opens a file in Unicode for input or output.
  • FREMOVE - Deletes a disk file, assuming that you have sufficient privileges. Takes the following parameters:
    location and filename
  • FRENAME - Renames an existing file to a new name, similar to the UNIX mv function. FRENAME takes the following parameters:
    the src_location, the src_filename, the dest_location, the dest_filename, and overwrite (a boolean). The overwrite parameter determines whether or not the file, if it already exists, will be overwritten.
  • FSEEK - Adjusts the file pointer forward or backward within the file by the number of bytes specified. FSEEK takes the following parameters:
    the file, the absolute_offset (a binary_integer), and the relative_offset (a binary_integer).
  • GET_LINE - Reads text from an open file. GET_LINE takes the following parameters:
    the file (record), buffer (varchar2), and len (a binary_integer).
  • GET_LINE_NCHAR - Reads text in Unicode from an open file. GET_LINE_NCHAR takes the following parameters:
    the file (record), buffer (nvarchar2), and len (a binary_integer).
  • GET_RAW - Reads a RAW string value from a file and adjusts the file pointer ahead by the number of bytes read. GET_RAW takes the following parameters:
    file (record), buffer (raw), len (a binary_integer)
  • IS_OPEN - Determines if a file handle refers to an open file.
  • NEW_LINE - Writes one or more operating system-specific line terminators to a file. NEW_LINE takes the following parameters:
    file (record), lines (a binary_integer).
  • PUT - Writes a string to a file. PUT takes the following parameters:
    file (record), buffer (a varchar2).
  • PUT_LINE - Writes a line to a file, and also appends an operating system-specific line terminator. If a line was already written, it starts the line with CR/LF. This implies that the file, when being written into, does not end with CR/LF. In Oracle 9i the maximum line length that can be written is 32K. PUT_LINE takes the following parameters:
    file (record), buffer (a varchar2).
  • PUT_LINE_NCHAR - Writes a Unicode line to a file. PUT_LINE takes the following parameters:
    file (record), buffer (a nvarchar2), autoflush (a boolean).
  • PUT_NCHAR - Writes a Unicode string to a file. PUT takes the following parameters:
    file (record), buffer (an nvarchar2).
  • PUTF - A PUT procedure with formatting.
  • PUTF_NCHAR - A PUT_NCHAR procedure with formatting, and writes a Unicode string to a file, with formatting.
  • PUT_RAW - Accepts as input a RAW data value and writes the value to the output buffer.

Example 1:

 In the below example, SYSDBA creates a directory TESTDIR and grants R/W access to the user Myuser. The user then creates a text file in the directory and writes a text into it.

SQL> Create Directory TESTDIR As 'F:\UTL_FILE_DATA';

Directory created.

SQL> Grant READ, WRITE On DIRECTORY TESTDIR To Myuser;

Grant succeeded.


SQL> Conn Myuser/Myuser
Connected.

SQL> DECLARE
  2  L_HANDLER UTL_FILE.FILE_TYPE;
  3  BEGIN
  4  L_HANDLER := UTL_FILE.FOPEN('TESTDIR', 'MYFILE.txt', 'W');
  5  UTL_FILE.PUTF(L_HANDLER, 'This is my First UTL_FILE Program');
  6  UTL_FILE.FCLOSE(L_HANDLER);
  7  END;
  8  /

PL/SQL procedure successfully completed.

File Output:
















Example 2: Another Example with Exception Handler:

SQL> DECLARE
  2  FileHandler UTL_FILE.FILE_TYPE;
  3  BEGIN
  4  FileHandler := UTL_FILE.FOPEN('TESTDIRR', 'TEST_FILE.txt', 'W'); 
  5  UTL_FILE.PUTF(FileHandler, 'Writing TO a file\n');
  6  UTL_FILE.FCLOSE(FileHandler);
  7  EXCEPTION
  8  WHEN UTL_FILE.INVALID_PATH THEN
  9  Raise_Application_Error(-20000, 'ERROR: Invalid PATH FOR file.');
 10  END;
 11  /
DECLARE
*
ERROR at line 1:
ORA-20000: ERROR: Invalid PATH FOR file.
ORA-06512: at line 9
/* We got Error Because TESTDIRR doesn't Exist*/

SQL> DECLARE
  2  FileHandler UTL_FILE.FILE_TYPE;
  3  BEGIN
  4  FileHandler := UTL_FILE.FOPEN('TESTDIR', 'TEST_FILE.txt', 'W');
  5  UTL_FILE.PUTF(FileHandler, 'Writing TO a file\n');
  6  UTL_FILE.FCLOSE(FileHandler);
  7  EXCEPTION
  8  WHEN UTL_FILE.INVALID_PATH THEN
  9  Raise_Application_Error(-20000, 'ERROR: Invalid PATH FOR file.');
 10  END;
 11  /

PL/SQL procedure successfully completed.

File Output:















Example 3:

SQL> DECLARE
  2  Fhandle  Utl_File.File_Type;
  3  Begin
  4  Fhandle := Utl_File.Fopen( 'TESTDIR','FILE_DATA.txt','W');
  5
  6  /* TESTDIR: File Location | FILE_DATA.txt: File Name | W: Open Mode: W- Write */
  7
  8  Utl_File.Put(Fhandle, 'How Are You Doing Today'|| CHR(10));
  9  Utl_File.Put(Fhandle, 'I am Doing Good');
 10  Utl_File.Fclose(Fhandle);
 11  Exception
 12  When Others Then
 13  Dbms_Output.Put_Line('ERROR: ' || SQLCODE|| ' - ' || SQLERRM);
 14  Raise;
 15  END;
 16  /

PL/SQL procedure successfully completed.

File Output:


















UTL_FILE Errors and How to Resolve Them
When you run UTL_FILE, you might encounter these errors – possible causes and fixes are given below.
1. PLS-00201: identifier ‘UTL_FILE’ must be declared

This error sometimes shows up when you run UTL_FILE for the first time on the database.Diagnosis and fix:
(a) Check that UTL_FILE package exists and is valid.
SQL> Select Owner  , Object_Type , Status From ALL_OBJECTS
  2  Where object_name = 'UTL_FILE';

OWNER                          OBJECT_TYPE         STATUS
------------------------------ ------------------- -------
SYS                            PACKAGE             VALID
SYS                            PACKAGE BODY        VALID
PUBLIC                         SYNONYM             VALID
APEX_030200                    SYNONYM             VALID

If you get a listing as above, all is well. Move to next step:
If the package status is invalid, RECOMPILE the package. 
If the package is not listed, run{ORACLE_HOME}/rdbms/admin/utlfile.sql to create it. 

(b) Check if the logged in user has execute privilege on UTL_FILE

A missing grant is the most likely cause of the PLS-00201 error.
SQL> Select Grantee From ALL_TAB_PRIVS
  2  Where table_name = 'UTL_FILE';

GRANTEE
------------------------------
PUBLIC

For UTL_FILE to work, the grantee should be either the user logged in, or PUBLIC. If this privilege is missing, log in as an admin user (e.g. SYS) and grant EXECUTE on UTL_FILE. Log back in as the application user, and check the execute privilege on UTL_FILE. The script should be able to recognize UTL_FILE now, without PLS-00201.

2. ORA-29280: invalid directory path


This indicates that the Oracle engine is unable to find the file location specified in UTL_FILE.FOPEN.
Diagnosis and fix:
(a) Check if ‘file location’ in the script has a reference to a valid Oracle directory


UTL_FILE uses Oracle directories, not OS directories. Do not write file location like this:
D:\App
Instead, login as SYS and create an Oracle directory with a reference to a valid OS directory path.
SQL> Create or Replace Directory UTL_DIR AS 'D:\App';
Directory created.
Grant read and write privilege to the application user (or PUBLIC) on the new directory.
SQL> Grant Read, Write On Directory UTL_DIR To Public;
Grant succeeded.

Note that the directory path can be case-sensitive on some operating systems like Unix.
(b) Check if ‘file location’ in the script exists on the Oracle server
The directory specified must exist on the Oracle server when the script is run. The ‘create directory’ command does not validate that the directory specified actually exists, nor does it create the directory on the server. It is the developer’s responsibility to specify a valid directory path.

 A typical mistake is to specify a *local* machine folder as the UTL_FILE directory when the Oracle database server is on another machine – this will not work. The UTL_FILE directory must exist on the *server* machine.
More things to watch out for when using the CREATE DIRECTORY command. Identify the file location:

SQL> Select Directory_Name , Directory_Path From All_Directories;
DIRECTORY_NAME   DIRECTORY_PATH
---------------- --------------
UTL_DIR          D:\TempPath

Fix the file location if required: login as SYS, and issue a create or replace directory:
SQL> Create or Replace Directory UTL_DIR As 'D:\App';
Directory created.
Log back in as the application user and verify the change:
SQL> Select Directory_Name , Directory_Path From All_Directories;
DIRECTORY_NAME   DIRECTORY_PATH
---------------- --------------
UTL_DIR          D:\App

(c) Check if ‘file location’ in the script has write permissions for the logged in user

SQL> Select Grantee , Privilege From All_Tab_Privs Where Table_Name = 'UTL_DIR';
GRANTEE   PRIVILEGE
--------- ------------------------
PUBLIC    READ
PUBLIC    WRITE

If you get a listing as above, all is well – move on to check step (d).
If you do not see WRITE permission granted to PUBLIC or to the logged in user, login as SYS and grant permissions.

SQL> Grant Read, Write On Directory UTL_DIR To Public;
Grant succeeded.

(d) Check if ‘file location’ in the script is written in uppercase


A directory object is a database object, and database object names are in UPPERCASE by default. Even if the ‘create directory’ command you issued had the directory name in lowercase, unless you put it within quotes, the directory name will be stored in UPPERCASE. Changing it to ‘UTL_DIR’ will resolve the error.

Get involved and leave your Comments in the Box Below. The more people get involved, the more we all benefit. So, leave your thoughts before you leave the page.

2 comments:

Subscribe to: Post Comments (Atom)