Save excel file to blob column

hi

we are using light cell data provider to generate excel.

we are trying to store excel in blob column of data field.

excel = ((OracleResultSet) writeBLOBrst).getBLOB(“excel”);
blobOutputStream = excel.getBinaryOutputStream();

writeWorkbook.save(blobOutputStream,FileFormatType.XLSX);


but we are getting

java.io.IOException: ORA-22990: LOB locators cannot span transactions


at oracle.jdbc.dbaccess.DBError.SQLToIOException(DBError.java:717)

at oracle.jdbc.driver.OracleBlobOutputStream.flushBuffer(OracleBlobOutputStream.java:279)

at oracle.jdbc.driver.OracleBlobOutputStream.flush(OracleBlobOutputStream.java:215)

at java.io.FilterOutputStream.flush(FilterOutputStream.java:123)

at sun.nio.cs.StreamEncoder$CharsetSE.implFlush(StreamEncoder.java:410)

at sun.nio.cs.StreamEncoder.flush(StreamEncoder.java:152)

at java.io.OutputStreamWriter.flush(OutputStreamWriter.java:213)

at java.io.BufferedWriter.flush(BufferedWriter.java:230)

at org.dom4j.io.XMLWriter.flush(XMLWriter.java:272)

at org.dom4j.io.XMLWriter.write(XMLWriter.java:495)

at com.aspose.cells.dF.a(Unknown Source)

at com.aspose.cells.dF.a(Unknown Source)

at com.aspose.cells.dF.a(Unknown Source)

at com.aspose.cells.hv.a(Unknown Source)

at com.aspose.cells.hv.c(Unknown Source)

at com.aspose.cells.hv.j(Unknown Source)

at com.aspose.cells.hv.a(Unknown Source)

at com.aspose.cells.kK.a(Unknown Source)

at com.aspose.cells.iG.b(Unknown Source)

at com.aspose.cells.iG.a(Unknown Source)

at com.aspose.cells.Workbook.save(Unknown Source)

at eis.oracle.apps.xxeis.reporting.server.EiSRSGenerateExcelFromClob.generateExcel(EiSRSGenerateExcelFromClob.java:321)

at eis.oracle.apps.xxeis.reporting.server.EiSRSGenerateExcelFromClob.genearateClob(EiSRSGenerateExcelFromClob.java:162)

at eis.oracle.apps.xxeis.reporting.server.EiSRSGenerateExcelFromClob.genearateClob(EiSRSGenerateExcelFromClob.java:112)

at _eisrs._jsp._reporting._eisRsPivot__aspose__fClob._jspService(eisRsPivot_aspose_fClob.jsp:107)

at oracle.jsp.runtime.HttpJsp.service(HttpJsp.java:139)

at oracle.jsp.runtimev2.JspPageTable.service(JspPageTable.java:317)

at oracle.jsp.runtimev2.JspServlet.internalService(JspServlet.java:465)

at oracle.jsp.runtimev2.JspServlet.service(JspServlet.java:379)

at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)

at com.evermind.server.http.ServletRequestDispatcher.invoke(ServletRequestDispatcher.java:727)

at com.evermind.server.http.ServletRequestDispatcher.forwardInternal(ServletRequestDispatcher.java:306)

at com.evermind.server.http.ServletRequestDispatcher.forward(ServletRequestDispatcher.java:209)

at com.evermind.server.http.EvermindPageContext.forward(EvermindPageContext.java:199)

at _eisrs._jsp._reporting._eisRSAspose__wait__excel._jspService(eisRSAspose_wait_excel.jsp:37)

at oracle.jsp.runtime.HttpJsp.service(HttpJsp.java:139)

at oracle.jsp.runtimev2.JspPageTable.service(JspPageTable.java:317)

at oracle.jsp.runtimev2.JspServlet.internalService(JspServlet.java:465)

at oracle.jsp.runtimev2.JspServlet.service(JspServlet.java:379)

at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)

at com.evermind.server.http.ServletRequestDispatcher.invoke(ServletRequestDispatcher.java:727)

at com.evermind.server.http.ServletRequestDispatcher.forwardInternal(ServletRequestDispatcher.java:306)

at com.evermind.server.http.HttpRequestHandler.processRequest(HttpRequestHandler.java:767)

at com.evermind.server.http.HttpRequestHandler.run(HttpRequestHandler.java:259)

at com.evermind.server.http.HttpRequestHandler.run(HttpRequestHandler.java:106)

at EDU.oswego.cs.dl.util.concurrent.PooledExecutor$Worker.run(PooledExecutor.java:803)

at java.lang.Thread.run(Thread.java:534)


could you please give some note on this one.

Thanks & regards

Ranjith.

Hi,

I am not sure if it is an issue with Aspose.Cells for Java. For confirmation, please try saving the Excel file using File Streams (e.g. in java.io package etc.) without involving/using the Aspose.Cells for Java API. e.g. the line of code:
writeWorkbook.save(blobOutputStream,FileFormatType.XLSX);

You should get the same kind of exception.

If you have different thoughts or if it works fine, give us more details with sample project, we will check it soon.

Thank you.

hi

Thanks for your response.

if i use the following code it will work fine.


Workbook workbook = new Workbook();
Worksheets worksheets = workbook.getWorksheets();
Worksheet worksheet = worksheets.getSheet(0);
worksheet.getCells().getCell(0,0).setValue(“ggggg”);
//Saving the Excel file
workbook.save(blobOutputStream,FileFormatType.XLSX);

if use this one with light cell data provider we are getting error.

if i save to the disk at fist the file created in disk later data dumped into the file.

could please give some note on how to save excel into blob column which generating by LightcellDataProvider

we do not how light cell provider work exactly.

thanks & regards

Ranjith.







Hi,

How much time cost by the save process when you are saving the workbook by LightCellsDataProvider?

If you can save the workbook successfully to a file outputstream, by the exception message I think maybe the issue is of the transaction control of your oracle server, such as the used time is too long and exceeds the time limit for a transaction.

To confirm, I think you can use the same logic but provide less data for LightCellsDataProvider, such as only one cell value “ggggg”, to test whether it can be saved successfully to the blobOutputStream.

Also, you can try to save the workbook to a ByteArrayOutputStream first with the same logic and then re-write the data in the ByteArrayOutputStream to blobOutputStream. Saving excel2007 workbook to ByteArrayOutputStream with LightCellsDataProvider also can save much memory for you than building the complete cells structure in memory.