Search code examples
javaapache-poifileinputstreamclob

Reading Excel Data Issue From DB (CLOB Column) in Java with POI

I have a question looks to me so hard at first glance but maybe has very easy solution that I cant figure it out yet. I need to read binary data of an excel file which stored in a oracle database CLOB column.

Everything is ok with reading CLOB as string in java. I get excel file as binaries on a string parameter. 

String respXLS = othRaw.getOperationData(); // here I get excel file
InputStream bais = new ByteArrayInputStream(respXLS.getBytes());

POIFSFileSystem fs = new POIFSFileSystem(bais);
HSSFWorkbook wb = new HSSFWorkbook(fs);

Then I try to read ByteStreamData and put in POIFSFileSystem but I get this exception:

java.io.IOException: Invalid header signature; read 0x00003F1A3F113F3F, expected 0xE11AB1A1E011CFD0

I googled some excel problems, they mention about read access. So I download same excel file to hdd and change nothing with it(even I did not open it), and use FileInputStream by giving the file path. It has worked flawless. So what is the reason?

Any advice or alternative way to read from CLOB will be appreciated.

Thanks in advance, My Regards.

Solution

  • CLOB means Character Large OBject; You want to use a BLOB - Binary Large OBject. Change your database schema.

    What happens is that a CLOB will use a Character Set to convert your String to/from the database internal format, whatever that is; this will cause file corruption on non-text contents.

    Repeat after me: a String is not a byte[], and a character is not a byte.