• • •

Oracle

Introduction

Any programmer who has faced an Oracle database will have realized that the information available from Oracle is limited, very scattered, and quite confusing. They will also have noticed that they need a "paid" account to access its knowledge base, so they will have to turn to alternative sources of information.
From a very naive perspective, the explanation I find is based on Oracle's business model: to hide information about their systems in order to boost their consulting business. In simple terms: Oracle is a database for high-income clients.

Without going into other considerations, the second problem a programmer encounters is deciding what type of connection they want to establish with the database and which driver to use. In Client / Server Interoperability Support Matrix for Different Oracle Versions you can find information about compatibility between different versions. Once decided, they will encounter the big problem of driver installation, which will be a tedious process when they want to have several fully operational drivers installed on the same client.

Oracle provides users with a "monster" called "Oracle Universal Installer (OUI)". This humble developer believes that "this thing" only serves to pollute the machine where it runs:
Oracle Database 21c (21.3). Section: Oracle Database 21c Client (21.3) for Microsoft Windows x64 (64-bit)
Differences between Oracle Client and Client Home
How to Install Oracle Client Home 21c on Windows
For the latest version of Oracle (23ai) this "monster" has not been found. It seems that even Oracle has realized its inefficiency.

However, Oracle also provides installers, "if you can call them that," that only install what is truly needed. However, the documentation offered for these "installers" is really poor.

It is important to note that no tests have been performed with Oracle Cloud, as the scarcity of available financial resources at MalaCharger prevents such work from being carried out.

Drivers Oracle

Managed Driver

Excel To Database includes the Managed Driver Oracle® Data Provider for .NET. This means that in many cases, there is no need to install drivers. However, this driver has some limitations, so it may be necessary to install additional drivers to use other types of connections (OLE DB or ODBC) without the limitations of this driver.
In System Requirements, Oracle states that the driver is suitable for accessing Oracle 19c databases or later. To access older versions, an OLE DB or ODBC connection is required, and the driver that allows connection to the desired Oracle version must be installed.
In Connection String Attributes, the different parameters supported by the connection string are described. Excel To Database sets the following attributes in the connection string:
Persist Security Info=false;Application Continuity=true;Self Tuning=true
Excel To Database can use two alternative methods for bulk data insertion. One of them is based on the use of the Bulk Copy feature. In Bulk Copy, the supported data types and the different driver and feature restrictions are indicated.
It is important to highlight, as already mentioned in "Oracle: Truncating Strings", that this option allows the insertion of ANYTHING into the database. In fact, the documentation specifies that CHECK, FOREIGN KEY, DEFAULTS, and INSERT TRIGGERS constraints are automatically disabled. It also indicates that indexes are rebuilt after bulk insertion, at which point UNIQUE constraints are checked, and if there is a problem with these types of constraints, the index will become unusable. See: Integrity Constraints Affecting Oracle Bulk Copy
There are countless problems associated with this feature. As an example, the following comments describe some of them:
What specifically does OracleBulkCopy do, and how can I optimize its performance?
Oracle bulk copy duplicate rows.
The same documentation specifies that if the clause "EVALUATE CHECK_CONSTRAINTS" is specified, CHECK constraints are enabled, but if any record does not meet these constraints, it will be deleted (it does not specify whether the user will receive a message indicating this). However, there is no way to specify this clause in .NET.
For all these reasons, it is recommended to NOT USE this major Bug in Managed Driver (Bulk Copy).
The other method is based on the use of the Array Binding feature. In Array Binding, the supported data types and the different restrictions of the driver and the Array Binding feature are indicated.
This feature is not as problematic as the previous one, although it seems to have some issues with LOBs:
Array Binding with Oracle(11g) Db and c#
Performance problems with array binding
OLE DB Drivers

