duckdb-oracle

A DuckDB extension that attaches Oracle databases, modelled after duckdb-postgres but using Oracle via ODPI-C.

Requirements

• Oracle Instant Client (or a full Oracle client installation) in your PATH / LD_LIBRARY_PATH / DYLD_LIBRARY_PATH
• The oracle.duckdb_extension binary for your platform

Quick start

-- Load the extension
LOAD 'oracle';

-- Attach an Oracle database (connect string follows Oracle EZConnect syntax)
ATTACH 'user/password@host:1521/service' AS mydb (TYPE oracle);

-- Optional: attach only a specific schema
ATTACH 'user/password@host:1521/service' AS mydb (TYPE oracle, SCHEMA 'HR');

-- Browse schemas and tables
SHOW ALL TABLES;
SELECT * FROM information_schema.tables WHERE table_catalog = 'mydb';

-- Query Oracle tables directly
SELECT * FROM mydb.hr.employees LIMIT 10;

-- Cross-database joins (DuckDB local + Oracle remote)
SELECT e.employee_id, e.last_name, d.department_name
FROM mydb.hr.employees e
JOIN mydb.hr.departments d ON e.department_id = d.department_id;

Connection string formats

-- Basic
user/password@host/service
user/password@host:1521/service

-- With TNS alias (requires tnsnames.ora or ORACLE_HOME set)
user/password@MYDB

-- Full EZConnect Plus (Oracle 19c+)
user/password@//host:1521/service?connect_timeout=10

Extension options

Option	Default	Description
ora_connection_limit	64	Maximum concurrent Oracle connections in the pool
ora_connection_cache	true	Keep connections alive between queries
ora_debug_show_queries	false	Print every Oracle SQL statement to stdout

-- Show each Oracle query that gets executed
SET ora_debug_show_queries = true;

Debugging with ODPI-C trace logging

For low-level diagnostics (connection issues, protocol errors, bind parameters), enable ODPI-C debug output by setting the environment variable before starting DuckDB:

:: Windows
set DPI_DEBUG_LEVEL=16
duckdb

:: PowerShell
$env:DPI_DEBUG_LEVEL = 16
duckdb

# Linux / macOS
export DPI_DEBUG_LEVEL=16
duckdb

DPI_DEBUG_LEVEL is a bitmask. Common values:

Value	What is logged
1	Errors
4	SQL statements sent to Oracle
8	Bind variable values
16	All of the above + ODPI-C function calls
64	Full trace including memory allocations

The trace is written to stderr. Redirect it to a file with 2>odpi.log.

DML — UPDATE and DELETE

UPDATE and DELETE are supported and work as expected from DuckDB:

UPDATE mydb.hr.employees SET salary = 5000  WHERE employee_id = 100;
UPDATE mydb.hr.employees SET salary = salary * 1.1 WHERE department_id = 10;
DELETE FROM mydb.hr.employees WHERE employee_id = 100;

How row identity works (ROWID)

DuckDB evaluates the SET expressions and WHERE filter on its side and then hands each affected row — along with its computed new values — to the Oracle extension one row at a time. The extension must therefore identify each individual Oracle row precisely so it can write back the correct value.

The extension uses Oracle's ROWID pseudo-column for this:

1. Scan phase — the Oracle scan includes ROWID alongside the projected columns. Each row's 18-character ROWID string is stored in a per-transaction registry; a BIGINT index is written into DuckDB's internal row-ID slot.
2. Write phase — for each row in the result the extension looks up the ROWID string and executes:

   UPDATE schema.table SET col = <value> WHERE ROWID = 'AAABozAAEAAAAGSAAA'
   

Using ROWID is necessary — not just a convenience — because DuckDB evaluates expressions like salary * 1.1 itself before calling the extension. At write-back time the extension only receives the already-computed numeric result (e.g. 5500.00), not the original expression. A WHERE-clause-only approach would reapply the same WHERE to every row in the batch, meaning all rows would get the value that was computed for the last row scanned. ROWID ensures each computed value reaches exactly the row it was read from, matching what Oracle's own UPDATE … SET col = col * 1.1 would produce.

Note: ROWID targets the physical row at scan time. If another session moves the row (e.g. via a table reorganisation or ALTER TABLE … MOVE) between the scan and the write-back, the update will silently skip that row. For normal OLTP workloads this is not a concern.

How it works

• Schema browser — on first ATTACH, a lightweight all_tables UNION ALL all_views query enumerates table/view names per schema. No column data is fetched yet, so the UI loads instantly regardless of schema size.
• Lazy column loading — the first time a table is referenced in a query, ReloadEntry fires two Oracle round-trips (columns + constraints) to fully populate the entry. The result is cached for the session.
• USER_ privilege optimization* — when querying the schema of the connected user, the extension uses user_tab_columns, user_tables, etc. instead of all_* views. These skip Oracle's privilege-check layer and are significantly faster on large databases.
• VIEW detection — views and base tables are distinguished via a UNION ALL (inner-join all_tables OR inner-join all_views). DuckDB catalog entries are tagged with CatalogType::VIEW_ENTRY accordingly.

Building from source

git clone --recurse-submodules https://github.com/rinie/duckdb-oracle
cd duckdb-oracle
cmake -B build -DCMAKE_BUILD_TYPE=Release
cmake --build build --config Release

The compiled extension is at build/release/oracle.duckdb_extension.