using utl_file to process variable number of files

[sweetleaf]

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!

 

[sem]

You may use server side java procedure.

Regards, Dima

 

[SantaMufasa]

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

 

[sem]

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

 

[SantaMufasa]

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.

 

[SantaMufasa]

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

 

[sem]

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

 

[sem]

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

 

[sweetleaf]

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!

 

[hashfati]

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

 

[SantaMufasa]

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:

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.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.

 

[hashfati]

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

 

[SantaMufasa]

Actually, hashfati,

The same rules apply for DIRECTORY names, as well:

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.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.

 

[hashfati]

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

 

[SantaMufasa]

Yes, hashfati, network paths work just as well. (I, too, am using Win2k Pro.):

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.


Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.

 

[hashfati]

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

 

[SantaMufasa]

c:\> dir progra~1 /b

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.