Oracle 12C and Firedac do not get along when a CLOB type is Null. Lob Locator Errors occur

[Todd Flora]

When trying to Set an Oracle CLOB type to Null using Oracle 12C and Firedac an error occurs:

[FireDAC][Phys][Ora] ORA-22275: invalid LOB locator

This only happens when the value trying to be set into the CLOB is Null. Currently we have code for handling Unicode strings into CLOBS in the TOracleSQLGenerator that looks like this:

function TOracleSQLGenerator.CreateParam(const paramField: TSQLParamField;
  const value: TValue): TDBParam;
begin
  Result := inherited;
  if Assigned(paramField.Column) and (paramField.Column.Length > 2000) then
    TOracleDBParam(Result).FParamType := ftWideMemo;
end;

It is still necessary to set the ParemType to ftWideMemo in order to preserve Unicode Characters as stated by the Firedac documentation, but now when the value is Null in order to avoid the Lob locator error above the ParamType needs to be ftOraClob.

Of course Embarcadero should fix this in a future release of Firedac and we have submitted an issue with them, but until such time as they do can you suggest a workaround for setting the paramtype to ftOraClob only when the value is null?

[Todd Flora]

The following code resolves the issue.

function TOracleSQLGenerator.CreateParam(const paramField: TSQLParamField;
  const value: TValue): TDBParam;
var
  S : TValue;
begin
  Result := inherited;
  if Assigned(paramField.Column) and (paramField.Column.Length > 2000) then
  begin
    S := Value;
    if IsNullable(Value.TypeInfo) and not Value.TryGetNullableValue(S) then
      S := TValue.Empty;

    if S.IsEmpty or (S.IsString and S.AsString.IsEmpty) then
      TOracleDBParam(Result).FParamType := ftOraClob
    else
      TOracleDBParam(Result).FParamType := ftWideMemo;
  end;
end;

[Stefan Glienke]

Why are you checking for empty string?

[Stefan Glienke]

I pushed the changes to the issue/166 branch. Please have a look

[Todd Flora]

The change looks good except for the check of String.IsEmpty.

If the string is a zero length string then the call to TryGetNullableValue will succeed and return a zero length string. In this case S will not be empty but rather a zero length string. This still causes the Oracle error when the param type is set to ftWideMemo. So the check for S being a String and empty is necessary.

And I know this because I had it coded just as you have it at first but it was still failing. When I debugged it I found this additional issue and added the check for S.IsString and S.AsString.IsEmpty. This resolved all permutations of the issue.

Also as to your comment about the right or wrong place. We just assumed this is a firedac/oracle 12 issue, but it is likely not firedac specific, so it seems this is the right place to put it.

Thanks,

Todd

[Stefan Glienke]

I cannot find your report about this in QP, can you please post the issue number?

[Stefan Glienke]

added workaround for FireDAC handling of large strings on Oracle (fixes #166)

→ <<cset 5e9cb70ce40f>>

[Stefan Glienke]

added workaround for FireDAC handling of large strings on Oracle (fixes #166)

→ <<cset 5e9cb70ce40f>>