Oracle's documentation about the OLE DB driver can be found at: Oracle® Provider for OLE DB, and the documentation for client installations can be found at: Installing Oracle Instant Client.
As mentioned earlier, to access older versions of Oracle databases, or to obtain features not available in the Managed Driver, it may be necessary to install OLE DB drivers.
For testing, the "ODAC Xcopy Packages" were downloaded: ODAC-21.7-Xcopy-64-bit.zip and ODAC-23.5-Xcopy-64.zip. That is, the tests were performed with versions of the "ODAC Xcopy Packages" 21 and 23.
The packages contain the Oracle Provider for OLE DB driver and the "Oracle Instant Client" component necessary for its operation.
Please note that you may need to install Downloads of the latest supported Microsoft Visual C++ Redistributable versions Microsoft Visual C++ Redistributable latest supported downloads or higher, and you may need to install both 64-bit and 32-bit versions.
Inside the package, there is the "readme.htm" document. It contains the installation instructions, which are not very detailed.
The "installer" simply consists of a ".bat" file that installs the components in a very rudimentary way. It is up to the user to perform the necessary checks to avoid conflicts, which are not detailed anywhere in the Oracle documentation.
Needless to say, the installer DOES NOT WORK even when run with administrator permissions. This is because the configurator (configure.bat), even though it modifies the PATH environment variable, the regsvr32 utility cannot find the necessary modules to register the library:
REM Add <Oracle Home> to the PATH environment variable set PATH=%HOME_DIR_NO_TRAILING_SLASH%;%HOME_DIR_NO_TRAILING_SLASH%\bin;%PATH% REM Register OraOLEDB DLL in the registry echo Register OraOLEDB DLL >> install.log regsvr32 /s "%HOME_DIR_NO_TRAILING_SLASH%\bin\OraOLEDB.dll"
The solution is to manually add both directories to the system PATH environment variable and manually register the "OraOLEDB.dll" library.
In this situation, simply wish the user good luck, insist they not despair, and try again and again until it works.
If you already have an installation of another driver or anything related to Oracle, we cannot assist you from here. We simply recommend that you back up your machine, because the only thing you can be sure of is that you will need it. However, you may get some ideas from the following answers:
Installing Oracle 32-bit Client on Windows Server Already Running 64-bit Oracle Database Server
NLS_LANG issue for two Oracle Homes on the same host, not refreshing the System and User Environment
Is there a command to refresh environment variables from the command prompt in Windows?
Setting NLS_LANG Value for Oracle
Since with Oracle you never know where connection issues may arise, you can use the following application to check the versions of drivers and libraries used: Verify Oracle Client Installation although it is not very efficient, as it obtains the version of the file but not the product version. For example, when installing "Oracle Database Express Edition (XE)", the version of the "OraOLEDB.dll" library installed with that package is 21.1.0.0, the same as the one installed with "ODAC-21.7-Xcopy-64-bit.zip". However, the product versions differ: for the library installed with "Oracle Database Express Edition (XE)", it is 21.1.0.0.20210772, while for "ODAC-21.7-Xcopy-64-bit.zip", it is 21.1.0.0.20211208.
NOTE: After installing "Oracle Database Express Edition (XE)" on Windows 10 and trying to update the Oracle OLE DB driver, the listener stopped working. Strange, right? Well, no: Oracle, besides consuming system resources, also consumes the patience of administrators, programmers, and other unsuspecting people who think that by following Oracle's instructions, everything will work.
It should be noted that this page is not meant to be a treatise on drivers or connection strings, so it is recommended to consult the page OraOLEDB-Specific Connection String Attributes.
Excel To Database sets the following attributes in the connection string:
Persist Security Info=False;OLEDB.NET=True;NDatatype=True;SPPrmsLOB=False;UseSessionFormat=True;Application Name=Excel To Database
The "Microsoft OLE DB Provider for Oracle" (MSDAORA) driver only works for 32-bit architectures. Microsoft considers it obsolete, so it is not taken into account for analyses performed with Excel To Database.
Note that the .NET classes for handling these types of connections, System.Data.OleDb, are not up to date, and may not handle the data types returned by the drivers correctly.
Oracle ODBC Drivers

