java code:
@SuppressWarnings({ "rawtypes", "resource" })
public void addAttachment(ChildrenValue childrenValue, String childrenId, Connection con) throws SQLException, IOException {
int i = 0;
// SQL queries for insertion and retrieval of BLOB data
String sqlNewRow = "INSERT INTO PA\_CHILDREN\_ATTACHMENT (QUOT\_ID, CHILD\_ID, QUOT\_NAME, QUOT\_FILETITLE, QUOT\_SIZE, QUOT\_FILENAME, QUOT\_DESCRIPTION) VALUES (?, ?, EMPTY\_BLOB(), ?, ?, ?, ?)";
String sqlLockRow = "SELECT QUOT\_NAME FROM PA\_CHILDREN\_ATTACHMENT WHERE QUOT\_ID = ? AND CHILD\_ID = ? FOR UPDATE";
String sqlSetBlob = "UPDATE PA\_CHILDREN\_ATTACHMENT SET QUOT\_NAME = ? WHERE QUOT\_ID = ? AND CHILD\_ID = ?";
PreparedStatement initAttachmentPrepdStmt = null;
PreparedStatement newAttachmentPrepdStmt = null;
PreparedStatement insertAttachmentPrepdStmt = null;
try {
initAttachmentPrepdStmt = con.prepareStatement(sqlNewRow);
newAttachmentPrepdStmt = con.prepareStatement(sqlLockRow);
insertAttachmentPrepdStmt = con.prepareStatement(sqlSetBlob);
Vector addedAttachment = childrenValue.getAddedAttachment();
Iterator iteratorQ = addedAttachment.iterator();
LOGGER.info("Starting to insert attachment data for children...");
while (iteratorQ.hasNext()) {
ChildrenActionForm quotData = (ChildrenActionForm) iteratorQ.next();
i++;
initAttachmentPrepdStmt.setLong(1, i);
initAttachmentPrepdStmt.setLong(2, Long.parseLong(childrenId));
initAttachmentPrepdStmt.setString(3, quotData.getFileTitle());
initAttachmentPrepdStmt.setString(4, quotData.getSize());
initAttachmentPrepdStmt.setString(5, quotData.getFileName());
initAttachmentPrepdStmt.setString(6, quotData.getContentType());
initAttachmentPrepdStmt.executeUpdate();
newAttachmentPrepdStmt.setLong(1, i);
newAttachmentPrepdStmt.setLong(2, Long.parseLong(childrenId));
ResultSet rs = newAttachmentPrepdStmt.executeQuery();
if (con != null && !con.isClosed()) {
// The connection is still open
LOGGER.info("connection is ok");
} else {
// The connection has been closed
LOGGER.info("connection closed");
}
if (rs != null) {
// The cursor is still open
LOGGER.info("cursor is open");
} else {
// The cursor has been closed
LOGGER.info("cursor closed");
}
if (rs.next()) {
Blob dbBlob = rs.getBlob("QUOT\_NAME");
byte\[\] byteAccess = quotData.getByteData();
LOGGER.info("Byte data size: " + byteAccess.length);
InputStream inputStream = dbBlob.getBinaryStream();
LOGGER.info("binary stream.....: " + dbBlob.getBinaryStream());
FileOutputStream fileOutputStream = new FileOutputStream(sqlSetBlob); //"PA\_CHILDREN\_ATTACHMENT"
byte\[\] buffer = new byte\[1024\];
int bytesRead;
while ((bytesRead = inputStream.read(buffer)) != -1) {
fileOutputStream.write(buffer, 0, bytesRead);
}
fileOutputStream.close();
inputStream.close();
}
con.close();
LOGGER.info("Transaction committed successfully.");
}
} catch (SQLException e) {
LOGGER.info("SQL Exception: " + e);
con.rollback();
throw e;
} catch (Exception e) {
LOGGER.info("Unexpected error: " + e);
con.rollback();
throw new SQLException("Unexpected error during attachment insertion.", e);
} finally {
try {
if (initAttachmentPrepdStmt != null) initAttachmentPrepdStmt.close();
if (newAttachmentPrepdStmt != null) newAttachmentPrepdStmt.close();
if (insertAttachmentPrepdStmt != null) insertAttachmentPrepdStmt.close();
} catch (SQLException e) {
LOGGER.info("Error closing resources: " + e);
}
}
}
tomcat log:
java.sql.SQLException: ORA-64219: invalid LOB locator encountered
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:168)
at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:208)
at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:543)
at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1405)
at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteFetch(TTC7Protocol.java:822)
at oracle.jdbc.driver.OracleStatement.executeNonQuery(OracleStatement.java:1446)
at oracle.jdbc.driver.OracleStatement.doExecuteOther(OracleStatement.java:1371)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1900)
at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:363)
at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:407)
at oracle.sql.LobPlsqlUtil.plsql\_getChunkSize(LobPlsqlUtil.java:1202)
at oracle.sql.LobPlsqlUtil.plsql\_getChunkSize(LobPlsqlUtil.java:121)
at oracle.jdbc.dbaccess.DBAccess.getLobChunkSize(DBAccess.java:955)
at oracle.sql.LobDBAccessImpl.getChunkSize(LobDBAccessImpl.java:111)
at oracle.sql.BLOB.getChunkSize(BLOB.java:228)
at oracle.sql.BLOB.getBufferSize(BLOB.java:242)
at oracle.sql.BLOB.getBinaryStream(BLOB.java:124)
at ess.personnelinfo.resource.ChildrenDataAccess.addAttachment(ChildrenDataAccess.java:1586)
at ess.personnelinfo.business.PersonnelInfoBusiness.submitChildren(PersonnelInfoBusiness.java:1668)
at ess.personnelinfo.presentation.PersonnelInfoBusinessDelegate.submitChildren(PersonnelInfoBusinessDelegate.java:770)
at ess.personnelinfo.presentation.ChildrenAction.doApplyChildrenResult(ChildrenAction.java:573)
at ess.personnelinfo.presentation.ChildrenActionBase.applyChildrenResult(ChildrenActionBase.java:135)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:606)
at org.apache.struts.actions.DispatchAction.perform(DispatchAction.java:236)
at org.apache.struts.action.ActionServlet.processActionPerform(ActionServlet.java:1787)
at org.apache.struts.action.ActionServlet.process(ActionServlet.java:1586)
at org.apache.struts.action.ActionServlet.doPost(ActionServlet.java:510)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:647)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:728)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:303)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
at ess.filter.AppFilter.doFilter(AppFilter.java:218)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
at com.opensymphony.module.sitemesh.filter.PageFilter.parsePage(PageFilter.java:119)
at com.opensymphony.module.sitemesh.filter.PageFilter.doFilter(PageFilter.java:55)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:219)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:110)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:492)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:165)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:104)
at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:1025)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:116)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:452)
at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1201)
at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:654)
at org.apache.tomcat.util.net.AprEndpoint$SocketProcessor.doRun(AprEndpoint.java:2531)
at org.apache.tomcat.util.net.AprEndpoint$SocketProcessor.run(AprEndpoint.java:2520)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
at java.lang.Thread.run(Thread.java:745)
i am using java 7, ojdbc10.jar, tomcat 7 and oracle 19c. before this, it could run with oracle 12c without this error.
Anyone please help me solve this. thanks