Rounding Decimal
In databases, the "Decimal" or "Numeric" ("Number" in Oracle) data type stores numeric data, but with a precision defined by the declaration of the scale (decimal places) and the precision (which determines the integer places = precision - scale).
The normal behavior requires that the scale be 0 or a positive number less than the precision. However, Oracle allows much more flexibility in defining this data type. In Numeric Data Types, the different numeric data types for Oracle are described.
Unlike integer rounding, decimal rounding is based on the Scale (number of decimal positions) defined in the target table. If you attempt to insert a value with 4 decimals into a DECIMAL(10,2) column, most drivers will throw an overflow error. Excel To Database automatically detects the allowed scale and adjusts the value.
"Currency" type fields in MS Access have a precision of 19 digits and a scale of up to 4 decimal places. In SQL Server, the "SmallMoney" type field has a precision of 10 digits and a scale of 4 decimal places, while "Money" has a precision of 19 digits and a scale of 4 decimal places. In both databases, the behavior of these data types may differ from the "Decimal" data type depending on the selected drivers.
Oracle does not have this type of data.
Some Data Providers, Drivers, and/or Databases are capable of performing the proper rounding for this type of data. Others, on the other hand, truncate the data, and others directly cause an error. Excel To Database will display a warning message indicating this fact.
To ensure predictable results, Excel To Database implements the MidpointRounding.AwayFromZero standard. See MidpointRounding Enumeration.
This ensures that a value like 2.5 is always converted to 3, regardless of whether the database driver uses banker's rounding or truncation.
This standard ensures that financial calculations remain consistent, avoiding the 'banker's rounding' that sometimes biases totals downward.
As usual, it should be emphasized that if a data type conversion error occurs, Excel To Database will attempt to load the string representing the data contained in the Excel Sheet, making it very likely that an error will occur during data loading. Checking the "No Conversion Errors" option will replace this string with NULL or the default value indicated in the Mappings Panel.
MS Access: Rounding Decimal
To demonstrate the behavior of this option with MS Access, the Database from the previous section containing the following table and the Excel Workbook from the previous section will be used. The Excel Sheet shown in the following figure will be used:
Option Compare Database
Option Explicit
Public Sub CreaTablaRedondeoDecimal()
On Error Resume Next
CurrentProject.Connection.Execute "DROP TABLE [Tabla Redondeo Decimal]"
On Error GoTo Sub_Err
CurrentProject.Connection.Execute "CREATE TABLE [Tabla Redondeo Decimal](" & _
"[Id] COUNTER NOT NULL, " & _
"[Proveedor Datos] TEXT (255) NOT NULL, " & _
"[Driver] TEXT (255) NOT NULL, " & _
"[Decimal_4_2] DECIMAL (4, 2), " & _
"[Moneda] CURRENCY, " & _
"[Decimal_19_4] DECIMAL (19, 4)," & _
"[Decimal_28_0] DECIMAL (28, 0)," & _
"[Decimal_0_28] DECIMAL (28, 28)" & _
")"
Dim db As Object, tbl As Object, fld As Object
Set db = CurrentDb
db.TableDefs.Refresh
Set tbl = db.TableDefs("Tabla Redondeo Decimal")
tbl.Fields.Refresh
Set fld = tbl.Fields("Proveedor Datos")
fld.AllowZeroLength = False
On Error Resume Next
fld.Properties.Append fld.CreateProperty("UnicodeCompression", 1, True)
On Error GoTo Sub_Err
Set fld = tbl.Fields("Driver")
fld.AllowZeroLength = False
fld.Properties.Append fld.CreateProperty("UnicodeCompression", 1, True)
Set fld = tbl.Fields("Decimal_4_2")
fld.Properties.Append fld.CreateProperty("Description", 10, "NULL. 4 posiciones, 2 dígitos decimales. Pruebas con separador decimal. Pruebas de redondeo. 12 Bytes")
Set fld = tbl.Fields("Moneda")
fld.Properties.Append fld.CreateProperty("Description", 10, "NULL. 19 posiciones, 4 dígitos decimales. Pruebas con separador decimal. Pruebas de redondeo. 8 Bytes")
fld.Properties.Append fld.CreateProperty("DecimalPlaces", 2, 5)
Set fld = tbl.Fields("Decimal_19_4")
fld.Properties.Append fld.CreateProperty("Description", 10, "NULL. 19 posiciones, 4 dígitos decimales. Pruebas con separador decimal. Pruebas de redondeo. 12 Bytes")
Set fld = tbl.Fields("Decimal_28_0")
fld.Properties.Append fld.CreateProperty("Description", 10, "NULL. 28 posiciones, 0 dígitos decimales. Pruebas con separador decimal. Pruebas de redondeo. 12 Bytes")
Set fld = tbl.Fields("Decimal_0_28")
fld.Properties.Append fld.CreateProperty("Description", 10, "NULL. 28 posiciones, 28 dígitos decimales. Pruebas con separador decimal. Pruebas de redondeo. 12 Bytes")
CurrentProject.Connection.Execute "CREATE UNIQUE INDEX PrimaryKey ON [Tabla Redondeo Decimal] (Id ASC) WITH PRIMARY"
Sub_Exit:
Set fld = Nothing
Set tbl = Nothing
If Not db Is Nothing Then db.Close
Set db = Nothing
Exit Sub
Sub_Err:
MsgBox "Se ha producido un error en 'CreaTablaRedondeoDecimal'." & vbNewLine & vbNewLine & vbTab & Err.Description, , "Error Nº: " & Err.Number
Resume Sub_Exit
Resume
End Sub
MS Access: Hoja Excel for Rounding Decimal
First, highlight the serious issues with the MS Access OLE DB Driver and the Currency and Decimal data types detailed in the chapter dedicated to MS Access: "The Strange Case of OLE DB with Decimal and Currency"
Thus, to load the sheet with this data provider, it is NECESSARY that the regional settings in Windows have a dot (.) as the decimal separator. Otherwise, the load will not work.
Note that the OLE DB data provider for MS Access has a completely chaotic behavior, so it is recommended to check the Excel To Database options "No Conversion Errors" and "Decimal Rounding", as this way control is handed over to Excel To Database, making it the only option to achieve the expected behavior.
However, the behavior of the ODBC provider is as expected for each combination of options.
Below is the behavior of both providers with the different combinations of options. Note that the ODBC provider truncates the data when it exceeds the scale of the field.
- OLE DB. Bugs in Microsoft.ACE.OLEDB. Only one combination of options achieves the expected behavior:
- "No Conversion Errors" NOT checked and "Decimal Rounding" NOT checked: Data loading behaves in a "very strange" way.
Decimal Fields: The Data Provider truncates the decimal part to the scale indicated by the field. (Bug Microsoft.ACE.OLEDB) However, it has problems when the data is at the limit or exceeds the capacity of the field.
Currency Fields: The Data Provider (Bug Microsoft.ACE.OLEDB) rounds with the MidpointRounding.ToEven or MidpointRounding.AwayFromZero option without any logical criteria.Range "A2:A4" the Microsoft.ACE.OLEDB driver truncates the decimal part to the scale indicated by the field.
Cell "A5:A7": Bug Microsoft.ACE.OLEDB truncates the entire integer part.
Cell "A12" contains 123.12 is stored as 0.12. Bug Microsoft.ACE.OLEDB truncates the entire integer part. The driver should produce an error when inserting this value.Cells "B3" and "B4" contain 0.12345 and 0.12346 respectively. They are stored as 0.1234 and 0.1235 respectively. The Bug Microsoft.ACE.OLEDB driver rounds with the MidpointRounding.ToEven option.
Cells "B6" and "B7" contain 9999.99995 and 9999.99996 respectively. They are stored as 10000 respectively. The Bug Microsoft.ACE.OLEDB driver rounds with the MidpointRounding.AwayFromZero option.
Cell "B12" exceeds the field's capacity, resulting in an error when inserting the value.Range "C2:C7" the Microsoft.ACE.OLEDB driver truncates the decimal part to the scale indicated by the field.
Cell "C12" contains 999999999999999.99995 and is stored as 99999999999999.9999. Bug Microsoft.ACE.OLEDB truncates the first digit of the integer part.Cell "D3" contains 99999999999999999999999999 and is stored correctly, bu MS Access displays an incorrect value.
Cell "D4" contains 999999999999999999999999999.6 and is stored as 999999999999999999999999999. The driver truncates the decimal part and MS Access displays an incorrect value, although it stores the correct data.
Cells "D5" and "D6" contain 7919999999999999999999999999.5 and 7919999999999999999999999999.6 respectively and are stored as 919999999999999999999999999 but display as 920000000000000000000000000. Bug Microsoft.ACE.OLEDB truncates the first digit of the integer part.
Cell "D12" contains 9999999999999999999999999999.6 which exceeds the precision of the .NET Decimal type (28 significant digits). When reading the data, .NET rounds it to 10000000000000000000000000000, which exceeds the field's capacity. Bug Microsoft.ACE.OLEDB truncates the first digit of the integer part and stores 0. The driver should produce an error when inserting this value.Cell "E4" contains 1E-29 which exceeds the precision of the .NET Decimal type (7.92...E-28). When reading the data, .NET rounds it to 0, which is what is stored in the field.
Cell "E12" contains 0.99999999999999999999999999996 which exceeds the precision of the .NET Decimal type (28 significant digits). When reading the data, .NET rounds it to 1.0000000000000000000000000000 which exceeds the field's capacity. Bug Microsoft.ACE.OLEDB truncates the first digit of the integer part. The driver should produce an error when inserting this value. - "No Conversion Errors" NOT checked and "Decimal Rounding" checked: Data loading behaves in a "very strange" way.
Rounding is performed by Excel To Database with the MidpointRounding.AwayFromZero option. See MidpointRounding Enumeration.
Decimal Fields: The Data Provider (Bug Microsoft.ACE.OLEDB) has problems when the data is at the limit or exceeds the field's capacity.
Currency Fields: The Data Provider behaves as expected.Cells "A6" and "A7" contain 99.995 and 99.996 respectively. Excel To Database rounds (MidpointRounding.AwayFromZero) the data to 100. Bug Microsoft.ACE.OLEDB truncates the first digit of the integer part. The driver should produce an error when inserting this rounded value.
Cell "A12" contains 123.12 is stored as 0.12. Bug Microsoft.ACE.OLEDB truncates the entire integer part. The driver should produce an error when inserting this value.Cell "B12" exceeds the field's capacity, resulting in an error when inserting the value.
Cell "C12" contains 999999999999999.99995. Excel To Database rounds (MidpointRounding.AwayFromZero) the data to 1000000000000000.0000. Bug Microsoft.ACE.OLEDB truncates the first digit of the integer part. The driver should produce an error when inserting this rounded value.
Cell "D3" contains 99999999999999999999999999 and is stored correctly, but MS Access displays an incorrect value.
Cell "D12" contains 9999999999999999999999999999.6 which exceeds the precision of the .NET Decimal type (28 significant digits). When reading the data, .NET rounds it to 10000000000000000000000000000, which exceeds the field's capacity. Bug Microsoft.ACE.OLEDB truncates the first digit of the integer part. The driver should produce an error when inserting this value.Cell "E4" contains 1E-29 which exceeds the capacity of the .NET Decimal type (7.92...E-28). When reading the data, .NET rounds it to 0, which is what is stored in the field.
Cell "E12" contains 0.99999999999999999999999999996 which exceeds the precision of the .NET Decimal type (28 significant digits). When reading the data, .NET rounds it to 1.0000000000000000000000000000 which exceeds the field's capacity. Bug Microsoft.ACE.OLEDB truncates the first digit of the integer part. The driver should produce an error when inserting this value. - "No Conversion Errors" checked and "Decimal Rounding" NOT checked: Data loading behaves in a "very strange" way.
Decimal Fields: The Data Provider (Bug Microsoft.ACE.OLEDB) trims or rounds (MidpointRounding.AwayFromZero) the data. It also has problems when the data is at the limit or exceeds the field's capacity.
Currency Fields: The Data Provider rounds using the MidpointRounding.ToEven option.The behavior is the same as the "No Conversion Errors" option NOT checked and "Decimal Rounding" NOT checked, except for the cells described below:
Cell "A12" contains 123.12. The number of digits in the integer part '3' exceeds the allowed '2'. Excel To Database inserts the default value defined in the Mapping Panel, in this case, a NULL.
Cell "B12" exceeds the field's capacity. Excel To Database inserts the default value defined in the Mapping Panel, in this case, a NULL.
Cell "D12" contains 9999999999999999999999999999.6, which exceeds the precision of the .NET Decimal type (28 significant digits). When reading the data, .NET rounds it to 10000000000000000000000000000, which exceeds the field's capacity. Excel To Database inserts the default value defined in the Mapping Panel, in this case, a NULL.
Cell "E12" contains 0.99999999999999999999999999996, which exceeds the precision of the .NET Decimal type (28 significant digits). When reading the data, .NET rounds it to 1.0000000000000000000000000000, which exceeds the field's capacity. Excel To Database inserts the default value defined in the Mapping Panel, in this case, a NULL.
- "No Conversion Errors" checked and "Decimal Rounding" checked: Data loading behaves as expected.
The rounding is done by Excel To Database with the option MidpointRounding.AwayFromZero. See MidpointRounding Enumeration.
Decimal Fields: The Data Provider behaves as expected.
Currency Fields: The Data Provider behaves as expected.Cells "A6" and "A7" contain 99.995 and 99.996, respectively. Excel To Database rounds (MidpointRounding.AwayFromZero) the data to 100. The number of digits in the integer part '3' exceeds the allowed '2'. Excel To Database inserts the default value defined in the Mapping Panel, in this case, a NULL.
Cell "A12" contains 123.12. The number of digits in the integer part '3' exceeds the allowed '2'. Excel To Database inserts the default value defined in the Mapping Panel, in this case, a NULL.Cell "B12" exceeds the field's capacity. Excel To Database inserts the default value defined in the Mapping Panel, in this case, a NULL.
Cell "D12" contains 9999999999999999999999999999.6, which exceeds the precision of the .NET Decimal type (28 significant digits). When reading the data, .NET rounds it to 10000000000000000000000000000, which exceeds the field's capacity. Excel To Database inserts the default value defined in the Mapping Panel, in this case, a NULL.
Cell "D3" contains 99999999999999999999999999 and is stored correctly, but MS Access displays incorrect data.
Cell "D12" contains 9999999999999999999999999999.6, which exceeds the precision of the .NET Decimal type (28 significant digits). When reading the data, .NET rounds it to 10000000000000000000000000000, which exceeds the field's capacity. Excel To Database inserts the default value defined in the Mapping Panel, in this case, a NULL.Cell "E4" contains 1E-29, which exceeds the precision of the .NET Decimal type (7.92...E-28). When reading the data, .NET rounds it to 0, which is what gets stored in the field.
Cell "E12" contains 0.99999999999999999999999999996, which exceeds the precision of the .NET Decimal type (28 significant digits). When reading the data, .NET rounds it to 1.0000000000000000000000000000, which exceeds the field's capacity. Excel To Database inserts the default value defined in the Mapping Panel, in this case, a NULL.
- "No Conversion Errors" NOT checked and "Decimal Rounding" NOT checked: Data loading behaves in a "very strange" way.
- ODBC. The behavior is as expected in all combinations:
- "No Conversion Errors" option NOT checked and "Decimal Rounding" NOT checked: The data load behaves as expected.
Decimal and Currency Fields: The ODBC "Microsoft Access Driver (*.mdb, *.accdb)" trims the data to the scale specified by the field. The data is stored correctly. And when the field's capacity is exceeded, the driver raises the appropriate error.
Range "D2:D6" the driver trims the data correctly, it is stored correctly, but MS Access shows a rounded value.
- "No Conversion Errors" option NOT checked and "Decimal Rounding" checked: The data load behaves as expected.
Decimal and Currency Fields: Excel To Database rounds the data using the MidpointRounding.AwayFromZero option. See MidpointRounding Enumeration. The data is stored correctly. And when the field's capacity is exceeded, the driver raises the appropriate error.
- "No Conversion Errors" option checked and "Decimal Rounding" NOT checked: The data load behaves as expected.
Decimal and Currency Fields: The ODBC "Microsoft Access Driver (*.mdb, *.accdb)" trims the data to the scale specified by the field. The data is stored correctly. And when the field's capacity is exceeded, Excel To Database stores the default value, in this case, NULL.
- "No Conversion Errors" option checked and "Decimal Rounding" checked: The data load behaves as expected.
Decimal and Currency Fields: Excel To Database rounds the data using the MidpointRounding.AwayFromZero option. See MidpointRounding Enumeration. The data is stored correctly. And when the field's capacity is exceeded, Excel To Database stores the default value, in this case, NULL.
- "No Conversion Errors" option NOT checked and "Decimal Rounding" NOT checked: The data load behaves as expected.
SQL Server: Rounding Decimal
Unlike MS Access, the behavior of the different Data Providers is predictable and reliable. The tests were conducted using the following table for SQL Server and the Data Sheet shown in the figure below:
DROP TABLE IF EXISTS [dbo].[Tabla Redondeo Decimal];
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Tabla Redondeo Decimal](
[SmallMoney] [smallmoney] NULL,
[Decimal_10_4] [decimal](10,4) NULL,
[Money] [money] NULL,
[Decimal_4_2] [decimal](4,2) NULL,
[Decimal_38_0] [numeric](38,0) NULL,
[Provider] [varchar](10) COLLATE Modern_Spanish_100_CS_AS NOT NULL,
[Driver] [varchar](50) COLLATE Modern_Spanish_100_CS_AS NOT NULL,
[Identidad] [int] IDENTITY(1,1) NOT NULL,
PRIMARY KEY CLUSTERED
(
[Identidad] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SQL Server: Excel Sheet for Rounding Decimal
- .NET (System.Data.SqlClient):
With "decimal" or "numeric" fields, this data provider trims the decimal part of the data to the number of digits specified in the scale.
With "smallmoney" and "money" fields, it rounds the data using the MidpointRounding.AwayFromZero option. See MidpointRounding Enumeration.The "Decimal Rounding" option will appear for "decimal", "numeric", "smallmoney", and "money" fields. This option will allow you to check if rounding a value exceeds the field's capacity. When combined with the option "No Conversion Errors", it will allow the load to continue by inserting a NULL or the default value indicated in the Mapping Panel.
The driver throws an exception when the data exceeds the field's capacity or cannot be converted to the .NET Decimal type, and no row is inserted.Using the "Decimal Rounding" option of Excel To Database, all fields are rounded using the MidpointRounding.AwayFromZero option.
- OLE DB (MSOLEDBSQL19, MSOLEDBSQL, SQLNCLI11, SQLOLEDB):
With "decimal" or "numeric" fields, this data provider trims the decimal part of the data to the number of digits specified in the scale.
With "smallmoney" and "money" fields, it rounds the data, although sometimes it uses the MidpointRounding.ToEven option and other times MidpointRounding.AwayFromZero. See MidpointRounding Enumeration.A "strange" behavior has been observed for all OLE DB drivers with cells "C6" and "C7". While the rest of the "smallmoney" or "money" data was rounded using MidpointRounding.ToEven, these two cells were rounded using the MidpointRounding.AwayFromZero option. In other words, there is a OLE DB Bug (MSOLEDBSQL19, MSOLEDBSQL, SQLNCLI11, SQLOLEDB)
The "Decimal Rounding" option will appear for "decimal", "numeric", "smallmoney", and "money" fields. This option will allow you to check if rounding a value exceeds the field's capacity. When combined with the option "No Conversion Errors", it will allow the load to continue by inserting a NULL or the default value indicated in the Mapping Panel.
The driver throws an exception when the data exceeds the field's capacity or cannot be converted to the .NET Decimal type. The number of rows inserted depends on the "With Transaction" option.Using the "Decimal Rounding" option of Excel To Database, all fields are rounded using the MidpointRounding.AwayFromZero option.
- ODBC ({ODBC Driver 18 for SQL Server}, {ODBC Driver 17 for SQL Server}, {SQL Server}, {SQL Server Native Client 11.0}) :
With "decimal", "numeric", "smallmoney", and "money" fields, it rounds the data using the MidpointRounding.AwayFromZero option. See MidpointRounding Enumeration.
The "Decimal Rounding" option will appear for "decimal", "numeric", "smallmoney", and "money" fields. This option will allow you to check if rounding a value exceeds the field's capacity, and if combined with the option "No Conversion Errors", it will allow the load to continue by inserting a NULL or the default value indicated in the Mapping Panel.
The driver throws an exception when the data exceeds the field's capacity or cannot be converted to the .NET Decimal type. The number of rows inserted depends on the "With Transaction" option.Using the "Decimal Rounding" option of Excel To Database, all fields are rounded using the MidpointRounding.AwayFromZero option.
Oracle: Rounding Decimal
The tests were conducted using the following table for Oracle and the Data Sheet shown in the figure below:
CREATE TABLE "Tabla Redondeo Decimal" (
Id NUMBER(6) GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
Provider VARCHAR2(10 CHAR),
Driver VARCHAR2(50 CHAR),
"Int 2" NUMBER(2),
"Redondeo 100" NUMBER(5, -2),
"Redondeo -5 (3, 5)" NUMBER(3, 5), --Mas raro que un perro verde: https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/Data-Types.html#GUID-A3C0D836-BADB-44E5-A5D4-265BA5968483
"Number (4 2)" NUMBER(4, 2),
"Number" NUMBER
);
Oracle: Hoja Excel for Rounding Decimal
It should be emphasized again that only the Managed Driver detects integer data types, so for this data provider, this option does not appear in the fields "Int 2" and "Rounding 100", which were studied in the section "Rounding Integer".
Furthermore, as previously mentioned, the Managed Driver with the Bulk Copy feature (option "Use Arrays" not selected) has a similar issue to the one described in the "Truncate Strings" section. The driver allows the insertion of ANYTHING without requiring it to meet any of the constraints defined in the table fields.
Thus, for example, with the Managed Driver and the Bulk Copy feature, with the "Rounding Decimal" option not selected and without performing the checks that Excel To Database does, the following load can occur:
Oracle: Load Error Rounding Decimal
As can be seen, there are records for the field "Rounding -5 (3, 5)" that do not meet the restriction of having two zeros immediately after the decimal separator, and for the field "Number 4 2" there are data where the integer part consists of three digits, despite being prohibited. This can cause the table to remain in an inconsistent state and, for example, the instruction 'SELECT * FROM "Decimal Rounding Table" ORDER BY ID;' might not work in "Oracle SQL Developer" as happened in the previous section.
Excel To Database contains a series of checks to prevent such issues from occurring. Thus, in the "Results Panel", messages like the following are displayed:
...
Cell: C7, Value: 9.9950000000000004E-3
The value contained in the cell has been converted to the .NET type Single: 0.009995
Cell: C7, Value: 0.009995, Table: Tabla Redondeo Decimal, Column: Redondeo -5 (3, 5), Precision: 3, Scale: 5
The number of decimal digits '6' exceeds the scale.
Oracle's Managed Driver with NO Arrays contains a bug that allows data to be inserted that does not comply with the column restrictions.
Check the "Decimal Rounding" option.
The Data Load Phase will not be executed.
Cell: D7, Value: 99.995, Table: Tabla Redondeo Decimal, Column: Number (4 2), Precision: 4, Scale: 2
The number of decimal digits '3' exceeds the scale.
Oracle's Managed Driver with NO Arrays contains a bug that allows data to be inserted that does not comply with the column restrictions.
Check the "Decimal Rounding" option.
The Data Load Phase will not be executed.
...
By selecting the "Decimal Rounding" option, Excel To Database takes care of rounding the data, while also protecting against unwanted insertions due to the Bugs Managed Driver with the Bulk Copy option. It will be necessary to check the "No Conversion Errors" option to replace problematic data with NULL or the value specified in the "Mapping Panel".
...
Cell: C7, Value: 9.9950000000000004E-3
The value contained in the cell has been converted to the .NET type Single: 0.009995
Cell: C7, Value: 0.009995, Table: Tabla Redondeo Decimal, Column: Redondeo -5 (3, 5), Precision: 3, Scale: 5
Number of decimal digits '6' exceeds Scale.
"Decimal Rounding" option checked. Rounded value: 0.01
Cell: C7, Value: 0.01, Table: Tabla Redondeo Decimal, Column: Redondeo -5 (3, 5), Precision: 3, Scale: 5. Scale greater than Precision.
Scale - Precision indicates the minimum number of significant zeros after the decimal separator.
Scale - Precision 2 > 1 Number of significant zeros.
Oracle's Managed Driver with NO Arrays contains a bug that allows data to be inserted that does not comply with the column restrictions.
Check the "No Conversion Errors" option to load the default value.
The Data Load Phase will not be executed.
Cell: D7, Value: 99.995, Table: Tabla Redondeo Decimal, Column: Number (4 2), Precision: 4, Scale: 2
Number of decimal digits '3' exceeds Scale.
"Decimal Rounding" option checked. Rounded value: 100
Cell: D7, Value: 100, Table: Tabla Redondeo Decimal, Column: Number (4 2), Precision: 4, Scale: 2
The number of digits of the integer part '3' exceeds the allowed '2' (Precision - Scale)
Oracle's Managed Driver with NO Arrays contains a bug that allows data to be inserted that does not comply with the column restrictions.
Check the "No Conversion Errors" option to load the default value.
The Data Load Phase will not be executed.
...
With the Managed Driver and the Array Binding feature (the "Use Arrays" option selected) and the "Rounding Decimal" option not selected, the behavior is different. Although Excel To Database shows the relevant warning messages, the data loading phase begins, and this is where errors occur. The messages that appear in the .log files will be similar to the following:
...
Cell: C7, Value: 9.9950000000000004E-3
The value contained in the cell has been converted to the .NET type Single: 0.00999500044
Cell: C7, Value: 0.009995, Table: Tabla Redondeo Decimal, Column: Redondeo -5 (3, 5), Precision: 3, Scale: 5
Number of decimal digits '6' exceeds Scale.
The Data Provider or Database may round or truncate the data.
Cell: D7, Value: 99.9950
The value contained in the cell has been converted to the .NET type Single: 99.9950027
Cell: D7, Value: 99.995, Table: Tabla Redondeo Decimal, Column: Number (4 2), Precision: 4, Scale: 2
Number of decimal digits '3' exceeds Scale.
The Data Provider or Database may round or truncate the data.
...
An exception occurred attempting to load Table 'Tabla Redondeo Decimal'.
Insert command: insert into "Tabla Redondeo Decimal" ("Number", "Number (4 2)", "Redondeo -5 (3, 5)", "Redondeo 100", "Int 2", "DRIVER", "PROVIDER") values(:0, :1, :2, :3, :4, :5, :6)
{
"$id": "1",
"$type": "Oracle.ManagedDataAccess.Client.OracleException, Oracle.ManagedDataAccess",
"OracleLogicalTransaction": {
"$type": "Oracle.ManagedDataAccess.Client.OracleLogicalTransaction, Oracle.ManagedDataAccess",
"Committed": null,
"UserCallCompleted": null,
"ConnectionString": null,
"LogicalTransactionId": null
},
"Errors": {
"$type": "Oracle.ManagedDataAccess.Client.OracleErrorCollection, Oracle.ManagedDataAccess",
"$values": [
{
"$id": "2",
"$type": "Oracle.ManagedDataAccess.Client.OracleError, Oracle.ManagedDataAccess",
"DataSource": "",
"Message": "ORA-24381: error(s) in array DML
https://docs.oracle.com/error-help/db/ora-24381/",
"Number": 24381,
"ParseErrorOffset": 157,
"Procedure": "",
"Source": "Oracle Data Provider for .NET, Managed Driver",
"ArrayBindIndex": -1
},
{
"$id": "3",
"$type": "Oracle.ManagedDataAccess.Client.OracleError, Oracle.ManagedDataAccess",
"DataSource": "",
"Message": "ORA-01438: value larger than specified precision allowed for this column
https://docs.oracle.com/error-help/db/ora-01438/",
"Number": 1438,
"ParseErrorOffset": -1,
"Procedure": "",
"Source": "Oracle Data Provider for .NET, Managed Driver",
"ArrayBindIndex": 3
},
{
"$id": "4",
"$type": "Oracle.ManagedDataAccess.Client.OracleError, Oracle.ManagedDataAccess",
"DataSource": "",
"Message": "ORA-01438: value larger than specified precision allowed for this column
https://docs.oracle.com/error-help/db/ora-01438/",
"Number": 1438,
"ParseErrorOffset": -1,
"Procedure": "",
"Source": "Oracle Data Provider for .NET, Managed Driver",
"ArrayBindIndex": 5
},
{
"$id": "5",
"$type": "Oracle.ManagedDataAccess.Client.OracleError, Oracle.ManagedDataAccess",
"DataSource": "",
"Message": "ORA-01438: value larger than specified precision allowed for this column
https://docs.oracle.com/error-help/db/ora-01438/",
"Number": 1438,
"ParseErrorOffset": -1,
"Procedure": "",
"Source": "Oracle Data Provider for .NET, Managed Driver",
"ArrayBindIndex": 6
}
]
},
"DataSource": "",
"Message": "ORA-24381: error(s) in array DML
https://docs.oracle.com/error-help/db/ora-24381/\nORA-01438: value larger than specified precision allowed for this column
https://docs.oracle.com/error-help/db/ora-01438/\nORA-01438: value larger than specified precision allowed for this column
https://docs.oracle.com/error-help/db/ora-01438/\nORA-01438: value larger than specified precision allowed for this column
https://docs.oracle.com/error-help/db/ora-01438/",
"Procedure": "",
"Source": "Oracle Data Provider for .NET, Managed Driver",
"Number": 24381,
"IsRecoverable": false,
"ErrorCode": -2147467259,
"Data": {
"$type": "System.Collections.ListDictionaryInternal, mscorlib"
},
"InnerException": null,
"HelpLink": null,
"HResult": -2147467259
}
Excel Row error: '5' Oracle error: '1438'
Message: ORA-01438: value larger than specified precision allowed for this column
https://docs.oracle.com/error-help/db/ora-01438/
Values:
Number: 0.0000000000000000000000000001
Number (4 2): 0.126
Redondeo -5 (3, 5): 0.003456
Redondeo 100: 12345678
Int 2: 3
DRIVER: Managed Driver (Arrays)
PROVIDER: .NET
Excel Row error: '7' Oracle error: '1438'
Message: ORA-01438: value larger than specified precision allowed for this column
https://docs.oracle.com/error-help/db/ora-01438/
Values:
Number:
Number (4 2): 99.995
Redondeo -5 (3, 5): 0.009995
Redondeo 100: 0
Int 2: 100
DRIVER: Managed Driver (Arrays)
PROVIDER: .NET
Excel Row error: '8' Oracle error: '1438'
Message: ORA-01438: value larger than specified precision allowed for this column
https://docs.oracle.com/error-help/db/ora-01438/
Values:
Number:
Number (4 2): 99.996
Redondeo -5 (3, 5): 0.009996
Redondeo 100: 0
Int 2: 100
DRIVER: Managed Driver (Arrays)
PROVIDER: .NET
By selecting the "Decimal Rounding" option, Excel To Database rounds the data using the MidpointRounding.AwayFromZero option. See MidpointRounding Enumeration.
However, errors similar to the above may occur with rounded data that exceeds the field's capacity. It will need to check the "No Conversion Errors" option to replace the problematic data with NULL or the value specified in the "Mappings Panel".
...
Cell: A5, Value: 2.6, Table: Tabla Redondeo Decimal, Column: Int 2
"Integer Rounding" option checked. Rounded Value: 3
Cell: B5, Value: 12345678, Table: Tabla Redondeo Decimal, Column: Redondeo 100, Precision: 5, Scale: -2
The number of digits of the integer part '8' exceeds the allowed '7'
Cell: C5, Value: 3.4559999999999999E-3
The value contained in the cell has been converted to the .NET type Single: 0.00345600001
Cell: C5, Value: 0.003456, Table: Tabla Redondeo Decimal, Column: Redondeo -5 (3, 5), Precision: 3, Scale: 5
Number of decimal digits '6' exceeds Scale.
"Decimal Rounding" option checked. Rounded value: 0.00346
Cell: D5, Value: 0.1260
The value contained in the cell has been converted to the .NET type Single: 0.126000002
Cell: D5, Value: 0.126, Table: Tabla Redondeo Decimal, Column: Number (4 2), Precision: 4, Scale: 2
Number of decimal digits '3' exceeds Scale.
"Decimal Rounding" option checked. Rounded value: 0.13
...
Cell: A7, Value: 99.5, Table: Tabla Redondeo Decimal, Column: Int 2
"Integer Rounding" option checked. Rounded Value: 100
Cell: A7, Value: 100, Table: Tabla Redondeo Decimal, Column: Int 2, Precision: 2
The number of digits '3' exceeds the allowed '2'
Cell: B7, Value: 49.5, Table: Tabla Redondeo Decimal, Column: Redondeo 100, Precision: 5, Scale: -2
Number of significant zeros to the left of the decimal point 0 is lower Abs(Scale) 2
"Rounding Integer" option checked. Rounded value: 0
Cell: C7, Value: 9.9950000000000004E-3
The value contained in the cell has been converted to the .NET type Single: 0.00999500044
Cell: C7, Value: 0.009995, Table: Tabla Redondeo Decimal, Column: Redondeo -5 (3, 5), Precision: 3, Scale: 5
Number of decimal digits '6' exceeds Scale.
"Decimal Rounding" option checked. Rounded value: 0.01
Cell: C7, Value: 0.01, Table: Tabla Redondeo Decimal, Column: Redondeo -5 (3, 5), Precision: 3, Scale: 5. Scale greater than Precision.
Scale - Precision indicates the minimum number of significant zeros after the decimal separator.
Scale - Precision 2 > 1 Number of significant zeros.
Cell: D7, Value: 99.9950
The value contained in the cell has been converted to the .NET type Single: 99.9950027
Cell: D7, Value: 99.995, Table: Tabla Redondeo Decimal, Column: Number (4 2), Precision: 4, Scale: 2
Number of decimal digits '3' exceeds Scale.
"Decimal Rounding" option checked. Rounded value: 100
Cell: D7, Value: 100, Table: Tabla Redondeo Decimal, Column: Number (4 2), Precision: 4, Scale: 2
The number of digits of the integer part '3' exceeds the allowed '2' (Precision - Scale)
Cell: A8, Value: 99.6, Table: Tabla Redondeo Decimal, Column: Int 2
"Integer Rounding" option checked. Rounded Value: 100
Cell: A8, Value: 100, Table: Tabla Redondeo Decimal, Column: Int 2, Precision: 2
The number of digits '3' exceeds the allowed '2'
Cell: B8, Value: 49.6, Table: Tabla Redondeo Decimal, Column: Redondeo 100, Precision: 5, Scale: -2
Number of significant zeros to the left of the decimal point 0 is lower Abs(Scale) 2
"Rounding Integer" option checked. Rounded value: 0
Cell: C8, Value: 9.9959999999999997E-3
The value contained in the cell has been converted to the .NET type Single: 0.00999599975
Cell: C8, Value: 0.009996, Table: Tabla Redondeo Decimal, Column: Redondeo -5 (3, 5), Precision: 3, Scale: 5
Number of decimal digits '6' exceeds Scale.
"Decimal Rounding" option checked. Rounded value: 0.01
Cell: C8, Value: 0.01, Table: Tabla Redondeo Decimal, Column: Redondeo -5 (3, 5), Precision: 3, Scale: 5. Scale greater than Precision.
Scale - Precision indicates the minimum number of significant zeros after the decimal separator.
Scale - Precision 2 > 1 Number of significant zeros.
Cell: D8, Value: 99.9960
The value contained in the cell has been converted to the .NET type Single: 99.9960022
Cell: D8, Value: 99.996, Table: Tabla Redondeo Decimal, Column: Number (4 2), Precision: 4, Scale: 2
Number of decimal digits '3' exceeds Scale.
"Decimal Rounding" option checked. Rounded value: 100
Cell: D8, Value: 100, Table: Tabla Redondeo Decimal, Column: Number (4 2), Precision: 4, Scale: 2
The number of digits of the integer part '3' exceeds the allowed '2' (Precision - Scale)
An exception occurred attempting to load Table 'Tabla Redondeo Decimal'.
Insert command: insert into "Tabla Redondeo Decimal" ("Number", "Number (4 2)", "Redondeo -5 (3, 5)", "Redondeo 100", "Int 2", "DRIVER", "PROVIDER") values(:0, :1, :2, :3, :4, :5, :6)
{
"$id": "1",
"$type": "Oracle.ManagedDataAccess.Client.OracleException, Oracle.ManagedDataAccess",
"OracleLogicalTransaction": {
"$type": "Oracle.ManagedDataAccess.Client.OracleLogicalTransaction, Oracle.ManagedDataAccess",
"Committed": null,
"UserCallCompleted": null,
"ConnectionString": null,
"LogicalTransactionId": null
},
"Errors": {
"$type": "Oracle.ManagedDataAccess.Client.OracleErrorCollection, Oracle.ManagedDataAccess",
"$values": [
{
"$id": "2",
"$type": "Oracle.ManagedDataAccess.Client.OracleError, Oracle.ManagedDataAccess",
"DataSource": "",
"Message": "ORA-24381: error(s) in array DML
https://docs.oracle.com/error-help/db/ora-24381/",
"Number": 24381,
"ParseErrorOffset": 157,
"Procedure": "",
"Source": "Oracle Data Provider for .NET, Managed Driver",
"ArrayBindIndex": -1
},
{
"$id": "3",
"$type": "Oracle.ManagedDataAccess.Client.OracleError, Oracle.ManagedDataAccess",
"DataSource": "",
"Message": "ORA-01438: value larger than specified precision allowed for this column
https://docs.oracle.com/error-help/db/ora-01438/",
"Number": 1438,
"ParseErrorOffset": -1,
"Procedure": "",
"Source": "Oracle Data Provider for .NET, Managed Driver",
"ArrayBindIndex": 3
},
{
"$id": "4",
"$type": "Oracle.ManagedDataAccess.Client.OracleError, Oracle.ManagedDataAccess",
"DataSource": "",
"Message": "ORA-01438: value larger than specified precision allowed for this column
https://docs.oracle.com/error-help/db/ora-01438/",
"Number": 1438,
"ParseErrorOffset": -1,
"Procedure": "",
"Source": "Oracle Data Provider for .NET, Managed Driver",
"ArrayBindIndex": 5
},
{
"$id": "5",
"$type": "Oracle.ManagedDataAccess.Client.OracleError, Oracle.ManagedDataAccess",
"DataSource": "",
"Message": "ORA-01438: value larger than specified precision allowed for this column
https://docs.oracle.com/error-help/db/ora-01438/",
"Number": 1438,
"ParseErrorOffset": -1,
"Procedure": "",
"Source": "Oracle Data Provider for .NET, Managed Driver",
"ArrayBindIndex": 6
}
]
},
"DataSource": "",
"Message": "ORA-24381: error(s) in array DML
https://docs.oracle.com/error-help/db/ora-24381/\nORA-01438: value larger than specified precision allowed for this column
https://docs.oracle.com/error-help/db/ora-01438/\nORA-01438: value larger than specified precision allowed for this column
https://docs.oracle.com/error-help/db/ora-01438/\nORA-01438: value larger than specified precision allowed for this column
https://docs.oracle.com/error-help/db/ora-01438/",
"Procedure": "",
"Source": "Oracle Data Provider for .NET, Managed Driver",
"Number": 24381,
"IsRecoverable": false,
"ErrorCode": -2147467259,
"Data": {
"$type": "System.Collections.ListDictionaryInternal, mscorlib"
},
"InnerException": null,
"HelpLink": null,
"HResult": -2147467259
}
Excel Row error: '5' Oracle error: '1438'
Message: ORA-01438: value larger than specified precision allowed for this column
https://docs.oracle.com/error-help/db/ora-01438/
Values:
Number: 0.0000000000000000000000000001
Number (4 2): 0.13
Redondeo -5 (3, 5): 0.00346
Redondeo 100: 12345678
Int 2: 3
DRIVER: Managed Driver (Arrays)
PROVIDER: .NET
Excel Row error: '7' Oracle error: '1438'
Message: ORA-01438: value larger than specified precision allowed for this column
https://docs.oracle.com/error-help/db/ora-01438/
Values:
Number:
Number (4 2): 100
Redondeo -5 (3, 5): 0.01
Redondeo 100: 0
Int 2: 100
DRIVER: Managed Driver (Arrays)
PROVIDER: .NET
Excel Row error: '8' Oracle error: '1438'
Message: ORA-01438: value larger than specified precision allowed for this column
https://docs.oracle.com/error-help/db/ora-01438/
Values:
Number:
Number (4 2): 100
Redondeo -5 (3, 5): 0.01
Redondeo 100: 0
Int 2: 100
DRIVER: Managed Driver (Arrays)
PROVIDER: .NET
The following results are obtained with the OLE DB and ODBC data providers when neither the "Decimal Rounding" nor the "No Conversion Errors" options are selected.
...
Cell: A5, Value: 2.6, Table: Tabla Redondeo Decimal, Column: Int 2, Precision: 2, Scale: 0
Number of decimal digits '1' exceeds Scale.
The Data Provider or Database may round or truncate the data.
Cell: B5, Value: 12345678, Table: Tabla Redondeo Decimal, Column: Redondeo 100, Precision: 5, Scale: -2
The number of digits of the integer part '8' exceeds the allowed '7'
Cell: C5, Value: 0.0034559999999999999, Table: Tabla Redondeo Decimal, Column: Redondeo -5 (3, 5), Precision: 3, Scale: 5
Number of decimal digits '19' exceeds Scale.
The Data Provider or Database may round or truncate the data.
Cell: D5, Value: 0.126, Table: Tabla Redondeo Decimal, Column: Number (4 2), Precision: 4, Scale: 2
Number of decimal digits '3' exceeds Scale.
The Data Provider or Database may round or truncate the data.
...
OLE DB (Oracle® Provider for OLE DB) Connections "Rounding Decimal" option not checked.
Row: 4
Command: INSERT INTO "Tabla Redondeo Decimal" ("Number", "Number (4 2)", "Redondeo -5 (3, 5)", "Redondeo 100", "Int 2", "DRIVER", "PROVIDER") VALUES (?, ?, ?, ?, ?, ?, ?)
Select Command: SELECT "Number", "Number (4 2)", "Redondeo -5 (3, 5)", "Redondeo 100", "Int 2", "DRIVER", "PROVIDER" FROM "Tabla Redondeo Decimal"
Exception:
{
"$id": "1",
"$type": "System.Data.OleDb.OleDbException, System.Data",
"ErrorCode": -2147217833,
"Errors": {
"$type": "System.Data.OleDb.OleDbErrorCollection, System.Data",
"$values": [
{
"$id": "2",
"$type": "System.Data.OleDb.OleDbError, System.Data",
"Message": "ORA-01438: value larger than specified precision allowed for this column",
"NativeError": 0,
"Source": "OraOLEDB",
"SQLState": ""
}
]
},
"Message": "ORA-01438: value larger than specified precision allowed for this column",
"Data": {
"$type": "System.Collections.ListDictionaryInternal, mscorlib"
},
"InnerException": null,
"HelpLink": null,
"Source": "OraOLEDB",
"HResult": -2147217833
}
Column: Number Value: 0.0000000000000000000000000001
Column: Number (4 2) Value: 0.1260
Column: Redondeo -5 (3, 5) Value: 0.0034559999999999999
Column: Redondeo 100 Value: 12345678
Column: Int 2 Value: 2.6
Column: DRIVER Value: OraOLEDB.Oracle
Column: PROVIDER Value: OLE DB
An exception occurred attempting to load Table 'Tabla Redondeo Decimal'. Row Error: 4
Query that indicates the used columns of the Table:
SELECT "Number", "Number (4 2)", "Redondeo -5 (3, 5)", "Redondeo 100", "Int 2", "DRIVER", "PROVIDER" FROM "Tabla Redondeo Decimal"
{
"$id": "1",
"$type": "System.Data.OleDb.OleDbException, System.Data",
"ErrorCode": -2147217833,
"Errors": {
"$type": "System.Data.OleDb.OleDbErrorCollection, System.Data",
"$values": [
{
"$id": "2",
"$type": "System.Data.OleDb.OleDbError, System.Data",
"Message": "ORA-01438: value larger than specified precision allowed for this column",
"NativeError": 0,
"Source": "OraOLEDB",
"SQLState": ""
}
]
},
"Message": "ORA-01438: value larger than specified precision allowed for this column",
"Data": {
"$type": "System.Collections.ListDictionaryInternal, mscorlib"
},
"InnerException": null,
"HelpLink": null,
"Source": "OraOLEDB",
"HResult": -2147217833
}
ODBC (Oracle ODBC Driver) Connections "Rounding Decimal" option not checked.
Row: 4
Command: INSERT INTO "Tabla Redondeo Decimal" ("Number", "Number (4 2)", "Redondeo -5 (3, 5)", "Redondeo 100", "Int 2", "DRIVER", "PROVIDER") VALUES (?, ?, ?, ?, ?, ?, ?)
Select Command: SELECT "Number", "Number (4 2)", "Redondeo -5 (3, 5)", "Redondeo 100", "Int 2", "DRIVER", "PROVIDER" FROM "Tabla Redondeo Decimal"
Exception:
{
"$id": "1",
"$type": "System.Data.Odbc.OdbcException, System.Data",
"Errors": {
"$type": "System.Data.Odbc.OdbcErrorCollection, System.Data",
"$values": [
{
"$id": "2",
"$type": "System.Data.Odbc.OdbcError, System.Data",
"Message": "[Oracle][ODBC][Ora]ORA-01438: value larger than specified precision allowed for this column\n",
"SQLState": "HY000",
"NativeError": 1438,
"Source": "SQORA32.DLL"
}
]
},
"Source": "SQORA32.DLL",
"ErrorCode": -2146232009,
"Message": "ERROR [HY000] [Oracle][ODBC][Ora]ORA-01438: value larger than specified precision allowed for this column\n",
"Data": {
"$type": "System.Collections.ListDictionaryInternal, mscorlib"
},
"InnerException": null,
"HelpLink": null,
"HResult": -2146232009
}
Column: Number Value: 0.0000000000000000000000000001
Column: Number (4 2) Value: 0.1260
Column: Redondeo -5 (3, 5) Value: 0.0034559999999999999
Column: Redondeo 100 Value: 12345678
Column: Int 2 Value: 2.6
Column: DRIVER Value: {Oracle en OraDB21Home1}
Column: PROVIDER Value: ODBC
An exception occurred attempting to load Table 'Tabla Redondeo Decimal'. Row Error: 4
Query that indicates the used columns of the Table:
SELECT "Number", "Number (4 2)", "Redondeo -5 (3, 5)", "Redondeo 100", "Int 2", "DRIVER", "PROVIDER" FROM "Tabla Redondeo Decimal"
{
"$id": "1",
"$type": "System.Data.Odbc.OdbcException, System.Data",
"Errors": {
"$type": "System.Data.Odbc.OdbcErrorCollection, System.Data",
"$values": [
{
"$id": "2",
"$type": "System.Data.Odbc.OdbcError, System.Data",
"Message": "[Oracle][ODBC][Ora]ORA-01438: value larger than specified precision allowed for this column\n",
"SQLState": "HY000",
"NativeError": 1438,
"Source": "SQORA32.DLL"
}
]
},
"Source": "SQORA32.DLL",
"ErrorCode": -2146232009,
"Message": "ERROR [HY000] [Oracle][ODBC][Ora]ORA-01438: value larger than specified precision allowed for this column\n",
"Data": {
"$type": "System.Collections.ListDictionaryInternal, mscorlib"
},
"InnerException": null,
"HelpLink": null,
"HResult": -2146232009
}
By selecting the "Decimal Rounding" option for OLE DB and ODBC data providers, Excel To Database rounds the data using the MidpointRounding.AwayFromZero option. See MidpointRounding Enumeration.
However, errors similar to the above may occur with rounded data that exceeds the field's capacity. It will need to check the "No Conversion Errors" option to replace the problematic data with NULL or the value specified in the "Mappings Panel".
...
Cell: A5, Value: 2.6, Table: Tabla Redondeo Decimal, Column: Int 2, Precision: 2, Scale: 0
Number of decimal digits '1' exceeds Scale.
"Decimal Rounding" option checked. Rounded value: 3
Cell: B5, Value: 12345678, Table: Tabla Redondeo Decimal, Column: Redondeo 100, Precision: 5, Scale: -2
The number of digits of the integer part '8' exceeds the allowed '7'
Cell: C5, Value: 0.0034559999999999999, Table: Tabla Redondeo Decimal, Column: Redondeo -5 (3, 5), Precision: 3, Scale: 5
Number of decimal digits '19' exceeds Scale.
"Decimal Rounding" option checked. Rounded value: 0.00346
Cell: D5, Value: 0.126, Table: Tabla Redondeo Decimal, Column: Number (4 2), Precision: 4, Scale: 2
Number of decimal digits '3' exceeds Scale.
"Decimal Rounding" option checked. Rounded value: 0.13
...
Cell: A7, Value: 99.5, Table: Tabla Redondeo Decimal, Column: Int 2, Precision: 2, Scale: 0
Number of decimal digits '1' exceeds Scale.
"Decimal Rounding" option checked. Rounded value: 100
Cell: A7, Value: 100, Table: Tabla Redondeo Decimal, Column: Int 2, Precision: 2, Scale: 0
The number of digits of the integer part '3' exceeds the allowed '2' (Precision - Scale)
Cell: B7, Value: 49.5, Table: Tabla Redondeo Decimal, Column: Redondeo 100, Precision: 5, Scale: -2
Number of significant zeros to the left of the decimal point 0 is lower Abs(Scale) 2
"Rounding Decimal" option checked. Rounded value: 0
Cell: C7, Value: 0.0099950000000000004, Table: Tabla Redondeo Decimal, Column: Redondeo -5 (3, 5), Precision: 3, Scale: 5
Number of decimal digits '19' exceeds Scale.
"Decimal Rounding" option checked. Rounded value: 0.01000
Cell: C7, Value: 0.01000, Table: Tabla Redondeo Decimal, Column: Redondeo -5 (3, 5), Precision: 3, Scale: 5. Scale greater than Precision.
Scale - Precision indicates the minimum number of significant zeros after the decimal separator.
Scale - Precision 2 > 1 Number of significant zeros.
Cell: D7, Value: 99.995, Table: Tabla Redondeo Decimal, Column: Number (4 2), Precision: 4, Scale: 2
Number of decimal digits '3' exceeds Scale.
"Decimal Rounding" option checked. Rounded value: 100.00
Cell: D7, Value: 100, Table: Tabla Redondeo Decimal, Column: Number (4 2), Precision: 4, Scale: 2
The number of digits of the integer part '3' exceeds the allowed '2' (Precision - Scale)
...
OLE DB (Oracle® Provider for OLE DB) Connections "Rounding Decimal" option checked.
Row: 4
Command: INSERT INTO "Tabla Redondeo Decimal" ("Number", "Number (4 2)", "Redondeo -5 (3, 5)", "Redondeo 100", "Int 2", "DRIVER", "PROVIDER") VALUES (?, ?, ?, ?, ?, ?, ?)
Select Command: SELECT "Number", "Number (4 2)", "Redondeo -5 (3, 5)", "Redondeo 100", "Int 2", "DRIVER", "PROVIDER" FROM "Tabla Redondeo Decimal"
Exception:
{
"$id": "1",
"$type": "System.Data.OleDb.OleDbException, System.Data",
"ErrorCode": -2147217833,
"Errors": {
"$type": "System.Data.OleDb.OleDbErrorCollection, System.Data",
"$values": [
{
"$id": "2",
"$type": "System.Data.OleDb.OleDbError, System.Data",
"Message": "ORA-01438: value larger than specified precision allowed for this column",
"NativeError": 0,
"Source": "OraOLEDB",
"SQLState": ""
}
]
},
"Message": "ORA-01438: value larger than specified precision allowed for this column",
"Data": {
"$type": "System.Collections.ListDictionaryInternal, mscorlib"
},
"InnerException": null,
"HelpLink": null,
"Source": "OraOLEDB",
"HResult": -2147217833
}
Column: Number Value: 0.0000000000000000000000000001
Column: Number (4 2) Value: 0.13
Column: Redondeo -5 (3, 5) Value: 0.00346
Column: Redondeo 100 Value: 12345678
Column: Int 2 Value: 3
Column: DRIVER Value: OraOLEDB.Oracle
Column: PROVIDER Value: OLE DB
An exception occurred attempting to load Table 'Tabla Redondeo Decimal'. Row Error: 4
Query that indicates the used columns of the Table:
SELECT "Number", "Number (4 2)", "Redondeo -5 (3, 5)", "Redondeo 100", "Int 2", "DRIVER", "PROVIDER" FROM "Tabla Redondeo Decimal"
{
"$id": "1",
"$type": "System.Data.OleDb.OleDbException, System.Data",
"ErrorCode": -2147217833,
"Errors": {
"$type": "System.Data.OleDb.OleDbErrorCollection, System.Data",
"$values": [
{
"$id": "2",
"$type": "System.Data.OleDb.OleDbError, System.Data",
"Message": "ORA-01438: value larger than specified precision allowed for this column",
"NativeError": 0,
"Source": "OraOLEDB",
"SQLState": ""
}
]
},
"Message": "ORA-01438: value larger than specified precision allowed for this column",
"Data": {
"$type": "System.Collections.ListDictionaryInternal, mscorlib"
},
"InnerException": null,
"HelpLink": null,
"Source": "OraOLEDB",
"HResult": -2147217833
}
ODBC (Oracle ODBC Driver) Connections "Rounding Decimal" option not checked.
Row: 4
Command: INSERT INTO "Tabla Redondeo Decimal" ("Number", "Number (4 2)", "Redondeo -5 (3, 5)", "Redondeo 100", "Int 2", "DRIVER", "PROVIDER") VALUES (?, ?, ?, ?, ?, ?, ?)
Select Command: SELECT "Number", "Number (4 2)", "Redondeo -5 (3, 5)", "Redondeo 100", "Int 2", "DRIVER", "PROVIDER" FROM "Tabla Redondeo Decimal"
Exception:
{
"$id": "1",
"$type": "System.Data.Odbc.OdbcException, System.Data",
"Errors": {
"$type": "System.Data.Odbc.OdbcErrorCollection, System.Data",
"$values": [
{
"$id": "2",
"$type": "System.Data.Odbc.OdbcError, System.Data",
"Message": "[Oracle][ODBC][Ora]ORA-01438: value larger than specified precision allowed for this column\n",
"SQLState": "HY000",
"NativeError": 1438,
"Source": "SQORA32.DLL"
}
]
},
"Source": "SQORA32.DLL",
"ErrorCode": -2146232009,
"Message": "ERROR [HY000] [Oracle][ODBC][Ora]ORA-01438: value larger than specified precision allowed for this column\n",
"Data": {
"$type": "System.Collections.ListDictionaryInternal, mscorlib"
},
"InnerException": null,
"HelpLink": null,
"HResult": -2146232009
}
Column: Number Value: 0.0000000000000000000000000001
Column: Number (4 2) Value: 0.13
Column: Redondeo -5 (3, 5) Value: 0.00346
Column: Redondeo 100 Value: 12345678
Column: Int 2 Value: 3
Column: DRIVER Value: {Oracle en OraDB21Home1}
Column: PROVIDER Value: ODBC
An exception occurred attempting to load Table 'Tabla Redondeo Decimal'. Row Error: 4
Query that indicates the used columns of the Table:
SELECT "Number", "Number (4 2)", "Redondeo -5 (3, 5)", "Redondeo 100", "Int 2", "DRIVER", "PROVIDER" FROM "Tabla Redondeo Decimal"
{
"$id": "1",
"$type": "System.Data.Odbc.OdbcException, System.Data",
"Errors": {
"$type": "System.Data.Odbc.OdbcErrorCollection, System.Data",
"$values": [
{
"$id": "2",
"$type": "System.Data.Odbc.OdbcError, System.Data",
"Message": "[Oracle][ODBC][Ora]ORA-01438: value larger than specified precision allowed for this column\n",
"SQLState": "HY000",
"NativeError": 1438,
"Source": "SQORA32.DLL"
}
]
},
"Source": "SQORA32.DLL",
"ErrorCode": -2146232009,
"Message": "ERROR [HY000] [Oracle][ODBC][Ora]ORA-01438: value larger than specified precision allowed for this column\n",
"Data": {
"$type": "System.Collections.ListDictionaryInternal, mscorlib"
},
"InnerException": null,
"HelpLink": null,
"HResult": -2146232009
}
Finally, note that the messages in the "Results Panel" may be different from one data provider to another, since each data provider maps database data types to different .NET data types. What happens if the Excel value exceeds the precision (not the scale)? If the total number of digits exceeds the column's precision (e.g., DECIMAL(5,2) and you try to insert 1000.50), an overflow error will occur, as rounding only affects decimals, not the number's magnitude.
For example: NUMBER(4, 2) is mapped as Single with the Managed Driver and as Decimal with OLE DB and ODBC. And, as mentioned, only the Managed Driver maps certain types to integer data types. Frequently Asked Questions