Oracle UTL_FILE

Oracle UTL_FILEVersion 10.2 General InformationNote: O/S permissions are those of the user 'Oracle' ... not the schema owner or connected userSource{ORACLE_HOME}/rdbms/admin/utlfile.sqlFirst Availability7.3.4Dependencies44 objects:

SELECT DISTINCT name
FROM (
  SELECT name
  FROM dba_dependencies
  WHERE referenced_name = 'UTL_FILE'
UNION
  SELECT referenced_name
  FROM dba_dependencies
  WHERE name = 'UTL_FILE');ExceptionsException NameError CodeReasonaccess_denied29289Access to the file has been denied by the operating systemcharsetmismatch29298A file is opened using FOPEN_NCHAR, but later I/O operations use nonchar functions such as PUTF or GET_LINEdelete_failed29291Unable to delete filefile_open?File is already openinternal_error29286Unhandled internal error in the UTL_FILE packageinvalid_filehandle29282File handle does not existinvalid_filename29288A file with the specified name does not exist in the pathinvalid_maxlinesize29287The MAX_LINESIZE value for FOPEN() is invalid; it should be within the range 1 to 32767invalid_mode29281The open_mode parameter in FOPEN is invalidinvalid_offset29290The ABSOLUTE_OFFSET parameter for FSEEK() is invalid; it should be greater than 0 and less than the total number of bytes in the fileinvalid_operation29283File could not be opened or operated on as requestedinvalid_path29280Specified path does not exist or is not visible to Oracleread_error29284Unable to read filerename_failed29292Unable to rename filewrite_error29285Unable to write to fileinit.ora Parametersutl_file_dirutl_file_dir=c:/oraload
utl_file_dir=c:/temp
utl_file_dir=*Open ModesAAppend TextABAppend Byte ModeRRead TextRBRead Byte ModeWWrite TextWBWrite Byte ModeRecord TypeHandle to a file. Used in the block declaration sectionTYPE file_type IS RECORD (id BINARY_INTEGER, datatype BINARY_INTEGER, byte_mode BOOLEAN); Demo SetupO/S Directory Creationmkdir c:/oraloadOracle Directory CreationCREATE DIRECTORY oraload AS 'c:/oraload/';

GRANT READ,WRITE ON DIRECTORY oraload TO UWCLASS;File To Create: test.txtDaniel,Morgan
Jack,ClineFile To Create: dump.txtDaniel,Morgan
Jack,ClineDemo Tablecreate table test (
fld1 VARCHAR2(20),
fld2 VARCHAR2(20));File To LoadDaniel,Morgan
Jack,Cline FCLOSEClose A File Opened By UTL_FILEutl_file.fclose(<file_handle>)see FOPEN demo FCLOSE_ALLClose All Files Opened By UTL_FILEutl_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;
/ FCOPYCopies a contiguous portion of a file to a newly created fileutl_file.fcopy (
location   IN VARCHAR2,
filename   IN VARCHAR2,
dest_dir   IN VARCHAR2,
dest_file  IN VARCHAR2,
start_line IN PLS_INTEGER DEFAULT 1,
end_line   IN PLS_INTEGER DEFAULT NULL);-- demo requires creating directory CTEMP ... see link at bottom of page

BEGIN
  utl_file.fcopy('ORALOAD', 'dump.txt', 'ORALOAD', 'didit.txt');
END;
/ FFLUSHPhysically writes pending data to the file identified by the file handleutl_file.fflush (<file_handle>);See Write demo FGETATTRReads and returns the attributes of a disk fileutl_file.fgetattr(
location    IN  VARCHAR2,
filename    IN  VARCHAR2,
exists      OUT BOOLEAN,
file_length OUT NUMBER,
blocksize   OUT NUMBER);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;
/ FGETPOSReturns the current relative offset position within a file, in bytesutl_file.fgetpos(fileid IN file_type) RETURN PLS_INTEGER;See Read-Write demo FOPENOpen A File For Read Operationsutl_file.fopen(
<file_location IN VARCHAR2>,
<file_name     IN VARCHAR2>,
<open_mode     IN VARCHAR2>,
<max_linesize  IN BINARY_INTEGER>)
RETURN <file_type_package_data_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_NCHAROpen a file to read or write a text file in Unicode instead of in the database charset FREMOVEDelete An Operating System Fileutl_file.fremove (location IN VARCHAR2, filename IN VARCHAR2);BEGIN
  utl_file.fremove('ORALOAD', 'dump.txt');
END fremove;
/ FRENAMERename An Operating System Fileutl_file.frename (
location  IN VARCHAR2,
filename  IN VARCHAR2,
dest_dir  IN VARCHAR2,
dest_file IN VARCHAR2,
overwrite IN BOOLEAN DEFAULT FALSE);BEGIN
  utl_file.frename('ORALOAD','test.txt','ORALOAD','x.txt',TRUE);
