Query Oracle Fusion from DuckDB through BI Publisher, with SSO, an ATTACH-able catalog and cached metadata
Installing and Loading
INSTALL fusion_scanner FROM community;
LOAD fusion_scanner;
Example
-- The connection lives in a secret, so credentials stay out of SQL text
-- and query history.
CREATE SECRET fusion (
TYPE oracle_fusion,
ENDPOINT 'https://<your-fusion-host>',
REPORT_PATH '/Custom/Financials/RP_ARB.xdo',
USERNAME '<user>',
PASSWORD '<password>'
);
SELECT * FROM oracle_fusion_query(
'SELECT currency_code, name FROM FND_CURRENCIES_TL WHERE rownum < 10'
);
-- Or attach it and query tables by name.
ATTACH 'fusion' AS f (TYPE oracle_fusion);
SELECT * FROM f.main.GL_JE_HEADERS LIMIT 10;
About fusion_scanner
Oracle Fusion exposes no SQL endpoint. This extension reaches its database
the way Oracle leaves open: a SELECT is wrapped in a SOAP runReport call
to BI Publisher, a report runs it through dbms_xmlgen, and the rows come
back as XML.
That detour requires a report deployed on the Fusion side — DM_ARB.xdm
and RP_ARB.xdo, taking a p_sql parameter. The extension cannot work
against a stock instance. The repository's README links the catalog
archives, which live in the ofjdbc repository.
Read-only by construction: BI Publisher cannot write.
Credentials live in a DuckDB secret. Where the instance is behind
corporate single sign-on, PROVIDER browser opens a browser, lets the
person sign in however their organisation requires, and collects the token
Fusion issues to that session — no client secret, no registered
application, and no password in the process. Tokens are kept in memory
only.
ATTACH exposes Fusion's tables as an ordinary read-only catalog, typed from Fusion's own dictionary. Attaching costs no request; resolving a table costs that table's columns. Metadata is cached on disk between sessions, because every dictionary read is a SOAP call measured in seconds.
Large results are paged by rewriting the statement, and transient
failures are retried with exponential backoff. Requests to one host are
serialised: each runReport opens a BI Publisher session that the server
holds on to, and a handful of parallel scans leaves hundreds behind.
Added Functions
| function_name | function_type | description | comment | examples |
|---|---|---|---|---|
| fusion_scanner_cache_invalidate | table | Removes an instance's dictionary from the metadata cache, or only one table's columns when table_name is given. | NULL | [SELECT * FROM fusion_scanner_cache_invalidate(table_name := 'AP_INVOICES_ALL')] |
| fusion_scanner_cache_status | table | Reports where the Oracle Fusion metadata cache lives, whether it can be written, and how much of an instance's dictionary it holds. | NULL | [SELECT * FROM fusion_scanner_cache_status()] |
| fusion_scanner_cache_warm | table | Fetches into the metadata cache the columns of Fusion tables whose names match pattern, a case-insensitive LIKE, up to max_tables of them (200 by default, 0 for no limit). | NULL | [SELECT * FROM fusion_scanner_cache_warm(pattern := 'AP_%')] |
| fusion_scanner_sso_login | table | Signs in to Oracle Fusion through a browser window and caches the token for the host, reusing a still-valid token unless force := true. | NULL | [SELECT * FROM fusion_scanner_sso_login()] |
| fusion_scanner_sso_logout | table | Forgets the sign-in token this process holds for the Fusion host; the browser profile, and the session it keeps, is left alone. | NULL | [SELECT * FROM fusion_scanner_sso_logout()] |
| fusion_scanner_sso_status | table | Reports whether a sign-in token is cached for the Fusion host and when it expires, without ever returning the token itself. | NULL | [SELECT * FROM fusion_scanner_sso_status()] |
| fusion_scanner_version | scalar | Returns the loaded fusion_scanner version and the date and time it was built. | NULL | [fusion_scanner_version()] |
| oracle_fusion_columns | table | Lists the columns of a Fusion table or view with their Oracle type, the DuckDB type they map to, and whether that mapping can lose values. | NULL | [SELECT * FROM oracle_fusion_columns('AP_INVOICES_ALL')] |
| oracle_fusion_query | table | Runs a SQL query in Oracle Fusion through the BI Publisher report and returns its rows, paging the result and inferring column types from the first page. | NULL | [SELECT * FROM oracle_fusion_query('SELECT invoice_num, invoice_amount FROM ap_invoices_all FETCH FIRST 10 ROWS ONLY')] |
| oracle_fusion_tables | table | Lists the tables and views in Oracle Fusion's dictionary, caching the list on disk after the first call. | NULL | [SELECT * FROM oracle_fusion_tables()] |
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 |
|---|---|---|---|---|
| fusion_scanner_filter_pushdown | Send WHERE predicates on an attached Oracle Fusion table to Fusion. Off by default: DuckDB removes a pushed filter from the plan, so a predicate that cannot be translated exactly must fail the query rather than be approximated. | BOOLEAN | GLOBAL | [] |
| fusion_scanner_metadata_page_size | Rows per page when listing Oracle Fusion's dictionary. Lower it if a listing keeps stopping short of the instance's own table count. | UBIGINT | GLOBAL | [] |
| fusion_scanner_stable_paging | Give a paged statement an order, so that its pages partition the result instead of sampling it: an attached table by its primary key (or ROWID), a query by every column it returns. On by default; turn it off only if Oracle refuses the ordering, and expect pages to repeat and skip rows when you do. | BOOLEAN | GLOBAL | [] |