The documentation for the Oracle ODBC Driver can be found at: Introduction to the Oracle Database ODBC Driver. Please note that the driver configuration dialog box Oracle Database ODBC Driver Configuration Dialog Box does not appear when you want to create a file DSN. However, it does appear when creating a user or system DSN.
The documentation for client installations can be found at: Installing Oracle Instant Client ODBC (Windows).
To install the Oracle ODBC Driver, the first challenge is finding the download page for this driver. At Oracle Instant Client Downloads for Microsoft Windows (x64) 64-bit you will find the download links for different driver versions.
Inside the compressed file, you will find the installation instructions. Basically, it consists of extracting the contents of the file to the directory where the "Oracle Instant Client" is located and running the installer with administrator permissions.
To access older versions of Oracle databases, or to obtain features not available in the Managed Driver, it may be necessary to install earlier versions of the Oracle ODBC Driver. For this, see the compatibility matrix mentioned at the beginning of this chapter.
In conclusion, installing a couple of "simple" drivers requires completing a full software engineering course. After four or five decades, Oracle is still unable to create simple, fully automated installers that do not clutter the machine with unnecessary junk and strange components that are completely unrelated to the few libraries that are truly needed.
Of course, just like with OLE DB, there are drivers for 32-bit architectures ("Microsoft ODBC for Oracle"), which Microsoft considers obsolete and therefore are not part of this analysis.
Note that this page is not intended to be a treatise on drivers or connection strings, so it is recommended to consult the pages Format of the Connection String and Oracle Database ODBC Driver Configuration Dialog Box (which does not appear in file DSNs but does in user or system DSNs) where the different attributes allowed in the connection string are described.
Excel To Database sets the following attribute in the connection string:
app=Excel To Database;apa=T;rst=T;bnf=F;lob=T;btd=F;num=NLS;dba=W;exc=F;fwc=T;mdi=F;bam=IfAllSuccessful APA Applications Attributes: T = Thread Safety Enabled. RST Result Sets: T = Result Sets Enabled. BNF Bind NUMBER as FLOAT: F = Bind NUMBER as NUMBER. LOB LOB Writes: T = LOBs Enabled. BTD Bind TIMESTAMP as DATE: F = Bind SQL_TIMESTAMP as Oracle TIMESTAMP NUM Numeric Settings: NLS = Use Oracle NLS numeric settings(to determine the decimal and group separator). DBA Database Attribute: W = write access. EXC EXEC Syntax: F = EXEC Syntax Disabled. FWC Force SQL_WCHAR Support: T = Force SQL_WCHAR Enabled. MDI Set Metadata ID Default: F = SQL_ATTR_METADATA_ID defaults to SQL_FALSE. BAM Batch Autocommit Mode: IfAllSuccessful = Commit only if all statements are successful(old behavior).
Finally, it is worth noting that the .NET classes for handling these types of connections, System.Data.Odbc, are not up to date and may not correctly handle the data types returned by the drivers.

Connection Options

As stated in Globalization Support, the Managed Driver is independent of NLS_LANG and all the environment variables that Oracle loves so much. The configuration of this driver is done directly from the code.
However, OLE DB and ODBC connections are dependent on the installation performed for the "Oracle Instant Client" component and its configuration. In Windows, by default, the configuration is defined in the Registry:
HKEY_LOCAL_MACHINE\SOFTWARE\Oracle\KEY_[OracleHome]\
The NLS_LANG key, after the installation of "Oracle Instant Client", is set to AMERICAN_AMERICA.WE8MSWIN1252.
However, due to the testing system (Windows 10 in Spanish), the NLS_LANG key is set in the Registry to SPANISH_SPAINWE8MSWIN1252 to ensure proper configuration.

In order to improve the performance of Excel To Database, each time a connection to Oracle is opened, the following options are intentionally set in the session:

 ALTER SESSION SET NLS_LANGUAGE = "*1";
ALTER SESSION SET NLS_TERRITORY = "*2";
ALTER SESSION SET NLS_NUMERIC_CHARACTERS = ".,";
ALTER SESSION SET NLS_NCHAR_CONV_EXCP = true;
ALTER SESSION SET NLS_DATE_FORMAT = "YYYY-MM-DD HH24:MI:SS";
ALTER SESSION SET NLS_TIME_FORMAT = "HH24:MI:SS.FF7";
ALTER SESSION SET NLS_TIME_TZ_FORMAT = "HH24:MI:SS.FF7 TZH:TZM";
ALTER SESSION SET NLS_TIMESTAMP_FORMAT = "MM/DD/YYYY HH24:MI:SS.FF7";
ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT = "MM/DD/YYYY HH24:MI:SS.FF7 TZH:TZM";
ALTER SESSION SET TIME_ZONE='*3'

*1 -> SELECT value FROM NLS_DATABASE_PARAMETERS WHERE parameter ='NLS_LANGUAGE'
*2 -> SELECT value FROM NLS_DATABASE_PARAMETERS WHERE parameter ='NLS_TERRITORY'
That is, the session will be set with the NLS_LANGUAGE and NLS_TERRITORY settings established in the database.

*3 -> It is set to the client's time zone at the moment of the connection.
That is, it depends on whether it's standard time or daylight saving time.


Thus, when a connection string to Oracle is configured, Excel To Database
displays in the Results Panel the information obtained by executing:

SELECT * FROM NLS_SESSION_PARAMETERS;

