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;
As OldProgrammer mentioned, you cannot call
SPOOL
withinPL/SQL
. But you can can print to a spooled file from withinPL/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:
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.
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.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
Now, the working-directory has three new files:
And each only has the data for its zone. For example:
Yields: