?
Solved

util_file in oracle 8i packages

Posted on 2006-05-18
11
Medium Priority
?
2,210 Views
Last Modified: 2007-12-19
Currently our code uses util_file in one of our packages to write contents of a record in a hard coded unix file directory, we are planning to remove the hardcoding and write to a NFS mount directory. DBA has written to us saying there is a NFS mounted drive on the unix and it is read-writable folder, but he feels that util_file cannot be used as this is not database file.

Could you guys clarify as to when util_file can be used to write in a folder, what are the requirements for this.. Currentlly we do manually transfer files everyday and wanted to eliminate this manual process.

The code uses utl_file.fopen, fetch and put_line and fclose.
0
Comment
Question by:mahjag
  • 5
  • 4
  • 2
11 Comments
 
LVL 19

Expert Comment

by:actonwang
ID: 16709710

1.use the following to create directory:

create directory TEST_DIR as '<your path>''


2. use it :

declare
   handle UTL_FILE.FILE_TYPE;
begin
   handle := UTL_FILE.FOPEN('TEST_DIR','test.txt','w');
--   DBMS_OUTPUT.PUT_LINE('line1');
   UTL_FILE.PUT_LINE(handle,'test data!');
   UTL_FILE.FCLOSE(handle);
EXCEPTION
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('error '||SQLERRM);
      UTL_FILE.FCLOSE(handle);
END;
/
0
 
LVL 19

Expert Comment

by:actonwang
ID: 16709730
>>create directory TEST_DIR as '<your path>''
should be:
create directory TEST_DIR as '<your path>'

I assume you use oracle9i or above.

otherwise you should use parameter UTL_FILE_DIR to specify directories.


actn
0
 
LVL 19

Expert Comment

by:actonwang
ID: 16709760
Oh, you are in oracle8i, you need to use UTL_FILE_DIR paramter to specify directories:
as:

UTL_FILE_DIR=/<your path>/log

instead of "create directory ..."

refer to this:

http://download-west.oracle.com/docs/cd/A87860_01/doc/appdev.817/a76936/utl_file.htm#998101

0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 16710034
Can you explain a little bit more about how an NFS mount point will eliminate the hard coding?

Oracle does have some problems with remote drives but I've only had problem in a Windows environment.  In UNIX a mount point is a mount point and Oracle shouldn't care as long as the oracle user has write access to it.
0
 
LVL 19

Expert Comment

by:actonwang
ID: 16710510
>> I've only had problem in a Windows environment
     did you map your network drive to virtual disk? using "Map Network Drive ..."?
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 16710701
>>did you map your network drive to virtual disk? using "Map Network Drive ..."?

This is really off topic from the askers question so I don't really want to get into a detailed discussion here.  I honestly forget how it was mapped since it's been a while since I did it but it was caused by the permissions 'feature'.  Check out Metalink Doc ID: 161107.1
0
 

Author Comment

by:mahjag
ID: 16710868
NFS mount is more like a virtual drive I think for unix, I am not sure as to each time unix machine comes up it retains the mapped NFS drive, since I have to hardcode again the NFS drive mounted. I am not sure if that answers the questions.

I understand utl_file can point to any drive, is that correct?
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 16710920
>> I am not sure as to each time unix machine comes up it retains the mapped NFS drive

You would need to set UNIX up to mount it on boot just like every other mount point.  If memory serves it's the /etc/fstab file (I'm not on UNIX any more).

>>hardcode again the NFS drive mounted

That's why I asked.  UTL_FILE needs some directory to write to.  It shouldn't matter if it's local or not (said with caveat above).  You should be able to change the proc to allow the file system to be passed in as a parameter.

>>I understand utl_file can point to any drive, is that correct?

Any drive you tell Oracle it can write to with the UTL_FILE_DIR parameter.
0
 

Author Comment

by:mahjag
ID: 16710975
>> Oracle does have some problems with remote drives

is this same as NFS mount drives?
0
 
LVL 19

Accepted Solution

by:
actonwang earned 375 total points
ID: 16711355
I suggest that you could take my example to do a quick test on you NFS mout drives.

IMHO, db is transparent to what os is dealing with drives so it shouldn't be a problem.

let me know how it goes.

I could search in metalink to see if anything wrong with NSF mount drive.

acton
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 16717302
>>is this same as NFS mount drives?

Yes.  I used the term 'remote' to mean any shared drives external to the current machine.  That's what NFS does, it mounts a filesystem from a remote machine.

I agree with actonwang, at this point you should just test it.
0

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!

Question has a verified solution.

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

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
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.
Suggested Courses

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question