NLS_LANGUAGE: AMERICAN
NLS_TERRITORY: AMERICA
NLS_CURRENCY: $
NLS_ISO_CURRENCY: AMERICA
NLS_NUMERIC_CHARACTERS: .,
NLS_CALENDAR: GREGORIAN
NLS_DATE_FORMAT: YYYY-MM-DD HH24:MI:SS
NLS_DATE_LANGUAGE: AMERICAN
NLS_SORT: BINARY
NLS_TIME_FORMAT: HH24:MI:SS.FF7
NLS_TIMESTAMP_FORMAT: MM/DD/YYYY HH24:MI:SS.FF7
NLS_TIME_TZ_FORMAT: HH24:MI:SS.FF7 TZH:TZM
NLS_TIMESTAMP_TZ_FORMAT: MM/DD/YYYY HH24:MI:SS.FF7 TZH:TZM
NLS_DUAL_CURRENCY: $
NLS_COMP: BINARY
NLS_LENGTH_SEMANTICS: BYTE
NLS_NCHAR_CONV_EXCP: TRUE

It is highly recommended to read the following Oracle page: NLS_LANG FAQ.

Books, Databases

The tests will be conducted on Windows 10 64-bit, Oracle Database 21c Express Edition (FULL VERSION: 21.3.0.0.0) and 64-bit Excel To Database compiled for .NET Framework 4.8. As expected, not all drivers or all possible options will be analyzed, but the most representative cases will be studied.

In order to demonstrate the behavior of the different application options, a series of tables for MS Access, SQL Server, and Oracle, as well as several Excel Workbooks, have been prepared. The table creation scripts, workbooks, MS Access databases, and the configuration file for all chapters can be downloaded from: "Download Examples".
The table creation scripts are also shown in the documentation, as well as the Excel Sheets used at each point (as images).

Oracle Databases and Connections

The database built for the tests has the configuration shown below. For it, 4 configurations have been prepared in Excel To Database, of which, the ODBC connection uses the DSN file shown below:

 SELECT * FROM NLS_DATABASE_PARAMETERS;

"NLS_RDBMS_VERSION"           "21.0.0.0.0"
"NLS_NCHAR_CONV_EXCP"         "FALSE"
"NLS_LENGTH_SEMANTICS"        "BYTE"
"NLS_COMP"                    "BINARY"
"NLS_DUAL_CURRENCY"           "$"
"NLS_TIMESTAMP_TZ_FORMAT"     "DD-MON-RR HH.MI.SSXFF AM TZR"
"NLS_TIME_TZ_FORMAT"          "HH.MI.SSXFF AM TZR"
"NLS_TIMESTAMP_FORMAT"        "DD-MON-RR HH.MI.SSXFF AM"
"NLS_TIME_FORMAT"             "HH.MI.SSXFF AM"
"NLS_SORT"                    "BINARY"
"NLS_DATE_LANGUAGE"           "AMERICAN"
"NLS_DATE_FORMAT"             "DD-MON-RR"
"NLS_CALENDAR"                "GREGORIAN"
"NLS_NUMERIC_CHARACTERS"      ".,"
"NLS_NCHAR_CHARACTERSET"      "AL16UTF16"
"NLS_CHARACTERSET"            "AL32UTF8"
"NLS_ISO_CURRENCY"            "AMERICA"
"NLS_CURRENCY"                "$"
"NLS_TERRITORY"               "AMERICA"
"NLS_LANGUAGE"                "AMERICAN"


      4 Configurations are prepared with the following connections to the Databases:

• Connecting to Managed Driver (BulkCopy): USER ID=UsuarioOracle;PASSWORD=Contrasenia;DATA SOURCE=localhost
• Connecting to Managed Driver (Array Binding): USER ID=UsuarioOracle;PASSWORD=Contrasenia;DATA SOURCE=localhost
• Connecting to OLE DB: Provider=OraOLEDB.Oracle.1;Data Source=localhost;Persist Security Info=False;Password=Contrasenia;User ID=UsuarioOracle;oledb.net=True;ndatatype=False;spprmslob=False;usesessionformat=True;application name=\"Excel To Database\"
• Connecting to ODBC: Driver={Oracle in 21_7};server=LOCALHOST;uid=UsuarioOracle;pwd=Contrasenia;dbq=LOCALHOST;dba=W;apa=T;exc=F;xsm=Default;fen=T;qto=T;frc=10;fdl=10;lob=T;rst=T;btd=F;bnf=F;bam=IfAllSuccessful;num=NLS;dpm=F;mts=T;mdi=F;csr=F;fwc=T;fbs=60000;tlo=O;mld=0;oda=F;ste=F;tsz=8192;ast=FLOAT;lps=8192;app=Excel To Database

      The ODBC connection uses the following DSN file:

