spool to .csv file using for loop

need to spool output into .csv file using loop, fyi…I have data in 4 diff partition.

but no sure how to proceed.

code be like.

begin
FOR var_1 in 1 .. 4
LOOP
set linesize 1000
set feedback off
set underline off
spool C:Usersfile.csv replace
        SELECT cust_no FROM customer PARTITION (customer_PR'||var_1||')
        WHERE city='ba' AND first_name='john'
        AND salary=1000;
spool off;
END LOOP;
END;
/

result:-

Error report -
ORA-06550: line 4, column 5:
PL/SQL: ORA-00922: missing or invalid option
ORA-06550: line 4, column 1:
PL/SQL: SQL Statement ignored
06550. 00000 -  "line %s, column %s:n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:

not sure if I am spooling properly, also please check the index variable of for loop in used correctly in my select statement.

Answer

You don’t need 4 different queries, just list all your partitions in the query:

set linesize 1000
set feedback off
set underline off
spool C:Usersfile.csv replace
SELECT cust_no FROM customer PARTITION (customer_PR1, customer_PR2, customer_PR3, customer_PR4)
        WHERE city='ba' AND first_name='john'
        AND salary=1000;
spool off;