SQLPlus dynamic spool filename from Table in a FOR Loop

Question

I am very new to Oracle Develop PL/SQL language (so please excuse limited understanding). I am trying to use a spool command to dynamically create a filename based upon a table row values.

Below is the FruitCost table. And I want to create a CSV file for each of the rows. e.g.

1. 10_apple.csv
2. 11_orange.csv etc.

id	name	minprice	maxprice
10	apple	2.5	4.5
11	orange	1.0	2.5
12	banana	0.5	1.5

With some effort and much help from Stackoverflow, I have come up with the below script, but it is not working. I have tried to use substitute variable and assign bind variable to substitute variable.

begin
 for r in (select * from fruitCost)
 Loop
 var id number(6)
 var name varchar2(10)
 begin
 :id := r.id;
 :name := r.name;
 end;
 
 column test new_value s_id noprint 
 select :id test from dual;
 
 column test new_value s_name noprint 
 select :name test from dual;
 spool \&s_id || '_' || &name..csv
 
 /* query */
 
 spool off;
 
 end loop;
end;

I am using SQL Developer v.22.2 on Windows 10. Please help. Thank you.

• Justin Cave: Why do you want this? You can't combine PL/SQL with SQL*Plus commands. PL/SQL runs on the server, SQL*Plus is a client tool. You could use the utl_file package in PL/SQL to generate a file but that would generate files on the server not on the client.
• DigitalNomad: I would like to create csv files on the client machine using spool. In my limited online search, there was some workaround to do this, but as I said, I am kind of new at this, so any useful information is much appreciated. Thanks.
• Justin Cave: You're probably not going to be able to do this solely in a SQL*Plus script. At least not without doing something hokey like writing a SQL*Plus script that queries the database and generates additional SQL*Plus scripts to do the actual spooling or "cheating" and calling a shell script from within SQL*Plus. It would be far more sensible in 99.999% of cases to write a SQL*Plus script that took a couple of parameters and call that from a shell script that queried the database and passed the parameters to the SQL*Plus script.
• psaraj12: stackoverflow.com/questions/55576215/…
• DigitalNomad: @JustinCave, can you please help with the shell script that can do as you recommended? Thank you.

Answer 1

You can use the below script for your requirement which will create a header and then spool the columns in csv format.Please note press F5 to run the script.

 set pages 0
 set lines 500
 set trimspool on
 set feedback off
 set echo off
 spool E:\temp_script.sql
 select distinct 'spool E:\'||id||'_'||name||'.csv' || chr(10)
 || ' SET SQLFORMAT CSV'||chr(10)||' select * from fruitCost where id = '''
 || id ||''''|| ' and name='''||name ||''''||' order by id,name;' || chr(10)|| 'spool off'||chr(10)
 from fruitCOst;
 spool off
 @E:\temp_script.sql