How to create a oracle sql script spool file

This will spool the output from the anonymous block into a file called output_<YYYYMMDD>.txt located in the root of the local PC C: drive where <YYYYMMDD> is the current date:

SET SERVEROUTPUT ON FORMAT WRAPPEDSET VERIFY OFFSET FEEDBACK OFFSET TERMOUT OFFcolumn date_column new_value today_varselect to_char(sysdate, 'yyyymmdd') date_column  from dual/DBMS_OUTPUT.ENABLE(1000000);SPOOL C:\output_&today_var..txtDECLARE   ab varchar2(10) := 'Raj';   cd varchar2(10);   a  number := 10;   c  number;   d  number; BEGIN   c := a+10;   --   SELECT ab, c      INTO cd, d      FROM dual;   --   DBMS_OUTPUT.put_line('cd: '||cd);   DBMS_OUTPUT.put_line('d: '||d);END; SPOOL OFFSET TERMOUT ONSET FEEDBACK ONSET VERIFY ONPROMPTPROMPT Done, please see file C:\output_&today_var..txtPROMPT

Hope it helps...

EDIT:

After your comment to output a value for every iteration of a cursor (I realise each value will be the same in this example but you should get the gist of what i'm doing):

BEGIN   c := a+10;   --   FOR i IN 1 .. 10   LOOP      c := a+10;      -- Output the value of C      DBMS_OUTPUT.put_line('c: '||c);   END LOOP;   --END; 

With spool:

  set heading off  set arraysize 1  set newpage 0  set pages 0  set feedback off  set echo off  set verify offvariable cd varchar2(10);variable d number; declare ab varchar2(10) := 'Raj'; a number := 10; c number; begin c := a+10; select ab,c into :cd,:d from dual; end; SPOOL  select :cd,:d from dual; SPOOL OFF EXIT;

To spool from a BEGIN END block is pretty simple. For example if you need to spool result from two tables into a file, then just use the for loop. Sample code is given below.

BEGINFOR x IN (    SELECT COLUMN1,COLUMN2 FROM TABLE1    UNION ALL    SELECT COLUMN1,COLUMN2 FROM TABLEB)    LOOP    dbms_output.put_line(x.COLUMN1 || '|' || x.COLUMN2);END LOOP;END;/