Formatting spool files in oracle

So, I use the following syntax: set colsep ' ' set echo off set feedback off set linesize set pagesize 0 set sqlprompt '' set trimspool on set headsep off spool output. I don't use sed for format the output file.

Hallison Batista Hallison Batista 4 4 silver badges 4 4 bronze badges. I see a similar problem Karlos Karlos 2 2 silver badges 2 2 bronze badges. What is the extra outer select x for? This should work without it. SQL Developer Version 4. Doc Doc 4 4 silver badges 12 12 bronze badges.

Nice catch on the underline option, needed that one. It would help anyone who may want to view the csv file, and figure out what they are looking at, etc It's crude, but: set pagesize 0 linesize trimspool on feedback off echo off select '"' empno '","' ename '","' deptno '"' as text from emp spool emp.

Tony Andrews Tony Andrews k 20 20 gold badges silver badges bronze badges. You can explicitly format the query to produce a delimited string with something along the lines of: select '"' foo '","' bar '"' from tab And set up the output options as appropriate. FrustratedWithFormsDesigner Rob Heusdens Rob Heusdens 4 4 bronze badges. The trim is unnecessary. For those of us still stuck in the past with I do it this way because it handles commas in the data.

I turns them to semi-colons. Charles Stepp Charles Stepp 21 2 2 bronze badges. See bash script below:! Larry R. Irwin Larry R. Irwin 5 5 bronze badges. Jared Still Jared Still 7 7 bronze badges.

Rajeesh Madambat 1, 1 1 gold badge 9 9 silver badges 16 16 bronze badges. Satheeshkumar P Satheeshkumar P 9 2 2 bronze badges. You could use csv hint. Adilson Silva Adilson Silva 1. The Overflow Blog. Podcast Helping communities build their own LTE networks. Podcast Making Agile work for data science. Featured on Meta. To print query results, spool them to a file as described in the previous section. To generate a final report and spool and print the results, create a command file named EMPRPT containing the following commands.

First, use EDIT to create the command file with your host operating system text editor. Storing and Printing Query Results Send your query results to a file when you want to edit them with a word processor before printing or include them in a letter, memo, or other document. To display a column heading on more than one line, use a vertical bar where you want to begin a new line.

See SET for more information. A format model is a representation of the way you want the numbers in the column to appear, using 9's to represent digits. You can also round the values to a given number of decimal places, display minus signs to the right of negative values instead of to the left , and display values in exponential notation. Otherwise, the default format width is A9. Suppressing and Restoring Column Display Attributes You can suppress and restore the display attributes you have given a specific column.

You may wish to wrap whole words to additional lines when a column value wraps to additional lines. If you do not do this, the breaks may appear to occur randomly. Thus, if you want to use different spacing techniques in one report or insert space after the value changes in more than one ordered column, you must specify multiple columns and actions in a single BREAK command.

The following table lists compute functions and their effects: Function Effect SUM Computes the sum of the values in the column. AVG Computes the average of the values in the column.

STD Computes the standard deviation of the values in the column. Table 4 - 1. The computed values print on a separate line when the value of the ordered column changes. When you establish the format of a NUMBER column, you must allow for the size of sums you will include in your report. Defining Page and Report Titles and Dimensions The word page refers to a screenful of information on your display or a page of a spooled printed report.