Param Names too long for Oracle.

Oracle has a 30 character limit for identifiers, which include (Field Names, Aliases for Fields, and Param Names to name a few).

When the TDMLCommand.GetAndIncParamaterName method generates parameter names it uses the table name underscore (_) Field Name. If the combination of these two values creates a parameter name greater than 30 characters this causes Oracle to fail with an identifier too long error.

A suggestion here would be instead of using the full table name just use the alias for the table which is also unique in the context of the SQL statement.

We are trying the following change to the code to see how this works out. I realize this is not a perfect solution as one can create a column name of 30 characters and then no alias will work, but we always keep our column names to 25 characters or less so only adding 3 characters "T0_" or "T1_" as a prefix will resolve the issue for us.

function TDMLCommand.GetAndIncParameterName(const fieldName: string): string;
var
  index: Integer;
  upperCaseFieldName: string;
begin
  {RPRO - Makes Param name too long if fields are long and table names are long. Use Alias name instead}
//  upperCaseFieldName := AnsiUpperCase(fTable.AlNameWithoutSchema + '_' + fieldName);
  upperCaseFieldName := AnsiUpperCase(fTable.Alias + '_' + fieldName);
  if fParameterNames.TryGetValue(upperCaseFieldName, index) then
    Inc(index)
  else
    index := 1;
  fParameterNames.AddOrSetValue(upperCaseFieldName, index);
  Result := Format(':%S%D', [upperCaseFieldName, index]);
end;

Comments (4)