To produce a comma-delimited text file, you need to do two things. First, you need to add commas between each field. Second, you need to enclose text fields within quotes. The final query looks like this:

SELECT TO_CHAR(employee_id) || ','
       || '"' || employee_name || '",' 
       || TO_CHAR(employee_hire_date,'MM/DD/YYYY') || ','
       || TO_CHAR(employee_termination_date,'MM/DD/YYYY') || ',' 
       || TO_CHAR(employee_billing_rate)
  FROM employee;

Oracle’s TO_CHAR function has been used to explicitly convert numeric fields to text strings. TO_CHAR is also used to convert date fields to text, and a date format string is included to get the dates into MM/DD/YYYY format. SQL’s concatenation operator, ||, is used to concatenate all the fields together into one long string, and you can see that commas are included between fields. The output from this query will look like this:

...
107,"Bohdan Khmelnytsky",01/02/1998,,45
108,"Pavlo Chubynsky",03/01/1994,11/15/1998,220
110,"Ivan Mazepa",04/04/1998,09/30/1998,84
111,"Taras Shevchenko",08/23/1976,,100
...

Notice that in addition to the commas, the employee_name field has been enclosed in quotes. This is done to accommodate the possibility that someone’s name will contain a comma. Most commercial programs that load comma-delimited data will allow text strings to be optionally enclosed in quotes.

You can use the same technique to generate tab-delimited data. Instead of a comma, use CHR(9) to put a tab character between each field. CHR( ) is an Oracle SQL function that converts an ASCII code into a character. The ASCII code uses the value 9 to represent a tab character.

The easiest way to produce an output file with fixed-width columns is to use the SQL*Plus COLUMN command to format the output from a standard SQL query. The following example shows one way to dump the employee data in a fixed-width column format:

COLUMN employee_id FORMAT 09999 HEADING ''
COLUMN employee_name FORMAT A20 HEADING '' TRUNCATED
COLUMN employee_hire_date FORMAT A10 HEADING ''
COLUMN employee_termination_date FORMAT A10 HEADING ''
COLUMN employee_billing_rate FORMAT S099.99 HEADING ''
SELECT employee_id,
       employee_name,
       TO_CHAR(employee_hire_date,'MM/DD/YYYY') employee_hire_date,
       TO_CHAR(employee_termination_date,'MM/DD/YYYY')
            employee_termination_date,
       employee_billing_rate
  FROM employee;

Notice some things about the example:

Here’s how the output from the example will look:

...
 00107 Bohdan Khmelnytsky   01/02/1998            +045.00
 00108 Pavlo Chubynsky      03/01/1994 11/15/1998 +220.00
 00110 Ivan Mazepa          04/04/1998 09/30/1998 +084.00
 00111 Taras Shevchenko     08/23/1976            +100.00
...

Each column in the output is separated by one space, because that’s the SQL*Plus default. If you like, you can use the SET COLSEP command to change the number of spaces or eliminate them entirely. To run the columns together, you could eliminate the space between columns by setting the column separator to a null string. For example:

SET COLSEP ""

Now the output will look like this:

...
 00107Bohdan Khmelnytsky  01/02/1998          +045.00
 00108Pavlo Chubynsky     03/01/199411/15/1998+220.00
 00110Ivan Mazepa         04/04/199809/30/1998+084.00
 00111Taras Shevchenko    08/23/1976          +100.00
...

You can use any column separation string that you like, and you aren’t limited to just one character.

Using SQL*Plus to format fixed-width output works best when you have some control over the format expected by the destination of that data. If you are also writing the program to load the data somewhere else, of course you can code it to match what you can easily produce with SQL*Plus. Sometimes though, you need to match an existing format required by the destination; one you cannot change. Depending on your exact requirements, it may be easier to code one large expression in your SQL statement, and use Oracle’s built-in functions to gain more control over the output. The following example produces the same output as before, but without a leading sign character in the numeric fields:

