Oracle: Oracle UTL_FILE with Examples

Definition:
In Oracle PL/SQL, UTL_FILE is an Oracle supplied package which is used for file operations (read and write) in conjunction with the underlying operating system. 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.

In earlier versions of Oracle, the parameter UTL_FILE_DIR was used to specify the file location path. This parameter is now deprecated and directory creation method is recommended.

UTL_FILE subprograms are listed as below.

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 - Opens a file for input or output. FOPEN takes the following parameters:
the file location, the filename, the open_mode and the max_linesize.
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 Usage:

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

--------------SYSDBA-----------------------

SQL> CREATE DIRECTORY MYDIR AS 'C:\TESTLOC';

Directory created.



SQL> GRANT READ, WRITE ON DIRECTORY MYDIR TO SCOTT

Grant succeeded.



--------------SCOTT-------------------------

 

DECLARE

  L_HANDLER UTL_FILE.FILE_TYPE;

BEGIN

  L_HANDLER := UTL_FILE.FOPEN('MYDIR', 'SYS.txt', 'W');

  UTL_FILE.PUTF(L_HANDLER, 'UTL_FILE write mode demonstration');

  UTL_FILE.FCLOSE(L_HANDLER);

END;



PL/SQL procedure successfully completed.

Note: O/S permissions are those of the user 'Oracle' ... not the schema owner or connected user

Source

{ORACLE_HOME}/rdbms/admin/utlfile.sql

First Availability

7.3.4

Data Types

-- file handle used in the block declaration section
TYPE file_type IS RECORD (
id BINARY_INTEGER, datatype BINARY_INTEGER, byte_mode BOOLEAN);

Dependencies

SELECT name FROM dba_dependencies WHERE referenced_name = 'UTL_FILE'
UNION
SELECT referenced_name FROM dba_dependencies WHERE name = 'UTL_FILE');

Exceptions

Exception Name	Error Code	Reason
access_denied	29289	Access to the file has been denied by the operating system
charsetmismatch	29298	A file is opened using FOPEN_NCHAR, but later I/O operations use nonchar functions such as PUTF or GET_LINE
delete_failed	29291	Unable to delete file
file_open	?	File is already open
internal_error	29286	Unhandled internal error in the UTL_FILE package
invalid_filehandle	29282	File handle does not exist
invalid_filename	29288	A file with the specified name does not exist in the path
invalid_maxlinesize	29287	The MAX_LINESIZE value for FOPEN() is invalid; it should be within the range 1 to 32767
invalid_mode	29281	The open_mode parameter in FOPEN is invalid
invalid_offset	29290	The ABSOLUTE_OFFSET parameter for FSEEK() is invalid; it should be greater than 0 and less than the total number of bytes in the file
invalid_operation	29283	File could not be opened or operated on as requested
invalid_path	29280	Specified path does not exist or is not visible to Oracle
read_error	29284	Unable to read file
rename_failed	29292	Unable to rename file
write_error	29285	Unable to write to file

init.ora Parameters (deprecated)

utl_file_dir=<directory_path_and_name>

utl_file_dir=c:\oraload
utl_file_dir=c: emp
utl_file_dir=*

Open Modes

A	Append Text
AB	Append Byte Mode
R	Read Text
RB	Read Byte Mode
W	Write Text
WB	Write Byte Mode

Security Model

Execute is granted to PUBLIC which is a security risk. It is recommended that this grant be revoked immediately following installation.

conn / as sysdba

REVOKE execute ON utl_file FROM public;

Demo Setup

O/S Directory Creation

mkdir c:\oraload

Oracle Directory Creation

CREATE DIRECTORY oraload AS 'c:\oraload\';

GRANT READ,WRITE ON DIRECTORY oraload TO UWCLASS;

File To Create: test.txt

Daniel,Morgan
Jack,Cline

FCLOSE

Close named file

utl_file.fclose(file IN OUT file_type);

see FOPEN demo

FCLOSE_ALL

Close all files

utl_file.fclose_all;

set serveroutput on

DECLARE
vInHandle utl_file.file_type;
vOutHandle utl_file.file_type;
BEGIN
vInHandle := utl_file.fopen('ORALOAD', 'test.txt', 'R');
vOutHandle := utl_file.fopen('ORALOAD', 'out.txt', 'W');

