Solved

# UTL_FILE file path problem

Posted on 2003-11-03
1,938 Views
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?

Thanks.
0
Question by:mfarid2

LVL 23

Expert Comment

ID: 9674896
2). it will be like this format :

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)
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.
0

LVL 4

Expert Comment

ID: 9675877
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
0

LVL 47

Expert Comment

ID: 9676814
In 9i:

ALTER SYSTEM SET utl_file= directory SCOPE=x

wher x is MEMORY, BOTH, SPFILE
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.
0

Author Comment

ID: 9686897
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?
0

LVL 23

Accepted Solution

seazodiac earned 125 total points
ID: 9687266
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 .
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…