SQLPLUS’ Forgotten Option: COLSEP

Today’s blog post came to my mind, as I am re-writing some scripts, written by others, with tones of concatenations of commas, to obtain a csv file. I am improving the scripts, by replacing tens of lines of code with just one line.

How many times have you started writing SQL statements, and concatenated columns with the comma character, to obtain a comma delimited file? I used to do that too! Until I knew better, and remembered that SQLPLUS has an option, to set a separator for the columns, a text to print between columns.

By default the text that is printed between columns is a single space. You can change this to anything you want. One of the most used separators would be the comma, but really this could be anything, a semicolon, a colon, two spaces, the letter A, anything you want.

Instead of this:

select USER_ID  || ',' ||  
       USERNAME || ',' || 
       ACCOUNT_STATUS || ',' ||  
       PROFILE 
from dba_users 
order by username;

USER_ID||','||USERNAME||','||ACCOUNT_STATUS||','||PROFILE
----------------------------------------------------------
46,ANONYMOUS,EXPIRED & LOCKED,DEFAULT
31,APPQOSSYS,EXPIRED & LOCKED,DEFAULT
43,CTXSYS,EXPIRED & LOCKED,DEFAULT
30,DBSNMP,OPEN,DEFAULT
...

Use this:

set colsep ","

select USER_ID, 
       USERNAME, 
       ACCOUNT_STATUS, 
       PROFILE 
from dba_users 
order by username;

   USER_ID,USERNAME  ,ACCOUNT_STATUS                  ,PROFILE
----------,----------,--------------------------------,--------------------------
        46,ANONYMOUS ,EXPIRED & LOCKED                ,DEFAULT
        31,APPQOSSYS ,EXPIRED & LOCKED                ,DEFAULT
        43,CTXSYS    ,EXPIRED & LOCKED                ,DEFAULT
        30,DBSNMP    ,OPEN                            ,DEFAULT

You can also use other separators:

set colsep ";"

select USER_ID, 
       USERNAME, 
       ACCOUNT_STATUS, 
       PROFILE 
from dba_users 
order by username;

   USER_ID;USERNAME  ;ACCOUNT_STATUS                  ;PROFILE
----------;----------;--------------------------------;-------------------------
        46;ANONYMOUS ;EXPIRED & LOCKED                ;DEFAULT
        31;APPQOSSYS ;EXPIRED & LOCKED                ;DEFAULT
        43;CTXSYS    ;EXPIRED & LOCKED                ;DEFAULT
        30;DBSNMP    ;OPEN                            ;DEFAULT

The benefit of using colsep is that your code is much easier to read, the columns are better aligned and even the heading of the columns will be comma (or other character) separated! In one quick sentence REPLACE || ‘,’ || WITH set colsep ‘,’

If you enjoyed this article, and would like to learn more about databases, please sign up to my weekly email, and you will receive my FREE guide: 7 Questions to Ask When Troubleshooting Database Performance Problems!

If you are interested in improving your Oracle Tuning skills, check out my course theultimatesqltuningformula.com. Follow the link to get Today‘s Special, only $13.99 CAD !