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 ?
DECLARE
a varchar2(100);
b varchar2(100);
c varchar2(100);
CURSOR c1
IS
select 'id' ||','||
'department_name' ||','||
'employee'
result from dual
union all
select id ||','||
department_name ||','||
employee
from EMPLYOEE_MARKET;
BEGIN
OPEN c1;
LOOP
spool H:\TEST_REPORT.csv ;
FETCH c1
INTO a, b, c;
EXIT WHEN c1%NOTFOUND;
spool off;
END LOOP;
CLOSE c1;
END;