Oracle table Export to CSV

import java.io.BufferedWriter;

import java.io.FileNotFoundException;

import java.io.FileWriter;

import java.io.IOException;

import java.sql.*;

public class DBMigration {

Connection connSRC = null;

Connection connDST = null;

int intColCount = 0;

Statement stmt = null;

ResultSet rs = null;

int totCounter=0;

private long recordsCount=0;

public DBMigration() throws Exception{

SrcConnectDatabase();

DestConnectDatabase();

/*String csql = “select count(*) from test_lyt”;

rs = connSRC.createStatement().executeQuery(csql);

rs.next();*/

countTotalRecords();

loadToPostgresDBTruncate();

long count =recordsCount;

System.out.println(“count “+count);

long initdivider = 1l;

long range = 1000000;

long divider = range;

//System.out.println(“divider “+divider);

long modulus = count%divider;

//System.out.println(“modulus “+modulus);

int filesnum = (int)(count/divider)+1;

//System.out.println(“filesnum “+filesnum);

String sql = “SELECT msisdn,cmpid,act_date,sub_dat,sub_res,unique_id,status,dob_dt FROM (SELECT rownum rn,trv.IDENTIFICATION_CODE_V as msisdn”

+”, trv.CAMP_ID_V as cmpid,trv.ACTIVE_DATE_D as act_date, trv.SUBSCRIPTION_DATE_D as sub_dat”

+”, trv.SUBSCRIPTION_RESPONSE_V as sub_res, trv.UNIQUE_ID_V as unique_id, trv.STATUS_V as status, sm.dob_dt as dob_dt”

+” FROM test_lyt trv left join LYT_SUBSCRIBER_MASTER sm”

+” on trv.IDENTIFICATION_CODE_V=sm.IDENTIFICATION_CODE_V) where rn between “;

stmt = connSRC.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);

for(int i=1;i<=filesnum;i++)

{

sql+= initdivider+" and "+divider;

System.out.println("inside for-query "+sql);

rs = stmt.executeQuery(sql);

ResultSetMetaData rsmd = rs.getMetaData();

intColCount = rsmd.getColumnCount();

writeToFile("OracleToCSV"+i+".csv");

totCounter = i;

initdivider = divider+1l;

if((count-divider) == modulus)

divider+=modulus;

else

divider+=range;

sql = "SELECT msisdn,cmpid,act_date,sub_dat,sub_res,unique_id,status,dob_dt FROM (SELECT rownum rn,trv.IDENTIFICATION_CODE_V as msisdn"

+", trv.CAMP_ID_V as cmpid,trv.ACTIVE_DATE_D as act_date, trv.SUBSCRIPTION_DATE_D as sub_dat"

+", trv.SUBSCRIPTION_RESPONSE_V as sub_res, trv.UNIQUE_ID_V as unique_id, trv.STATUS_V as status, sm.dob_dt as dob_dt"

+" FROM test_lyt trv left join LYT_SUBSCRIBER_MASTER sm"

+" on trv.IDENTIFICATION_CODE_V=sm.IDENTIFICATION_CODE_V) where rn between ";

try{Thread.sleep(10000);}catch(Exception e){}

System.gc();

try{Thread.sleep(5000);}catch(Exception e){}

System.out.println("completed "+i);

}

}

/*

Count the total Number of Records

*/

public void countTotalRecords()

{

try{

Statement stmt = null;

ResultSet rs = null;

stmt = connSRC.createStatement();

rs = stmt.executeQuery("select count(*) from test_lyt");

rs.next();

recordsCount = rs.getLong(1);

System.out.println(recordsCount);

stmt.close();

//connSRC.close();

}catch(Exception e){

System.out.println("Error"+e);

}

}

/*

Truncate the Destination PostgreSQL table

*/

public void loadToPostgresDBTruncate()

{

try

{

Statement stmt = null;

stmt = connDST.createStatement();

String query = "truncate table croretable";

stmt.executeQuery(query);

}

catch (SQLException se)

{

System.out.println("Function name :loadToPostgresDBTruncate");

se.printStackTrace();

}

catch(Exception exe)

{

System.out.println("Error:"+exe);

}

}

/*Connection to the database. DBConnection is the class present in same package.*/

public void SrcConnectDatabase() throws Exception

{

SourceDBConnection dbConSRC = new SourceDBConnection();

dbConSRC.ConnectDB();

connSRC = dbConSRC.conn;

//System.out.println("SRC Connection:"+connSRC);

}

/*

Destination DB Connecton PostgreSQL

*/

public void DestConnectDatabase()

{

DestinationDBConnection dbConDST = new DestinationDBConnection();

dbConDST.ConnectDB();

connDST = dbConDST.conn;

//System.out.println("DST Connection:"+connDST);

}

/**

* Prints some data to a file using a BufferedWriter

*/

public void writeToFile(String filename) throws Exception{

BufferedWriter bufferedWriter = null;

try {

//Construct the BufferedWriter object

bufferedWriter = new BufferedWriter(new FileWriter(filename));

int count = 0;

String strFull = "";

while(rs.next()){

String strRow = "";

for(int i = 1; i 1000){

bufferedWriter.write(strFull);

count = 0;

strFull = “”;

}

count++;

}

} catch (FileNotFoundException ex) {

ex.printStackTrace();

} catch (IOException ex) {

ex.printStackTrace();

} finally {

//Close the BufferedWriter

try {

if (bufferedWriter != null) {

bufferedWriter.flush();

bufferedWriter.close();

}

} catch (IOException ex) {

ex.printStackTrace();

}

}

}

/**

* This is for load Oracle to PostgressQL

*/

public void loadToPostgresDBUpload()

{

try

{

for(int k=1;k<=totCounter;k++)

{

Statement stmt1 = null;

stmt1 = connDST.createStatement();

//String query1 = "copy ACT_BASE_MOM_SUB from '//home//ashoka//Desktop//orcl//ACT_BASE_MOM_SUB.csv' using delimiters ',' with CSV header";

String query1 = "copy croretable from '//home//abiba//Desktop//orcl//OracleToCSV"+k+".csv' using delimiters ',' ";

stmt1.executeUpdate(query1);

}

}catch (SQLException se)

{

System.out.println("Couldn't Connect to Database:"+se);

se.printStackTrace();

}

catch(Exception exe)

{

System.out.println("Function name :loadToPostgresDBUpload");

System.out.println("Error:"+exe);

}

}

/**

* @param args the command line arguments

*/

public static void main(String[] args) throws Exception{

DBMigration x= new DBMigration();

x.loadToPostgresDBUpload();

//x.loadToPostgresDBUpload();

}

}

About ashokabhat

I am a C,C ,JAVA,Adobe Flex,.NET Programmer Currently working as a Software Developer
This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s