---
warning: DO NOT CHANGE THIS MANUALLY, THIS IS GENERATED BY https://github.com/duckdb/community-extensions repository, check README there
title: oraduck
excerpt: |
  DuckDB Community Extensions
  Read Oracle tables in parallel, load query results into Oracle through the OCI Direct Path API and run statements in Oracle

extension:
  name: oraduck
  description: Read Oracle tables in parallel, load query results into Oracle through the OCI Direct Path API and run statements in Oracle
  version: '0.5.0'
  language: C++
  build: cmake
  license: MIT
  maintainers:
    - hugues31
  # Oracle Instant Client, loaded at run time, needs glibc and exists for x86_64 and arm64 Linux and
  # x86_64 Windows; macOS (Instant Client 23 only on Apple Silicon) is not tested yet
  excluded_platforms: "wasm_mvp;wasm_eh;wasm_threads;windows_amd64_mingw;windows_arm64;linux_amd64_musl;osx_amd64;osx_arm64"

repo:
  github: hugues31/oraduck
  ref: e8f04ef00b37851a5f291e0b411365c9377dcf13

install_notes: |
  **Oracle Instant Client** (Basic or Basic Light, version 19 or later) is required to talk to Oracle.
  OraDuck loads it at run time: put its directory in `LD_LIBRARY_PATH` (Linux) or `PATH` (Windows)
  before starting DuckDB. On Linux, Instant Client also needs `libaio` and `libnsl`.
  Without it, the extension loads and reports what is missing.

docs:
  hello_world: |
    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');
  extended_description: |
    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

extension_star_count: 4
extension_star_count_pretty: 4
extension_download_count: 853
extension_download_count_pretty: 853
image: '/images/community_extensions/social_preview/preview_community_extension_oraduck.png'
layout: community_extension_doc
---
### Compatibility Matrix

This table covers all DuckDB releases that have not reached [end-of-life](https://duckdb.org/release_calendar), 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.

<div class="extension_compatibility_table"></div>

| Platform | 1.4.4 | 1.4.5 | 1.5.5 | 1.5.6 | 2.0.0 |
| :--- | :---: | :---: | :---: | :---: | :---: |
| Linux AMD64 | ❌ | ❌ | ✅ | ✅ | ✅ |
| Linux ARM64 | ❌ | ❌ | ✅ | ✅ | ✅ |
| macOS AMD64 | ❌ | ❌ | ❌ | ❌ | ❌ |
| macOS ARM64 | ❌ | ❌ | ❌ | ❌ | ❌ |
| Windows AMD64 | ❌ | ❌ | ✅ | ✅ | ✅ |
| Windows ARM64 | ❌ | ❌ | ❌ | ❌ | ❌ |
| WebAssembly | ❌ | ❌ | ❌ | ❌ | ❌ |

### Installing and Loading
```sql
INSTALL {{ page.extension.name }} FROM community;
LOAD {{ page.extension.name }};
```

{% if page.docs.hello_world %}
### Example
```sql
{{ page.docs.hello_world }}```
{% endif %}

{% if page.docs.extended_description %}
### About {{ page.extension.name }}
{{ page.docs.extended_description }}
{% endif %}
### Added Functions

<div class="extension_functions_table"></div>

|    function_name    | function_type | description | comment | examples |
|---------------------|---------------|-------------|---------|----------|
| oraduck_execute     | table         | NULL        | NULL    |          |
| oraduck_oci_version | scalar        | NULL        | NULL    |          |
| oraduck_scan        | table         | NULL        | NULL    |          |

### Overloaded Functions

<div class="extension_functions_table"></div>

This extension does not add any function overloads.

### Added Types

<div class="extension_types_table"></div>

This extension does not add any types.

### Added Settings

<div class="extension_settings_table"></div>

|      name      |                       description                       | input_type | scope  | aliases | typed_value |
|----------------|---------------------------------------------------------|------------|--------|---------|-------------|
| temp_directory | Set the directory to which temporary files are written. | VARCHAR    | GLOBAL | []      | post.db.tmp |