Setting Oracle CLOB field to null fails with ORA 22275

Question

We have one(!) customer where setting an Oracle CLOB field to NULL fails with

[FireDAC][Phys][Ora] ORA 22275 - Invalid LOB locator specified

The query that is sent to the database^* is

update tt_hrs set
 TT_INFO = ?
where
 TT_HRS_ID = ?

Params:
0 -  : <NULL>
1 -  : 276727

Querying the dataset through FireDAC shows me that lDataset.Fields[i].DataType for field TT_HRS is ftWideMemo.

Many things I find on the internet are associated with the 'old way' (Oracle 8.0.5 IIRC) of updating CLOBS where you used

UPDATE ClobTable
SET
  Value = EMPTY_CLOB()
WHERE
  Id = :Id
RETURNING
  Value
INTO
  :Value

but AFAIK these kind of statements are no longer required.

In SQLPLUS I can execute these without issues on our own Oracle 12c database, so the difference between EMPTY_CLOB() and NULL does not seem to matter:

update tt_hrs set tt_info='test' where tt_hrs_id=276727;
update tt_hrs set tt_info=NULL where tt_hrs_id=276727;
update tt_hrs set tt_info=empty_clob() where tt_hrs_id=276727;

As the error message shows we use FireDAC in a Delphi Tokyo 10.2.2 32-bit Windows app.
There is no NOT NULL constraint on the field, it is not in an index, there are no triggers.
The client uses OracleDB12 Release 1.
Our update code is generated by FireDAC from a TClientDataSet connected to a grid that the user edits.

Question:

Is there anything in Oracle settings that might explain this behavior?
Maybe they set some 'compatibility mode' to support old applications or something... I'm not familiar enough with Oracle.

Note: It wouldn't by any chance be related to the issue with 2-byte chars I reported earlier?

Clutching at straws here...

^{* We can log this because we have a TDataSetProvider descendant that logs what is being sent in an overridden DoBeforeExecute.}

score 0 · Answer 1 · answered Sep 24 '18 at 07:41

We have never been able to find the exact cause of this, but have found a workaround.

The affected code all works through TClientDataSet, and we already used a TDataSetProvider descendant with an overriden DoBeforeExecute to log the actual SQL sent to the database (for debugging purposes). This calls a logging procedure in our code and we have added the below to that:

// Force parameter to ftOraClob when NULL:
if TFDQuery(TDataSetProvider(Sender).Dataset).Connection.Params.DriverID = S_FD_OraId then
  for i := 0 to params.count-1 do
    if (params[i].DataType = ftMemo) and (VarIsNull(params[i].Value) or VarIsClear(params[i].Value)) then
       params[i].DataType := ftOraClob;

This forces the AddField subroutine in TSQLResolver.GenUpdateSQL in Datasnap.Provider.pasto follow the path:

else if UseFieldInUpdate(Field) then
begin
  Result := True;
  if (Field.DataType = ftOraClob) and (not InformixLob) then
  begin
    NoParam := True;
    if InObject then
      SQL.Add(string.Format(' %s.%s = EMPTY_CLOB(),', [Alias, QuoteFullName(Field.FullName, PSQLInfo(Tree.Data).QuoteChar),   { Do not localize }
        Field.FullName]))

thereby writing = EMPTY_CLOB() instead of = NULL, which the Oracle DB happily accepts.

Note: We test for ftMemo because we have a mapping rule active that says

with AConnection.FormatOptions.MapRules.Add do 
begin
  SourceDataType := dtWideHMemo;
  TargetDataType := dtMemo;
end;

(see https://stackoverflow.com/a/47904988/512728)
If you don't have this, testing for ftWideMemo would be required.

Setting Oracle CLOB field to null fails with ORA 22275

1 Answers1