Saturday, November 3, 2012

Exporting from OracleXE

Here are 3 things I did to export from OracleXE with various levels of success:

  • Use SQLDeveloper to run a query, then save the results
    • pretty fast but failed for results above a certain size
  • Use SQL*Plus to run the query, spool the results to a file
    • works pretty well but has to display all of the results on the screen so that is slowdown
    • ~5% CPU usage
    • 58 minutes for 15.7 million rows
  • Use a custom written quick Java application to run the query and print the results - redirect the results to a file]
    • fast and efficient
    • ~10% CPU usage
    • 15 minutes


From this Oracle FAQ website, I used this SQL*Plus code to export from oracle:

set colsep ',' echo off newpage 0 space 0 pagesize 0 feed off head off trimspool on

spool myData.csv

select col1, col2, col3    from tab1   where col2 = 'XYZ';

spool off

Java application:

Below is the code for a simple java (1.7) application to export from an Oracle database.  Very crude - database connection information is hard-coded, but I wanted to just see how efficient the process was.  Answer:  efficient!

There are two classes, Main and DbConn.

Replace username, password, connectionUrl to point to your database and then recompile.  

package main;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;

public class Main {
private static final String username = "change-me";
private static final String password = "change-me";
private static final String connectionUrl = "jdbc:oracle:thin:@localhost:1521:XE";

private static final String delimeter = ",";
private static final long lineBatch = 10000;
* @param args
* @throws SQLException 
public static void main(String[] args) throws SQLException {
final int numCols = Integer.valueOf(args[0]);
final String queryStr = args[1];
System.err.println("numCols: " + numCols);
System.err.println("query: " + queryStr);
Date startDate = new Date();
System.err.println("started at: " + startDate);
DbConn dbConn= new DbConn(username, password, connectionUrl);
ResultSet rs = dbConn.getConnection().createStatement().executeQuery(queryStr);
long row = 0;
while ( {
StringBuilder sb = new StringBuilder();
for (int i = 1; i < numCols; i++) {

if ((row%lineBatch) == 0) {
Date endDate = new Date();
double durationMinutes = ((double)(endDate.getTime() - startDate.getTime())) / 60000.0;
System.err.println("finished at: " + endDate);
System.err.println("duraction [min]: " + durationMinutes);


package main;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;

import oracle.jdbc.OracleDriver;

public class DbConn {
private Connection cbipConnection = null;

public DbConn(String username, String password,
String connectionUrl) throws SQLException {
Properties connectionProperties = new Properties();
connectionProperties.put("user", username);
connectionProperties.put("password", password);
DriverManager.registerDriver(new OracleDriver());
cbipConnection = DriverManager.getConnection(connectionUrl, connectionProperties);

public  Connection getConnection() throws SQLException {
return cbipConnection;

public void closeConnection() throws SQLException {
cbipConnection = null;


  • modified code to change output so that there is not a trailing comma
  • added link to SQL*Plus FAQ


  1. I think you can prevent results coming on screen (to speed up the query using a SET command:

    *SET FLU[SH] {ON | OFF}
    Controls when output is sent to the user's display device.


  2. Thanks Simon! I tried that but it didn't appear to reduce the output.