Multiple spool files in a single process
I've got a need to generate dozens, perhaps hundreds of identical reports from a single data source. (Identical in format, not content.)
What I'd like to do is write a single script that will execute the same query repeatedly with a slightly different filter and spool the output to different file names. Pseudo-code is below:
BEGIN
FOR cid IN 1..100
LOOP
SPOOL c:\temp\data_file_&cid
select item_list INTO C FROM c_prrd where coalition_id = cid;
END LOOP;
END;
I can seem to find a "reasonable" solution. :(
Kent
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
>>'select item_list INTO C
Not sure where 'C' is defined/declared so this might be an issue.
Not sure where 'C' is defined/declared so this might be an issue.
ASKER
Yeah, it is chicken and egg. Can't create the loop except inside a block and can't execute the spool statement except outside the block.
The right way is to chase cursors and write the results with the UTL_FILE utility, but it sure would be "easier" if sqlplus and PL/SQL were more on speaking terms.
Kent
The right way is to chase cursors and write the results with the UTL_FILE utility, but it sure would be "easier" if sqlplus and PL/SQL were more on speaking terms.
Kent
ASKER
>>'select item_list INTO C
Not sure where 'C' is defined/declared so this might be an issue.
Oops. :) Copied one too many things from "just another experiment gone awry".
I do kind of like the meta-sql approach, but I generally despise writing these temporary files as it just adds headaches to the next guy that might have his hands in this. Still, it's shorter and cleaner than chasing cursors.
Kent
Not sure where 'C' is defined/declared so this might be an issue.
Oops. :) Copied one too many things from "just another experiment gone awry".
I do kind of like the meta-sql approach, but I generally despise writing these temporary files as it just adds headaches to the next guy that might have his hands in this. Still, it's shorter and cleaner than chasing cursors.
Kent
Maybe some other Expert will arrive later and have some tricks that I'm unaware of.
That way we both get some new tricks!
That way we both get some new tricks!
ASKER
Sorry for not closing this right off, but I was busy using the results of the answer.
:^}
:^}
No problem. Glad you were able to use one of the methods.
As soon as you add BEGIN and END, it is PL/SQL.
You can always use UTL_FILE to open the files and write the values.