END frename;
/FSEEKAdjusts the file pointer forward or backward within the file by the number of bytes specifiedutl_file.fseek (
fid             IN utl_file.file_type,
absolute_offset IN PL_INTEGER DEFAULT NULL,
relative_offset IN PLS_INTEGER DEFAULT NULL);See Read-Write demo GETLINERead a Line from a fileutl_file.getline (
file     IN FILE_TYPE,
buffer   OUT VARCHAR2,
linesize IN NUMBER,
len      IN PLS_INTEGER DEFAULT NULL);See Read demos GETLINE_NCHARSame as GETLINE except can be used to read Unicode rather than the database's character set GET_RAWReads a RAW string value from a file and adjusts the file pointer ahead by the number of bytes readutl_file.get_raw (
fid IN  utl_file.file_type,
r   OUT NOCOPY RAW,
len IN  PLS_INTEGER DEFAULT NULL);See UTL_MAIL demo IS_OPENReturns True If A File Handle Is Open: Otherwise Falseutl_file.is_open (file IN FILE_TYPE) RETURN BOOLEAN;See FCLOSE_ALL Demo NEW_LINEWrites one or more operating system-specific line terminators to a fileutl_file.NEW_LINE (file IN FILE_TYPE, lines IN NATURAL := 1);See Read Demo PUTWrites a string to a fileutl_file.put(
file   IN FILE_TYPE,
buffer IN VARCHAR2);See Write demo PUTFA PUT procedure with formattingutl_file.putf(
file   IN FILE_TYPE,
format IN VARCHAR2,
[arg1  IN VARCHAR2 DEFAULT NULL,
. . .
arg5   IN VARCHAR2 DEFAULT NULL]);See Write demo PUT_LINEWrites a line to a file.  Appends an operating system-specific line terminatorutl_file.put_line(
file      IN FILE_TYPE,
buffer    IN VARCHAR2,
autoflush IN BOOLEAN DEFAULT FALSE);See Read-Write demo PUT_NCHARWrites a Unicode string to a file PUT_RAWAccepts as input a RAW data value and writes the value to the output bufferutl_file.PUT_RAW (
fid       IN utl_file.file_type,
r         IN RAW,
autoflush IN BOOLEAN DEFAULT FALSE);See extract_blob Demo PUT_LINE_NCHARWrites a Unicode line to a file PUTF_NCHARWrites a Unicode string to a file UTL_FILE Demos
Read DemoCREATE OR REPLACE PROCEDURE read_demo (file_name VARCHAR2) IS
 vSFile   utl_file.file_type;
 vNewLine VARCHAR2(200);
BEGIN
  vSFile := utl_file.fopen('ORALOAD', 'test.txt','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
  InFile := utl_file.fopen('CTEMP', 'in.txt','r');
  OutFile := utl_file.fopen('CTEMP', 'out.txt', 'w');

  IF utl_file.is_open(InFile) THEN
    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*LoaderPROCEDURE create_cmd_file IS

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(100) := 'DailyLoad 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/ncontrol=%s/ndata=%s/nlog=%s/nbad=%s/n', '/ /',
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/n',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, '[e-mail]', vSubject, vMessage);
  WHEN utl_file.invalid_path THEN
    vErrMsg := SQLERRM;
    vMessage := Emsg || '-20052, Invalid Path';
    sp_SendEmail (0, '[e-mail]', vSubject, vMessage);
  WHEN utl_file.invalid_filehandle THEN
    vErrMsg := SQLERRM;
    vMessage := Emsg || '-20053, Invalid Filehandle';
    sp_SendEmail (0, '[e-mail]', vSubject, vMessage);
  WHEN utl_file.invalid_operation THEN
    vErrMsg := SQLERRM;
    vMessage := Emsg || '-20054, Invalid Operation';
    sp_SendEmail (0, '[e-mail]', vSubject, vMessage);
  WHEN utl_file.read_error THEN
    vErrMsg := SQLERRM;
    vMessage := Emsg || '-20055, Read Error';
    sp_SendEmail (0, '[e-mail]', vSubject, vMessage);
  WHEN utl_file.write_error THEN
    vErrMsg := SQLERRM;
    vMessage := Emsg || '-20056, Write Error';
    sp_SendEmail (0, '[e-mail]', 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, '[e-mail]', vSubject, vMessage);
END create_cmd_file;
/
Extract BLOB DemoCREATE 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('CTEMP', 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;
/  Related TopicsDirectoriesDBMS_LOB Contact Us ? Legal Notices and Terms of Use ?Privacy Statement