I am reproducing below an email from John Kanagaraj for your info..
It might help you further...
Regards
Rafiq
Date: Thu, 15 Aug 2002 00:08:35 -0800
Kathy,
This is probably not documented, but you can have multiple lines of
UTL_FILE_DIR _anywhere_ as _long_as_ you bunch them all up without any
gaps
/ lines inbetween them (in which case the last set overrides the
previous
ones). I have the following in a 7.3.4 Db, all at the end (and I bet
that
you are recognizing your favorite ERP system therein - Oops my version
is
showing :)
#
# ******** VERY VERY IMPORTANT ***********
# *** Keep ALL lines for UTL_FILE_DIR ***
# *** together at the end of the file ***
# *** Otherwise only the last set is ***
# *** effective ***
# ******** VERY VERY IMPORTANT ***********
#
utl_file_dir = /u001/app/smartdb/files/data/AR
utl_file_dir = /u01/home/arftp/edi
utl_file_dir = /u01/home/xxusc4xx
utl_file_dir = /u009/app/XXXX/R10.7/xxloftware/loftware/error
utl_file_dir = /u009/app/XXXX/R10.7/xxloftware/loftware/log
utl_file_dir = /u009/app/XXXX/R10.7/xxloftware/loftware/processed
utl_file_dir = /u009/app/XXXX/R10.7/xxloftware/loftware
I believe you will have to either name specific directories or use a
single
'*' (the latter is _not_ a good idea!)
John Kanagaraj
Oracle Applications DBA
DB Soft Inc
Work : (408) 970 7002
Reply-To: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
Date: Thu, 03 Oct 2002 12:45:17 -0800
so right you are! i just tested in sqlplus and it shows both. thanks. one
thing noticed, is that if you include another file w/ utl_file_dir setting
to something, it ignores the original setting, i.e. part of my problem.
all my initsid.ora files include a common 8i init.ora. in the common
init.ora file i have this set, but it gets reset not appended to when i set
utl_file_dir it in the initsid.ora file. thanks again.
>>> [EMAIL PROTECTED] 10/03/02 04:03PM >>>
Here you are right. You will see here only one because of width. Please
don't rely on svrmgrl for such info. Instead use sqlplus and check it from
v$parameter
result is
PARAMETER
------------------------------
VALUE
--------------------------------------------------------------------------------
utl_file_dir
/u327/applmgr/10_7/finprod_output/hfs_data,
/u327/applmgr/10_7/har/1.0.0/mm_cbda
ta
Both entries are appearing here,
HTH
Regards
Rafiq
Reply-To: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
Date: Thu, 03 Oct 2002 11:13:42 -0800
hmm, when you go into svrmgrl connect internal and show parameters, do both
show up or just the last one? on my system, i only see the last.
>>> [EMAIL PROTECTED] 10/03/02 01:13PM >>>
Like these
utl_file_dir=/u327/applmgr/10_7/finprod_output/hfs_data
utl_file_dir=/u327/applmgr/10_7/har/1.0.0/mm_cbdata
and these are the last entries in initSID.ora file.
Regards
Rafiq
Reply-To: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
Date: Thu, 03 Oct 2002 07:58:38 -0800
What does your utl_file_dir parameter look like in your init.ora? I am
using AIX.
>>> [EMAIL PROTECTED] 10/03/02 11:43AM >>>
What flavor of unix...ON HP-UX 10.20/11 Oracle ver 7.3.4.5 we have no
problem with separate entries as mentioned by Jared. However, don't keep gap
between these entries...and define it the way it was explained by Jared.
Regards
Rafiq
Reply-To: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
Date: Thu, 03 Oct 2002 05:28:22 -0800
On unix, multiple entries in the init.ora file results in the last entry
being the only valid value (i.e. last time variable is set). I tested below
and only dir3 is listed as a utl_file_dir parameter. But if you comma
delimit them w/ 1 instance of the variable then all dir's are listed. Maybe
NT is different, fortunately never had to support Oracle on NT :).
Gene
>>> [EMAIL PROTECTED] 10/02/02 07:13PM >>>
Gene,
> utl_file_dir = D:\directory name1
> utl_file_dir = D:\directory name2
> utl_file_dir = D:\directory name3
The multiple lines shown are actually the documented method for doing
this.
The single line with comma delimited entries may also work, though I'm
not sure about it.
Jared
"Gene Sais" <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
10/01/2002 11:25 AM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L
<[EMAIL PROTECTED]>
cc:
Subject: RE: Utl_file and OPENVMS
i believe its
utl_file_dir=dir1,dir2,dir3,...
in your example, dir 3 would be the only valid dir.
hth,
gene
>>> [EMAIL PROTECTED] 10/01/02 12:53PM >>>
Make sure you have the directory name entry covered in INIT.ORA
e.g.
utl_file_dir = D:\directory name1
utl_file_dir = D:\directory name2
utl_file_dir = D:\directory name3
You need one entry per directory that you want to write to using UTL_FILE
Package
Hope this helps
Regards
Shiva
-----Original Message-----
Sent: Tuesday, October 01, 2002 9:48 AM
To: Multiple recipients of list ORACLE-L
the vms user oracle needs rights to the directory.
>>> [EMAIL PROTECTED] 09/30/02 10:53AM >>>
List,
I have a package that creates files on the server. The directory
location and file name are obtained from tables in oracle. The procedure
works as designed on Novell 7.3.4 and no changes were needed when the
database way loaded on Linux Oracle 8.1.7. I am trying to move the
database from the Novell to an OPENVMS Oracle 8.1.7 server and I can't
get the package to write the files to the OS directory.
The package is created by the Oracle user DTSUSER and executed by
DTSUSER. There is no OPENVMS user DTSUSER.
The sysadmin assures me that the permissions are correct to write to
the directory.
I have place a Dbms_output in the package to display the directory
information and it looks correct.
Is there anything different that has to be done to an OPENVMS server
that will allow a package to write to a directory using the Utl_File
package?
Listing from the Oracle tables:
DTS_PARAMETER_NAME DTS_PARAMETER_VALUE
------------------------------ ------------------------------
LOAD_USERID LOADITUP
LOAD_PASSWORD ILOADIT
LOAD_SERVICE_NAME GLC_ALPHADEV-TCP
LOAD_PAR_FILE_DIR ORADSK:[ORACLE8.DATA.PAR]
LOAD_DATA_FILE_DIR Disk1:[Pro_ics.Iqfiles]
LOAD_CONTROL_FILE_DIR ORADSK:[ORACLE8.DATA.CONTROL]
LOAD_LOG_FILE_DIR ORADSK:[ORACLE8.DATA.LOG]
LOAD_BAD_FILE_DIR ORADSK:[ORACLE8.DATA.BAD]
LOAD_DISCARD_FILE_DIR ORADSK:[ORACLE8.DATA.DISCARD]
Listing from the package that writes the parameter file:
Procedure Create_New_Par_File
(
P_Current_Table_Name In
Varchar2
,
P_Run_Date In Date ,
P_Load_Userid In
Varchar2
,
P_Load_Password In
Varchar2
,
P_Load_Service_Name In
Varchar2
,
P_Load_Par_File_Dir In
Varchar2
,
P_Load_Data_File_Dir In
Varchar2
,
P_Load_Control_File_Dir In Varchar2 ,
P_Load_Log_File_Dir In
Varchar2
,
P_Load_Bad_File_Dir In
Varchar2
,
P_Load_Discard_File_Dir In Varchar2
)
as
Begin
Declare
L_Par_File_Hand Utl_FIle.File_Type; --
Local
variable to cc:
Subject:
hold the File Pointer for the parameter file.
Begin
I PLACED A DBMS_OUTPUT STATEMENT HERE AND IT DISPLAYED THE
PROPER DIRECTORY LOCATION FOR THE FILE TO BE WRITT
EN TO.
-- Open a new parameter file
L_Par_File_Hand :=
Utl_File.Fopen(P_Load_Par_File_Dir,P_Current_Table_Name||'.PAR','w');
-- Print the following lines into the parameter file.
Utl_File.Put
(L_Par_File_Hand,'UserId='||P_Load_UserId||'/'||P_Load_Password||'@'||P_Load
_Service_Name);
Utl_File.New_Line(L_Par_File_Hand);
If Not P_Current_Table_Name = 'GLCRET'
Then
Utl_File.Put
(L_Par_File_Hand,'Errors=1');
Else
Utl_File.Put
(L_Par_File_Hand,'Errors=50');
End If;
Utl_File.New_Line(L_Par_File_Hand);
Utl_File.Put
(L_Par_File_Hand,'Control='||P_Load_Control_File_Dir||'/'||P_Current_Table_N
ame||'.CTL');
Utl_File.New_Line(L_Par_File_Hand);
Utl_File.Put
(L_Par_File_Hand,'Log='||P_Load_Log_File_Dir||'/'||P_Current_Table_Name||to_
char(P_Run_Date,'mmdd')||'.LOG');
Utl_File.New_Line(L_Par_File_Hand);
Utl_File.Put
(L_Par_File_Hand,'Bad='||P_Load_Bad_File_Dir||'/'||P_Current_Table_Name||to_
char(P_Run_Date,'mmdd')||'.BAD');
Utl_File.New_Line(L_Par_File_Hand);
Utl_File.Put
(L_Par_File_Hand,'Discard='||P_Load_Discard_File_Dir||'/'||P_Current_Table_N
ame||to_char(P_Run_Date,'mmdd')||'.DSC');
as
Utl_File.New_Line(L_Par_File_Hand);
Utl_File.Put
(L_Par_File_Hand,'Data='||P_Load_Data_File_Dir||'/'||P_Current_Table_Name||'
/'||
P_Current_Table_Name||to_char(P_Run_Date,'yyyymmdd')||'.DAT');
-- Close the file after printing.
Utl_File.Fclose(L_Par_File_Hand);
Exception
When Others then
Dbms_Output.Put_line(to_char(sqlcode)||'-'||SQLERRM);
End;
Thanks, Any assistance would be appreciated. I am just getting in to
the OPENVMS OS.
Ron
ROR mª¿ªm
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Ron Rogers
INET: [EMAIL PROTECTED]
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Gene Sais
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
_________________________________________________________________
Send and receive Hotmail on your mobile device: http://mobile.msn.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Mohammad Rafiq
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Gene Sais
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
_________________________________________________________________
Send and receive Hotmail on your mobile device: http://mobile.msn.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Mohammad Rafiq
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Gene Sais
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------/www.o
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
_________________________________________________________________
Join the world's largest e-mail service with MSN Hotmail.
http://www.hotmail.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Mohammad Rafiq
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Gene Sais
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
_________________________________________________________________
Send and receive Hotmail on your mobile device: http://mobile.msn.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Mohammad Rafiq
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
