USER MANUALS

Oracle

To be able to use the Oracle API to perform bulk data loads, first follow these steps:

  1. On the Oracle database, enable direct path loads. To prepare it for direct path loads, the administrator of Oracle needs to run the setup script catldr.sql of Oracle, which creates the necessary views in Oracle to do direct loads. The administrator only needs to run this script once for each Oracle installation you plan to do direct loads to.

    The documentation of Oracle explains how direct path loads work.

    Important

    If you cannot enable this feature on the database, do not continue with these steps. If you enable bulk data loads in Virtual DataPort and the feature of Oracle “direct path loads” is disabled, the performance will be much worse than with the regular method of inserting data into Oracle (i.e. executing INSERT statements).

  2. Download the package Oracle Database Client (you do not need the installer of the full database). Download it from the URLs below:

    For Oracle 19c or older:

    1. If the Denodo server runs on Windows: Instant Client Downloads for Microsoft Windows (x64) 64-bit

    2. If the Denodo server runs on Linux: Instant Client Downloads for Linux x86-64 (64-bit).

  3. Install this package on the host where the Virtual DataPort server runs.

    During the installation, select the Administrator installation type, which includes the program sqlldr. This is necessary to do bulk data loads.

    The default options of the installer are correct.

  4. Edit the JDBC data source and do this:

    1. In the tab Connection, verify that the connection URL is like this:

      jdbc:oracle:thin:@<host>[:<port>]/<service>
jdbc:oracle:thin:@<TNSName>[?TNS_ADMIN=<path>]

      In case of using the syntax jdbc:oracle:thin:@<TNSName>[?TNS_ADMIN=<path>] without specifying the TNS_ADMIN URL parameter, the oracle.net.tns_admin driver property must be present in the data source. This property should indicate the path where Oracle connection files, such as tnsnames.ora, are located.

      The URLs below may cause the bulk loads to fail:

      URLs that may cause the bulk loads to fail
      jdbc:oracle:thin:[<user>/<password>]@<host>[:<port>]:<SID>

    2. Click the tab Read & Write. Then, select Use bulk data load APIs and in the Sqlldr executable location box, enter the path to the file sqlldr. This file is located where the “Oracle Database Client” was installed.

Bulk data load with vectors

Virtual DataPort disables bulk data load for vector fields by default.

When performing a bulk load, the Oracle adapter serializes vector values into a text representation. The size limit for text fields in Oracle is controlled by the MAX_STRING_SIZE parameter. As explained in the official Oracle documentation, the default value for this parameter is STANDARD, which limits VARCHAR2 data types to 4000 bytes.

To enable bulk data load for vector columns, follow these steps:

  1. Configure Oracle: Set the MAX_STRING_SIZE parameter to EXTENDED. This increases the size limit of text fields to 32767 bytes.

  2. Configure Virtual DataPort: Execute the following command to enable bulk load for vector fields:

    SET 'com.denodo.vdb.util.tablemanagement.sql.insertion.oracle.maxStringSize.extended'='true';

Note

If the text representation of a vector exceeds 32767 bytes, the system throws an error indicating that the vector is too long.

Add feedback