Re: How to export data from query to flat ascii file

Tomislav Kokoska wrote:
> I have oracle 9.2.0.1
>
> In T.O.A.D i wrote complex query and with option export to flat file (no
> delimeter) i generate spec file and i exported data from query into
> ascii.txt file.
>
> How can i export data without T.O.A.D using SQL Plus or Import/Export
> utililty or other way?

Two ways come to mind - both uses PL/SQL a) write it to file
b) stream it to a web browser

For option A, you use the UTL_FILE PL/SQL package (provided by Oracle) to write the contents of a cursor to a flat file. The resulting CSV file resides on the Oracle server - which still leaves the issue of how to get that CSV file to the client.

Option B uses MOD_PLSQL. This is an Apache DSO (Dynamic Shared Object) module from Oracle. It basically allows you to call and execute a PL/SQL procedure in an Oracle database, from a web browser via the URL. The PL/SQL procedure can return any Mime type - e.g. HTML, plain text, images and of course csv files.

For both options the core code will look the same. The only differences being that option A will open a UTL_FILE, whereas option B will set the Mime type header of the HTTP response to the web browser. One will use UTL_FILE.PRN and the other HTP.PRN to create the CSV contents.

UTL_FILE is covered in the Supplied PL/SQL Packages and Types Reference guide
(http://otn.oracle.com/pls/db92/db92.to_toc?pathname=appdev.920%2Fa96609%2Ftoc.htm&remark=docindex). MOD_PLSQL in the PL/SQL Web Toolkit Reference guide (http://download-west.oracle.com/docs/cd/A97329_01/web.902/a90101/toc.htm).

--
Billy