IF utl_file.is_open(vInHandle) THEN
utl_file.fclose_all;
dbms_output.put_line('Closed All');
END IF;
END fopen;
/

FCOPY

Copies a contiguous portion of a file to a newly created file

utl_file.fcopy(
src_location IN VARCHAR2,
src_filename IN VARCHAR2,
dest_location IN VARCHAR2,
dest_filename IN VARCHAR2,
start_line IN BINARY_INTEGER DEFAULT 1,
end_line IN BINARY_INTEGER DEFAULT NULL);

BEGIN
utl_file.fcopy('ORALOAD', 'test.txt', 'ORALOAD', 'dump.txt');
END;
/

FFLUSH

Physically writes pending data to the file identified by the file handle

utl_file.fflush(file IN file_type);

See WRITE Demo Below

FGETATTR

Reads and returns the attributes of a disk file

utl_file.fgetattr(
location    IN VARCHAR2,
filename    IN VARCHAR2,
fexists     OUT BOOLEAN,
file_length OUT NUMBER,
block_size OUT BINARY_INTEGER);

set serveroutput on

DECLARE
ex    BOOLEAN;
flen NUMBER;
bsize NUMBER;
BEGIN
utl_file.fgetattr('ORALOAD', 'test.txt', ex, flen, bsize);

IF ex THEN
    dbms_output.put_line('File Exists');
ELSE
    dbms_output.put_line('File Does Not Exist');
END IF;
dbms_output.put_line('File Length: ' || TO_CHAR(flen));
dbms_output.put_line('Block Size: ' || TO_CHAR(bsize));
END fgetattr;
/

FGETPOS

Returns the current relative offset position within a file, in bytes

utl_file.fgetpos(file IN file_type) RETURN BINARY_INTEGER;

See READ_WRITE Demo Below

FOPEN

Open a file for read operations

utl_file.fopen(
file_location IN VARCHAR2,
file_name IN VARCHAR2,
open_mode IN VARCHAR2,
max_linesize IN BINARY_INTEGER DEFAULT NULL)
RETURN file_type;

DECLARE
vInHandle utl_file.file_type;
vNewLine VARCHAR2(250);
BEGIN
vInHandle := utl_file.fopen('ORALOAD', 'test.txt', 'R');
LOOP
    BEGIN
      utl_file.get_line(vInHandle, vNewLine);
      dbms_output.put_line(vNewLine);
    EXCEPTION
      WHEN OTHERS THEN
        EXIT;
    END;
END LOOP;
utl_file.fclose(vInHandle);
END fopen;
/

Open A File For Write Operations

<file_handle> := utl_file.fopen(<file_location, file_name, 'W')

FOPEN_NCHAR

Open a file for multibyte characters

Note: since NCHAR contains mutibyte character, it is recommended that the max_linesize be less than 6400.

utl_file.fremove(
location     IN VARCHAR2,
filename     IN VARCHAR2,
open_mode    IN VARCHAR2,
max_linesize IN BINARY_INTEGER DEFAULT NULL)
RETURN file_type;

TBD

FREMOVE

Delete An Operating System File

utl_file.fremove(location IN VARCHAR2, filename IN VARCHAR2);

-- dump.txt is created in the FCOPY demo
BEGIN
utl_file.fremove('ORALOAD', 'dump.txt');
END fremove;
/

FRENAME

Rename An Operating System File

utl_file.frename (
src_location IN VARCHAR2,
src_filename IN VARCHAR2,
dest_location IN VARCHAR2,
dest_filename IN VARCHAR2,
overwrite IN BOOLEAN DEFAULT FALSE);

BEGIN
utl_file.frename('ORALOAD','test.txt','ORALOAD','x.txt',TRUE);
END frename;
/

FSEEK

Adjusts the file pointer forward or backward within the file by the number of bytes specified

utl_file.fseek(
file            IN OUT file_type,
absolute_offset IN     BINARY_INTEGER DEFAULT NULL,
relative_offset IN     BINARY_INTEGER DEFAULT NULL);

See Read-Write demo

GETLINE

Read a Line from a file

utl_file.getline(
file IN FILE_TYPE,
buffer OUT VARCHAR2,
len IN BINARY_INTEGER DEFAULT NULL);

See Read demos

GETLINE_NCHAR

Read a line from a file containing multi-byte characters

