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: Ron Rogers <RROGERS_at_galottery.org>
Date: Tue, 01 Oct 2002 11:08:28 -0800
Message-ID: <F001.004DDB4F.20021001110828@fatcity.com>


Thanks to all of you for the assistance.  I have the package working by coding the directory into the utl_file.fopen command and the files are being created okay. I got the batch procedure to work with the SQLLDR command( I did not know you had to set noon and each line starts with a $). It works okay as a database. It takes 5 min 48 sec to load 20 different tables with a total of 178000 rows.
 When I get the database up to date the developers will test their applications and I will start making the production server. Thanks,
Ron

>>> Gsais_at_co.palm-beach.fl.us 10/01/02 02:25PM >>> 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		I
n	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
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.

         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 mm

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

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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Baswannappa, Shiva INET: SXBaswan_at_dcss.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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ron Rogers INET: RROGERS_at_galottery.org 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 Tue Oct 01 2002 - 14:08:28 CDT

Original text of this message

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