Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

using utl_file to process variable number of files

Status
Not open for further replies.

sweetleaf

Programmer
Jan 16, 2001
439
CA
Hi,

I was wondering if anyone knew how, using plsql alone, i could process a variable/unkown number of files in a given unix directory - where even the filenames are not known beforehand.

Is this even possible? My understanding is that utl_file.fopen can process 1 file alone and that
in order to be able to count/loop thru all files in a given directory one would have to use pro * C or a unix shell script.. Yet, i hear theres an 8i package out there that will handle this..

Any help would be greatly appreciated!

 
You may use server side java procedure.

Regards, Dima
 
Sweetleaf,

Besides a java procedure that Sem mentions, you certainly may use PL/SQL to do precisely what you want.

To test your request, I just wrote and tested a PL/SQL script that does the following:

1) Outputs to a flat file a listing of text files in a directory: "host dir *.txt /b > TempDir.lst" (For Windows, but you can do the same type of thing for Unix.)
2) Opens the directory-listing flat file (File 1) and loops through File 1.
3) For each text-file name, opens that file (File 2, iteratively) and loops through each text file, writing each file's contents to a File 3 output file.

Rather than reproduce all that code here in this thread, e-mail me at dave@dasages.com with your e-mail address and I'll send you the PL/SQL code.

Dave
 
Dave: I'm pretty sure you mix up server side pl sql engine with client side one, used in Oracle Developer products. HOST can not be called on server.

Regards, Dima
 
Dima, No, I don't believe I'm mixing up client-side with server-side PL/SQL. I just successfully did the following:
1) connected to my Unix Oracle Server,
2) ran my PL/SQL script, which successfully hosted out to my Unix path of choice, and in turn,
3) successfully piped the results of an "ls" command to a flat file
4) opened and read the "ls" file as input in PL/SQL,
5) opened and read each of the files listed in the "ls" file as input, and
6) opened and wrote to my output listing file from PL/SQL.

Even if I couldn't host out of SQL*Plus on the server side, that is not a big issue since the point of SweetLeaf's original question was can one process an unknown number of o/s files, looping through the records of each file and the answer certainly is "yes".

Without question, I certainly can read and write to multiple flat files simultaneously from PL/SQL, both on the server side and the client side. The flat files that I process, however, would each need to reside on their respective machines.
 
...As a clarification: In my script, which I successfully run on both client and server side, my "host" command occurs from SQL*Plus (not PL/SQL itself), then continues on to process an anonymous PL/SQL block, which opens and processes (loops through) multiple input and output flat files simultaneously. The flat-file processing can likewise occur successfully from within stored PL/SQL procedures in addition to anonymous blocks.
 
Dave: check again whether you call HOST from sql*plus or pl/sql block? It's not the same. Sql*plus supports a lot of its own commands that are not sql (pl/sql) ones.

Of course, you may create some simple external procedure, wrap it by pl/sql procedure called host and then call it, but this is not built-in.

Regards, Dima
 
You've answered my question before I asked it :)
Thus, if you're on remote workstation, you can not use utl_file, because host may list only files local to your sql*plus, while utl_file works on server. Further, if you're on server, you don't need HOST, because you may pass file names to sql*plus from some shell script.

Regards, Dima
 
Thanks friends,

Your input is useful. I will try out Dave's ingenious sounding solution to my problem. Dave I'll email you.

Thanks for your valued analysis/attention to my problem!

 
Dave, your solution seems good. but I think it doesn't cater for folders which have spaces in their name like c:\program files
could you paste that code here for us?

thanks
 
Hashfati,

(Wow...you resurrected an very old thread.)

If you wish to see Windows file names without spaces, then you can issue the following command:
Code:
d:\>dir /x hash*
 Volume in drive D is New Volume
 Volume Serial Number is 4C84-F6D0

 Directory of d:\

04/28/2005  09:51a                  18 HASHFA~1.TXT    hash fati file with spaces.txt
               1 File(s)             18 bytes
*************************************************************************************
Notice that the full file name is "hash fati file with spaces.txt", but the name of the file without spaces is "HASHFA~1.TXT". You may successfully access the file by either name.

Let me know if this resolves your question.

[santa]Mufasa
(aka Dave of Sandy, Utah, USA)

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.
 
thanks Dave for a quick response. I m not talking about filenames with spaces I m talking about DIRECTORY names with spaces. this doesn't seem to work like
dir c:\program files > file.txt

do u have any idea?

thanks again
 
Actually, hashfati,

The same rules apply for DIRECTORY names, as well:
Code:
D:\>dir /x
 Volume in drive D is New Volume
 Volume Serial Number is 4C84-F6D0

 Directory of D:\
...
03/02/2005  03:11p      <DIR>          PROGRA~1        Program Files
...
Notice how my "Program Files" directory is alternately accessible as "PROGRA~1".

Let me know if problems still exist on this.

[santa]Mufasa
(aka Dave of Sandy, Utah, USA)

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.
 
Thanks again
u r so responsive. I m using Win2k pro. I'll try it but I m not sure its gonna work. secondly u said something about network paths? I think we cant do the same with unc path like
dir \\server\d\folder
I m facing exactly the same problem which sweetleaf had but I mtrying to get it from network path with spaces in folders

I hope u will help me out
thanks
 
Yes, hashfati, network paths work just as well. (I, too, am using Win2k Pro.):
Code:
D:\>dir \\draper\users\dhunt /x
 Volume in drive \\draper\users is UserVol
 Volume Serial Number is 307D-03A3

 Directory of \\draper\users\dhunt

07/07/2004  11:04p      <DIR>                          .
07/07/2004  11:04p      <DIR>                          ..
06/28/2000  02:08p                  90                 ASBCSQL.TXT
06/28/2000  02:08p               3,009                 ASBCSQL1.TXT
06/28/2000  06:42p              29,304                 ASBCSQL2.TXT
08/02/2000  03:55p               3,117 CLASSN~1.TXT    CLASSNOTES1.TXT
Let me know if you encounter any problems.


[santa]Mufasa
(aka Dave of Sandy, Utah, USA)

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.
 
Thanks Dave
I'll try this & then let u know but one problem is still there
c:\> dir program files /b
how do i make it work?

thanks
hash
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top