CODEWITHSUNDEEP
oracle-databasesqlplus
Frederik Hoeft
Frederik Hoeft

Reputation: 1440

SQL*Plus ignores colsep option

So we are trying to dump the content of our oracle database to a .CSV file but when executing the following command

set colsep ',' echo off newpage 0 space 0 pagesize 0 linesize 3000 feed off head off trimspool on 
spool C:\Users\whocares\Desktop\test.CSV
select ID, LOCATION_ID from club; 
spool off

the output looks as follows:

...


     17499        902
     17500        902
     17501        902
     17502        902
     17503        902
     17504        902
     17505        902
     17506        902
     17507        902
     17508        902
     17509        902

...

and sqlplus seems to completely ignore the set colsep ',' option. To get a valid csv output we need to have the output look like this:

...

17499,902
17500,902
17501,902
17502,902
17503,902
17504,902
17505,902
17506,902
17507,902
17508,902
17509,902

...

So how do you properly use the set colsep option? We don't have much experience with sqlplus and for some reason other stackoverflow solutions didn't seem to work for us.

Upvotes: 1

Views: 2402

Answers (1)

Littlefoot
Littlefoot

Reputation: 142720

SET COLSEP replaces SET SPACE, and that's so since Oracle 9.2 (see Obsolete SQL*Plus Commands).

If you use them both, there's no separator:

SQL> set colsep ','
SQL> select * from dept where rownum = 1;

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

SQL> set space 0
SQL> select * from dept where rownum = 1;

    DEPTNODNAME               LOC
--------------------------------------------------
        10ACCOUNTING          NEW YORK
    
SQL> set space 1
SQL> select * from dept where rownum = 1;

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

SQL>

So - remove SET SPACE.

Another option is to concatenate columns. Yes, that's a tedious job but it works, e.g.

SQL> select deptno ||','|| dname ||','|| loc from dept;

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

SQL>

If there are many tables involved, you could write a query which will write a query for you:

SQL> select 'select ' || listagg(column_name, '||'',''||') within group (order by column_id) ||
  2         ' from '  || table_name result
  3  from user_tab_columns
  4  where table_name = 'DEPT'
  5  group by table_name;

RESULT
--------------------------------------------------------------------------------
select DEPTNO||','||DNAME||','||LOC from DEPT

SQL>

Now just copy/paste the result and run it.

Upvotes: 1

Related Questions