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.

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;"

Execute an SQL select statement: select * from test_table

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.

Tip

  • check Depending on the SQL*Plus installation, spooling may need to be started prior to running the SQL and stopped after the SQL to create an output file.

References

About the Author

Based in Texas, Sara Wayne has been a freelance writer since 2008, as well as a veteran IT professional. She graduated from Virginia Commonwealth University in 1989 with a Bachelor of Science in management information systems and an English minor. Wayne's work has been published on eHow.com and Answerbag.com.

Photo Credits

  • photo_camera Keyboard image by S.Q T from Fotolia.com