CODEWITHSUNDEEP
javaoracle-databaseoracle-sqldeveloperoracle12c
kapiell
kapiell

Reputation: 697

Issue inserting Oracle Blob content into another record using Java

I am working on proof of concept to read a blob content from Oracle, manipulate it and then insert back as new record using Java. Currently, I am trying to just read and then write back blob content to Oracle but facing issues. I am able to write back but looks like the file is not getting inserted completely.

Error when trying to view/download Blob via SQL developer

Error

Code used to read and write back

conn.setAutoCommit(false);
stmt = conn.createStatement();
sql = "SELECT DOC_ID, NAME, BLOB_CONTENT FROM DOCUMENTS WHERE DOC_ID = " + String.valueOf(docid);

ResultSet rs_docs = stmt.executeQuery(sql);
while (rs_docs.next()) {



Show_Message("Conversion sub process started ...");
doc_name = rs_docs.getString("name");
Blob ib = rs_docs.getBlob("blob_content");


Show_Message("Uploading converted pdf to database ... ");
InputStream input = ib.getBinaryStream();
String filename = doc_name;

CallableStatement callableStatement = conn.prepareCall("begin INSERT INTO DOCUMENTS(NAME, BLOB_CONTENT) VALUES(?,?) RETURNING DOC_ID INTO ?; end;");
callableStatement.setString(1, filename);
callableStatement.setBinaryStream(2, input, input.available());
callableStatement.registerOutParameter(3, Types.INTEGER);
callableStatement.executeQuery();
docid = callableStatement.getInt(3);
callableStatement.close();

Show_Message("New record created, doc # " + String.valueOf(docid));
Show_Message("Conversion Process completed!!!");

}
stmt.close();
conn.commit();
conn.close();
rs_docs.close();

Upvotes: 0

Views: 130

Answers (1)

John Bollinger
John Bollinger

Reputation: 181714

  1. Connection.prepareCall() is for creating a Statement that calls a stored procedure. If you want to do that then you should define an SP in the database, outside the scope of this method, and call it by name via your [Callable]Statement. But if the only point is to get the DOC_ID assigned to the new row, then there are other ways, such as Statement.getGeneratedKeys().

  2. Do not use InputStream.available() to determine the size of the blob. "Available" means the number of bytes readable without blocking, right now, and it is allowed to be an arbitrarily inaccurate underestimate -- even zero. It is not a reliable measure of the total number of bytes that may eventually be readable from the stream. Instead, use the Blob's length() method.

Upvotes: 1

Related Questions