Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Utl_file and OPENVMS

RE: Utl_file and OPENVMS

From: Gene Sais <Gsais_at_co.palm-beach.fl.us>
Date: Thu, 03 Oct 2002 12:45:17 -0800
Message-ID: <F001.004DFFD2.20021003124517@fatcity.com>


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.

>>> rafiq9857_at_hotmail.com 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: ORACLE-L_at_fatcity.com
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> 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.

 >>> rafiq9857_at_hotmail.com 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: ORACLE-L_at_fatcity.com
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> 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.

  >>> rafiq9857_at_hotmail.com 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: ORACLE-L_at_fatcity.com
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> 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

   >>> Jared.Still_at_radisys.com 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" <Gsais_at_co.palm-beach.fl.us>
Sent by: root_at_fatcity.com

    10/01/2002 11:25 AM
    Please respond to ORACLE-L

           To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>

           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

   >>> SXBaswan_at_dcss.com 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.

   >>> RROGERS_at_galottery.org 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');
                    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');

                    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 mm

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com 
--
Author: Ron Rogers
     INET: RROGERS_at_galottery.org 




--
Please see the official ORACLE-L FAQ: http://www.orafaq.com 
--
Author:
     INET: Jared.Still_at_radisys.com 

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: ListGuru_at_fatcity.com (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: Gsais_at_co.palm-beach.fl.us 

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: ListGuru_at_fatcity.com (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: rafiq9857_at_hotmail.com 

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: ListGuru_at_fatcity.com (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: Gsais_at_co.palm-beach.fl.us 

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: ListGuru_at_fatcity.com (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: rafiq9857_at_hotmail.com 

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: ListGuru_at_fatcity.com (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: Gsais_at_co.palm-beach.fl.us 

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: ListGuru_at_fatcity.com (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: rafiq9857_at_hotmail.com 

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: ListGuru_at_fatcity.com (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: Gsais_at_co.palm-beach.fl.us

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: ListGuru_at_fatcity.com (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).
Received on Thu Oct 03 2002 - 15:45:17 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US