How to spool from a cursor ?

[JR]

Hi
I have to spool to a file the contents of a table.
To do that I'm using a sql script like this:
set (linesize, heading,...)
spool msg.out
select text
from message_lines
where msg_num =(select message_num from replies)
and flg_msg='R'
order by num_line;
spool off;

This works fine, as long as I have one reply on replies table.
Now if I have multiple replies, I have to spool to diferent files.

I would like to do something like:
declare
cursor A (cursor declaration)
begin
for x in A loop
set ...
spool msg.out
select text from message_lines
where msg_num =x.message_num ...
...
spool off;
! <change name at o.s. level>
end loop;
end;

How can I do that ?
I can't use the UTL_FILE

Thanks in advance
Jose Raposo

[Jan-Marcel]

Hi Jose,

You don't have to rename your spool file at os level. Just define a variable
which contains your file name. Update this variable after in your loop after
spooling the first fetch.

declare
cursor A (cursor declaration)

v_filename varchar2
v_filename_name varchar2;
v_filename_number number(3) := 1;

begin
for x in A loop
set ...

spool v_filename

select text from message_lines
where msg_num =x.message_num ...
...
spool off;

v_filename_number := v_filename_number + 1
v_filename := v_filename_name||to_char(v_filename_number)

end loop;
end;

JR heeft geschreven in bericht <7bm27j$tdi$1...@duke.telepac.pt>...