SELECT LTRIM(TO_CHAR(employee_id,'09999'))
       || SUBSTR(RPAD(employee_name,20,' '),1,20)
       || TO_CHAR(employee_hire_date,'MM/DD/YYYY')
       || NVL(TO_CHAR(employee_termination_date,'MM/DD/YYYY'),'          ')
       || LTRIM(TO_CHAR(employee_billing_rate,'099.99'))
  FROM employee;

The output from this example looks like this:

00107Bohdan Khmelnytsky  01/02/1998          045.00
00108Pavlo Chubynsky     03/01/199411/15/1998220.00
00110Ivan Mazepa         04/04/199809/30/1998084.00
00111Taras Shevchenko    08/23/1976          100.00

Look carefully, and you will see that neither the employee_id field nor the employee_billing_rate field has a leading sign. Not only do they not have a leading sign, there isn’t even space left for one. The LTRIM function was used to remove it.

There is a wide variety of built-in Oracle functions available. Add to that the ability to write your own, and you should be able to generate output in any conceivable format.

If you are extracting data from Oracle in order to move it to another database, and if the volume of data isn’t too high, you can use SQL*Plus to generate a file of INSERT statements. Here is a query to generate INSERT statements for each employee record:

SELECT 'INSERT INTO employee' || chr(10)
    || '  (employee_id, employee_name, employee_hire_date' || chr(10)
    || '  ,employee_termination_date, employee_billing_rate)' || chr(10)
    || '  VALUES (' || TO_CHAR(employee_id) || ',' || chr(10)
    || '          ''' || employee_name || ''',' || chr(10)
    || '          TO_DATE('''
                    || TO_CHAR(employee_hire_date,'MM/DD/YYYY')
                    || ''',''MM/DD/YYYY''),' || chr(10)
    || '          TO_DATE('''
                    || TO_CHAR(employee_termination_date,'MM/DD/YYYY')
                    || ''',''MM/DD/YYYY''),' || chr(10)
    || '          ' || TO_CHAR(employee_billing_rate) || '),'
  FROM employee;

As you can see, this type of query can get a bit hairy. You have to deal with nested, quoted strings; you have to concatenate everything together; and you have to place line breaks so the output at least looks decent. The doubled-up quotes you see in the above statement are there because single quotes are required in the final output. So, for example, the string "`'MM/DD/YYYY'')" will resolve to "`MM/DD/YYYY')" when the SELECT statement is executed. The SELECT statement just shown will produce the following INSERT statement for each employee record:

INSERT INTO employee
  (employee_id, employee_name, employee_hire_date
  ,employee_termination_date, employee_billing_rate)
  VALUES (111,
          'Taras Shevchenko',
          TO_DATE('08/23/1976','MM/DD/YYYY'),
          TO_DATE('','MM/DD/YYYY'),
          100);

This is not a technique I use very often, because it can be frustrating to get the SQL just right. I use it most often on code tables and other small tables with only two or three columns. I also use it sometimes when I’m sending data to a client. That way I just send one file, and my client doesn’t have to mess with SQL*Loader or Oracle’s Import utility.

Another twist on using SQL to write SQL is to generate commands that help you maintain your database. Such commands are referred to as Data Definition Language, or DDL, commands. Using SQL*Plus to generate DDL scripts can help in automating many database administration tasks, and is often well worth the effort. The following command, for example, generates CREATE PUBLIC SYNONYM commands for each table you own:

SELECT 'CREATE PUBLIC SYNONYM ' || table_name
       || ' for ' || user || '.' || table_name || ';'
  FROM USER_TABLES;

It’s not unusual to need public synonyms, and if you have a large number of tables, you can save yourself a lot of typing by letting SQL*Plus do the work for you. The output from the above command looks like this:

CREATE PUBLIC SYNONYM EMPLOYEE for JONATHAN.EMPLOYEE;
CREATE PUBLIC SYNONYM PROJECT for JONATHAN.PROJECT;
CREATE PUBLIC SYNONYM PROJECT_HOURS for JONATHAN.PROJECT_HOURS;

Once you have spooled the above commands to a file, you can then execute that file to create the synonyms. In addition to one-off tasks like creating synonyms, you can use SQL*Plus to generate DDL commands for use by ongoing maintenance tasks. Going beyond that, you can even use SQL*Plus to generate operating-system script files.