How to spool to multiple files within a loop?

Question

I am using Oracle 12c.

I have a table of EMPLOYEES that looks something like this:

EMPLOYEE_NAME | DEPARTMENT_NAME
--------------|----------------
Jim           | Sales
Barb          | Marketing
Paul          | Sales
Frank         | Sales
Cindy         | Accounting
Carl          | Marketing
... and so on ...

I would like to do something like this if PL/SQL permitted it:

FOREACH dept IN (SELECT DISTINCT DEPARTMENT_NAME FROM EMPOLYEES) DO
    SPOOL 'list_' || :dept || '.txt';
    SELECT EMPLOYEE_NAME
    FROM EMPLOYEES
    WHERE DEPARTMENT_NAME = :dept;
    SPOOL OFF;
DONE;

which would result in a set of files like this:

list_Sales.txt :

Jim
Paul
Frank

list_Marketing.txt

Barb
Carl

list_Accounting.txt

Cindy

... and so on ...

Any ideas on how I can accomplish this?

Thank you.

Answer 1

You can use utl_file package to generate operating system stream file as @kfinity pointed out

1) Create a directory in Oracle where you want file to be written. This directory/path has to be accessible and writeable from the database server.

CREATE DIRECTORY UTL_FILE_DIR AS '/scratch/data/file_data';

2) Now get read write access to this directory.

grant READ, WRITE on UTL_FILE_DIR to <reporting_user>;

3) Code that will generate the file.

declare
    v_outfile  utl_file.file_type;
    v_file     varchar2(100);
    v_path     varchar2(100) := 'UTL_FILE_DIR';
                           -- alias for the directory where your text files generated at OS.
begin
    for d in ( select distinct department_name as dept from employees )  
    loop  
        v_file    := 'list_'||d.dept||'.txt';
        v_outfile := utl_file.fopen(v_path, v_file, 'w');
      for c in ( select employee_name from employees where department_name = d.dept )
      loop
       begin 
         utl_file.put_line(v_outfile,c.employee_name);                 
       end; 
      end loop;
        utl_file.fclose(v_outfile); 
    end loop;
end;

Answer 2

spool is a client command, it doesn't have any meaning within a PL/SQL block; and you can't write to files on the client machine from PL/SQL. You could write to the server using utl_file (as @kfinity suggested, and @BarbarosÖzhan demonstrated), but that might not be appropriate for your situation.

If you want to stick with spooling to the client machine, and don't want to post-process the output into multiple files (as @KaushikNayak suggested), you could use another level of spool to generate a script, something like:

spool temp_script.sql

select distinct 'spool list_' || department_name || '.txt' || chr(10)
  || 'select employee_name from employees where department_name = '''
    || department_name || ''' order by employee_name;' || chr(10)
from employees;

spool off

@temp_script.sql

As a more worked-through example using the default HR schema employee and department tables:

set pages 0
set lines 500
set trimspool on
set feedback off
set echo off

spool temp_script.sql

prompt set pages 0
prompt set lines 500
prompt set trimspool on
prompt set feedback off
prompt set echo off

select distinct 'spool list_' || department_name || '.txt' || chr(10)
  || 'select employee_name from employees where department_name = '''
    || department_name || ''' order by employee_name;' || chr(10)
from employees;

prompt spool off

spool off

@temp_script.sql

The contents of temp_script.sql in this case end up as something like:

set pages 0
set lines 500
set trimspool on
set feedback off
set echo off
spool list_Administration.txt
select first_name || ' ' || last_name from employees where department_id = 10 order by last_name, first_name;
spool list_Marketing.txt
select first_name || ' ' || last_name from employees where department_id = 20 order by last_name, first_name;
spool list_Purchasing.txt
select first_name || ' ' || last_name from employees where department_id = 30 order by last_name, first_name;
...
spool list_Payroll.txt
select first_name || ' ' || last_name from employees where department_id = 270 order by last_name, first_name;
spool off

and one file for each department, e.g. list_Accounting.txt:

William Gietz
Shelley Higgins

and list_Executive.txt:

Lex De Haan
Steven King
Neena Kochhar

etc. In this example there are several empty files, but if I'd made the initial query join the two tables there wouldn't have been; as you're querying a single table that won't happen for you either.