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 s

Solution 1:

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

set linesize 1000set feedback off
set underline off
spool C:\Users\file.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;

Solution 2:

Spool is a SQL*Plus command and PL/SQL doesn't "understand" it. You'd rather switch to UTL_FILE package, if it has to be PL/SQL.

Though, if that's the only code you have, I'd suggest you to simply use 4 separate SELECT statements, each using its own partition, and spool the result into a CSV file.

If you did that, you'd be done HOURS ago.

Solution 3:

spool is a SQL*Plus command which might be combined with an embedded PL/SQL code containing a cursor such as SYS_REFCURSOR with a dynamic SQL as

rec=$(sqlplus -S /nolog << EOF
 conn hr/hr
 whenever sqlerror exit sql.sqlcode
 SET LINESIZE 1000SET FEEDBACK OFFSET UNDERLINE OFFSET SERVEROUTPUT ON
 SPOOL file.csv REPLACE
 DECLARE
  v_cursor SYS_REFCURSOR;
  v_sql    VARCHAR2(32767); 
  cust     customer.cust_no%TYPE;
 BEGIN
  FOR var_1 in1..4LOOP
    v_sql := 
     'SELECT cust_noFROM customer PARTITION(customer_pr'||var_1||')WHERE city = :ct
         AND first_name = :fn
         AND salary = :sl';           
    OPEN v_cursor FOR v_sql USING'ba','john',1000; LOOP
      FETCH v_cursor INTO cust;
      DBMS_OUTPUT.PUT_LINE(cust);
      EXITWHEN v_cursor%NOTFOUND;
    ENDLOOP;
  ENDLOOP;  
  CLOSE v_cursor;
 END;
 /
 SPOOL OFF
EOF
)