CODEWITHSUNDEEP
javaoracle-databasenullsql-updateclob
Oleg Sandro
Oleg Sandro

Reputation: 59

java.sql.SQLException: ORA-22275: invalid LOB locator specified

How can I update records in my database?

I work with db in Oracle. I have the table with columns RQUID(VARCHAR), STATUS(VARCHAR) and XMLDATA(CLOB). I want to update records in this table by RQUID. When I update STATUS and XMLDATA, things are good. When xmlData=null, I want to set null to XMLDATA in table, but in the line int rows = pstmt.executeUpdate(); I get next message:

[WARN] 2020-08-11 10:46:54,038 [pool-2-thread-1] com.example.dao.AbstractDAO closeCLob - java.sql.SQLException: ORA-22275: invalid LOB locator specified

and my program freezes. Also the record is not updated. Can you have any suggestions, how to force my program to update records in both cases: when xmlData="someData" and when xmlData=null?

My code:

public boolean updateRecord(String rqUid, Integer code, String xmlData) throws MyException {
    if (rqUid != null) {
        String sql = "UPDATE MY_TABLE SET STATUS=?, XMLDATA=? WHERE RQUID=?";
        try (Connection con = pool.getConnection();
             PreparedStatement pstmt = con.prepareCall(sql)) {
            pstmt.setString(1, (code != null) ? code.toString() : null);
            pstmt.setString(2, xmlData);
            pstmt.setString(3, rqUid);
            int rows = pstmt.executeUpdate();
            return rows > 0;
        } catch (Exception e) {
            String errorMessage = "Ошибка обновления записи";
            LOGGER.info(errorMessage, e);
            throw new MyException(errorMessage + ".", e);
        }
    } else {
        return false;
    }
}

Thanks for any help!

Upvotes: 0

Views: 991

Answers (2)

Roberto Hernandez
Roberto Hernandez

Reputation: 8528

When the field XMLDATA is null, you should update the table like this

UPDATE MY_TABLE  SET XMLDATA = EMPTY_CLOB();

I think you might add a new String sql when XMLDATA is null

String sql = "UPDATE MY_TABLE SET STATUS=?, XMLDATA=empty_clob() WHERE RQUID=?";

Upvotes: 1

Alex Poole
Alex Poole

Reputation: 191560

You could explicitly set it to null:

if (xmlData == null || xmlData.length() == 0) {
    pStmt.setNull(2, java.sql.Types.CLOB);
}
else {
    pstmt.setString(2, xmlData);
}

Upvotes: 1

Related Questions