oraduck

853 downloads this week 4 stars Extension descriptor

Read Oracle tables in parallel, load query results into Oracle through the OCI Direct Path API and run statements in Oracle

Compatibility Matrix

This table covers all DuckDB releases that have not reached end-of-life, including LTS releases, and shows the 2 most recent patch releases of each. Older patch releases are not listed, but the extension may be available for them as well.

Installing and Loading

INSTALL oraduck FROM community;
LOAD oraduck;

Example

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

-- Read: attach the Oracle database (read-only); filters and columns are pushed to Oracle
ATTACH '' AS ora (TYPE oraduck, SECRET oracle_test);
SELECT * FROM ora.MY_SCHEMA.MY_TABLE WHERE part_date = DATE '2024-05-07';

-- Or read one table with the table function
SELECT count(*) FROM oraduck_scan('MY_SCHEMA.MY_TABLE', connection := 'oracle_test');

-- Write: 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');

-- Run one SQL statement or PL/SQL block in Oracle (committed; rolled back on error)
CALL oraduck_execute('TRUNCATE TABLE MY_SCHEMA.MY_TABLE', connection := 'oracle_test');

About oraduck

OraDuck reads Oracle tables into DuckDB and loads DuckDB query results into Oracle, both through OCI with one Oracle session per DuckDB thread and no intermediate file. It also runs SQL statements and PL/SQL blocks in Oracle.

Reading:

• ATTACH '' AS ora (TYPE oraduck, SECRET '<secret>') exposes an Oracle database as a read-only catalog (schemas are Oracle users; tables, views and synonyms); oraduck_scan('SCHEMA.TABLE', …) reads one table or view
• projection and filter pushdown: only the selected columns are fetched, and WHERE conditions that Oracle evaluates exactly like DuckDB run in Oracle, which prunes partitions
• parallel sessions over ROWID ranges (from DBA_EXTENTS or DBMS_PARALLEL_EXECUTE) or ORA_HASH(ROWID), with a common AS OF SCN snapshot when the user may read the current SCN
• exact types: NUMBER(p,s) as integers or DECIMAL, DATE/TIMESTAMP, CLOB/BLOB read inline with bounded memory, UTF-8 validated strings

Writing:

• COPY ... TO 'SCHEMA.TABLE' (FORMAT oraduck, CONNECTION '<secret>') through the OCI Direct Path API, the engine behind sqlldr direct=true; all sessions commit together, any error aborts the load
• on Oracle 19c, 1.2 to 3.2 times faster than a CSV export loaded by parallel direct path SQL*Loader
• indexed tables need SKIP_INDEX_MAINTENANCE true, which leaves their indexes UNUSABLE until rebuilt

Running statements:

• CALL oraduck_execute('<statement>', connection := '<secret>') runs one SQL statement or PL/SQL block in its own Oracle session and commits it; rows_affected gives the row count of an INSERT, UPDATE, DELETE or MERGE
• an error rolls the statement back; Ctrl-C and max_execution_time stop it in Oracle and roll it back
• upsert: COPY into a staging table, then MERGE it into the target with oraduck_execute

Options, type mapping, recommended Oracle privileges, benchmarks 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