CODEWITHSUNDEEP
oracle-databaseoracle-sqldevelopersqlplusspool
Arko Sen
Arko Sen

Reputation: 1

script to fetch oracle database table in csv format

I am using this script as below

set colsep ','
set heading on
set headsep on
set pagesize 0
set trimspool off
spool C:\DXC\books11.csv
Select * from test_extract;
spool off
exit

but the problem with this is 

ARKO                ,1A                  ,        20
ARKO1               ,1B                  ,        20

space is comming after the values of each attribute as per the lenght of the attribute.

required output :

ARKO,1A,20
ARKO1,1B,20

Upvotes: 0

Views: 273

Answers (2)

thatjeffsmith
thatjeffsmith

Reputation: 22467

Use SQLcl, it's in your SQL Developer bin directory, works like SQLPlus, only better.

It's SQL.exe on Windows for example, but also available as it's own 25mb download on Oracle.com.

set sqlformat csv
spool file.csv
select * from table;

It'll give you exactly what you're asking for.

Upvotes: 0

Littlefoot
Littlefoot

Reputation: 143103

As far as I can tell, no SET command will help.

One option - that helps - is to name all columns you're spooling and concatenate them using desired column separator.

For example, this is what you currently have:

SQL> set colsep ','
SQL> set heading on
SQL> set headsep on
SQL> set pagesize 0
SQL> set trimspool off
SQL> select * From dept;
        10,ACCOUNTING    ,Zagreb
        20,RESEARCH      ,DALLAS
        30,SALES         ,CHICAGO
        40,OPERATIONS    ,BOSTON

But, if you do it as follows:

SQL> select deptno ||','|| dname ||','|| loc from dept;
10,ACCOUNTING,Zagreb
20,RESEARCH,DALLAS
30,SALES,CHICAGO
40,OPERATIONS,BOSTON

it looks as you wanted. Drawback? You'll have to type all those columns.

Upvotes: 1

Related Questions