Oracle_ODBC_X64.dsn
[ODBC]
DRIVER=Oracle in 21_7
UID=UsuarioOracle
LPS=8192
AST=FLOAT
TSZ=8192
STE=F
ODA=F
MLD=0
TLO=O
FBS=60000
FWC=F
CSR=F
MDI=Me
MTS=T
DPM=F
NUM=NLS
BAM=IfAllSuccessful
BNF=F
BTD=F
RST=T
LOB=T
FDL=10
FRC=10
QTO=T
FEN=T
XSM=Default
EXC=F
APA=T
DBA=W
DBQ=LOCALHOST
SERVER=LOCALHOST

Excel Workbook

For the tests conducted, an Excel Workbook has been used, and its Sheets will be displayed as images throughout the chapter.
It is important to note that no cells with errors have been included for the tests, so the option No Error Values will not be used.
The data has been prepared in a way that allows verifying the data load functionality. These are not exhaustive test cases, so it can be inferred that the data load is not guaranteed to occur as the user might expect or as described on this page.
You can download the test files from here: Download Examples

Oracle Tests: "Floating Point" Numbers

To demonstrate the behavior of the drivers with this type of Oracle data, the table whose creation script is shown below is used, along with the Excel Sheet shown in the following figure:

 CREATE TABLE "Tabla Numeros" (
  Id NUMBER(6) GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  Provider                  VARCHAR2(10 CHAR),
  Driver                    VARCHAR2(50 CHAR),

  "Float 5"                 FLOAT(5),
  "Float"                   FLOAT,
  "Binary Float"            BINARY_FLOAT,
  "Binary Double"           BINARY_DOUBLE
);

Please note that the definition of FLOAT in Oracle is somewhat unusual. The field defined in the example as "Float 5" cannot exceed 5 "binary" digits. If this precision is exceeded, the data will be truncated. Oracle does not recommend its use, so the tests will be limited to checking its compatibility with the different drivers.
However, the "Float" field behaves more or less like the "Binary Double" field.

Oracle: Excel Sheet for "Floating Point" Numbers

It is important to highlight that the cell format in the Excel Sheet is "Text", "Number", or "Scientific". It is "Text" for cases where Excel’s precision is exceeded. In these cases, the "Decimal separator" is the dot ".", as it is the separator used by Excel To Database to convert texts into numbers.

First of all, it should be noted that the Bug Managed Driver, with the BulkCopy option, has issues with the Float data type (this does not happen with the Array Binding option or with the other drivers). Thus, when attempting to insert data into this field, the following error occurs:

 An exception occurred attempting to load Table 'Tabla Numeros'.
     Query that indicates the used columns of the Table:
     SELECT "Float 5", "DRIVER", "PROVIDER" FROM "Tabla Numeros"
{
  "$id": "1",
  "$type": "Oracle.ManagedDataAccess.Types.OracleTruncateException, Oracle.ManagedDataAccess",
  "Message": "ORA-16550: truncated result
https://docs.oracle.com/error-help/db/ora-16550/",
  "Number": 16550,
  "Source": "Oracle Data Provider for .NET, Managed Driver",
  "Data": {
    "$type": "System.Collections.ListDictionaryInternal, mscorlib"
  },
  "InnerException": null,
  "HelpLink": null,
  "HResult": -2146233087
}

The messages that Excel To Database displays during data reading are as follows:

 Cell: A3, Value: 7.89
    The value contained in the cell has been converted to the .NET type Double: 7.8899999999999997

Cell: B3, Value: 7.89
    The value contained in the cell has been converted to the .NET type Double: 7.8899999999999997

Cell: A4, Value: 12.79
    The value contained in the cell has been converted to the .NET type Double: 12.789999999999999

Cell: B4, Value: 12.79
    The value contained in the cell has been converted to the .NET type Double: 12.789999999999999

Cell: D6, Value: 0.1234567890123456
    The value contained in the cell has been converted to the .NET type Double: 0.12345678901234559

Cell: D10, Value: 9.99999999999999E+307
    The value contained in the cell has been converted to the .NET type Double: 9.9999999999999901E+307

Cell: C15, Value: 1.2345678
    The value contained in the cell has been converted to the .NET type Single: 1.23456776

Cell: B16, Value: 3.4028230E+38
    The value contained in the cell has been converted to the .NET type Double: 3.4028229999999999E+38

Cell: C16, Value: 3.4028230E+38
    The value contained in the cell has been converted to the .NET type Single: 3.40282306E+38

