How to use in Oracle – Output files -Comma, Pipe, or Tab Delimited Output

To generate comma or pipe delimited output for tools such as Excel, Access, and so on while spooling from SQL*Plus, use settings from SQL*Plus such as:

SET COLSEP ,

Or

SET COLSEP |

Example:

set pages 0 feed off
set colsep ,
select
object_name,
object_type,
status,
created
from all_objects
where owner='PUBLIC'
and rownum < 10 /* 9 rows for Sample Output */
;

Output:

ALL_ALL_TABLES ,SYNONYM ,VALID ,01-APR-2004
ALL_ARGUMENTS ,SYNONYM ,VALID ,01-APR-2004
ALL_ASSOCIATIONS ,SYNONYM ,VALID ,01-APR-2004
ALL_CATALOG ,SYNONYM ,VALID ,01-APR-2004
ALL_CLUSTERS ,SYNONYM ,VALID ,01-APR-2004
ALL_CLUSTER_HASH_EXPRESSIONS ,SYNONYM ,VALID ,01-APR-2004
ALL_COLL_TYPES ,SYNONYM ,VALID ,01-APR-2004
ALL_COL_COMMENTS ,SYNONYM ,VALID ,01-APR-2004
ALL_COL_PRIVS ,SYNONYM ,VALID ,01-APR-2004

For pipe delimited output:

set colsep |
select
object_name,
object_type,
status,
created
from all_objects
where owner='PUBLIC'
and rownum < 10 /* 9 rows for Sample Output */
;

Output:

ALL_ALL_TABLES |SYNONYM |VALID |01-APR-2004
ALL_ARGUMENTS |SYNONYM |VALID |01-APR-2004
ALL_ASSOCIATIONS |SYNONYM |VALID |01-APR-2004
ALL_CATALOG |SYNONYM |VALID |01-APR-2004
ALL_CLUSTERS |SYNONYM |VALID |01-APR-2004
ALL_CLUSTER_HASH_EXPRESSIONS |SYNONYM |VALID |01-APR-2004
ALL_COLL_TYPES |SYNONYM |VALID |01-APR-2004
ALL_COL_COMMENTS |SYNONYM |VALID |01-APR-2004
ALL_COL_PRIVS |SYNONYM |VALID |01-APR-2004

For TAB delimited output, you can use the following:

col TAB# new_value TAB NOPRINT
select chr(9) TAB# from dual;
set colsep "&TAB"

select
object_name,
status,
created
from all_objects
where owner='PUBLIC'
and rownum < 10 /* 9 rows for Sample Output */
;

Output:

ALL_ALL_TABLES VALID 01-APR-2004
ALL_ARGUMENTS VALID 01-APR-2004
ALL_ASSOCIATIONS VALID 01-APR-2004
ALL_CATALOG VALID 01-APR-2004
ALL_CLUSTERS VALID 01-APR-2004
ALL_CLUSTER_HASH_EXPRESSIONS VALID 01-APR-2004
ALL_COLL_TYPES VALID 01-APR-2004
ALL_COL_COMMENTS VALID 01-APR-2004
ALL_COL_PRIVS VALID 01-APR-2004