Chapter 24. UTL_FILE Built-in Package

Beginner

Q:

24-1.

You must add one or more UTL_FILE_DIR entries to enable access to a specific directory. So unless you include an entry for your directory, you can’t read/write in that location.

Q:

24-2.

Entries are:

  1. Invalid. You cannot combine multiple directories onto a single line.

  2. Invalid. This set of parameters allow access to /tmp, but not to any subdirectories of /app/datafiles/*; you cannot use wildcards in your directory specifications.

  3. Valid.

  4. Valid.

  5. Invalid. You should never append the directory delimiter (/ for Unix and \ for DOS/Windows, for example) at the end of the directory specification. You should not end your directory name with a delimiter, such as the forward slash in Unix. I even found in tests on Oracle 8.0 and 8.1 in Windows NT that if I terminated a UTL_FILE_DIR entry with a backslash, the database would not even start up!

By the way, I wasn’t even certain that (d) would work, so I wrote a script to test them all. If you are interested in running this test yourself, see utlfile.tst and utlfile.ora on the book’s web page.

Q:

24-3.

You can use the following shorthand entry to specify that you want to read/write in any directory on the server:

UTL_FILE_DIR = *

This is definitely not recommended practice for any production system, since there can be serious ramifications (like writing over your database log file). It is a quick-and-dirty ...

Get Oracle PL/SQL Programming: A Developer's Workbook now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.