Automatically export result sets into csv

Question

I have to run several queries on an oracle 11g database, within SQLDeveloper 3.1.

For example:

select * from product;
select * from customer;
select * from prices;

At the moment I am exporting the resultsets "per hand", I simply right-clickonto the result and thenexport` it.

I would like to automatically save the resultset of each query in a specific folder.

Any recommendation how I could do that?

UPDATE

I tried using the csv and als the txt extesion of testFile:

spool C:\Users\User\Desktop\testFile.csv --I tried also .txt extension here!!!

set colsep ';'

select * from product;

spool off;

However, when I open the file I get for csv and txt the following result:

> set colsep '
> select * from product

I appreciate your replies!

possible duplicate of [Directly export a query to CSV using SQL Developer](http://stackoverflow.com/questions/19448322/directly-export-a-query-to-csv-using-sql-developer) — Frank Schmitt, Mar 06 '15 at 10:11

score 1 · Accepted Answer · edited Jan 17 '18 at 18:59

1

set echo off

set feedback off
set linesize 1000
set pagesize 0
set sqlprompt ''
set trimspool on

spool output.csv

select  columnA || ',' || columnB || ',' || ...... 
from table 
where ...

spool off;
exit 0;

Then create a shell script that calls the sql file

sqlplus >/dev/null 2>&1 "user/pass@DATABASE" << EOF

whenever sqlerror exit 1

@file.sql
EOF

UPDATE just saw you are on windows, same principle still applies, you probably will need to use PowerShell

edited Jan 17 '18 at 18:59

marc_s

732,580
175
1,330
1,459

answered Mar 06 '15 at 10:03

Sibster

3,081
21
18

Thx for your answer! The problem is that I do not have sqlplus installed on the computer I am using and I do not have sufficent access rights. I ony have SQLDeveloper available. – Carol.Kar Mar 06 '15 at 10:06
the SQL script should still work in SQLDeveloper you just cant make it run automaticaly. – Sibster Mar 06 '15 at 10:09
A second option is to create a PLSQL procedure that exports to directory, but that is abit more complicated. can you create procedures and oracle directories ? – Sibster Mar 06 '15 at 10:10

score 0 · Answer 2 · answered Mar 06 '15 at 09:57

0

You can use Spool, http://docs.oracle.com/cd/B19306_01/server.102/b14357/ch12043.htm

spool OutFile.txt
select row1||','||row2... from product; --format you prefer
spool off;

answered Mar 06 '15 at 09:57

Fran_R

1
2

Thx foryour answer! However, this does not work for me. Please have a look at my update! – Carol.Kar Mar 06 '15 at 10:02

Automatically export result sets into csv

2 Answers2