How Do I Create a Tab Delimiter to an Oracle SQL Statement?

By Sara Wayne

Oracle database tables are accessed using structured query language (SQL). The user views the SQL results on a screen or spools it to a file for later use. Several SQL commands are used in sequential order to add a delimiter to separate column values in the output file. Using a delimiter makes importing the file into Excel, Access or other applications an easier task because it eliminates the need to define the specific length of each field.

Step 1

Open an SQL*Plus session. Use the "set" command to define "colsep" (column separator) to be the tab special character. Type the following command and press "Enter":

set colsep "&TAB"

Step 2

Execute an SQL select statement:

select * from test_table

Step 3

View the results. Each column's value should appear separated by a space (a tab special character is not displayable) instead of the pipe (|) that is usually used.

Results without changing the delimiter are displayed in the following manner:

VALUE 1 | VALUE 2 | VALUE 3

Results after changing the column separator to a tab delimiter are displayed in the following manner:

VALUE 1 VALUE 2 VALUE 3

Use the SQL*Plus spool function to write the output to a file. Import the file into any application that requires a tab delimited input file.

×