Error Fixed Length column [MyColumn] data length mismatch in ORACLE

[icarpio 0]

Hello.

I have an error when doing:

 

FDTable.Refresh;

 

The error is the following:

Quote

"Fixed Length column [COL3] data length mismatch. Value length - [34], column fixed length - [4]"

 

The above error does not always occur, and sometimes the problem is removed when I rename the table in ORACLE (RENAME TABLE ..) to any other name.

 

My table is like the following:

 

CREATE TABLE "MyDB"."TABLE_A"(
COL1 VARCHAR2(1 CHAR) DEFAULT(' ') COLLATE USING_NLS_COMP NOT NULL ENABLE,
COL2 NUMBER(10, 0) NOT NULL ENABLE,
COL3 NUMBER(10, 0) NOT NULL ENABLE
//other columns
CONSTRAINT TABLE_A1 PRIMARY KEY (COL1, COL2, COL3)
) DEFAULT COLLATION USING_NLS_COMP

 

I have the following "Map Rule":

with MapRules.Add do begin
   PrecMin:= 10;
   PrecMax:= 10;
   ScaleMin:= 0;
   ScaleMax:= 0;
   SourceDataType:= dtBCD;
   SourceDataType:= dtInt32;
end;

 

I have observed in "TFDPhysOracleCommand.CreateDefineInfo", that when I activate (TFDTable.Active:= True), the value of the precision of this column is 10, however, when it fails, the value that is taken in the precision (variable " iPrec") is equal to 38.

 

Information:

Quote

Oracle 12c R2.
RAD Studio XE7.

 

Any suggestion?

Thanks in advance.

[Jirka52 1]

You got the answer in error message. You want to insert some text with length 34 charcters into column with maximal length 4 characters.

 

[icarpio 0]

Hello,

It seems simple, however, there are important elements:

 

1. The data type is NUMBER(10, 0).
2. A Refresh is being performed, and an Append or Edit is not being done to the TFDTable.

Yesterday, I was able to isolate the problem and it replicates as follows:

FDTable.Active: =True;
FDTable.IndexName:= 'TABLE_A1';
FDTable.Refresh;
FDTable.IndexName:= 'TABLE_A2';
FDTable.Refresh;//Error occurs here

 

I found that the problem only occurs, if the field "COL3" exists in an index where it is sorted as DESCENDING:

CREATE INDEX "MyDB"."TABLE_A3"(COL1, COL2, COL3 DESC) ..;

 

The paleative solution is to remove the "TABLE_A3" index, or remove it from being DOWN.

Ideally, FireDAC should always work, however, this is not the case when there are indexes with descending fields on the table in ORACLE.