Prerequisites

To access files, certain conditions must be met:

  1. The files must be located in a folder that is accessible by the database server
  2. The user under which the Oracle service is running, must have read-/write access to the directory
  3. It exists an Oracle Directory Object, that points to the directory
  4. The database user has read-/write access to the directory

Assumption

  • the database is running on a Windows PC,
  • the files lie on partition D:
  • in the folder D:\marcus\ora\doc

Conditions 1 and 2 are usually already met, if access has not been explicitly denied.

The Directory has to be created by a DBA or a user having the necessary privileges

CREATE OR REPLACE DIRECTORY doc
    AS 'D:\marcus\ora\doc';
GRANT READ, WRITE ON DIRECTORY doc TO hr;

Importand: in this script the directory name is lower case, but it will be created by Oracle in upper case and therefore must be referenced by 'DOC'.

The available Directories and their privileges can be checked with all_directories .The meaning of Java privileges will be explained below.

SELECT  ad.directory_name "Directory Name"
       ,ad.directory_path "Path"
       ,atp.grantee "Grantee"
       ,atp.privilege "Privilege"
       ,ujp.type_name "Java Priv. Typ"
       ,ujp.action "Java Priv."
FROM    all_directories ad
        JOIN all_tab_privs atp
          ON (ad.directory_name = atp.table_name)
        LEFT JOIN user_java_policy  ujp
          ON (ujp.name = ad.directory_path)
ORDER BY ad.directory_name;
  • utl_file The package, Oracle provides to access files

Read files

When opening a file, a handle is created you can use to access the file afterwards

utl_file.FILE_TYPE

To read a file you need to know its name; in the text below I describe a way to circumvent this.

Datei öffnen

DECLARE
  v_fileHandle            utl_file.FILE_TYPE;
  v_line                  VARCHAR2(32767);
BEGIN
  v_fileHandle   := utl_file.fopen('DOC','testfile.txt','R',32767);

In this example a file named testfile.txt in the Directory DOC is opened for read access.

Read row by row

  utl_file.get_line (
         v_fileHandle
        ,v_line
        ,32767
        );

This reads a linje with the maximum possible length. When a smaller number is specified, then it is possible that the line is read only partially, the next read operation will then not beginn in the next line, but at the end position of the previous.

Close a file

  utl_file.fclose(v_fileHandle);
END;
/

Load a file directly into a CLOB

You can load a file directly into a CLOB,without reading it row by row.. For this there is dbms_lob.loadClobFromfile.

A possible implementation is the prozedur getClobFromFile in the packagemam_file you can download at the end of the page.

Write files

Open a file

DECLARE
  v_fileHandle            utl_file.FILE_TYPE;
BEGIN
  v_fileHandle   := utl_file.fopen('DOC','testfile.txt','W',32767);

When a file is opened for writing, an empty file will be created. If the file already exists it will be deleted without warning. To prevent this you can check the existence with utl_file.fgetattr

To add text to the end of an existing file you can choose the option 'A' (append) on opening.

Write text

  utl_file.put_line (
         v_fileHandle
        ,'Lorem ipsum'
        );

utl_file.put_line writes the text and appends a line break. If you want to ass text on the same line, then you use utl_file.put

Write CLOB

To write a CLOB into a file you can do this writing line by line with utl_file, or you use

  dbms_xslprocessor.clob2file (
         v_clob
        ,'DOC'
        ,'<filename>'
        ,
        );

For a discussion about different charsets have a look at

Read directory contents

As described above Oracle offers possibilities to list directory content.

  • Ask Tom Source for the following solution

Except when you use the XE Editionyou can use the built in JVM, it allows to use Java to get the information.

First the DBA has to allow the user to access the directory via Java

BEGIN
    dbms_java.grant_permission (
         'HR'--User
        ,'SYS:java.io.FilePermission'--File access
        ,'D:\marcus\ora\doc'--Path! not Directory Name
        ,'read'--Permission
        );
END;
/

Afterwards the code to access the files is installed.You can download an implementation together with other file utilities.

The access to a directory with this package might look like:

DECLARE
   v_directory           all_directories.directory_name%TYPE := 'DOC';
   tab_filelist          mam_file.t_filelist;
BEGIN
   --read all files with the suffix .xml
   tab_filelist  := mam_file.getFilelist(v_directory,'.xml');
   IF tab_filelist.COUNT > 0 THEN
      <<loop_allFiles>>
      FOR i IN tab_filelist.FIRST .. tab_filelist.LAST LOOP
         --Do something with the files
      END LOOP loop_allFiles;
   END IF;
END;
/

Alternative

A simple alternative if you only want to read a directory: mam_file_java_util

A Select is all you need to read attributes like name, modification date or size.

SELECT  *
FROM    TABLE(mam_file_java_util.ls('MY_DIRECTORY'))
WHERE   name LIKE 'T%'
AND     modified >= SYSDATE - 3
AND     file_size > 10;

Additionally you can move or delete files. Why when you can do it with utl_file?

If you want to copy binary data then Java is superior.
I implemented the delete because on a Windows CIFS share, mounted on a Linux db server, files were not deleted but only the hidden flag was set. I still don't know why but with Java it works.

Download

The linked Package contains utilities for file access.
Note: In the package there is a test before every file access whether the necessary permissions were granted. This is not necessary each time when you frequently open/close files. Check it before the first access, then use utl_file directly.

To use it with the XE-Edition you have to delete the lines to list folder contents in mam_file!

oracle@matzberger.de For contacts of any kind including error reports. Impressum