---
warning: DO NOT CHANGE THIS MANUALLY, THIS IS GENERATED BY https://github.com/duckdb/community-extensions repository, check README there
title: oracle_scanner
excerpt: |
DuckDB Community Extensions
Read and write Oracle Database with no Oracle client
extension:
name: oracle_scanner
description: Read and write Oracle Database with no Oracle client
version: '0.6.2'
language: C++
build: cmake
license: Apache-2.0
maintainers:
- krokozyab
excluded_platforms: wasm_mvp;wasm_eh;wasm_threads;windows_amd64_mingw
requires_toolchains: vcpkg
repo:
github: krokozyab/quack-oracle
# The commit tagged v0.6.2, built for DuckDB v2.0.0, pinned by SHA so what is
# built cannot move.
ref: aee367eaec18b338ea047dc7e534afe1cd7a4a0b
# DuckDB 1.5.6 stays on v0.4.0, the last release built for that line.
variegata: d4aa50f9bf23ca44b44f0df48fb0065ba751f5be
docs:
hello_world: |
-- The connection lives in a secret, so credentials stay out of SQL text
-- and query history. HOST/PORT/SERVICE_NAME, an Easy Connect string in
-- DSN, or an alias from tnsnames.ora all work.
CREATE SECRET ora (
TYPE oracle,
DSN 'db.example.com:1521/ORCLPDB1',
USER 'scott',
PASSWORD '...'
);
-- Run Oracle SQL and get the rows back as a DuckDB table.
SELECT * FROM oracle_query('ora', 'SELECT id, label FROM app.items');
-- Or attach the schema and use plain DuckDB SQL against it.
ATTACH 'ora' AS o (TYPE oracle_scanner);
SELECT * FROM o.ITEMS WHERE id > 100 ORDER BY id LIMIT 10;
-- Writes go through the same catalog, inside a real transaction.
INSERT INTO o.ITEMS SELECT * FROM 'new_items.parquet';
extended_description: |
`oracle_scanner` reads and writes Oracle Database directly. It speaks
Oracle's own network protocol (TNS/TTC) in C++, so there is no Oracle
Instant Client, OCI, ODPI-C, ODBC, JDBC, Python runtime or helper
process: `INSTALL` downloads one file and nothing else is needed on the
machine or on the Oracle server.
**Connect** with a DuckDB secret: host, port and service name; an Easy
Connect string or a full descriptor in `DSN`; an alias from
`tnsnames.ora` (`CONFIG_DIR`, `TNS_ADMIN`); or an Autonomous Database
wallet ZIP or folder, read in memory and never unpacked to disk. TCPS
always verifies the certificate and host name.
**Read** with `oracle_query` (bind parameters supported) or `ATTACH` a
schema and query its tables and views by name. Filters, `ORDER BY ...
LIMIT`, aggregates, joins between two attached tables, and JSON path
filters are sent to Oracle when its answer is provably the same; large
tables are read through several sessions by ROWID ranges at one SCN;
heavy columns are fetched only for rows that pass the filter.
`EXPLAIN ANALYZE` shows the SQL Oracle ran and the bytes it moved.
**Write** with `INSERT`, `UPDATE`, `DELETE` (including `RETURNING`)
through the attached schema, in DuckDB transactions that commit or roll
back on Oracle, or with `oracle_execute` for batches.
**Call** procedures and functions (`oracle_call_auto`, `oracle_call_named`,
`oracle_arguments`) and run anonymous PL/SQL blocks (`oracle_plsql`).
**Types** include NUMBER (exact), DATE, TIMESTAMP with time zones,
INTERVAL, RAW, BINARY_FLOAT/DOUBLE, CLOB/BLOB, JSON (OSON), XMLTYPE,
BOOLEAN and 23ai VECTOR, with `oracle_vector_search` for nearest
neighbours through Oracle's vector index.
**Snapshots:** `oracle_snapshot` copies the result of a query over the
attached schema into an ordinary DuckDB table, read `AS OF` one SCN, and
`oracle_snapshot_refresh` takes it again.
Full documentation, supported types and limits:
https://github.com/krokozyab/quack-oracle
extension_star_count: 6
extension_star_count_pretty: 6
extension_download_count: 2236
extension_download_count_pretty: 2.2k
image: '/images/community_extensions/social_preview/preview_community_extension_oracle_scanner.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.
| 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
| function_name | function_type | description | comment | examples |
|--------------------------------------|---------------|-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|---------|----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
| oracle_arguments | table | Lists the arguments of every overload of an Oracle procedure or function, with the reason for any argument type this client cannot bind. | NULL | [SELECT * FROM oracle_arguments('demo', 'QUACK_DEMO_ADD');] |
| oracle_call | table | Calls an Oracle procedure whose only argument is an OUT SYS_REFCURSOR, and returns a cursor handle rather than the cursor's rows. | NULL | [SELECT * FROM oracle_call('demo', 'QUACK_DEMO_LIST', 'P_ROWS');] |
| oracle_call_auto | table | Reads an Oracle procedure's or function's signature from the data dictionary and calls it with the given values in argument declaration order. | NULL | [SELECT * FROM oracle_call_auto('demo', 'QUACK_DEMO_ADD', ['2', '3']);] |
| oracle_call_cursors | table | Calls an Oracle procedure whose arguments are the listed OUT SYS_REFCURSORs, and returns a cursor handle for each. | NULL | [SELECT * FROM oracle_call_cursors('demo', 'QUACK_DEMO_LIST', ['P_ROWS']);] |
| oracle_call_implicit | table | Calls an Oracle procedure that takes no arguments and returns a cursor handle for each implicit result set it returns. | NULL | [SELECT * FROM oracle_call_implicit('demo', 'QUACK_DEMO_IMPLICIT');] |
| oracle_call_inout_number | table | Passes a NUMBER, given as text, to an Oracle procedure's only argument, an IN OUT, and returns the updated value as text. | NULL | [SELECT * FROM oracle_call_inout_number('demo', 'QUACK_DEMO_DOUBLE', 'P_VALUE', '21');] |
| oracle_call_inout_varchar | table | Passes a VARCHAR2 to an Oracle procedure's only argument, an IN OUT, and returns the updated value. | NULL | [SELECT * FROM oracle_call_inout_varchar('demo', 'QUACK_DEMO_SHOUT', 'P_TEXT', 'quack');] |
| oracle_call_named | table | Calls an Oracle procedure with arguments whose names, directions, types and values are given explicitly, and returns its OUT values and cursor handles. | NULL | [SELECT * FROM oracle_call_named('demo', 'QUACK_DEMO_GREET', [{'name': 'P_NAME', 'direction': 'in', 'type': 'varchar', 'value': 'world'}, {'name': 'P_GREETING', 'direction': 'out', 'type': 'varchar', 'value': NULL}]);] |
| oracle_call_named_function | table | Calls an Oracle function with the given return type and explicitly described arguments, and returns its result and OUT values. | NULL | [SELECT * FROM oracle_call_named_function('demo', 'QUACK_DEMO_ADD', 'number', [{'name': 'P_A', 'direction': 'in', 'type': 'number', 'value': '2'}, {'name': 'P_B', 'direction': 'in', 'type': 'number', 'value': '3'}]);] |
| oracle_call_named_transaction | table | Calls a NUMBER/VARCHAR scalar procedure on the caller transaction, with no adapter COMMIT. The procedure itself must preserve transaction ownership. | NULL | [SELECT * FROM oracle_call_named_transaction('ora', 'QUACK_DEMO_MOTTO', [{name:'P_TEXT',direction:'out',type:'varchar',value:NULL::VARCHAR}]);] |
| oracle_call_number | table | Calls an Oracle function that takes no arguments and returns NUMBER, and returns its result as text. | NULL | [SELECT * FROM oracle_call_number('demo', 'QUACK_DEMO_ANSWER');] |
| oracle_call_number_args | table | Calls an Oracle function that returns NUMBER with named IN arguments taken from a STRUCT, and returns its result as text. | NULL | [SELECT * FROM oracle_call_number_args('demo', 'QUACK_DEMO_ADD', {P_A: 2, P_B: 3});] |
| oracle_call_out_number | table | Calls an Oracle procedure whose only argument is a NUMBER OUT, and returns that value as text. | NULL | [SELECT * FROM oracle_call_out_number('demo', 'QUACK_DEMO_COUNT_DEPARTMENTS', 'P_COUNT');] |
| oracle_call_out_varchar | table | Calls an Oracle procedure whose only argument is a VARCHAR2 OUT, and returns that value. | NULL | [SELECT * FROM oracle_call_out_varchar('demo', 'QUACK_DEMO_MOTTO', 'P_TEXT');] |
| oracle_close_call | table | Closes the remaining registered cursors of the whole call a cursor handle belongs to, and returns closed. | NULL | [SELECT * FROM oracle_close_call(getvariable('oracle_close_handle'));] |
| oracle_cursor | table | Consumes a cursor handle created earlier on the same DuckDB connection and returns the cursor's rows. | NULL | [SELECT * FROM oracle_cursor(getvariable('oracle_example_handle'));] |
| oracle_execute | table | Runs one Oracle INSERT, UPDATE or DELETE with optional positional or named bind parameters, commits it in Oracle, and returns affected_rows. | NULL | [SELECT * FROM oracle_execute('demo', 'INSERT INTO QUACK_DEMO_LOG (LABEL, AMOUNT) VALUES (:label, :amount)', {'label': 'example', 'amount': 1.5});] |
| oracle_execute_admin | table | Runs one administrative DDL command on a dedicated session, only outside an explicit DuckDB transaction. | NULL | [SELECT * FROM oracle_execute_admin('demo', 'CREATE TABLE QUACK_DEMO_SCRATCH (ID NUMBER)');] |
| oracle_execute_composite_transaction | table | Runs up to 16 INSERT, UPDATE or DELETE statements in one round trip on the attached Oracle catalog's transaction, with Oracle checking each statement's exact row count and no COMMIT. | NULL | [SELECT * FROM oracle_execute_composite_transaction('ora', ['INSERT INTO QUACK_DEMO_LOG (LABEL) VALUES (:label)', 'DELETE FROM QUACK_DEMO_LOG WHERE LABEL = :label'], {'label': 'composite example'}, [1, 1]);] |
| oracle_execute_many | table | Runs one Oracle INSERT, UPDATE or DELETE once for each bind record in a list using array DML, commits it in Oracle, and returns the total affected_rows. | NULL | [SELECT * FROM oracle_execute_many('demo', 'INSERT INTO QUACK_DEMO_LOG (LABEL, AMOUNT) VALUES (:label, :amount)', [{'label': 'a', 'amount': 1.5}, {'label': 'b', 'amount': 2.5}]);] |
| oracle_execute_many_transaction | table | Executes bounded DML on the caller transaction. Exact per-row counts default to scalar execution; exact_counts=false uses native array DML and returns one explicitly aggregate count with NULL row_index. | NULL | [SELECT * FROM oracle_execute_many_transaction('ora', 'DELETE FROM QUACK_DEMO_LOG WHERE LOG_ID = :id', [{id: 1}]);] |
| oracle_execute_transaction | table | Runs one INSERT, UPDATE or DELETE in the attached Oracle catalog's transaction, without an independent COMMIT. | NULL | [SELECT * FROM oracle_execute_transaction('ora', 'INSERT INTO QUACK_DEMO_LOG (LABEL) VALUES (:label)', {'label': 'example'});] |
| oracle_plsql | table | Runs one anonymous PL/SQL block (BEGIN ... END; or DECLARE ... END;) on its own session and commits it when it succeeds. Binds are STRUCT(name, direction, type, value) matched to the block's :name placeholders; the OUT and IN OUT ones come back as rows, a REF CURSOR as a handle for oracle_cursor. | NULL | [SELECT * FROM oracle_plsql('demo', 'BEGIN :greeting := ''hello '' \|\| :who; END;', [{name:'greeting',direction:'out',type:'varchar',value:NULL::VARCHAR}, {name:'who',direction:'in',type:'varchar',value:'world'}]);] |
| oracle_plsql_transaction | table | Runs one anonymous PL/SQL block on the caller transaction, with no adapter COMMIT. The block must not commit, run DDL or use an autonomous transaction; REF CURSOR binds are refused. | NULL | [SELECT * FROM oracle_plsql_transaction('ora', 'BEGIN :n := 1 + 1; END;', [{name:'n',direction:'out',type:'number',value:NULL::VARCHAR}]);] |
| oracle_query | table | Runs an Oracle SELECT query with optional positional or named bind parameters and returns its rows. | NULL | [SELECT * FROM oracle_query('demo', 'SELECT :1 AS value FROM dual', [42]);] |
| oracle_query_transaction | table | Reads through the attached Oracle catalog's current transaction, pinning one session until COMMIT or ROLLBACK. | NULL | [SELECT * FROM oracle_query_transaction('ora', 'SELECT 1 FROM dual');] |
| oracle_query_transaction_typed | table | Reads through the attached Oracle catalog's current transaction with caller-declared result columns, so binding runs nothing in Oracle and the query runs once against a describe that must match them. | NULL | [SELECT * FROM oracle_query_transaction_typed('ora', 'SELECT DEPARTMENT_ID, DEPARTMENT_NAME FROM QUACK_DEMO_DEPARTMENTS ORDER BY DEPARTMENT_ID', ['DEPARTMENT_ID', 'DEPARTMENT_NAME'], ['BIGINT', 'VARCHAR']);] |
| oracle_scan_parallel | table | Reads one Oracle table through several sessions in ranges of an integral NUMBER key, all at a single SCN. | NULL | [SELECT * FROM oracle_scan_parallel('demo', 'QUACK_DEMO_DEPARTMENTS', 'DEPARTMENT_ID', shards := 4);] |
| oracle_scanner_io_deadline_version | scalar | Returns the version of the IO deadline contract this build offers to companion extensions. | NULL | [oracle_scanner_io_deadline_version()] |
| oracle_scanner_version | scalar | Returns the version of this oracle_scanner build. | NULL | [oracle_scanner_version()] |
| oracle_snapshot | table | Builds a local DuckDB table from a query over attached Oracle tables, reading every table of that catalog AS OF one SCN, and records the SCN and the query in the table's comment so it can be listed and refreshed. | NULL | [SELECT * FROM oracle_snapshot('ora', 'departments_snapshot', 'SELECT * FROM ora.main.QUACK_DEMO_DEPARTMENTS WHERE department_id > 10');] |
| oracle_snapshot_refresh | table | Runs the query of a table made by oracle_snapshot again, at a new SCN, and replaces its rows. | NULL | [SELECT * FROM oracle_snapshot_refresh('departments_snapshot');] |
| oracle_snapshots | table | Lists the local tables made by oracle_snapshot, in every attached database: where each is, the catalog, the SCN, when it was taken, the row count and the query. | NULL | [SELECT * FROM oracle_snapshots();] |
| oracle_transaction_status | table | Returns local structured status of the current or most recent Oracle transaction; opens no remote session. | NULL | [SELECT * FROM oracle_transaction_status('ora');] |
| oracle_vector_search | table | Returns the k rows of an Oracle 23ai table whose VECTOR column is nearest to the query vector, with their distance, ranked by Oracle's VECTOR_DISTANCE so a vector index is used when there is one. | NULL | [SELECT * FROM oracle_vector_search('demo', 'DOCS', 'EMBEDDING', [0.1, 0.2, 0.3]::FLOAT[3], k := 5, metric := 'COSINE');] |
### Overloaded Functions
This extension does not add any function overloads.
### Added Types
This extension does not add any types.
### Added Settings
| name | description | input_type | scope | aliases | typed_value |
|------------------------------|-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|------------|--------|---------|-------------|
| oracle_aggregate_pushdown | Let Oracle compute COUNT, SUM, MIN, MAX and GROUP BY over an attached table (numbers, dates and timestamps) so the groups travel and not the rows. | BOOLEAN | GLOBAL | [] | true |
| oracle_consistent_read | Read every attached table of one statement at one Oracle change number (AS OF SCN), taken when its first scan starts, so a join or a union of scans sees one moment. Needs flashback on the tables; a transaction that has written reads its own session instead. | BOOLEAN | GLOBAL | [] | false |
| oracle_deferred_columns | Read CLOB, BLOB, JSON, VECTOR and XMLTYPE columns only for the rows that pass a filter DuckDB applies itself, by ROWID at the same change number as the rows. | BOOLEAN | GLOBAL | [] | true |
| oracle_filter_pushdown | Send WHERE predicates on an attached Oracle table to Oracle. Only filters whose Oracle meaning is provably identical are translated; the rest are applied by the scan to the rows Oracle returns. | BOOLEAN | GLOBAL | [] | true |
| oracle_join_pushdown | Let Oracle join two attached tables of the same database (inner joins on numbers, dates and timestamps) so the join's result travels and not both tables. | BOOLEAN | GLOBAL | [] | true |
| oracle_parallel_scan | How many Oracle sessions one scan of an attached table may read through at the same SCN, each taking ROWID ranges cut at extent boundaries. -1 (the default) chooses: as many as DuckDB has threads, up to 8, for a table whose statistics say 500,000 rows or more, else one. 0 or 1 reads with one session. A table under 2,048 blocks, a sample, a transaction that has written, and a database that will not give out an SCN are read with one. | BIGINT | GLOBAL | [] | -1 |
| oracle_session_pool_size | How many authenticated Oracle sessions one DuckDB connection keeps for reuse, per secret. 0 opens a fresh session for every statement. | BIGINT | GLOBAL | [] | 4 |
| oracle_top_n_pushdown | Let Oracle rank and cut an ORDER BY ... LIMIT over an attached table (numbers, dates and timestamps) so only the first rows are read. DuckDB still sorts what comes back. | BOOLEAN | GLOBAL | [] | true |
| oracle_transaction_pool_size | How many idle Oracle sessions an attached catalog keeps between its transactions, so a transaction need not connect and authenticate again. Read at ATTACH; 0 disables reuse. | BIGINT | GLOBAL | [] | 2 |
| oracle_unconstrained_number | What a NUMBER with no declared precision (plain NUMBER, SUM(x), count(*)) becomes: 'varchar' (the default, exact), 'double', or 'decimal(precision, scale)'. Read when a query is bound or a table is first used; the lossy choices round what does not fit. | VARCHAR | GLOBAL | [] | varchar |
| temp_directory | Set the directory to which temporary files are written. | VARCHAR | GLOBAL | [] | post.db.tmp |