0

I am trying to insert blob in Oracle SQL 11 by the following JDBC java code. But it throws an exception:-

    private static final String INSERT_INTERIOR_ROOM_LOG = "INSERT INTO INTERIOR_ROOM_LOG"
    + "(PNO12, STR_WEEK_FROM, STR_WEEK_TO, RESPONSE_XML, MODIFIED_DATE, LOG_TIME ) " + "VALUES(?, ?, ?, EMPTY_BLOB(), SYSDATE, SYSTIMESTAMP)";
private static final String selectNextSequenceId = "select INTERIOR_LOG_INCREMENT_SEQ.nextval from dual";
private static final String selectIteriorRoomUpdate = "select RESPONSE_XML from INTERIOR_ROOM_LOG where id = ? for update";

static public Long insertIntoInteriorMaster(Connection connection, String pno12, long startWeek, long endWeek, String responseXml) {
    Long retValue = -1l;
    PreparedStatement pst = null;
    ResultSet rset = null;
    try {
        pst = connection.prepareStatement(INSERT_INTERIOR_ROOM_LOG);
        pst.setString(1, pno12);
        pst.setLong(2, startWeek);
        pst.setLong(3, endWeek);
        pst.execute();
        connection.commit();

        PreparedStatement selectNextSequenceIdStatement = null;
        ResultSet selectNextSequenceIdRs = null;
        int sequenceId = -1;
        PreparedStatement insertLogQueryStatement = null;
        PreparedStatement selectMqMessageUpdateStatement = null;
        ResultSet selectMqMessageUpdateRs = null;

        selectNextSequenceIdStatement = connection.prepareStatement(selectNextSequenceId);
        selectNextSequenceIdRs = selectNextSequenceIdStatement.executeQuery();
        selectNextSequenceIdRs.next();
        sequenceId = selectNextSequenceIdRs.getInt(1);
        System.out.print("next id: " + sequenceId);
        byte[] byteXaml = responseXml.getBytes();
        if (byteXaml != null) {
            selectMqMessageUpdateStatement =
                connection.prepareStatement(selectIteriorRoomUpdate);
            selectMqMessageUpdateStatement.setInt(1, sequenceId);
            selectMqMessageUpdateRs =
                    selectMqMessageUpdateStatement.executeQuery();
            selectMqMessageUpdateRs.next();
            BLOB queuedMessage = (BLOB)selectMqMessageUpdateRs.getBlob(1); // exception in this line
            OutputStream bos = queuedMessage.setBinaryStream(1);
            int bufferSize = queuedMessage.getBufferSize();
            int bytesToWrite = byteXaml.length;

            for (int i = 0; i <= bytesToWrite && bufferSize > 0; i +=
                    bufferSize) {
                if (i + bufferSize >= bytesToWrite) {
                    bufferSize = bytesToWrite - i;
                }
                bos.write(byteXaml, i, bufferSize);
            }

            bos.flush();
            bos.close();
            connection.commit();
        }
        System.out.println("Interior Log insert commited");
    } catch (SQLException e) {
        String errorMsg = String.format("SQL State: %s\n%s", e.getSQLState(), e.getMessage());
        System.out.println(errorMsg);
    } catch (Exception ex) {
        System.out.println("Error when insert in interior log. Handle error " + ex.getMessage());
    }
    return retValue;
}

SQL State: 99999 Exhausted result set

Could please tell me how do I insert blob into Oracle SQL?

masiboo
  • 4,537
  • 9
  • 75
  • 136
  • Is the `interior_room_log.id` being set by a trigger, from that sequence? If so, don't you want `currval` rather than `nextval`? Or better, return the generated ID back into a variable instead of having a separate query for it? – Alex Poole Aug 19 '19 at 21:57
  • [This might be useful](https://stackoverflow.com/a/17459510/266304). – Alex Poole Aug 19 '19 at 22:03

0 Answers0