CODEWITHSUNDEEP
sqloracle-databaseoracle11g
Hue
Hue

Reputation: 413

How to properly Export results of select statement into Csv file using spool in oracle sql developer

Code:

set heading off
  set arraysize 1
  set newpage 0
  set pages 0
  set feedback off
  set echo off
  set verify off

spool 'c:\farmerList.csv'
/

select FIRSTNAME','LASTNAME','TRN','CELL','PARISH

spool off

The file is being saved to the directory, however it is saving the "select FIRSTNAME','LASTNAME','TRN','CELL','PARISH" and not the results of the query in csv format. What am i doing wrong?

Upvotes: 2

Views: 28076

Answers (2)

peter dsouza
peter dsouza

Reputation: 1

this is the correct solution please go through this 

import java.sql.*;
import java.io.*;
import au.com.bytecode.opencsv.CSVWriter;

public class TableExport {
public static void main(String[] args) {

try{
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE","name","password");
conn.setAutoCommit(false);
Statement statement = conn.createStatement();
ResultSet resultData = statement.executeQuery("select * from your_table");

            CSVWriter writer = new CSVWriter(new FileWriter(new File("D:/Uploads/Output5.csv")), '|');
            writer.writeAll(resultData, true);
            writer.close();

}catch (Exception e){
    System.out.println("Error" +e);
    }
  }
}

if anyone likes this please note you would need oracle-jdbc.jar and opencsv1.7.jar in library folder to properly execute this code.

Upvotes: -1

Alex Poole
Alex Poole

Reputation: 191235

Your select is incomplete as you don't have a from clause, but not sure if you've lost that in the copy-and-paste. As it is there is nothing to run, since the partial statement is never executed (no terminating ; or / on the next line). If you did have a from farmers; clause then it would show the command plus an ORA-00923 error, probably.

You can't just put a quoted comma between the fields, you need to concatenate the fields with that character using the || concatenation symbol:

spool 'c:\farmerList.csv'

select FIRSTNAME
    ||','|| LASTNAME
    ||','|| TRN
    ||','|| CELL
    ||','|| PARISH
from farmers;

gives a file containing

Joe,Grundy,X,Y,Ambridge

The fields don't have to be on separate lines, I jut find that easier to read and keep track of the commas.

You don't need the / after the spool command - that will re-excute the last statement before the spool, if there is one - and you don't need the quotes around the spool file name unless it contains spaces, but they don't hurt.

There's also a set colsep command which you can use to make the column separator into a comma, but you have to worry about padding, so I find it easier to concatenate the columns together as you're (almost) doing.

Except that's for SQL*Plus, as I didn't notice the SQL Developer reference in the title. Spool is a bit odd in Developer as it seems to trap and echo things you probably don't want, and not all of the set commands work (which ones depends on the version).

The safer and preferred way, I think, is to run a normal query without concatenated commas:

select FIRSTNAME, LASTNAME, TRN, CELL, PARISH
from farmers;

and with 'run' rather than 'run script', so that the results appear in the grid view in the query result window. Right-click on the grid and choose 'export'. You can then save as a CSV, or even as an XLS, and can choose to not have a header row if you prefer.

Upvotes: 3

Related Questions