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.Yes, you can. For example,
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 ...|||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.|||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.
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;|||thanks for the help, ill give that a try.
