Hi,
I have a text file on UNIX file system. This text files contains some 5000 lines. Where each line represents a record in an Oracle Table.( basically i spooled a oracle table's data into this text file)
I have a java class that writes this text file into a BLOB column on oracle database.
As you know there are two modes: ASCII and BINARY.
Basically, ASCII mode should be used when end-of-line character translation is required. and Binary mode (a.k.a. image mode) should be used whenever no end-of-line translation is required.
I need end-of-line character. i.e, each record should be written in new line in the BLOB.
But with the code i have, i am doing it the other way.
for ex: the data in blob should be like
1|VA|ARTNVAFC|FALLS CHURCH|2|H2009|8A00746|8A00738|FALLS CHURCH||TESINC|RELEASED TO CONSTRUCTION|03/30/2004||139|28|0|0|0|0|0|0|167||827|DISTRIBUTION|112635|O|||||||
2|VA|ARTNVAFC|FALLS CHURCH|2|H2010|8A00746|8A00736|FALLS CHURCH||TESINC|RELEASED TO CONSTRUCTION|02/20/2004||178|0|0|0|0|0|0|0|178||1711|DISTRIBUTION|135843|O|||||||
JAVA PROGRAM: I am not including all the code.. these the snippet that uploads the BLOB file in oracle table with the file from UNIX file system. .
byte[] fileData=
getFileData(fileName , filePath);
updateFileData(conn,fileName,fileData); public byte[]
getFileData(String fileName,String filePath)
{
// Returns the contents of the file in a byte array.
File file=null;
try
{
file=new File(filePath + fileName);
if (!file.exists())
{
logFileException(filePath + fileName + " does not exists in the directory /u22/extract/");
return null;
}
InputStream is = new FileInputStream(file);
// Get the size of the file
long length = file.length();
/* You cannot create an array using a long type. It needs to be an int type.
Before converting to an int type, check to ensure that file is not larger than Integer.MAX_VALUE.*/
// Create the byte array to hold the data
byte[] bytes = new byte[(int)length];
// Read in the bytes
int offset = 0;
int numRead = 0;
while (offset < bytes.length && (numRead=is.read(bytes, offset, bytes.length-offset)) >= 0)
{
offset += numRead;
}
// Ensure all the bytes have been read in
if (offset < bytes.length)
{
logFileException(filePath + fileName + " Could not completely read file ");
return null;
}
// Close the input stream and return bytes
is.close();
return bytes;
}
catch(Exception e)
{}
return null;
}
public boolean updateFileData(Connection conn , String fileName,byte[] fileData)
{
PreparedStatement pstmt = null;
ResultSet rs1=null;
java.sql.Blob tempBlob=null;
FileInputStream is;
java.io.OutputStream os;
try
{
String fileNameStr=fileName.toUpperCase() ;
fileNameStr=fileNameStr.replaceAll("'","''");
pstmt=conn.prepareStatement("select ATTACH_FILE_CONTENT FROM TABLE_ATTACHMENTS "
+" Where upper(ATTACH_NAME)='" + fileNameStr + "' for update ");
rs1 = pstmt.executeQuery();
while (rs1.next())
{
tempBlob=rs1.getBlob("ATTACH_FILE_CONTENT");
}
pstmt.close();
rs1.close();
os=((oracle.sql.BLOB)tempBlob).getBinaryOutputStream();
os.write(fileData);
os.flush();
os.close();
pstmt = conn.prepareStatement("UPDATE " + TABLE_ATTACHMENTS + " set UPDATED_BY='USER',ATTACH_FILE_CONTENT=?, UPDATED_DT=SYSDATE Where upper(ATTACH_NAME)=? ");
pstmt.setBlob(1,tempBlob);
pstmt.setString(2,fileName.toUpperCase());
pstmt.executeUpdate();
pstmt.close();
return true;
}
catch(Exception e)
{
e.printStackTrace();
log ("updateFileData() - file Name=" + fileName + " exception " + e.toString() );
try
{
pstmt.close();
rs1.close();
}
catch(Exception ex)
{ex.printStackTrace();}
}
finally{
try
{
if (rs1 != null) {rs1.close();}
if (pstmt != null) {pstmt.close();}
}
catch(Exception e)
{e.printStackTrace();}
}
return false;
}
the data in the blob with above code is like :
1|VA|ARTNVAFC|FALLS CHURCH|2|H2009|8A00746|8A00738|FALLS CHURCH||TESINC|RELEASED TO CONSTRUCTION|03/30/2004||139|28|0|0|0|0|0|0|167||827|DISTRIBUTION|112635|O|||||||
with a line delimiter here 2|VA|ARTNVAFC|FALLS CHURCH|2|H2010|8A00746|8A00736|FALLS CHURCH||TESINC|RELEASED TO CONSTRUCTION|02/20/2004||178|0|0|0|0|0|0|0|178||1711|DISTRIBUTION|135843|O|||||||
Thanks a lot
Naveen