utl_file.getline_nchar(
file IN FILE_TYPE,
buffer OUT NVARCHAR2,
len IN BINARY_INTEGER DEFAULT NULL);

See Read demos

GET_RAW

Reads a RAW string value from a file and adjusts the file pointer ahead by the number of bytes read

utl_file.get_raw(file IN file_type,
buffer OUT NOCOPY RAW,
len IN BINARY_INTEGER DEFAULT NULL);

See UTL_MAIL demo

IS_OPEN

Returns True If A File Handle Is Open: Otherwise False

utl_file.is_open(file IN FILE_TYPE) RETURN BOOLEAN;

See FCLOSE_ALL Demo

NEW_LINE

Writes one or more operating system-specific line terminators to a file

utl_file.new_line(file IN FILE_TYPE, lines IN NATURAL := 1);

See Read Demo

PUT

Writes a string to a file

utl_file.put(file IN FILE_TYPE, buffer IN VARCHAR2);

See WRITE Demo Below

PUTF

A PUT procedure with formatting

utl_file.putf(
file   IN file_type,
format IN VARCHAR2,
arg1   IN VARCHAR2 DEFAULT NULL,
arg2   IN VARCHAR2 DEFAULT NULL,
arg3   IN VARCHAR2 DEFAULT NULL,
arg4   IN VARCHAR2 DEFAULT NULL,
arg5   IN VARCHAR2 DEFAULT NULL);

See Write demo

PUT_LINE

Writes a line to a file. Appends an operating system-specific line terminator

utl_file.put_line(
file IN FILE_TYPE,
buffer IN VARCHAR2,
autoflush IN BOOLEAN DEFAULT FALSE);

See READ-WRITE Demo Below

PUT_NCHAR

Writes a Unicode string to a file

utl_file.put_nchar(file IN file_type, buffer IN NVARCHAR2);

TBD

PUT_RAW

Accepts as input a RAW data value and writes the value to the output buffer

utl_file.put_raw(
file IN file_type,
buffer IN RAW,
autoflush IN BOOLEAN DEFAULT FALSE);

See EXTRACT_BLOB Demo Below

PUT_LINE_NCHAR

Writes a Unicode line to a file

utl_file.put_line_nchar(file IN file_type, buffer IN NVARCHAR2);

TBD

PUTF_NCHAR

Writes a Unicode string to a file

utl_file.putf_nchar(
file   IN file_type,
format IN NVARCHAR2,
arg1   IN NVARCHAR2 DEFAULT NULL,
arg2   IN NVARCHAR2 DEFAULT NULL,
arg3   IN NVARCHAR2 DEFAULT NULL,
arg4   IN NVARCHAR2 DEFAULT NULL,
arg5   IN NVARCHAR2 DEFAULT NULL);

TBD

UTL_FILE Demos

Read Demo

create table test (
fld1 VARCHAR2(20),
fld2 VARCHAR2(20));

CREATE OR REPLACE PROCEDURE read_demo(file_name VARCHAR2) IS
vSFile   utl_file.file_type;
vNewLine VARCHAR2(200);
BEGIN
  vSFile := utl_file.fopen('ORALOAD', file_name,'r');

  IF utl_file.is_open(vSFile) THEN
    LOOP
      BEGIN
        utl_file.get_line(vSFile, vNewLine);

        IF vNewLine IS NULL THEN
          EXIT;
        END IF;

        INSERT INTO test
        (fld1, fld2)
        VALUES
        (vNewLine, file_name);
      EXCEPTION
        WHEN NO_DATA_FOUND THEN
          EXIT;
      END;
    END LOOP;
    COMMIT;
END IF;
utl_file.fclose(vSFile);
utl_file.frename('ORALOAD', 'test.txt', 'ORALOAD', 'x.txt', TRUE);
EXCEPTION
WHEN utl_file.invalid_mode THEN
   RAISE_APPLICATION_ERROR (-20051, 'Invalid Mode Parameter');
WHEN utl_file.invalid_path THEN
   RAISE_APPLICATION_ERROR (-20052, 'Invalid File Location');
WHEN utl_file.invalid_filehandle THEN
   RAISE_APPLICATION_ERROR (-20053, 'Invalid Filehandle');
WHEN utl_file.invalid_operation THEN
   RAISE_APPLICATION_ERROR (-20054, 'Invalid Operation');
