//
you're reading...
PL/SQL

PL/SQL Package : UTL_FILE

Introduced in Oracle 7.3, UTL_FILE PL/SQL package provides a functionality of reading and writing text file into operating system files. That means you can load external data from other source to database or can generate report from your PL/SQL code and write to text file on operating system.

UTL_FILE package provides access to text file on the OS of the database server. You can use one of the following approaches to restrict access to text file from database server.

    • Use the UTL_FILE_DIR parameter in init.ora initialization file to specify the accessible directory for the UTL_FILE functions to read or write operation
      UTL_FILE_DIR ='E:\app\madhurendra\wr_file'   
      
    • Use the CREATE DIRECTORY statement [ORACLE 9i or above]to associate an operating system directory with alias to provide read/write operation
      CREATE DIRECTORY my_demo_dir AS 'E:\app\madhurendra\wr_file';
      GRANT READ, WRITE ON DIRECTORY my_demo_dir TO user$plsql;  
      

If want to grant read and write privilege to public, replace user$plsql with public.

The main difference between two option is with first directory is accessible to all, since parameter in init.ora file is valid for all users in database and with directory object feature you can restrict access to the directory.  The latter option is recommended by ORACLE but you must have a CREATE ANY DIRECTRY privilege to create a directory.

Followings are some procedures, functions and exceptions of the UTL_FILE package.

DATA TYPE

FILE_TYPE          Data type for a file-handle

PROCEDURES & FUCTIONS

IS_OPEN            Checks whether file is open or not. Returns TRUE if open and FALSE if close.
FOPEN              Opens a file for read or write.
FCLOSE             Closes an open file
FCLOSE_ALL         Closes all open file for the current session
NEW_LINE           Insert a new line terminator to a file
PUT_LINE           Write strings of text followed by a new line terminator
PUT                Same as PUT_LINE but don’t place a new line terminator.
PUTF               Writes formatted text into file
GET_LINE           Read a line from file. The maximum output
FFLUSH             Writes all the buffered data into file

EXCEPTIONS

INVALID_PATH       File location is not valid
INVALID_MODE       OPEN_FILE mode is not valid. [Specific to FOPEN]
INVALID_FILENAME   Filename is not valid
READ_ERROR         Operating system error occurred during the read operation.
WRITE_ERROR        Operating system error occurred during the write operation.
FILE_OPEN          Operation failed because the file is already open.

These exceptions must be prefixed with UTL_FILE. Apart from that UTL_FILE can also raise pre-defined exceptions like NO_DATA_FOUND and VALUE_ERROR.

Followings are a couple of very simple example of UTL_FILE package:

In the following anonymous block, data is written by UTL_FILE package to a text file ‘my_f_file.txt’.

DECLARE
  v_file UTL_FILE.FILE_TYPE;
BEGIN
  v_file := UTL_FILE.FOPEN('MY_DEMO_DIR', 'my_f_file.txt', 'w');
  UTL_FILE.PUT_LINE(v_file, 'Hello World!');
  UTL_FILE.PUT_LINE(v_file, 'I stared using UTL_FILE');
  UTL_FILE.FCLOSE(v_file);
END;
/

NOTE:
FOPEN takes the following parameters:

    • location: name of a directory defined by a CREATE DIRECTORY statement, or an existing directory path specified by using the utl_file_dir database parameter
    • filename: Specifies the name of the file with extension and without path information
    • open_mode: Specifies how the file is to be opened. Values are:
        • R‘ for reading text
        • W‘ for writing text
        • A‘ for appending text
    • max_linesize: Maximum number of characters for each line, including the newline character. If unspecified, Oracle supplies a default value of 1024.

Procedure file_read reads data from the file and prints them using dbms_output. It also has exception section which has one pre-defined PL/SQL exception NO_DATA_FOUND and ACCESS_DENIED is specific to UTL_FILE package. Since we are reading a file in a loop, the NO_DATA_FOUND exception is used to exit the loop when there nothing to read.

CREATE OR REPLACE PROCEDURE file_read
AS
  v_file UTL_FILE.FILE_TYPE;
  v_line VARCHAR2(70);
BEGIN
  v_file := UTL_FILE.FOPEN ('MY_DEMO_DIR', 'my_f_file.txt', 'r');
   LOOP
      UTL_FILE.GET_LINE (v_file, v_line);
      DBMS_OUTPUT.PUT_LINE(v_line);
   END LOOP;

EXCEPTION
       WHEN NO_DATA_FOUND THEN
            DBMS_OUTPUT.PUT_LINE(CHR(10));
            DBMS_OUTPUT.PUT_LINE('READING OPERATION FINISHED....');
	     UTL_FILE.FCLOSE(v_file);

       WHEN UTL_FILE.ACCESS_DENIED THEN
            DBMS_OUTPUT.PUT_LINE(CHR(10));
            DBMS_OUTPUT.PUT_LINE('DIRECTORY ACCESS DENIED....');

END;

user$plsql@O11R2>exec file_read
Hello World!
I stared using UTL_FILE

READING OPERATION FINISHED....

PL/SQL procedure successfully completed.

Revoke read privilege on directory my_demo_dir from user user$plsql.

dbadmin@O11R2>REVOKE READ ON DIRECTORY my_demo_dir FROM hr;
Revoke succeeded.

user$plsql@O11R2>exec file_read

DIRECTORY ACCESS DENIED....

PL/SQL procedure successfully completed.

DOCUMENT LINK:

Advertisements

Discussion

No comments yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: