Processing LAN files using UTL_FILE

[pdwissink]

I need to process multiple files from a LAN. This will be a continuing process. Is there any way to identify the directory which contains the files?

Also, how can I process multiple files at one time? It looks to me that with fopen only opens one file at a time and I would need to know the name of the file when using fopen. Am I wrong?

 

[vgg]

Hi ,

As you have said,it is possible to read only one file at a time using UTL_FILE.fopen
But it depends on how extensive your processing is.
If it is going to be a few files,you can very well do the
processing in a loop,otherwise you have to ask some external agent (say a C-routine through an user-exit) to do the processing,so that you can do multi-processing of files.
The directories of all the files you need to process must be specified in the the initialization parameter file,
INIT.ORA

For ex:
UTL_FILE_DIR = /usr/pwdissink/data1,/usr/pwdissink/data2,..

or

UTL_FILE_DIR = *

which will enable your program to read any directory.

Regards,

VGG

 

[pdwissink]

I will not know the names of the files before I processs. Is there a way to determine the names of the files in the directory in order to loop through the files?

Thanks,

pdwissink

 

[jee]

I am addressing the LAN part of your question. I assume you mean that you want to access files that are on a disk on another computer (other than your database server), but which are accessible on your network. I have some experience doing this with Windows-NT/2000. I expect the general idea is the same with UNIX, but perhaps some details differ:

Assuming a Windows environment:

1) You need to set up the folder(s) that holds the file(s) to be accessed for sharing.

2) Your Oracle service needs to connect to the operating system as a user who has privs to access the share. Normally under Windows-NT/2000 your Oracle service connects to the operating system as SYSTEM, but this user cannot access files on the network.

3) In your INIT.ORA file, with the UTL_FILE_DIR param, use UNC notation to specify the share.

To determine the names of the files on the fly, I expect you would have to write a PL/SQL callable routine in C or some other language that can access the OpSys API.

 

[vgg]

Hi

You may try these two options.

Option 1:
What I would suggest you is to call a pro*C executable from a shell script on the UNIX server.
This stored procedure can itself be called from the pro*c program.
This is what you can do:
1.Pass as argument to the shell script,the desired directory.
2.Pass this as argument again when you call your PRO*C
exe from the shell script.
3.In the Pro*C program,manipulate argv,get the directory,get the files in those directory using system calls(see FILE *popen(char *cmd,char *type)) and store them in an array(host array).
4.Then pass is this array as a table of record to your
stored procedure,and then use UTL_FILE in a loop within your procedure.

Option 2:
Have a table with the dir_name,file_name as columns.
Get the file_name(s) using the dir_name and then use
UTL_FILE.fopen.
But this is not a dynamic solution as the previous one.

regards,
VGG