Shivmohan Purohit's Oracle ERP BLOG

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…

View original post 85 more words

Advertisements

Want to give some comment to author ( Shivmohan Purohit )

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s