generate excel file using cursor in anonymous block in oracle

Question

I have below anonymous block where i am using cursor to generate the resultset and save it into TEST_REPORT.csv file. But i am getting error on the line where i am using spool as:

PLS-00103: Encountered the symbol "H" when expecting one of the following:
   := . ( @ % ;

I believe i cannot use spool in PL/SQL but not sure so i have tried below code. And also i cannot use UTL_FILE or UTL_FILE_DIR logic because of security restriction on the Production. As employees works on different department i want to generate separate csv file for each employee with respect to their department.

Is there anyway where i can break this code and use spool to generate csv file or any other logic ? Also if it is not possible to use spool in anonymous block then can i use it during execution of this code to generate files ?

If its not possible using spool then is it possible if i can organize my query result in such a way that it will be easy to export the result into single csv file after executing this anonymous block and then i can separate the single csv file into multiple files depending on the employee with their department manually ?

Generate-And-Run.sql file

SET SERVEROUTPUT ON;
set verify off
SET LONG 100000
SET lines 1000 
SET sqlformat SELECT;
SPOOL C:\Loop-Flattener.sql;

PROMPT VAR V_A VARCHAR2(64);


BEGIN
  FOR TARGET_POINTER IN (select ID,
       name,
       ST_ID      
from TEST_REPORT
where rownum <5)
    LOOP
    DBMS_OUTPUT.PUT_LINE('DEFINE TARGET = '''||TARGET_POINTER.ID||''';');
    DBMS_OUTPUT.PUT_LINE('EXEC :V_A := '''||TARGET_POINTER.ID||'''; ');
    DBMS_OUTPUT.PUT_LINE('@@Target-Csv-Generator.sql;');
  END LOOP;
END;
/

SPOOL OFF;

Answer 1

As OldProgrammer mentioned, you cannot call SPOOL within PL/SQL. But you can can print to a spooled file from within PL/SQL, and can pre-compile/flatten the loop into a delegating intermediate worker-script that repeatedly calls the csv-generator.

Update. In response to the specifics you provided, the following should work for you.

Example Test data:

INSERT INTO CSS_BOUTIQUE VALUES ('EUR', 'Belgium', 'a0Hb0000006LLdQ');
INSERT INTO CSS_BOUTIQUE VALUES ('EUR', 'Portugal', 'a0HB0000006LLOG');
INSERT INTO CSS_BOUTIQUE VALUES ('EUR', 'Portugal', 'a0HB0000006LLYu');

INSERT INTO CSS_BOUTIQUE VALUES ('ASIA', 'Korea', 'e0HB0000016MEIi');
INSERT INTO CSS_BOUTIQUE VALUES ('ASIA', 'China', 'e0HB0000026MEIi');
INSERT INTO CSS_BOUTIQUE VALUES ('ASIA', 'Japan', 'e0HB0000036MEIi');

INSERT INTO CSS_BOUTIQUE VALUES ('SA', 'Chile', 's0HB0000016MEIi');
INSERT INTO CSS_BOUTIQUE VALUES ('SA', 'Argentina', 's0HB0000026MEIi');
INSERT INTO CSS_BOUTIQUE VALUES ('SA', 'Equador', 's0HB0000036MEIi');

Then create the following two scripts:

Generate-And-Run.sql This script will create a flat, pseudo-loop by generating an intermediate script filled with iterative commands to set new variable names and call a reusable csv-generator.

SET ECHO OFF;
SET FEEDBACK OFF;
SET HEAD OFF;
SET LIN 256;
SET TRIMSPOOL ON;
SET WRAP OFF;
SET PAGES 0;
SET TERM OFF;
SET SERVEROUTPUT ON;

SPOOL Loop-Flattener.sql;

PROMPT VAR V_ZONE_NAME VARCHAR2(64);


BEGIN
  FOR TARGET_POINTER IN (SELECT DISTINCT ZONE FROM CSS_BOUTIQUE)
    LOOP
    DBMS_OUTPUT.PUT_LINE('DEFINE TARGET = '''||TARGET_POINTER.ZONE||''';');
    DBMS_OUTPUT.PUT_LINE('EXEC :V_ZONE_NAME := '''||TARGET_POINTER.ZONE||'''; ');
    DBMS_OUTPUT.PUT_LINE('@@Target-Csv-Generator.sql;');
  END LOOP;
END;
/

SPOOL OFF;

@@Loop-Flattener.sql;

Target-Csv-Generator.sql: This script will do the work of generating a single csv. Please note, the report-name here is a simple REPORT_FOR... without any additional path to help ensure it gets created in the working directory.

SPOOL REPORT_FOR_&&TARGET..csv;

PROMPT zone,market, boutique_id;

select zone||','||
       market||','||
       boutique_id      
from CSS_BOUTIQUE
where rownum <5 and ZONE = :V_ZONE_NAME;

SPOOL OFF;

Then run it:

Place the above two scripts into the directory where you want your CSVs to be created, then Start SQLPlus in that directory

SQL*Plus: Release 12.2.0.1.0 Production on Sun Sep 10 14:38:13 2017
SQL> @@Generate-And-Run

Now, the working-directory has three new files:

REPORT_FOR_EUR.csv
REPORT_FOR_SA.csv
REPORT_FOR_ASIA.csv

And each only has the data for its zone. For example:

cat REPORT_FOR_ASIA.csv 

Yields:

zone,market, boutique_id
ASIA,Korea,e0HB0000016MEIi
ASIA,China,e0HB0000026MEIi
ASIA,Japan,e0HB0000036MEIi