Outputting a query to a text file rather than to the screen

[Navster]

Hi want to output a simple query to a text file.

Select * from bookings

Please note I do not want it to be displayed on the screen as the table is very large.

Any help would be appreciated

 

[SantaMufasa]

Navster,

Since your query produces tons of results, I recommend that you try this first on a tiny table, then do it for your large table once you have confirmed that everything works like you wanted for the tiny table:

set termout off
spool <text-file name>
Select * from bookings;
spool off
set termout on

Let us know how this works for you.


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]

 

[Navster]

By setting term off Sql still outputs to screen, but I would like it to Just output to the text file.

btw I am using oracle 9i sql*plus

 

[SantaMufasa]

Navster,

Aren't you glad you ran it first on a tiny table? Now try the code again, but put it into a script (e.g. Bookings.sql), then at your SQL*Plus prompt say:

@bookings

...and let us know how that works for you.

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]

 

[Navster]

You my friend are a freaking genius. Thankyou

 

[Navster]

Hi I have one more question about this method. I would like to see how many rows the query returns. When I do this method it puts all the results into a file but it does not state how many rows retrieved.

Any thoughts?

 

[SantaMufasa]

Just replace the "*" with "count(*)", which returns one row (with the count) instead of all the rows that you are currently seeing.

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]

 

[Navster]

I think the problem is that since termout is off it does display the number of records.

This is my query

select b.office_id from bookings b, customers c, flights f
where b.cust_id = c.cust_id and b.flight_no = f.flight_no
and b.office_id < 101
order by c.cust_ID;

It returns all the office ids but in the output file it doesnt not include the number of rows returned. Is there a way to fix this?

 

[Navster]

Never mind obout the previous post I have fixed the problem

Thanks for the help

 

[SantaMufasa]

Even though you have fixed the problem, the correct method for a query (in SQL*Plus) to display the number of rows affected by any SQL is:

set feedback on

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]