Re: Dynamic spool files

From: Rene Nyffenegger <rene.nyffenegger_at_gmx.ch>
Date: 15 Mar 2005 20:09:30 GMT
Message-ID: <39otnpF5uj0fkU1@individual.net>

I'd use utl_file and create directory.

The following has been tested on 10g.

create or replace directory dir_temp as 'c:\temp';

declare
  output_file utl_file.file_type;
begin
  FOR rec IN (SELECT table_name FROM user_tables) LOOP

      output_file := utl_file.fopen('DIR_TEMP', rec.table_name, 'w');
      utl_file.put_line(output_file, 'some values to populate file');
      utl_file.fclose(output_file);

  END LOOP;
end;
/

hth
Rene

On 2005-03-14, Alex <avilner_at_gmail.com> wrote:
> Dear Netters,
>
> Seems that the question has been answered in the past, but for a single
> file...
>
> What I am looking to do is to generate a set of spool files, where the
> file name is based on the table_name from user_tables.
>
> So, in a nutshell, a script that would look like this:
>
> FOR rec IN (SELECT table_name FROM user_tables
> LOOP
> spool rec.table_name
> <select some values to populate the file>
> spool off
> END LOOP;
>
> So, it is the spool file per entry in the user_tables, and it needs to
> be done on the client side, rather than the server.
>
> Any ideas will be greatly appreciated!
> Thank you in advance.
>
> --Alex
>

-- 
  Rene Nyffenegger
  http://www.adp-gmbh.ch/

Received on Tue Mar 15 2005 - 14:09:30 CST