Suggestion for multiple SQL execution from list of values

[MCubitt]

A bit of a cryptic subject perhaps, but this is my question:

I have an SQL script which uses THREE variables to generate a CSV output row. FromUserID, ToUserId and ToName.

Currently the user is prompted for these. However, more common than not the user needs to run the same thing for different values (sometimes FromUserID will be the same).

My plan is for the user (technical staff) to create a text file with the variables, eg:
bloggsj,smithj,John Smith
bloggsj,collinsp,Pauline Collins
drewt,mousem,Mickey Mouse

Can SQLPlus read a text file as well as writing it (spool) ?

I thought of using a VB script to read the file but my understanding is that with 9i the spool will not append, only overwrite the file.

Does any one have any super suggestions?

Thanks



There's no need for sarcastic replies, we've not all been this sad for that long!

 

[nagornyi]

Instead of the text file, I would suggest creating a regular table with those variable values. The table can then be joined to the original SQL to run it for all variable combinations.

 

[SantaMufasa]

Martin,

Although "spool" does not append, you can append using Oracle (PL/SQL's) "utl_file" package. The package allows you to read ('R'), write ('W'), or append ('A') a file. Keep in mind, however, that "utl_file" only sees the server file system, just at "spool" only sees your client file system. If that poses no problem for you, then "utl_file" may contribute to you solution.

Mufasa
(aka Dave of Sandy, Utah, USA @ 16:58 (10Aug04) UTC (aka "GMT" and "Zulu"), 09:58 (10Aug04) Mountain Time)

 

[MCubitt]

nag: Thanks, but I would rather not affect Oracle, keeping things local.

Stanta: Thanks, I have used an easier, lazy way I have passed 10 parameters and run the query 10 times. Not perfect but it's kinda ad-hoc.

Cheers guys



There's no need for sarcastic replies, we've not all been this sad for that long!