8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » 9i » Here

UTL_FILE Enhancements

Oracle9i Release 2 includes some long overdue enhancements to the UTL_FILE package including basic file handling and support for NCHAR and RAW data.

First we create a directory object pointing to the location of the file of interest.

CREATE OR REPLACE DIRECTORY my_docs AS '/usr/users/oracle/';

The basic file handling is summarised below.

SET SERVEROUTPUT ON
DECLARE
  v_exists       BOOLEAN;
  v_file_length  NUMBER;
  v_block_size   NUMBER;
BEGIN
  UTL_FILE.FRENAME (src_location  => 'MY_DOCS',
                    src_filename  => 'test.txt',
                    dest_location => 'MY_DOCS',
                    dest_filename => 'test1.txt',
                    overwrite     => TRUE);

  UTL_FILE.FCOPY(src_location  => 'MY_DOCS',
                 src_filename  => 'test1.txt',
                 dest_location => 'MY_DOCS',
                 dest_filename => 'test2.txt');

  UTL_FILE.FREMOVE(location  => 'MY_DOCS',
                   filename  => 'test2.txt');

  UTL_FILE.FGETATTR(LOCATION => 'MY_DOCS',
                    FILENAME => 'test1.txt',
                    FEXISTS => v_exists,
                    FILE_LENGTH => v_file_length,
                    BLOCK_SIZE => v_block_size);
  
  IF v_exists THEN                  
    DBMS_OUTPUT.PUT_LINE('Exists: TRUE');
  ELSE
    DBMS_OUTPUT.PUT_LINE('Exists: FALSE');
  END IF;
  DBMS_OUTPUT.PUT_LINE('File Length:' || v_file_length);
  DBMS_OUTPUT.PUT_LINE('Block Size :' || v_block_size);
END;
/

Support for NCHAR and RAW data has been included with the following procedures.

All the GET_% procedures now include a LEN parameter which specifies the number of characters to read. To support this functionality the FGETPOS function allows you to read the current pointer position, while the FSEEK procedure allow you to set it.

For more information see:

Hope this helps. Regards Tim...

Back to the Top.