Export data from Oracle to CSV

Question

I am using Oracle 12c on an instance of Amazon Web Services EC2.

I want to export data from one Oracle table with 5M records to a local folder in CSV format.

Is there a script or program to do that quickly in Redhat/bash environment?

I am looking for minimal installation and setup.

use the search - there must be a dozen variations of this question already covered, here's just one example https://stackoverflow.com/questions/19448322/directly-export-a-query-to-csv-using-sql-developer/19448806 — thatjeffsmith, Dec 18 '17 at 22:28

Littlefoot · Answer 1 · 2017-12-20T19:53:07.063

You want it quickly? How about a simple SPOOL SQLPlus command? You can make it prettier using different SET commands (type HELP SET on SQLPlus command prompt), but the general idea is as follows:

SQL> set colsep ','
SQL> spool emp.csv
SQL> select employee_id, first_name, last_name
  2  from employees
  3  where rownum < 5;

EMPLOYEE_ID,FIRST_NAME          ,LAST_NAME
-----------,--------------------,-------------------------
        100,Steven              ,King
        101,Neena               ,Kochhar
        102,Lex                 ,De Haan
        103,Alexander           ,Hunold

SQL> spool off;
SQL> $type emp.csv
SQL> select employee_id, first_name, last_name
  2  from employees
  3  where rownum < 5;

EMPLOYEE_ID,FIRST_NAME          ,LAST_NAME
-----------,--------------------,-------------------------
        100,Steven              ,King
        101,Neena               ,Kochhar
        102,Lex                 ,De Haan
        103,Alexander           ,Hunold

SQL> spool off;

SQL>

[EDITED by LF, after seeing OP's comment]

OK then, as you didn't take that effort and examine what SET offers, here you go: if you want to get a clean output (no headings, underlines, SELECT command, etc.), create a SQL file (let's name it SP.SQL):

SET ECHO OFF
SET VERIFY OFF
SET TRIMSPOOL ON
SET TRIMOUT ON
SET LINESIZE 9999
SET PAGESIZE 0
SET FEEDBACK OFF
SET TIMING OFF
SET TIME OFF
SET COLSEP ','
SPOOL emp.csv

SELECT employee_id, first_name, last_name
FROM employees
WHERE rownum < 5;

SPOOL OFF

Now connect to SQLPlus and run that script:

SQL> @sp
        100,Steven              ,King
        101,Neena               ,Kochhar
        102,Lex                 ,De Haan
        103,Alexander           ,Hunold
SQL>

Finally, if you take a look at EMP.CSV, you'll see that it is nice and clean.

Satisfied?

This was not giving exact CSV format as output.. There was a lot of headers, dashes, column dividers etc.. Is there a way to get a clean CSV format? Thanks.. — Joe, Dec 20 '17 at 17:14

Export data from Oracle to CSV

1 Answers1