Spooling speed from SQLPlus

0
votes

I have a table with 70000 rows MYPROMPT varchar2(100), Mystring1 varchar2(100), Mystring2 varchar2(100)

So, data might look like this:

 'GREETING','Hello there','Joe' 
 'GREETING','Hello there','Mike'
 'GREETING','Hi','Steve' 
 'FIRING','You are fired','Dave'
 'FIRING','Outta here','Bob'

I need to spool this to a file like

GREETING
Hello there Joe
Hello there Mike
Hi Steve
FIRING
You are fired Dave
Outta here Bob

I have a script that handles this, doing a select for each MYPROMPT, and this works fine. It runs a series of queries based on the various values for MYPROMPT. The problem is that it takes a long time to spool to the output file I need (well over an hour). Is there something I can set, either in SQLPlus or at the database that will speed this up?

1
oraclesqlplus
When you're spooling the data to a file, you can SET TERMOUT OFF, this will be much faster as it doesn't have to spend the time to write it to the screen,or you can have a look to this stackoverflow.com/questions/2560398/oracle-sql-plus-spool - Gaurav Soni
THanks much! TERMOUT has helped markedly and I'll check out the other advice as well. - moleboy
Sadly, none of this helped. BUT, its possible there's more of a system issue at the moment. Working on that now. - moleboy
You mention multiple queries. What are they? If could easily be that they are slowing you down. - Ben
@Ben, actually, everything is running slow on that database (it takes me several seconds just to sort 70k records). So I don't think I can point fingers until I get that resolved. - moleboy

1 Answers

0
votes

My bad. It wasn't SQLPlus causing the problem, but rather a problem with my query.