[oracle] How to create a oracle sql script spool file

I have a question about spooling the the results of my program. My sample sql script looks like this.

  whenever sqlerror exit failure rollback
  set heading off
  set arraysize 1
  set newpage 0
  set pages 0
  set feedback off
  set echo off
  set verify off

 declare
 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;
 end;

 SPOOL 
 select cd,d from dual;
 SPOOL OFF
 EXIT;

The above script does not work, but I want to do something like this where in the begin end block we compute some values and i want to spool those results.

Thanks.

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.

BEGIN

FOR 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;
/

With spool:

  set heading off
  set arraysize 1
  set newpage 0
  set pages 0
  set feedback off
  set echo off
  set verify off

variable 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;

In order to execute a spool file in plsql Go to File->New->command window -> paste your code-> execute. Got to the directory and u will find the file.