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

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.