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.

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.