output of Select statement to a file

[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. Even spool is also an Oracle command.

My backend database is Oracle, but as I am using only SQL, so i need a SQL command. Thus, "Spool table.rpt; select * from table" also fails.

any kind of help will be appreciated
Thanks in advance

 

[carp]

If you are using SQL, you could probably use PL/SQL just as easily.

If you use PL/SQL, you can use your select statement to populate a cursor, then go through the cursor and write out the results using the UTL_FILE package. You can also write out your HTML tags at the same time to generate web-ready pages.
[sig][/sig]

 

[Jeronimo]

Hi Nanda,

it's a good idea to use the UTL_File package (see

http://www.orafaq.com/faqplsql.htm).

Here are some lines of code, taken from that page and slightly modified:

DECLARE
fileHandler UTL_FILE.FILE_TYPE;
h_var VARCHAR2(20);
BEGIN
fileHandler := UTL_FILE.FOPEN('/tmp', 'myfile.txt', 'w');
-- retrieve data from a table...
select something into h_var from sometable;
-- now write the retrieved data to the file...
UTL_FILE.PUTF(fileHandler, h_var);
UTL_FILE.FCLOSE(fileHandler);
EXCEPTION
WHEN utl_file.invalid_path THEN
raise_application_error(-20000, 'ERROR: Invalid path for file or path not in INIT.ORA.');
END;
/

Hope this helps,
Jeron