< Previous Section: The Four Step Program to using utPLSQL | Next Section: Build Test Packages >

Administrative Topics

Reporting Bugs and Enhancement Requests

Administrative Topics

Configuring UTL_FILE

If you want utPLSQL to automatically recompile your test packages, you will need to make sure that UTL_FILE is enabled in your database (this allows you to read/write operating system files). The database initialization parameter file (aka, the "init.ora" file) must have at least one utl_file_dir parameter in it for this to work. Here is some background and guidelines for working with UTL_FILE:

UTL_FILE lets you read and write files accessible from the server on which your database is running. So, theoretically, you could use UTL_FILE to write right over your tablespace data files, control files and so on. That is, of course, a very bad idea. Server security requires the ability to place restrictions on where you can read and write your files.

UTL_FILE implements this security by limiting access to files that reside in one of the directories specified in the init.ora file (parameter initialization file) for the database instance on which UTL_FILE is running.

When you call UTL_FILE.FOPEN to open a file, you must specify both the location and the name of the file, in separate arguments. This file location is then checked against the list of accessible directories.

The format of the parameter for file access in the init.ora file is:

utl_file_dir = <directory>

Include a parameter for utl_file_dir for each directory you want to make accessible for UTL_FILE operations. The following entries, for example, enable four different directories in Unix:

utl_file_dir = /tmp
utl_file_dir = /ora_apps/hr/time_reporting
utl_file_dir = /ora_apps/hr/time_reporting/log
utl_file_dir = /users/test_area

To bypass server security and allow read/write access to all directories, you can use this special syntax:

utl_file_dir = *

You should not use this option on production systems. In a development system, this entry certainly makes it easier for developers to get up and running on UTL_FILE and test their code. You should, however, only allow access to a few specific directories when you move the application to production.

Some observations on working with and setting up accessible directories with UTL_FILE:

Access is not recursive through subdirectories. If the following lines were in your init.ora file, for example,

utl_file_dir = c:\group\dev1
utl_file_dir = c:\group\prod\oe
utl_file_dir = c:\group\prod\ar

then you would not be able to open a file in the c:\group\prod\oe\reports subdirectory.

Do not include the following entry in Unix systems:

utl_file_dir = .

This would allow you to read/write on the current directory in the operating system.

Do not enclose the directory names within single or double quotes.

In the UNIX environment, a file created by UTL_FILE.FOPEN has as its owner the shadow process running the Oracle instance. This is usually the oracle owner. If you try to access these files outside of UTL_FILE, you will need to have the correct privileges (or be logged in as oracle) to access or change these files.

You should not end your directory name with a delimiter, such as the forward slash in Unix. The following specification of a directory will result in problems when trying to read from or write to the directory:

utl_file_dir = /tmp/orafiles/

After you modify your parameter initialization file, you will need to stop and then restart your database instance.

Test UTL_FILE Access

If you have never before used or relied on UTL_FILE, you should write a simple test to verify that UTL_FILE is now working. You can use the code shown below (after changing your directory names and names for existing and new files) to make sure you've got it running properly.

SET SERVEROUTPUT ON

DECLARE
   fid UTL_FILE.FILE_TYPE;
   v VARCHAR2(32767);
   PROCEDURE recNgo (str IN VARCHAR2)
   IS
   BEGIN
      DBMS_OUTPUT.PUT_LINE ('UTL_FILE error ' || str);

      UTL_FILE.FCLOSE (fid);
   END;
BEGIN
   /* Change the directory name to one to which you at least 
   || THINK you have read/write access.
   */
   fid := UTL_FILE.FOPEN ('e:\demo', 'existing_file', 'R');
   UTL_FILE.GET_LINE (fid, v);
   dbms_output.put_line (v);

   UTL_FILE.FCLOSE (fid);

   fid := UTL_FILE.FOPEN ('e:\demo', 'new_file', 'W');

   UTL_FILE.PUT_LINE (fid, v);

   UTL_FILE.FCLOSE (fid);
EXCEPTION
   WHEN UTL_FILE.INVALID_PATH
      THEN recNgo ('invalid_path');
   WHEN UTL_FILE.INVALID_MODE
      THEN recNgo ('invalid_mode');
   WHEN UTL_FILE.INVALID_FILEHANDLE
      THEN recNgo ('invalid_filehandle');
   WHEN UTL_FILE.INVALID_OPERATION
      THEN recNgo ('invalid_operation');
   WHEN UTL_FILE.READ_ERROR
      THEN recNgo ('read_error');
   WHEN UTL_FILE.WRITE_ERROR
      THEN recNgo ('write_error');
   WHEN UTL_FILE.INTERNAL_ERROR
      THEN recNgo ('internal_error');
END;
/

If an error occurs, it will be displayed on your screen (note: the "set serveroutput on" is not required for UTL_FILE to work, but simply to display any errors which might occur).

Join the utPLSQL Project Team

To take part in the utPLSQL project, have a look round the utPLSQL project site, in particular the CONTRIBUTING.md and issues tracker. Once you are up to speed on the project, you can choose a issue and begin to contribute.

Reporting Bugs and Enhancement Requests

To identify the version of utPLSQL you are running, you can execute the following program in SQL*Plus:

SQL> set serveroutput on
SQL> exec dbms_output.put_line (utPLSQL.version)

You can also look inside the utPLSQL package (utPLSQL.pkb) and check the value of the g_version private variable.

< Previous Section: The Four Step Program to using utPLSQL | Next Section: Build Test Packages >

Administrative Topics

Configuring UTL_FILE

Join the Project Team

Reporting Bugs and Enhancement Requests