Simpler Navigation for Servers and Operating Systems - Please Update Your Bookmarks
Completed: a much simpler Servers and Operating Systems section of the Community. We combined many of the older boards, so you won't have to click through so many levels to get at the information you need. Check the consolidated boards here as many sub-forums are now single boards.
If you have bookmarked forums or discussion boards in Servers and Operating Systems, we suggest you check and update them as needed.
cancel
Showing results for 
Search instead for 
Did you mean: 

Spool Data

intp
Frequent Advisor

Spool Data

Hi,

I have to spool 1.5 million records into a flat file.

First line (header record) in the spool file needs to be current date (YYYYMMDD)

starting second line till end of result set
is whatever returned by my actual query.

Last (Trailer) line should be the total count of records in the flat file. Excluding header and trailer records.

I tried using UNION...but the order getting changed and header record comes somewhere in between of the actual query result and same with trailer record too..i guess its sorting
implicitly..how to get it in same order?

Select from dual
union
actual query
union
Select count(*) from


Thanks
9 REPLIES
Patti Johnson
Respected Contributor

Re: Spool Data

Just use individual queries.

set feedback off
set heading off
set pages 0
select sysdate from dual;
select * from dual;
select count(*) from dual;


returns these three lines

18-APR-06
X
1


Patti
intp
Frequent Advisor

Re: Spool Data

ok.

This is my problem... this is how the order changes..

select to_char(sysdate,'YYYYMMDD') from dual
union
select 'ABCD' from dual
union
Select to_char(12345) from dual

my actual query return character type data.
intp
Frequent Advisor

Re: Spool Data

Sorry...i got your point...

Just ignore union and run as 3 queries..

OK thanks...let me try it.
TwoProc
Honored Contributor

Re: Spool Data

You'll need to aggregate the result of the union as a sub-query, and add a fake order by column (1,2,3) external to that subquery. Then that whole thing above is treated once again a subquery, and only the dates themselves are drawn out from the level 2 subquery.

Basically, you need a subquery that is ordered outside of the union, but from stubbed data from inside the union, and then in the outermost query, just don't select the stubbed data as part of the result set.

select a
from
(select a,aa
from
(select to_char(sysdate,'YYYYMMDD') a , 1 aa from dual
union
select 'ABCD' a ,2 aa from dual
union
select to_char(12345) a ,3 aa from dual)
order by 2)
/

A
--------
20060418
ABCD
12345


We are the people our parents warned us about --Jimmy Buffett
intp
Frequent Advisor

Re: Spool Data

yes...i tried this way first ...but it takes around 1.5 hrs to complete...may be bcos of to_char and then order by..

if i run as 3 seperate queries..it only takes 15-20mins...i dont need to put to_char or order by .. still testing ...will
update time and performance.
intp
Frequent Advisor

Re: Spool Data

it takes around 25 mins to spool the file (1.5 million records).. order is coming as i expect ..so fine.

Select from dual
union
actual query
union
Select count(*) from

actual query ==> returns 1.5 million records
and takes the most part of execution time.

Select count(*) from ==>
again has to run the same query(almost alike my actual query) again to find the count.

IS there a way i can avoid third query and get the count from / while running my second query ?

Hope i didnt confuse.

Patti Johnson
Respected Contributor

Re: Spool Data

If you don't set feeback off - the actual query will return the number of rows selected. Then you can reformat the
"1 row selected"
statement to be whatever you need - just a number. It will require a little formatting after the query completes, but it will eliminate reading all the data again to get the count.

Patti
intp
Frequent Advisor

Re: Spool Data

how to capture that "1 Row Selected"
and add it as last record in the spool file ?
Yogeeraj_1
Honored Contributor

Re: Spool Data

hi,

One easy way would be to use grep after you have finished spooling the output to your file.

e.g.
grep -v "1 row selected" filename.lis > newspoolfile.lis
echo "1 row selected" >> newspoolfile.lis



hope this helps too!

kind regards
yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)