Go Premium for a chance to win a PS4. Enter to Win

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1948
  • Last Modified:

UTL_FILE file path problem

My questions are:

1)What are the file path restrictions for the UTL_FILE package?
2)I know that I have to have the value UTIL_FILE_DIR set in INIT.ORA file on the server.
Can someone give me an example as to how it should look like?
3)What is the role of V$PARAMETER?
4)What if I don't have access to INIT.ORA? Restarting the database is simply not an option. Is
there a way around it?
5)What are the directories to which I can read/write if I don't have access to INIT.ORA?

1 Solution
1). any directory (file path) is fine as long as the oracle admin user account have the full read/write access to it.
2). it will be like this format :
 UTL_FILE_DIR = g:\oracle\admin\ora81\util  (in windows)
UTL_FILE_DIR = /u01/opt/oracle/admin/util (in unix)

3). take a look at the definition of v$parameter view, you can derive its role:
SQL> desc v$parameter

 Name                                                                                                      Null?    Type
----------------------------------------------------------------------------------------------------------------- -------- -------------
NUM                                                                                                                NUMBER
NAME                                                                                                               VARCHAR2(64)
TYPE                                                                                                               NUMBER
VALUE                                                                                                              VARCHAR2(512)
ISDEFAULT                                                                                                          VARCHAR2(9)
ISSES_MODIFIABLE                                                                                                   VARCHAR2(5)
ISSYS_MODIFIABLE                                                                                                   VARCHAR2(9)
ISMODIFIED                                                                                                         VARCHAR2(10)
ISADJUSTED                                                                                                         VARCHAR2(5)
DESCRIPTION                                                                                                        VARCHAR2(64)
UPDATE_COMMENT                                                                                                     VARCHAR2(255)

Among its columns , ISSES_MODIFIABLE and ISSYS_MODIFIABLE are two most useful ones, it can tell you which parameters in INIT<SID>.ora can be dynamically changed either in session level or system level.
4). NO, you have to bounce the database to make it take effect.
5). None in PL/SQL.
I'm not sure about 9i, but in 8i, you will have to bounce the database to pick up the change for the utl_file
In 9i:

ALTER SYSTEM SET utl_file= directory SCOPE=x

MEMORY - changes for this running instance
BOTH - changes registered in SPFILE, but  not in the running instance
BOTH - both above

You do not need to bounce the instance.
mfarid2Author Commented:
seazodiac, so let me get this straight. We have Oracle on an Unix server. We can read and write from any location as long as that location is specified in the UTIL_FILE_DIR parameter in Init.Ora file. Correct?
No, that's not true.
In Unix, for example, the user account who create the oracle databases must have read/write to the directory defined in UTL_FILE_DIR ( NOT UTIL_FILE_DIR).

so for example: the user named "oracle" created the Oracle databse, if you want to create a directory,
/tmp/util to store those files. then you need to log in as ROOT , to assign user "oracle" the read/write permission on this folder .

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now