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

SQL*Plus

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

Click here for more info about SQL*Plus

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 (rs.next()) {

StringBuilder sb = new StringBuilder();

for (int i = 1; i < numCols; i++) {

sb.append(rs.getObject(i)).append(delimeter);

}
   sb.append(rs.getObject(numCols));



System.out.println(sb.toString());

if ((row%lineBatch) == 0) {

System.err.println(row);

}

row++;

}

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);

dbConn.closeConnection();

}




}







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.close();

cbipConnection = null;

}

}




Edits:

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