output of Select statement to a flat file 1

[Nanda]

Is there anyway to send the SELECT statement ouput to a file, e.g. myresult.rpt?
like select * from table > 'C:\table.rpt' or
select * from table; output to 'C:\table.rpt'

I need to send the output to a file that I download from the web later or diaplay it on the web page.

The later command above (output to) fails when I execute it from inside my ASP page.

My backend database is Oracle, but as I am using only SQL, so i need a SQL command.

any kind of help will be appreciated
Thanks in advance

 

[karluk]

If your backend database is Oracle you will have to use the appropriate Oracle command. Probably you should repost this question in the Oracle forum.

One way of writing output to a flat file is with the UTL_FILE package. In rough outline you would open a cursor to select the data you wish to write, then call utl_file.fopen to open the flat file, loop through the cursor, fetching rows and using utl_file.put_line to write to the file, and finally using utl_file.fclose to close the file.

I hope this helps. The experts in the Oracle forum may have additional suggestions. [sig][/sig]

 

[Risk]

If you are using Oracle SQL then you can simply use the Spool command in your query

i.e

Spool 'c:\mysql\sql1.txt'

Select * from my_table

Spool off

 

[Andel]

If you're using MS SQL, you can do this:

exec master..xp_cmdshell 'isql /q "Select * from MyDatabase..MyTable" -n >> C:\MyTable.txt'

Andel
andel@barroga.net

http://andyb.8m.com