Cell: C17, Value: 0.0000000E+00
    The value contained in the cell has been converted to the .NET type Single: 0

Cell: D17, Value: 1.797693134862325E+308, Table: Tabla Numeros, Column: Binary Double
    Cell content could not be converted to data type Double
    It will attempt to load the cell as NULL (or the default value). "No Conversion Errors" option checked.

Cell: B18, Value: 3.4028236E+38
    The value contained in the cell has been converted to the .NET type Double: 3.4028235999999999E+38

Cell: C18, Value: 3.4028236E+38, Table: Tabla Numeros, Column: Binary Float
    Cell content could not be converted to data type Single
    It will attempt to load the cell as NULL (or the default value). "No Conversion Errors" option checked.

Cell: C19, Value: 1.4012980E-45
    The value contained in the cell has been converted to the .NET type Single: 1.40129846E-45

Cell: D19, Value: 4.9406565E-307
    The value contained in the cell has been converted to the .NET type Double: 4.9406565000000001E-307

Cell: C20, Value: 1.401298E-45
    The value contained in the cell has been converted to the .NET type Single: 1.40129846E-45

Cell: D20, Value: 4.94065645841246544E-324
    The value contained in the cell has been converted to the .NET type Double: 4.9406564584124654E-324

Cell: C21, Value: 1.0E-46
    The value contained in the cell has been converted to the .NET type Single: 0

Cell: D21, Value: 1.0E-324
    The value contained in the cell has been converted to the .NET type Double: 0

Below is the behavior of Excel To Database and the different drivers to explain the data stored in the Oracle database:

Behavior of Excel To Database:
Due to the imprecision of floating-point numeric data, many of the values read do not match the converted value to the corresponding .NET data type. These are the cells "A3:B4", "D6", "D10", "C15:C16", "B16", "B18", and "C19:D21".
The cells "D17" and "C18" exceed .NET's capacity and thus cannot be read. When the "No Conversion Errors" option is checked, they are replaced by the default value specified in the "Mapping Panel".
A special case is cell "D19". The cell format is Scientific with 7 decimals, but it exceeds Excel's capabilities. Thus, the data is stored as text in the Excel sheet. However, since the operating system language is Spanish, Excel stores the number as text using the comma (",") as the decimal separator. Excel To Database interprets it incorrectly, as it internally uses the dot (".") as the decimal separator. This is why the exponent does not match the one shown in Excel.
However, in cell D20, the same data is contained, but with a Text format, and the decimal separator is the dot ("."). Excel To Database correctly interprets it, and given the 15-digit precision for the Double data type in .NET, the value is rounded to 15 digits.
Behavior of Oracle:
Notable cases include cells "C19:C20", which exceed the field precision and are therefore rounded by the database, storing the value in field 1.4E-45. The same happens with cell "D20", which stores 4.9E-324. The case of cell "D19" has been previously discussed.
Behavior of Managed Driver:
First, note that the "Float 5" field cannot be loaded with the BulkCopy option, as previously mentioned. However, it can be perfectly loaded with the Array Binding option, as Oracle truncates the value appropriately.
It's worth highlighting that, for some strange reason, the cells with inaccuracies are stored correctly: "B3:B4", "D6", "D10", "C15:C16", "B16", "B18", and "C19:C20". The cases for "D19" and "D20" have already been discussed. With Array Binding, cells "A3:A4" are also correctly stored.
Behavior of Oracle ODBC Driver:
With this driver, the strange issue mentioned earlier does not occur, so the cells "B3:B4", "B16", and "B18" are loaded with the .NET value. In other words, they are not rounded with the driver, as it seems to happen with the Managed Driver and OraOLEDB.Oracle.
For cells "D6", "D10", and "C15:C16", this mysterious rounding does occur.
The cases for cells "C19:D20" have already been discussed earlier.

Behavior of Oracle Provider for OLE DB (OraOLEDB.Oracle):
The behavior of this driver is the same as the one described for the Managed driver with the Array Binding option, except for BINARY_DOUBLE type data. In this case, the driver has the following bug when the data is large numbers. Thus, for cells "D7:D10", it causes an overflow error. This overflow error occurs during the data loading phase in the table and halts that phase.
Note: The errors that occur during this phase prevent the rest of the loads from continuing.

 Row: 6
