Export a Fixed Length AScii file from Oracle

Apart from using an export utility, is there a way to export a file to ascii format, fixed length using sql. I would like to wrap this in a stored procedure.


Thanks

Lans

Let's assume that you are talking about exporting the data from the execution of a SQL script, using Oracle's SQL*Plus utility.

Whenever you execute a SQL query in Oracle SQL*Plus, the output is already formatted in fixed length ASCII. If you save your results to a file, you will see that there are fixed-length column boundaries in the output.

The Oracle SQL*Plus Reference has an entire section on custom-formatting the output of a query, using COLUMN commands. However, these are commands to the SQL session only. The width, precision, and/or format of a data field reverts back to the default (defined in the database's data dictionary) in each session.

Because they are session commands, not query commands, they would not be recognized in a stored procedure. You could use a stored procedure to generate the output columns of the export, and create a script file with COLUMN commands for those output columns, and call the stored procedure in the script. The same thing could be achieved with selections against a view in a script.

There are some good examples of formatted query output scripts in the Oracle SQL*Plus Reference, and even more in the book "Oracle: A Complete Reference".


------------------
Scott Wehrly
Software Engineer
swehrly@schoolhousesoftware.com