WHEN utl_file.read_error THEN
   RAISE_APPLICATION_ERROR (-20055, 'Read Error');
WHEN utl_file.internal_error THEN
   RAISE_APPLICATION_ERROR (-20057, 'Internal Error');
WHEN utl_file.charsetmismatch THEN
   RAISE_APPLICATION_ERROR (-20058, 'Opened With FOPEN_NCHAR
    But Later I/O Inconsistent');
  WHEN utl_file.file_open THEN
  RAISE_APPLICATION_ERROR (-20059, 'File Already Opened');
WHEN utl_file.invalid_maxlinesize THEN
   RAISE_APPLICATION_ERROR(-20060,'Line Size Exceeds 32K');
  WHEN utl_file.invalid_filename THEN
  RAISE_APPLICATION_ERROR (-20061, 'Invalid File Name');
WHEN utl_file.access_denied THEN
  RAISE_APPLICATION_ERROR (-20062, 'File Access Denied By');
  WHEN utl_file.invalid_offset THEN
  RAISE_APPLICATION_ERROR (-20063,'FSEEK Param Less Than 0');
WHEN OTHERS THEN
  RAISE_APPLICATION_ERROR (-20099, 'Unknown UTL_FILE Error');
END read_demo;
/

Read-Write Demo

CREATE OR REPLACE PROCEDURE rw_demo IS
InFile   utl_file.file_type;
OutFile utl_file.file_type;
vNewLine VARCHAR2(4000);
i        PLS_INTEGER;
j        PLS_INTEGER := 0;
SeekFlag BOOLEAN := TRUE;
BEGIN
-- open a file to read
InFile := utl_file.fopen('ORALOAD', 'in.txt','r');
-- open a file to write
OutFile := utl_file.fopen('ORALOAD', 'out.txt', 'w');

-- if the file to read was successfully opened
IF utl_file.is_open(InFile) THEN
    -- loop through each line in the file
    LOOP
      BEGIN
        utl_file.get_line(InFile, vNewLine);

        i := utl_file.fgetpos(InFile);
        dbms_output.put_line(TO_CHAR(i));

        utl_file.put_line(OutFile, vNewLine, FALSE);
        utl_file.fflush(OutFile);

        IF SeekFlag = TRUE THEN
          utl_file.fseek(InFile, NULL, -30);
          SeekFlag := FALSE;
        END IF;
      EXCEPTION
        WHEN NO_DATA_FOUND THEN
          EXIT;
      END;
    END LOOP;
    COMMIT;
END IF;
utl_file.fclose(InFile);
utl_file.fclose(OutFile);
EXCEPTION
WHEN OTHERS THEN
    RAISE_APPLICATION_ERROR (-20099, 'Unknown UTL_FILE Error');
END rw_demo;
/

Write Demo

This demo writes out a Korn Shell script to run SQL*Loader

CREATE OR REPLACE PROCEDURE create_cmd_file AS
CURSOR sll_cur IS
SELECT loadname, loadfilename, loadfiledate
FROM sqlldrlog
WHERE run_status = 'B'
ORDER BY sequenceno;

sll_rec   sll_cur%ROWTYPE;

DirLoc    VARCHAR2(30) := 'ORALOAD';
LFileName sqlldrlog.loadfilename%TYPE;
LFileDate sqlldrlog.loadfiledate%TYPE;
ctl_file VARCHAR2(500);
dat_file VARCHAR2(500);
log_file VARCHAR2(500);
bad_file VARCHAR2(500);
Emsg      VARCHAR2(90) := 'Load CREATE_CMD_FILE Failed with ERROR ';
vSubject := 'SQL Loader Failure Notification';
DayFile  utl_file.file_type;
LogFile  utl_file.file_type;
BEGIN
DayFile := utl_file.fopen(DirLoc, 'execsqlldr.ksh','W');
LogFile := utl_file.fopen(DirLoc, 'log_list.dat','W');

OPEN sll_cur;
LOOP
    FETCH sll_cur INTO sll_rec;
    EXIT WHEN sll_cur%NOTFOUND;

    ctl_file := '/data/cload/ctl/'|| LOWER(sll_rec.loadname) || '.ctl \';

    dat_file := '/data/cload/data/' || sll_rec.loadfilename || ' \';

    log_file := '/data/cload/log/' || LOWER(sll_rec.loadname) || '_' || TO_CHAR(sll_rec.loadfiledate, 'YYYYMMDD') || '.log \';

        bad_file := '/data/cload/bad/' || LOWER(sll_rec.loadname) || '_' || TO_CHAR(sll_rec.loadfiledate, 'YYYYMMDD') || '.bad';

    utl_file.putf(dayfile, 'sqlldr userid=%s control=%s data=%s log=%s bad=%s ', '/ \',
ctl_file, dat_file, log_file, bad_file);

    log_file := '/data/cload/log/' || LOWER(sll_rec.loadname) || '_' || TO_CHAR(sll_rec.loadfiledate, 'YYYYMMDD') || '.log';

    utl_file.putf(logfile,'%s ',log_file);
END LOOP;
utl_file.fclose(DayFile);
utl_file.fclose(LogFile);
EXCEPTION
WHEN utl_file.invalid_mode THEN
    vErrMsg := SQLERRM;
    vMessage := Emsg || '-20051, Invalid Option';
    sp_SendEmail (0, 'dba@psoug.org', vSubject, vMessage);
WHEN utl_file.invalid_path THEN
    vErrMsg := SQLERRM;
    vMessage := Emsg || '-20052, Invalid Path';
    sp_SendEmail (0, 'dba@psoug.org', vSubject, vMessage);
WHEN utl_file.invalid_filehandle THEN
    vErrMsg := SQLERRM;
    vMessage := Emsg || '-20053, Invalid Filehandle';
    sp_SendEmail (0, 'dba@psoug.org', vSubject, vMessage);
WHEN utl_file.invalid_operation THEN
    vErrMsg := SQLERRM;
    vMessage := Emsg || '-20054, Invalid Operation';
    sp_SendEmail (0, 'dba@psoug.org', vSubject, vMessage);
WHEN utl_file.read_error THEN
    vErrMsg := SQLERRM;
    vMessage := Emsg || '-20055, Read Error';
    sp_SendEmail (0, 'dba@psoug.org', vSubject, vMessage);
WHEN utl_file.write_error THEN
    vErrMsg := SQLERRM;
    vMessage := Emsg || '-20056, Write Error';
    sp_SendEmail (0, 'dba@psoug.org', vSubject, vMessage);
WHEN utl_file.internal_error THEN
    vErrMsg := SQLERRM;
    vMessage := Emsg || '-20057, Internal Error';
WHEN OTHERS THEN
    vErrMsg := SQLERRM;
    vMessage := Emsg || vErrMsg;
    sp_SendEmail (0, 'dba@psoug.org', vSubject, vMessage);
END create_cmd_file;/

Extract BLOB Demo

CREATE OR REPLACE PROCEDURE blob2file(
pdname VARCHAR2, psname VARCHAR2, pfname VARCHAR2) IS

vblob   BLOB;
vstart NUMBER := 1;
bytelen NUMBER := 32000;
len     NUMBER;
my_vr   RAW(32000);
x       NUMBER;
l_output utl_file.file_type;
BEGIN
-- define output directory
l_output := utl_file.fopen('ORALOAD', pfname, 'WB', 32760);

-- get length of blob
SELECT dbms_lob.getlength(iblob)
INTO len
FROM pdm
WHERE dname = pdname
AND sname = psname
AND fname = pfname;

-- save blob length
x := len;

-- select blob into variable
SELECT iblob
INTO vblob
FROM pdm
WHERE dname = pdname
AND sname = psname
AND fname = pfname;

-- if small enough for a single write
IF len < 32760 THEN
    utl_file.put_raw(l_output,vblob);
    utl_file.fflush(l_output);
ELSE -- write in pieces
    vstart := 1;
    WHILE vstart < len
    LOOP
      dbms_lob.read(vblob,bytelen,vstart,my_vr);

      utl_file.put_raw(l_output,my_vr);
      utl_file.fflush(l_output)

      -- set the start position for the next cut
      vstart := vstart + bytelen;

      -- set the end position if less than 32000 bytes
      x := x - bytelen;
      IF x < 32000 THEN
        bytelen := x;
      END IF;
    END LOOP;
END IF;
utl_file.fclose(l_output);
END blob2file;
/

Oracle

Search This Blog

Thursday 7 June 2012

Oracle UTL_FILE with Examples

2 comments: