Re: How to spool from a cursor ?

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;

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_at_duke.telepac.pt>...
>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
>
>
>
Received on Fri Mar 05 1999 - 10:41:42 CET