Command: INSERT INTO "Tabla Numeros" ("Binary Double", "DRIVER", "PROVIDER") VALUES (?, ?, ?)
Select Command: SELECT "Binary Double", "DRIVER", "PROVIDER" FROM "Tabla Numeros"
Exception:
{
  "$id": "1",
  "$type": "System.Data.OleDb.OleDbException, System.Data",
  "ErrorCode": -2147217900,
  "Errors": {
    "$type": "System.Data.OleDb.OleDbErrorCollection, System.Data",
    "$values": [
      {
        "$id": "2",
        "$type": "System.Data.OleDb.OleDbError, System.Data",
        "Message": "ORA-01426: numeric overflow",
        "NativeError": 0,
        "Source": "OraOLEDB",
        "SQLState": ""
      }
    ]
  },
  "Message": "ORA-01426: numeric overflow",
  "Data": {
    "$type": "System.Collections.ListDictionaryInternal, mscorlib"
  },
  "InnerException": null,
  "HelpLink": null,
  "Source": "OraOLEDB",
  "HResult": -2147217900
}

    Column: Binary Double    Value: 1.79769313486232E+308
    Column: DRIVER    Value: OraOLEDB.Oracle
    Column: PROVIDER    Value: OLE DB
An exception occurred attempting to load Table 'Tabla Numeros'. Row Error: 6
     Query that indicates the used columns of the Table:
     SELECT "Binary Double", "DRIVER", "PROVIDER" FROM "Tabla Numeros"
{
  "$id": "1",
  "$type": "System.Data.OleDb.OleDbException, System.Data",
  "ErrorCode": -2147217900,
  "Errors": {
    "$type": "System.Data.OleDb.OleDbErrorCollection, System.Data",
    "$values": [
      {
        "$id": "2",
        "$type": "System.Data.OleDb.OleDbError, System.Data",
        "Message": "ORA-01426: numeric overflow",
        "NativeError": 0,
        "Source": "OraOLEDB",
        "SQLState": ""
      }
    ]
  },
  "Message": "ORA-01426: numeric overflow",
  "Data": {
    "$type": "System.Collections.ListDictionaryInternal, mscorlib"
  },
  "InnerException": null,
  "HelpLink": null,
  "Source": "OraOLEDB",
  "HResult": -2147217900
}

It should be noted that currently Excel To Database uses the capabilities of .NET for reading numerical data. However, in future versions, this functionality will be modified so that these capabilities adapt to what Oracle or SQL Server can offer.

Oracle Tests: Dates

As a complement to the tests conducted here, it is recommended to read the section dedicated to dates in MS Access: MS Access Tests: Dates and SQL Server: SQL Server Tests: Dates.

Excel To Database performs the following mapping of Oracle data types to .NET data types:

.NET Data Provider (Managed Driver): Date -> OracleDate, TimeStamp -> OracleTimeStamp, TimeStamp WITH TIME ZONE -> OracleTimeStampTZ, TimeStamp WITH LOCAL TIME ZONE -> OracleTimeStampLTZ
OLE DB Data Provider (System.Data.OleDb): Date, TimeStamp, TimeStamp WITH TIME ZONE, TimeStamp WITH LOCAL TIME ZONE -> String
ODBC Data Provider (System.Data.Odbc): Date, TimeStamp, TimeStamp WITH TIME ZONE, and TimeStamp WITH LOCAL TIME ZONE -> String

Since the Managed Driver has issues when inserting data that does not meet the database field constraints, the only solution found is to use Oracle structures. However, these are so poorly programmed that it is necessary to preprocess the data in .NET beforehand. This preprocessing may result in the precision achieved with .NET data types being lower than the capabilities of the Oracle fields.

This does not happen with the OLE DB or ODBC data providers. For them, data can be inserted as character strings. Thus, it will be the data providers and the database itself that perform the rounding and truncating that their developers considered appropriate.

The following table and sheet were used for the tests:

 CREATE TABLE "Tabla Fechas" (
  Id NUMBER(6) GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  Provider                          VARCHAR2(10 CHAR),
  Driver                            VARCHAR2(50 CHAR),

  "Date"                            DATE,
  "Timestamp2"                      TIMESTAMP(2),
  "Timestamp2 with time zone"       TIMESTAMP(2) WITH TIME ZONE,
  "Timestamp2 with local time zone" TIMESTAMP(2) WITH LOCAL TIME ZONE
);

Oracle: Excel Sheet for Dates

Since handling dates and intervals in Oracle is complex, the following page is provided as help through examples to understand its functionality: How to use DATEs, TIMESTAMPs, and INTERVALs in Oracle Database - SQL Office Hours script

