Reading data froma file and inserting it into oracle database
1 posts in topic
Flat View  Flat View
TOPIC ACTIONS:
 

Posted By:   amerta_singh
Posted On:   Monday, April 9, 2001 05:45 AM

my problem is this that I have a binary file containing millions of records. What i hve to read the file and insert into the oracle databse . >>> the code which i write take 42 miniutes to enter the one lac (100000) records in the databse, i want to reduce this time to almost 20 minutes. therefore >>> kindly tell me isthere any fastest method to do this. ****** here is the code which i write******import java.io.*; import java.util.*; import java.sql.*; import com.comcept.database.*; public class read { private PushbackInputStream fis; public StringWriter strwriter; private Statement statement; private CallableStatemen   More>>

my problem is this that I have a binary file containing millions of records. What i hve to read the file and insert into the oracle databse .

>>> the code which i write take 42 miniutes to enter the one lac (100000) records in the databse, i want to reduce this time to almost 20 minutes. therefore

>>> kindly tell me isthere any fastest method to do this.


****** here is the code which i write******import java.io.*;
import java.util.*;
import java.sql.*;

import com.comcept.database.*;


public class read
{
private PushbackInputStream fis;
public StringWriter strwriter;
private Statement statement;
private CallableStatement cstatement;
private PreparedStatement pstatement;
private Connection connection;
private dbinfo info;
private dbconnection dbconn;
private String omcreport;



public read() throws IOException
{

fis = new PushbackInputStream(new FileInputStream("abc.dat"));

// these are basically the utility classes for establishing the database connection
// written by me
// simply connection methods can be used
dbinfo info = new dbinfo("","192.168.0.58","1521","orcl","msdetest","msdetest");
dbconn= new dbconnection();
dbconn.orcldbConnection(info);
this.connection= dbconn.conn;

}// end constructor



private byte[] readNameLength() throws IOException
{

byte buff[] = new byte[4];
int gg;

for(int i=0;i <4;i++)
{

buff[i] = (byte)fis.read();
}

return buff;

}

private byte[] readYearLength() throws IOException
{

byte buff[] = new byte[4];

for(int i=0;i <4;i++)
{
buff[i] = (byte)fis.read();
}

return buff;

}


private byte[] readCounterLength() throws IOException
{

byte buff[] = new byte[4];

for(int i=0;i <4;i++)
{
buff[i] = (byte)fis.read();
}

return buff;

}


private int getYearLength (byte b[] ) throws IOException
{

int hh=0;
for (int i=3;i>-1;i--) hh |= ((b[i] & 0xff) < < 8*i);

return hh;

}

private int getNameLength(byte b[] ) throws IOException
{
int hh=0;
for (int i=3;i>-1;i--) hh |= ((b[i] & 0xff) < < 8*i);


return hh;


}


private int getCounterLength (byte b[] ) throws IOException
{

int hh=0;
for (int i=3;i>-1;i--) hh |= ((b[i] & 0xff) < < 8*i);
return hh;

}

private String getName(int len ) throws IOException
{

String S = "";
for(int i=0;i
{
S+=(char)fis.read();
}

return S;

}
private String getData()throws IOException
{

byte buff1[] =readCounterLength();
int tp =getCounterLength(buff1);

byte buff2[] = readCounterLength();
int counter1 =getCounterLength(buff2);
byte buff3[] = readCounterLength();
int counter2 =getCounterLength(buff3);
byte buff4[] = readCounterLength();
int counter3 =getCounterLength(buff4);
byte buff5[] = readCounterLength();
int counter4 =getCounterLength(buff5);
byte buff6[] = readCounterLength();
int counter5 =getCounterLength(buff6);
byte buff7[] = readCounterLength();
int counter6 =getCounterLength(buff7);
byte buff8[] =readCounterLength();
int counter7 =getCounterLength(buff8);
byte buff9[] =readCounterLength();
int counter8 =getCounterLength(buff9);

String tpcounter =""+tp+","+counter1+","+counter2+","+counter3+","+counter4+","+counter5+","+counter6+","+counter7+","+counter8;
return tpcounter;
}


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

read r = new read();

byte buff[] = r.readNameLength();
int length = r.getNameLength(buff);
System.out.println("length = "+length);
String name = r.getName(length);
System.out.println("name = "+name);
int ext = r.fis.read();
System.out.println("Ex Type = "+ext);
int day = r.fis.read();
System.out.println("day = "+day);
int month = r.fis.read();
System.out.println("month = "+month);
byte buff1[] = r.readYearLength();
int year = r.getYearLength(buff1);
System.out.println("year = "+year);

String date =""+day+"-"+month+"-"+year;


int counter = 0;
String dummydate = "14-12-2001";


while(r.fis.available()>0)
{


String q = r.getData();

String query = "call sp_UpdDwnRecord ( \'"+name+"\',\'"+"20-04-2001"/*date.trim()*/+"\',"+q+")";
String record = name+" "+date+" "+q;
try
{

r.pstatement = r.connection.prepareStatement(query);
r.pstatement.execute();
r.pstatement.close();
}
catch (Exception e)
{
System.out.println("exception"+e.getMessage());

}
counter++;
System.out.println(""+counter+" "+record);
}

}







}// end clsss

   <<Less

Re: Reading data froma file and inserting it into oracle database

Posted By:   Michael_Wax  
Posted On:   Monday, April 9, 2001 11:24 AM

Is writing your own Java application the best way to do this? Oracle has a high-speed loading application that will do the same thing. I understand that the Oracle application can load millions of records over a few minutes. If the Oracle application doesn't read your file format, then the best way to go might be to write a small app to read your file format and out output a new file formatted in a way that the Oracle app accepts.
About | Sitemap | Contact