When you run a query against an Oracle database using SQLcl or SQL*Plus, the results are displayed according to the SQLFORMAT option. But within that, there are other things you can do to customise the format of the results.

One of the things you can do is specify a column separator for the output grid.

This allows you to set your output grid to look more like a grid – or a table – with its columns more clearly defined with a kind of vertical border.

The default column separator is a single blank space. Below, I show you how to change the column separator to a more visible separator.

The Default Separator

First, let’s set the SQLFORMAT to DEFAULT:

SET SQLFORMAT DEFAULT;

That clears all previous SQLFORMAT settings and returns to the default output.

Now, let’s see what the default column separator looks like:

SELECT 
    employee_id,
    first_name,
    last_name,
    job_id
FROM EMPLOYEES
ORDER BY employee_id ASC
FETCH FIRST 5 ROWS ONLY;

Result:

EMPLOYEE_ID FIRST_NAME           LAST_NAME                 JOB_ID    
----------- -------------------- ------------------------- ----------
        100 Steven               King                      AD_PRES   
        101 Neena                Kochhar                   AD_VP     
        102 Lex                  De Haan                   AD_VP     
        103 Alexander            Hunold                    IT_PROG   
        104 Bruce                Ernst                     IT_PROG   

As mentioned, the default column separator is a single space.

Let’s change it.

Set the Column Separator

Let’s set the column separator to the pipe symbol (|):

SET COLSEP '|'

And now let’s run the query again:

SELECT 
    employee_id,
    first_name,
    last_name,
    job_id
FROM EMPLOYEES
ORDER BY employee_id ASC
FETCH FIRST 5 ROWS ONLY;

Result:

EMPLOYEE_ID|FIRST_NAME          |LAST_NAME                |JOB_ID    
-----------|--------------------|-------------------------|----------
        100|Steven              |King                     |AD_PRES   
        101|Neena               |Kochhar                  |AD_VP     
        102|Lex                 |De Haan                  |AD_VP     
        103|Alexander           |Hunold                   |IT_PROG   
        104|Bruce               |Ernst                    |IT_PROG   

Now the pipe symbol is displayed like vertical borders for the columns.

We can add extra space around these borders if so desired:

SET COLSEP ' | '

And now let’s run the query again:

SELECT 
    employee_id,
    first_name,
    last_name,
    job_id
FROM EMPLOYEES
ORDER BY employee_id ASC
FETCH FIRST 5 ROWS ONLY;

Result:

EMPLOYEE_ID | FIRST_NAME           | LAST_NAME                 | JOB_ID    
----------- | -------------------- | ------------------------- | ----------
        100 | Steven               | King                      | AD_PRES   
        101 | Neena                | Kochhar                   | AD_VP     
        102 | Lex                  | De Haan                   | AD_VP     
        103 | Alexander            | Hunold                    | IT_PROG   
        104 | Bruce                | Ernst                     | IT_PROG