First, it is important to highlight the Oracle Provider for OLE DB (OraOLEDB.Oracle) Bug. It is unable to store fractions of a second.

Both Oracle and the drivers round the data with MidpointRounding.AwayFromZero. See MidpointRounding Enumeration.

It is also worth highlighting the Oracle Provider for OLE DB (OraOLEDB.Oracle) Bug and the Oracle ODBC Driver Bug for the "TIMESTAMP WITH TIME ZONE" data type.
With the Managed Driver, the time zone for the cells "C4:C7" is stored correctly: At 3:00:00 AM on October 27, 2024, the time changed in Spain. The new time will be 2:00:00 AM, and the time difference with GMT goes from +02:00 to +01:00. This fact is perfectly reflected in the data stored in the database.
However, with ODBC and OLE DB, it can only store the client’s time zone at the moment of executing the configurations, not the one that can be inferred from the data.

The "TIMESTAMP WITH LOCAL TIME ZONE" data type has a very strange behavior. This humble engineer has been unable to figure out where about 50 minutes and 20 seconds difference comes from with certain data using any of the drivers.

Lastly, it should be noted that while Oracle allows the insertion of time-only data into these fields by adding the first day of the month when the insertion is executed, Excel To Database does not allow this. Thus, the cells "A10:D11" and "A13:D13" are considered as data that cannot be inserted into the corresponding fields.

Oracle Tests: Intervals

Excel To Database performs the following mapping of Oracle data types to .NET data types:

Data Provider .NET (Managed Driver): Interval YEAR TO MONTH -> OracleIntervalYM and Interval DAY TO SECOND -> OracleIntervalDS
Data Provider OLE DB (System.Data.OleDb): Interval YEAR TO MONTH and Interval DAY TO SECOND -> String

Data Provider ODBC (System.Data.Odbc): The Oracle ODBC Driver Bug causes the data types "Interval YEAR TO MONTH" and "Interval DAY TO SECOND" to be unrecognized. When trying to retrieve the data type information for these fields, the following error appears (which has no associated message):

 An exception occurred attempting to load Table 'Tabla Intervalos'. Row Error: 0
     Query that indicates the used columns of the Table:
     SELECT "Interval day (3) to second (2)", "Interval year (2) to month", "PROVIDER", "DRIVER" FROM "Tabla Intervalos"
{
  "$id": "1",
  "$type": "System.Data.Odbc.OdbcException, System.Data",
  "Errors": {
    "$type": "System.Data.Odbc.OdbcErrorCollection, System.Data",
    "$values": []
  },
  "Source": "",
  "ErrorCode": -2146232009,
  "Message": "",
  "Data": {
    "$type": "System.Collections.ListDictionaryInternal, mscorlib"
  },
  "InnerException": null,
  "HelpLink": null,
  "HResult": -2146232009
}

For the tests, the following table and the following sheet have been used:

 CREATE TABLE "Tabla Intervalos" (
  Id NUMBER(6) GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  Provider                          VARCHAR2(10 CHAR),
  Driver                            VARCHAR2(50 CHAR),

  "Interval year (2) to month"      INTERVAL YEAR (2) TO MONTH,
  "Interval day (3) to second (2)"  INTERVAL DAY (3) TO SECOND (2)
);

Oracle: Excel Sheet for Intervals

Either Oracle or the drivers round the data with MidpointRounding.ToEven. See MidpointRounding Enumeration.

Excel incorrectly interprets the cell format [h]:mm:ss. Thus, the cell "B6" displays 564:59:59, which should be equivalent to 23 days with 12:59:59. However, Excel stores the date as 23/01/1900 12:59:59. Given Excel's issues with dates (1900 was not a leap year, the reference date is 31/12/1899, etc.), this causes the interval to be stored in the database as +24 12:59:59.000000.
The same happens with the content of cell "B23." It's the same data as cell "B6" but with a different cell format.
However, the data in cell "B18" with the "Text" Cell Format is stored correctly in the database: +23 12:59:59.600000.

There is a Bug in Oracle Provider for OLE DB (OraOLEDB.Oracle): Cells "B7:B8", "B16:B17" are incorrectly rounded. Thus, the fractions of a second ".005" and ".006" are stored as ".1", when they should be stored as ".01".

As a conclusion after conducting all the tests shown in the different chapters for Oracle, it is recommended to use the Managed Driver (Array Binding), as this option is also the fastest.

Frequently Asked Questions

How to view the ORA error code in Excel To Database?

Checking the StatusCode property in the JSON log exported after the failure.