Downloads 157this week

GitHub stars 0

Extension descriptor (YAML)

Fast COPY of query results into Oracle through the OCI Direct Path API

Installing and Loading

INSTALL oraduck FROM community;
LOAD oraduck;

Example

CREATE SECRET oracle_test (TYPE oraduck, USER 'scott', PASSWORD 'tiger', DSN 'dbhost:1521/ORCLPDB1');

-- The target table must exist in Oracle; columns are matched by name
COPY (SELECT id, amount, operation_date, label FROM source)
TO 'MY_SCHEMA.MY_TABLE' (FORMAT oraduck, CONNECTION 'oracle_test');

About oraduck

OraDuck loads the result of a DuckDB query into an existing Oracle table through the OCI Direct Path API, the engine behind sqlldr direct=true: one Oracle session per DuckDB thread, native encodings (NUMBER, DATE, TIMESTAMP, BINARY_FLOAT/DOUBLE) and no intermediate file.

• COPY ... TO 'SCHEMA.TABLE' (FORMAT oraduck, CONNECTION '<secret>'), with an oraduck secret (USER, PASSWORD, DSN in EZConnect form)
• all sessions commit together at the end; any error aborts the whole load
• on Oracle 19c, 1.2 to 3.2 times faster than a CSV export loaded by parallel direct path SQL*Loader, and 6 to 500 times faster than loading with INSERT statements from DuckDB (quack-oracle, duckdb-oracle), from 100k x 5 to 10M x 50 rows x columns

The target table must exist. Parallel direct path loads cannot maintain indexes: an indexed table needs SKIP_INDEX_MAINTENANCE true (like sqlldr skip_index_maintenance=true), which leaves its indexes UNUSABLE until they are rebuilt. Benchmark, type mapping and limitations: https://github.com/hugues31/oraduck

Added Functions

Overloaded Functions

This extension does not add any function overloads.

Added Types

This extension does not add any types.

Added Settings

This extension does not add any settings.