Error: Cannot convert to Number to BCD (ODAC 11.4.4)

[dados]

Hi,

I'm using ODAC 11.4.4
Tested with Delphi 10.2.3 and 10.4.2 - 32bit and 64bit - Using OCI and Direct connection
I would like to point out a recent previous bug regarding BCD here: viewtopic.php?f=5&t=44595

So I have a table on Oracle 19c with a NUMBER column
And I have a row with this value for that number column
"999000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000"

With Session default options properties I get this value:
"9,99E125"
With EnableNumbers set to true I get this value: "999000000000000000000000000000000000000000000000000000000000000"
With EnableFMTBCD set to true I get this error:
"Cannot convert to Number to BCD"

So I cannot get the whole value by any means.
Please advice how to proceed


Here is the error details from Delphi 10.2 IDE:

[12248822]{odac250.bpl } Oranumber.TOCINumber.ToBCD + $2D2
[122FC31D]{odac250.bpl } Oraclasses.TOraNumber.GetAsBCD + $41
[122F3896]{odac250.bpl } Oraclasses.TOCIRecordSet.ProcessFetchedBlock + $88E
[1228DFC7]{odac250.bpl } Oranetclasses.OOCCQ0COQ0.O0C0QOCOQ0 + $12B
[122E5B50]{odac250.bpl } Oraclasses.TOCICommand.InternalCancel + $D4
[122F481F]{odac250.bpl } Oraclasses.TOCIRecordSet.FetchArray + $92B
[122F282E]{odac250.bpl } Oraclasses.TOCIRecordSet.InternalFetch + $1A
[126DAE12]{dac250.bpl } Craccess.TCRRecordSet.Fetch + $6E
[126DCC3A]{dac250.bpl } Craccess.TCRRecordSet.GetNextRecord + $26
[1266DC96]{dac250.bpl } Memds.TMemDataSet.GetRecord + $62
[1266DC24]{dac250.bpl } Memds.TMemDataSet.GetRecord + $1C
[511D3E15]{dbrtl250.bpl} Data.DB.TDataSet.GetNextRecord (Line 14005, "Data.DB.pas" + 9) + $17
[126A555B]{dac250.bpl } Dbaccess.TCustomDADataSet.GetNextRecord + $B
[511D41BD]{dbrtl250.bpl} Data.DB.TDataSet.GetNextRecords (Line 14149, "Data.DB.pas" + 2) + $D
[511D3C1E]{dbrtl250.bpl} Data.DB.TDataSet.SetBufferCount (Line 13935, "Data.DB.pas" + 25) + $5
[511D3CDD]{dbrtl250.bpl} Data.DB.TDataSet.UpdateBufferCount (Line 13959, "Data.DB.pas" + 14) + $5
[511D1221]{dbrtl250.bpl} Data.DB.TDataSet.DoInternalOpen (Line 12684, "Data.DB.pas" + 4) + $2
[511D12C2]{dbrtl250.bpl} Data.DB.TDataSet.OpenCursor (Line 12711, "Data.DB.pas" + 3) + $0
[1266B1CA]{dac250.bpl } Memds.TMemDataSet.OpenCursor + $A
[1269EC54]{dac250.bpl } Dbaccess.TCustomDADataSet.OpenCursor + $1F8
[1232C678]{odac250.bpl } Ora.TOraDataSet.OpenCursor + $0
[511D1179]{dbrtl250.bpl} Data.DB.TDataSet.SetActive (Line 12663, "Data.DB.pas" + 12) + $7
[1269EA4A]{dac250.bpl } Dbaccess.TCustomDADataSet.SetActive + $36
[50120EB6]{rtl250.bpl } System.TypInfo.SetOrdProp (Line 2871, "System.TypInfo.pas" + 37) + $0
[211C97DA]{designide250.bpl} DesignEditors.TPropertyEditor.SetOrdValue (Line 840, "DesignEditors.pas" + 2) + $E
[21221533]{designide250.bpl} VCLEditors.TBooleanProperty.MouseUp (Line 1915, "VCLEditors.pas" + 5) + $10
[213E2E85]{vclide250.bpl} PropBox.TCustomPropListBox.ItemMouseUp (Line 1682, "PropBox.pas" + 17) + $1B
[213E307B]{vclide250.bpl} PropBox.TCustomPropListBox.MouseUp (Line 1747, "PropBox.pas" + 1) + $D
[50AE5604]{vcl250.bpl } Vcl.Controls.TControl.DoMouseUp (Line 7582, "Vcl.Controls.pas" + 2) + $28
[50AE5692]{vcl250.bpl } Vcl.Controls.TControl.WMLButtonUp (Line 7595, "Vcl.Controls.pas" + 9) + $6
[213E8F9C]{vclide250.bpl} IDEInspListBox.TInspListBox.WMLButtonUp (Line 1643, "IDEInspListBox.pas" + 3) + $4
[50AE4C5E]{vcl250.bpl } Vcl.Controls.TControl.WndProc (Line 7338, "Vcl.Controls.pas" + 91) + $6
[1FC8A199]{EhLib250.bpl} Toolctrlseh.TPopupMenuEh.Popup + $45
[50AE8FFF]{vcl250.bpl } Vcl.Controls.TWinControl.IsControlMouseMsg (Line 9964, "Vcl.Controls.pas" + 1) + $9
[50AE9843]{vcl250.bpl } Vcl.Controls.TWinControl.WndProc (Line 10209, "Vcl.Controls.pas" + 166) + $6
[50B0E179]{vcl250.bpl } Vcl.StdCtrls.TCustomListBox.WndProc (Line 7251, "Vcl.StdCtrls.pas" + 55) + $5
[50AE8E10]{vcl250.bpl } Vcl.Controls.TWinControl.MainWndProc (Line 9908, "Vcl.Controls.pas" + 3) + $6
[5016EBCC]{rtl250.bpl } System.Classes.StdWndProc (Line 17408, "System.Classes.pas" + 11) + $2
[50C31E1F]{vcl250.bpl } Vcl.Forms.TApplication.ProcessMessage (Line 10641, "Vcl.Forms.pas" + 23) + $1
[50C31E62]{vcl250.bpl } Vcl.Forms.TApplication.HandleMessage (Line 10671, "Vcl.Forms.pas" + 1) + $4
[50C32195]{vcl250.bpl } Vcl.Forms.TApplication.Run (Line 10809, "Vcl.Forms.pas" + 26) + $3
[005088F2]{bds.exe } bds.bds (Line 212, "" + 7) + $7

[dados]

The more plain question would be

How can I run this statement and get the full value returned with maximum precision/scale range?

select 999000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 from dual

or this

select 0.0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001 from dual

[dados]

Just curious to know where the limitation is. Is it in ODAC's code or Delphi's or a combination of both?
I'm able to display the Exponential Scientific value but not the full value for such a huge number.

It would be nice if ODAC supported the full value as well

Oracle's number definition:
"Number having precision p and scale s. The precision p can range from 1 to 38. The scale s can range from -84 to 127. Both precision and scale are in decimal digits. A NUMBER value requires from 1 to 22 bytes. "

https://docs.oracle.com/en/database/ora ... 4C1EA928E6

Oracle are able to do this with SQLDeveloper and sqlcl (jdbc) Why not our Delphi community?

-Arni

[MaximG]

Hi Arni!

Thank you for contacting Devart and for your inquiry!

The error you described is a consequence of the limitation of the BCD type itself. You can easily verify this with the following code snippet:

Code: Select all

   uses Data.FmtBcd;
   ...
   var
     MyValue: TBCD;
   begin
     MyValue := StrToBcd('999000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000');
   end;

To work with the values you specified, we recommend to use the DOUBLE type or string data types.

Should you have any questions, do not hesitate to ask!