plsql into text file

[wsam]

Newbie - I am trying to create a plsql statement that takes results from one select statement and puts them into the 2nd and then puts the results from the 2nd statement into a text file

ie:

1st

SELECT HistNum FROM Hist WHERE date >= to_datefdt,'mm/dd/yyyy') and date < to_datetdt,'mm/dd/yyyy')

The results from above should go into:

2nd

SELECT * FROM Hist_Segs WHERE HistNum IN (results of 1st statement)

Thanks in Advance

 

[LKBrwnDBA]

Try this:

SPOOL MyResult.txt
SELECT * FROM Hist_Segs WHERE HistNum IN (
SELECT HistNum FROM Hist
 WHERE date >= to_date(:fdt,'mm/dd/yyyy')
   AND date < to_date(:tdt,'mm/dd/yyyy'));
SPOOL OFF

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[wsam]

Thanks: What I have is

Spool MyResult.txt
SELECT hist_segs-* FROM Hist A, Hist_Segs B
WHERE A.HistNum = B.HistNum AND
A.date >= to_datefdt,'mm/dd/yyyy')
AND A.date < to_datetdt,'mm/dd/yyyy'));

spool off

This query takes about 2 minutes to run. With the query above, I run out of extents, even after I increase the extents as much as I can.

 

[wsam]

What I am trying to do is decrease the time that it takes for the query to run. I thought that using a PLSQL query, I could speed things up.

 

[LKBrwnDBA]

You need an index on HistNum and date.

Analyze tables and indexes.




----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[SantaMufasa]

Also, Theresa, could you please post the "extents error" message that you are receiving? We can help you resolve that issue.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]