oracle pl/sql multiple spool help required

Vader · #1 (**permalink**) 03-25-04, 05:13

Now Im not an expert or anything with SQL so bear with me...

I have 3 seperate queries that each use the spool command to write to a file on the server.
e.g. i use the following construct in each of the three sql files:

set blah
set blah
spool /blah/blah.rpt
script
spool off

I wish to put these three queries into one script. Can I use the spool command three times in the one file? i.e. have spool then spool off, three times in one *.sql file?

I have read a couple of posts on pl/sql spooling and people mention utl_file, but I have no idea what this is and even if i have it...

Any help would be great. Thanks.

Littlefoot · #2 (**permalink**) 03-25-04, 05:43

Yes, you can. For example,

PHP Code:


			
spool a1.txt

select count(*) from tab;

spool off;



spool a2.txt

select sysdate from dual;

spool off;



spool a3.txt

select 'x' dummy from dual;

spool off;

will generate 3 .txt files. However, I can't figure out why didn't you try it yourself ...

Vader · #3 (**permalink**) 03-25-04, 05:59

I did try it and it didnt work the way I expected...

I have the three spools and also I have a title for each of the three files:

e.g.
ttitle 'Thanet ** Items made MISSING between 7 and 14 days ago' skip 2

I have one of these fore each script [different txt of course]. I have found that if the query returns no results it will not print the title to the file and so i have an emty file. If there are some results from on of the queries then the title does display.

I was wanting to make sure that I was using the spool correctly so thats why i asked.

Littlefoot · #4 (**permalink**) 03-25-04, 06:32

Perhaps this helps ... if you include a "dummy" query into every "spool block", you won't get an empty file even though your "real" query returns no records.

PHP Code:


			
SPOOL a1.txt

  TTITLE 'First top title' skip 2 

  BTITLE 'First bottom title'

  COLUMN dummy noprint;

  SELECT 'x' dummy FROM DUAL;



  SELECT COUNT (*) FROM tab;

SPOOL off;



SPOOL a2.txt

  TTITLE 'Second top title' skip 2 

  BTITLE 'Second bottom title'

  COLUMN dummy noprint;

  SELECT 'x' dummy FROM DUAL;

 

  -- this query returns no rows 

  SELECT 'x' FROM dual WHERE sysdate = sysdate + 1;

SPOOL off;

Vader · #5 (**permalink**) 03-25-04, 07:15

thanks for the help, ill give that a try.

Internet Services

Web Development

Digital Marketing

Consumer Tech