Oracle support fot clob and nclob. Currently FireDAC throws an error when a string is larger than 1000

[Todd Flora]

When a large string is inserted or updated into an oracle clob or nclob field FireDAC will throw an exception if the FDparams data type is not set to ftwidememo.

The FireDAC docs suggest using ftwidememo for clob/nclob param types which works fine in our current implementation. See the Oracle section under Result Set Fields chart on this page Unicode Support Firedac

Currently there is no indicater on the column attribute to differentiate a varchar2 from a clob/nclob type. Also the Spring.Persistence.SQL.Params.TDBParam class has a method to convert the TValue.PTypeInfo to a db field type but does not take into consideration the length of a string and therefore always returns ftWidestring. This is found in the method TypeInfoToFieldType(typeInfo : PTypeInfo): TFieldType;

I have created a test under the TestFireDacAdapterOracle.pas unit Attached, If you run this test against an Oracle database it will fail with the above error.

BTW: Setting TFDParam.Size to a greater value does not work as the size can be only so big, and then another exception occurs. The solution to this that has worked for us is to use ftWideMemo on these large String types.

[Todd Flora]

To get this to work:

This test should added to the ORMTests project.

A valid Oracle Database must be available

Edit the CONNECTION_STRING const at the top of the file to provide your TNSNames value in Database, also provide your user_name and password values.

[Stefan Glienke]

I pushed a proposal to the branch issue/141 - please take a look and let me know if that works.

I also looked into the Hibernate doc and found they have a Lob attribute that you can put on properties that should be handled as blob/clob then. So instead of checking the Size we can also add such attribute and check for its occurence instead of putting yet another parameter on the Column attribute.

[Todd Flora]

OK at the risk of being rejected again, Here is another suggested solution. :) BTW I knew you weren't going to like my mmAnywhereIgnoreCase solution. LOL.

Instead of passing in just the pTypeInfo to the TDBParam.TypeInfoToFieldType method the entire TValue is passed.

function TDBParam.TypeInfoToFieldType(Value: TValue): TFieldType;

  function GetMemberValueDeep(const initialValue: TValue): TValue;  <-- NEED This method but since it is no longer exposed I just copied it here for now. I leave that to you to decide.
  begin
    Result := initialValue;
    if IsNullable(Result.TypeInfo) then
    begin
      if not initialValue.TryGetNullableValue(Result) then
        Result := TValue.Empty;
    end
    else if IsLazyType(Result.TypeInfo) then
      if not initialValue.TryGetLazyValue(Result) then
        Result := TValue.Empty;
  end;
var
  typeInfo : PTypeInfo;
begin
  typeInfo := Value.TypeInfo;
  if not Assigned(typeInfo) then
    Exit(ftUnknown);

  case typeInfo.Kind of
    tkInteger, tkSet: Result := ftInteger;
    tkInt64: Result := ftLargeint;
    tkChar, tkLString, tkString: Result := ftString;
    tkWChar, tkWString, tkUString: Result := ftWideString;
    tkEnumeration:
      if typeInfo = System.TypeInfo(Boolean) then
        Result := ftBoolean
      else
        Result := ftInteger;
    tkFloat:
      if typeInfo = System.TypeInfo(TDateTime) then
        Result := ftDateTime
      else if typeInfo = System.TypeInfo(TDate) then
        Result := ftDate
      else if typeInfo = System.TypeInfo(TTime) then
        Result := ftTime
      else if typeInfo = System.TypeInfo(Currency) then
        Result := ftCurrency
      else if typeInfo = System.TypeInfo(Extended) then
        Result := TFieldType.ftExtended
      else
        Result := ftFloat;
    tkClass, tkArray, tkInterface, tkDynArray: Result := ftBlob;
    tkVariant: Result := ftVariant;
    tkRecord:
      if IsNullable(typeInfo) or IsLazyType(TypeInfo) then
        Result := TypeInfoToFieldType(GetMemberValueDeep(Value))
      else
        Result := ftBlob;
    tkClassRef, tkPointer: Result := ftReference;
  else
    Result := ftUnknown;
  end;
end;

Now that we have the TValue and can ask for it as a string, the TOracleDBParam.TypeInfoToFieldType method can check the length of the value and return ftWideMemo appropriately

function TOracleDBParam.TypeInfoToFieldType(Value : TValue): TFieldType;
begin
  Result := inherited TypeInfoToFieldType(Value);
  if assigned(Value.TypeInfo) then
    case Value.TypeInfo.Kind of
      tkClass, tkArray, tkDynArray, tkInterface:
        Result := ftOraBlob;
      tkLString:
      begin
        {Hard Oracle VarChar2 Limit of 4000 Bytes,
          For Ansi String LString, this corresponds to 4000 characters}
        if Value.ToString.Length > 4000 then
          Result := ftWideMemo
      end;
      tkWString, tkUString:
      begin
        {Hard Oracle VarChar2 Limit of 4000 Bytes,
          for Wide and Unicode Strings this corresponds to 2000 characters}
        if Value.ToString.Length > 2000 then
          Result := ftWideMemo
      end;
    end;
end;

What do you think (wince)

[Todd Flora]

Sorry I did not see your post before I posted my simplistic solution. I will take a look at this. Yes I like the idea of a LOB attribute, just as long as we can make it work for the appropriate database, as Oracle has special ftOraBlob and ftOraClob types in the DB unit.

[Todd Flora]

Wow yours is so much simpler than mine. I think your proposal will work. I will try it. Although it should not set the limit to the FireDac limit, but to the oracle limit IMO as I believe that TFDParam.Size can be increased until you get to the oracle limit.

[Todd Flora]

ALSO another BTW on this is that the LAZY Record type is not being handled in this method. I added this to my solution above.

[Todd Flora]

Sorry, didn't work at first, but I did not totally get what you were doing. I see now that you are depending on the column definition, defined by the developer to determine if the type should be a WideMemo. I also had a suggestion from one of our rocket scientist developers, that we could use WideString as the field definition and then in the OracleDBParam do the following

OracleDBParam.TypeInfoToFieldType(Value : TValue): TFieldType;
begin
  Result := inherited TypeInfoToFieldType(Value);
  if assigned(Value.TypeInfo) then
    case Value.TypeInfo.Kind of
      tkClass, tkArray, tkDynArray, tkInterface:
        Result := ftOraBlob;
      tkWString: 
        Result := ftWideMemo

This of course will work for later version of XE when String was maped to ftUString and not ftWString.

[Todd Flora]

OH BTW way this proposal works. to set the column.length to a value greater than 2000.

[Stefan Glienke]

fixed issue #141

→ <<cset 47f28a3bcb24>>