How to display select query in nice way using sqlplus in unix

Question

I need to select a lot of data (over 4,5 mln records) and when I am doing that with sqlplus from unix, data is presented in hard to read way:

Columns1
--------------------------------------------------------------------------------
Columns2
--------------------
110345947
107110345947

110345948
107110345948

110345949
107110345949


Columns1
--------------------------------------------------------------------------------
Columns2
--------------------
110345950
107110345950

110345951
107110345951

What I want to get is this:

Columns1,Columns2
110345950,107110345950
110345951,107110345951

My sql script looks like this:

set long 30000;
set colsep ",";
SELECT Columns1, Columns2 from table;

Any ideas how to do that?

Why Unix specifically? Is the behaviour different on Windows? — William Robertson
– William Robertson, Commented Aug 17, 2018 at 12:45

Alex Poole · Accepted Answer · 2018-08-17 11:52:29Z

1

If you're stuck using SQL*Plus the simplest thing to do is concatenate the values yourself:

SELECT Columns1 ||','|| Columns2 from table;

If you can use SQLcl or SQL Developer instead then they can output CSV natievly:

set sqlformat csv
SELECT Columns1, Columns2 from table;

and that will include wrapping string in double-quotes etc. to avoid issues with embedded commas.

answered Aug 17, 2018 at 11:52

Alex Poole

192k11 gold badges198 silver badges349 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

freza19 Over a year ago

thanks, also 'SET NEWPAGE NONE;' is useful - it will remove empty lines

Littlefoot · Accepted Answer · 2018-08-17 12:12:11Z

A few more options:

Use SET LINESIZE SQL*Plus command and enlarge it so that all columns fit into a single line:

SQL> set linesize 30
SQL> select * from dept;

    DEPTNO DNAME
---------- --------------
LOC
-------------
        10 ACCOUNTING
NEW YORK

        20 RESEARCH
DALLAS

        30 SALES
CHICAGO

        40 OPERATIONS
BOSTON


SQL> set linesize 100
SQL> select * from dept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

SQL>

Or, use SQL*Plus COLUMN command and "shorten" every column that is too wide (that is somewhat boring, though):

SQL> create table test (ename varchar2(50), job varchar2(50));

Table created.

SQL> insert into test select ename, job from emp where rownum < 5;

4 rows created.

SQL> set linesize 50
SQL> select * from test;

ENAME
--------------------------------------------------
JOB
--------------------------------------------------
SMITH
CLERK

ALLEN
SALESMAN

WARD
SALESMAN

JONES
MANAGER


SQL> col ename format a12
SQL> col job   format a20
SQL> select * from test;

ENAME        JOB
------------ --------------------
SMITH        CLERK
ALLEN        SALESMAN
WARD         SALESMAN
JONES        MANAGER

SQL>

William Robertson · Accepted Answer · 2018-08-18 08:21:21Z

From SQL*Plus 12.2, you can use

SET MARKUP CSV {ON|OFF} [DELIMI[TER] character] [QUOTE {ON|OFF}]

e.g. (using all defaults)

set markup csv on

Or, if your command line is able to launch a web browser, you can set markup html, save to a file and open that in a browser. (I realise you asked for CSV format so this may not be any use to you, but it's a neat trick so I'm sharing it anyway.)

https://uhesse.com/2011/06/30/sqlplus-output-in-nice-html-format/

For example, save the following as html.sql (this example is for Windows, so you'll need to replace the start command with your Linux equivalent):

set termout off feedback off

set markup html on head "<title>SQL*Plus Output &_user@&_connect_identifier &_date</title> -
<style> -
html { -
   font-family: consolas, monospace; -
   font-size: 9pt; -
   background-color: #dce1e9; -
} -
table, td, th { -
   text-align: left; -
   vertical-align: top; -
   border: 1px solid #808090; -
   background: white; -
   padding: .5em .6em; -
} -
table { -
   border-collapse: collapse; -
   margin-top: 1.2em;  /* space above table itself */ -
   margin-bottom: 1.2em; -
   border-width: 3px; -
   margin-bottom: 1em; -
} -
td { -
   margin: .2em; -
   font-size: 80%; -
} -
th { -
   background: #f0f4fd; -
   font-weight: bold; -
   font-size: 95%; -
   margin: .2em; -
   padding-bottom: .4em; -
} -
</style>" -
body "" -
table "align='left' summary='Script output'" -
spool on entmap on preformat off

spool sqlplus_output.html

run

spool off
set markup html off spool off
host start sqlplus_output.html
set termout on feedback on

Then you can enter a query at the SQL prompt (without running it), then

@html

and the results will appear in your default browser.

Collectives™ on Stack Overflow

How to display select query in nice way using sqlplus in unix

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related