Read and write Oracle Database with no Oracle client
Maintainer(s):
krokozyab
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.
| 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
INSTALL oracle_scanner FROM community;
LOAD oracle_scanner;
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_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_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');] |
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_filter_pushdown | Send WHERE predicates on an attached Oracle table to Oracle. Only filters whose Oracle meaning is provably identical are translated; anything else raises. | BOOLEAN | GLOBAL | [] | false |
| 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_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 |
| temp_directory | Set the directory to which temporary files are written. | VARCHAR | GLOBAL | [] | post.db.tmp |