How to create a oracle sql script spool file

Question

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.

Ollie · Accepted Answer · 2011-12-21T15:47:48.130

16

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 WRAPPED
SET VERIFY OFF

SET FEEDBACK OFF
SET TERMOUT OFF

column date_column new_value today_var
select to_char(sysdate, 'yyyymmdd') date_column
  from dual
/
DBMS_OUTPUT.ENABLE(1000000);

SPOOL C:\output_&today_var..txt

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;
   --
   DBMS_OUTPUT.put_line('cd: '||cd);
   DBMS_OUTPUT.put_line('d: '||d);
END; 

SPOOL OFF

SET TERMOUT ON
SET FEEDBACK ON
SET VERIFY ON

PROMPT
PROMPT Done, please see file C:\output_&today_var..txt
PROMPT

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;

edited Dec 21 '11 at 15:47

answered Dec 21 '11 at 14:16

Ollie

17,058
7
48
59

What happens if there is a cursor in the block and it loops inside the begin-end. Like 'loop c := a+10; select c into :d from dual; end loop; end; SPOOL select :d from dual; SPOOL OFF EXIT;' So, now will it give all the results or else the final output stored in :d. How to spool if there is a loop. – user987900 Dec 21 '11 at 15:39
1

You could use DBMS_OUTPUT within the cursor loop to output the value at every iteration of the cursor. See edit.. – Ollie Dec 21 '11 at 15:45
Hi Ollie, i have one more question. The above code works fine when i run from SQL*plus. But, i have a shell script which invokes this SQL script and spools the output to text file. I registered the shell script in Oracle apps, and when i submit a request from apps to run. It shows normal and running but it never gets completed. Do, i have to make any changes to the above script when invoking from a shell script. – user987900 Jan 10 '12 at 14:04
2

If you are running it in the background, I'd worry about where you expect the output to go. Under these circumstances you might be better off using the UTL_FILE package to write the output to a file directly from within the PL/SQL block, you can then read that file using whatever tool you want (PL/SQL, Java, UNIX, etc.). it's pretty simple to do and you'd have less complexity than spooling output through SQLPlus and then into a shell script etc. http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/u_file.htm – Ollie Jan 10 '12 at 14:22
To run this from a script in SQL Plus (like @myscript.sql) I had to add "/" after the "END;" of the Anonymous block. – Dzyann Jun 03 '15 at 18:58
Does anyone have a super simple spool example? I just want to export a single line table, but I'm going to have to do this same export several times so would love to not use (right click -> export ...) every time. I can't find an example that doesn't involve 30+ lines of code. – J.D Jul 16 '18 at 16:08

score 2 · Answer 2 · answered Dec 21 '11 at 14:22

2

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;

answered Dec 21 '11 at 14:22

Florin Ghita

17,525
6
57
76

What happens if there is a cursor in the block and it loops inside the begin-end. Like 'loop c := a+10; select c into :d from dual; end loop; end; SPOOL select :d from dual; SPOOL OFF EXIT;' So, now will it give all the results or else the final output stored in :d. How to spool if there is a loop. – user987900 Dec 21 '11 at 15:39
Won't work in that way. You can spool SQL selects, not PL/SQL. For output in pl/sql code, you can use dbms_output package, as Ollie has indicated. – Florin Ghita Dec 21 '11 at 20:14

score -1 · Answer 3 · answered Aug 21 '17 at 06:18

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

score -3 · Answer 4 · answered Jun 20 '12 at 16:14

-3

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.

answered Jun 20 '12 at 16:14

sweety

1

How to create a oracle sql script spool file

4 Answers4

Linked