1

In one of the PL/SQL packages in our Oracle database, there's a global variable, g_file_path, that points to a location on the system where certain files will be stored:

create or replace
PACKAGE xyz_package
AS

...

   g_file_path     VARCHAR2 (80) := '/usr/tmp';

...

This variable is used in various UTL_FILE operations throughout the package.

Unfortunately, the path chosen is inadequate, and I need to figure out how to set a path dynamically depending on the environment where the database is running, e.g. so the path becomes /opt/ENVDB/xyz, where ENVDB changes depending on the env.

One idea is to emulate the behavior of the shell script:

>echo $XYZ_DB_TOP

That points to a suitable folder where the files can be stored. I can't think of a suitable PL/SQL function that emulates this behavior though. Any smart/simple solution to this problem? Any help is appreciated!

BeeDog
  • 1,835
  • 5
  • 17
  • 20

1 Answers1

8

If you're using Oracle 9i or higher you should use a directory object instead. This is safer, because it only permits complete paths (no wildcards). It also doesn't require a database restart, unlike using UTL_FILE_DIR in the init.ora file. And it is far more secure because we can grant privileges on each directory to specific individual users.

But the aspect that will interest you the most right now is the that the abstraction of the directory object makes it a cinch to change the actual OS path, so it can be different in each environment. Just like this: 

alter directory temp_data as '/home/oracle/tmp';

Find out more.

APC
  • 144,005
  • 19
  • 170
  • 281