ERPL Extension Suite — API Reference

Complete function reference for SAP data integration in DuckDB

DuckDB Version: >= v1.2.0 Extensions: erpl_rfc, erpl_bics, erpl_odp

Overview

The ERPL extension suite brings SAP data integration directly into DuckDB. It enables analysts, data engineers, and scientists to query SAP ERP tables, SAP BW cubes, and replicate data using familiar SQL — no middleware, no data movement.

Key Benefits:

SQL-native: All operations expressed as SQL functions, pragmas, and secrets
Zero middleware: Connect directly to SAP via RFC and BICS protocols
Parallel extraction: Multi-threaded reads for large tables
Virtual catalog: ATTACH SAP systems as DuckDB databases

Extension	Purpose
erpl_rfc	Core SAP RFC connectivity — table reads, function calls, metadata
erpl_bics	SAP BW queries via BICS — cubes, hierarchies, lineage
erpl_odp	SAP ODP data extraction and replication
(SSH tunnelling)	moved to the erpl_tunnel extension

Quick Start

-- 1. Install and load
INSTALL 'erpl' FROM 'http://get.erpl.io';
LOAD 'erpl';

-- 2. Create SAP connection secret
CREATE SECRET my_sap (
    TYPE sap_rfc,
    ASHOST 'sap-server.example.com',
    SYSNR '00',
    CLIENT '100',
    USER 'DEVELOPER',
    PASSWD 'secret',
    LANG 'EN'
);

-- 3. Read a table
SELECT * FROM sap_read_table('SFLIGHT');

-- 4. Attach SAP as a virtual database
ATTACH '' AS sap (TYPE sap_rfc);
SELECT * FROM sap."SFLIGHT" WHERE CARRID = 'LH';

-- 5. Query SAP BW
SELECT * FROM sap_bics_show_cubes();

Quick Reference

Function	Purpose	Example
`sap_read_table`	Read SAP table data	`SELECT * FROM sap_read_table('SFLIGHT')`
`sap_rfc_invoke`	Call any RFC function	`SELECT * FROM sap_rfc_invoke('STFC_CONNECTION', {'REQUTEXT': 'Hi'})`
`sap_show_tables`	Search SAP tables	`SELECT * FROM sap_show_tables(TABLENAME='FLIGHT')`
`sap_describe_fields`	Get table field metadata	`SELECT * FROM sap_describe_fields('SFLIGHT')`
`sap_rfc_authorizations`	List RFC modules each function uses (for S_RFC)	`SELECT * FROM sap_rfc_authorizations()`
`sap_bics_show_cubes`	List BW cubes	`SELECT * FROM sap_bics_show_cubes()`
`sap_bics_hierarchy`	Extract BW hierarchy	`SELECT * FROM sap_bics_hierarchy('MY_HIER')`
`sap_bics_set_char_prop`	AO-style char property (Display/Sort/Totals)	`SELECT * FROM sap_bics_set_char_prop('q1', '0CNTRY', 'DISPLAY', 'TEXT')`
`sap_odp_read_full`	Extract ODP data (full snapshot)	`SELECT * FROM sap_odp_read_full('BW', 'MY_ODP')`
`sap_odp_read_delta`	Extract ODP data (incremental delta)	`SELECT * FROM sap_odp_read_delta('BW', 'MY_ODP', 'MY_PIPELINE')`
`sap_odp_get_last_modified`	Last-modified timestamp of an ODP object (cheap delta probe)	`SELECT * FROM sap_odp_get_last_modified('ABAP_CDS', 'MY_CDS$E')`
`sap_odp_get_subscriptions`	List subscriptions for one ODP object	`SELECT * FROM sap_odp_get_subscriptions('ABAP_CDS', 'MY_CDS$E')`
`ATTACH`	Mount SAP as database	`ATTACH '' AS sap (TYPE sap_rfc)`

erpl_rfc — SAP RFC Connectivity

Data Access

`sap_read_table(table_name [, THREADS, COLUMNS, FILTER, ...])`

Read data from an SAP table or CDS view. Supports projection pushdown, filter pushdown, and parallel reads.

Parameter	Type	Default	Description
`table_name`	VARCHAR	required	SAP table or CDS view name
`THREADS`	UINTEGER	0	Number of parallel read threads
`COLUMNS`	LIST(VARCHAR)	all	Columns to retrieve
`FILTER`	VARCHAR	—	SAP WHERE clause filter
`fetch_size`	UINTEGER	`erpl_rfc_fetch_size`	Concurrent result rows per round-trip; transport only, never changes the rows returned
`partitions`	UINTEGER	`erpl_rfc_partitions`	Read this many row ranges in parallel. Same rows, unspecified order
`MAX_ROWS`	UINTEGER	0 (all)	Maximum rows to return
`READ_TABLE_FUNCTION`	VARCHAR	`'RFC_READ_TABLE'`	RFC function to use (see note)
`READ_TABLE_DELIMITER`	VARCHAR	—	Delimiter for TABLE2 variants
`SECRET`	VARCHAR	—	Named secret to use

Supported READ_TABLE_FUNCTION values: RFC_READ_TABLE, /BODS/RFC_READ_TABLE, /SAPDS/RFC_READ_TABLE, /BODS/RFC_READ_TABLE2, /SAPDS/RFC_READ_TABLE2

-- Basic read
SELECT * FROM sap_read_table('SFLIGHT');

-- With DuckDB filter pushdown (automatically pushed to SAP)
SELECT * FROM sap_read_table('SFLIGHT') WHERE CARRID = 'LH';

-- SAP-side filter, column selection, parallel threads, row limit
SELECT * FROM sap_read_table('SFLIGHT',
    COLUMNS=['CARRID', 'CONNID', 'FLDATE'],
    FILTER='CARRID = ''LH''',
    THREADS=4,
    MAX_ROWS=1000
);

-- Using a named secret
SELECT * FROM sap_read_table('SFLIGHT', SECRET='my_sap');

`sap_rfc_invoke(function_name, ...args [, path, secret])`

Invoke any SAP RFC function module. Accepts variable arguments as STRUCT or scalar values.

Parameter	Type	Default	Description
`function_name`	VARCHAR	required	RFC function module name
`...args`	ANY (varargs)	—	Function parameters as structs/values
`path`	VARCHAR	—	Path to select specific output (e.g. `'/RFCTABLE'`)
`secret`	VARCHAR	—	Named secret to use

-- Simple function call
SELECT * FROM sap_rfc_invoke('STFC_CONNECTION', {'REQUTEXT': 'Hello SAP'});

-- Select specific output table via path
SELECT * FROM sap_rfc_invoke('BAPI_FLIGHT_GETLIST',
    {'AIRLINE': 'LH'},
    path='/FLIGHT_LIST'
);

Discovery & Metadata

`sap_show_tables([TABLENAME, TEXT, THREADS])`

Search for SAP tables and views. No positional arguments.

Parameter	Type	Default	Description
`TABLENAME`	VARCHAR	`'%'`	Table name pattern (supports `*` and `%` wildcards)
`TEXT`	VARCHAR	`'%'`	Description text pattern
`THREADS`	UINTEGER	0	Parallel read threads

Returns: table_name, text, class (VIEW, TRANSP, POOL, CLUSTER)

SELECT * FROM sap_show_tables(TABLENAME='*FLIGHT*');
SELECT * FROM sap_show_tables(TEXT='%booking%');

`sap_describe_fields(table_name [, LANGUAGE])`

Get field metadata for an SAP table or view.

Parameter	Type	Default	Description
`table_name`	VARCHAR	required	Table/view name
`LANGUAGE`	VARCHAR	`'E'`	Language for descriptions

Returns: pos, is_key, field, text, sap_type, length, decimals, check_table, ref_table, ref_field, language

SELECT field, is_key, sap_type, length FROM sap_describe_fields('SFLIGHT');

`sap_rfc_show_function([FUNCNAME, GROUPNAME, LANGUAGE])`

Search for RFC function modules by name or group.

Parameter	Type	Default	Description
`FUNCNAME`	VARCHAR	`'*'`	Function name pattern (wildcard `*`)
`GROUPNAME`	VARCHAR	—	Function group name pattern
`LANGUAGE`	VARCHAR	—	Language for descriptions

SELECT * FROM sap_rfc_show_function(FUNCNAME='BAPI_FLIGHT*');
SELECT * FROM sap_rfc_show_function(GROUPNAME='RSBOLAP_BICS');

`sap_rfc_show_groups([GROUPNAME, LANGUAGE])`

Search for RFC function groups.

Parameter	Type	Default	Description
`GROUPNAME`	VARCHAR	`'*'`	Group name pattern (wildcard `*`)
`LANGUAGE`	VARCHAR	—	Language for descriptions

Returns: name, text

SELECT * FROM sap_rfc_show_groups(GROUPNAME='RSBOLAP*');

`sap_rfc_describe_function(function_name)`

Get detailed metadata about an RFC function module (parameters, types, structures).

Parameter	Type	Default	Description
`function_name`	VARCHAR	required	Function module name

SELECT * FROM sap_rfc_describe_function('STFC_CONNECTION');

`sap_rfc_authorizations()`

List which SAP RFC function modules each ERPL function invokes, so an SAP admin can scope the S_RFC authorization object for the ERPL service user to least privilege. This is a static reference — it needs no SAP connection (no secret required) and makes no RFC calls.

Returns: extension, duckdb_function, rfc_function_module, invocation, purpose

invocation is one of: always (called every time), fallback (one of a runtime-selected, capability-dependent chain — e.g. the RFC_READ_TABLE variants), optional (attempted, skipped gracefully on failure), metadata (a secondary DDIC/describe call), or user-specified (the FM is the one you pass to sap_rfc_invoke). Note: opening a connection and sap_rfc_ping use the SDK directly and invoke no function module.

-- All RFC modules to authorize for the whole ERPL suite
SELECT DISTINCT rfc_function_module FROM sap_rfc_authorizations()
WHERE rfc_function_module NOT IN ('<user-specified>', '<none>') ORDER BY 1;

-- Just what sap_read_table needs
SELECT rfc_function_module, invocation, purpose
FROM sap_rfc_authorizations() WHERE duckdb_function = 'sap_read_table';

Connection & Diagnostics

`PRAGMA sap_rfc_ping([secret])`

Test SAP RFC connection. Returns 'PONG' on success.

Parameter	Type	Default	Description
`secret`	VARCHAR	—	Named secret to use

PRAGMA sap_rfc_ping;
PRAGMA sap_rfc_ping(secret='my_sap');

`sap_rfc_backend()`

Which implementation is serving SAP RFC calls: 'nwrfc' or 'proto'. Resolves the backend if that has not happened yet. See Selecting the RFC backend.

SELECT sap_rfc_backend();

`PRAGMA sap_rfc_set_trace_level(level)`

Set SAP NetWeaver RFC SDK trace level.

Parameter	Type	Description
`level`	INTEGER	0=Off, 1=Brief, 2=Verbose, 3=Detailed, 4=Full

PRAGMA sap_rfc_set_trace_level(2);

`PRAGMA sap_rfc_set_trace_dir(directory)`

Set directory for SAP RFC SDK trace files.

PRAGMA sap_rfc_set_trace_dir('/tmp/sap_traces');

`PRAGMA sap_rfc_set_maximum_trace_file_size(size, unit)`

Set maximum size for SAP RFC trace files.

Parameter	Type	Description
`size`	INTEGER	File size value
`unit`	ENUM	`'M'` (megabytes) or `'G'` (gigabytes)

PRAGMA sap_rfc_set_maximum_trace_file_size(100, 'M');

`PRAGMA sap_rfc_set_maximum_stored_trace_files(count)`

Set maximum number of trace files to keep.

PRAGMA sap_rfc_set_maximum_stored_trace_files(10);

`PRAGMA sap_rfc_set_ini_path(path)`

Set path to SAP RFC INI configuration file (sapnwrfc.ini).

PRAGMA sap_rfc_set_ini_path('/etc/sap/sapnwrfc.ini');

`PRAGMA sap_rfc_reload_ini_file`

Reload the SAP RFC INI configuration file.

PRAGMA sap_rfc_reload_ini_file;

ATTACH — Virtual SAP Catalog

Attach an SAP system as a virtual DuckDB database. Tables appear as views backed by sap_read_table().

-- Attach with default secret
ATTACH '' AS sap (TYPE sap_rfc);
SELECT * FROM sap."SFLIGHT";

-- Attach with named secret
ATTACH '' AS sap (TYPE sap_rfc, SECRET 'my_sap');

-- Restrict to specific tables
ATTACH '' AS sap (TYPE sap_rfc, TABLES 'SFLIGHT,SPFLI,SCARR');

-- Scope to tables matching a glob pattern (resolved against the dictionary at ATTACH)
ATTACH '' AS sap (TYPE sap_rfc, TABLES '/DMO/*,Z*');
SHOW TABLES FROM sap;   -- lists the resolved set

-- Detach
DETACH sap;

Option	Type	Description
`TYPE`	—	Must be `sap_rfc`
`SECRET`	VARCHAR	Named secret for SAP connection
`TABLES`	VARCHAR	Comma-separated list of exact table names and/or glob patterns (`*`, `?`) to expose. Empty = on-demand lookup.

SHOW TABLES and table enumeration. A SAP system exposes tens of thousands of tables, so an attached catalog does not list them all. SHOW TABLES FROM <catalog> (and information_schema.tables) reflect only the tables named or matched by TABLES:

With TABLES (exact names and/or patterns) → those tables are listed and queryable; tables outside the set are not accessible through the catalog.
Without TABLES → tables are resolved on demand when referenced by name (SELECT * FROM sap."SFLIGHT"), and SHOW TABLES is empty by design.

Patterns use * (any run of characters) and ? (single character) and are resolved once, at ATTACH time, against the data dictionary (DD02V, the same source as sap_show_tables()). To browse the full catalog without scoping, use sap_show_tables().

erpl_bics — SAP Business Warehouse

Discovery

`sap_bics_show([obj_type, search, search_in_key, search_in_text, fetch_levels, secret])`

List InfoProviders, cubes, queries, or info areas.

Parameter	Type	Default	Description
`obj_type`	ENUM	—	`'INFOPROVIDER'`, `'QUERY'`, `'CUBE'`, `'INFOAREA'`
`search`	VARCHAR	—	Search pattern (supports wildcards)
`search_in_key`	BOOLEAN	—	Search in technical names
`search_in_text`	BOOLEAN	—	Search in descriptions
`fetch_levels`	UINTEGER	—	Number of hierarchy levels to fetch
`secret`	VARCHAR	—	Named secret to use

Returns: technical_name, text, type, cube_name, is_folder, last_changed, last_changed_by, level

SELECT * FROM sap_bics_show();
SELECT * FROM sap_bics_show(obj_type='QUERY', search='*SALES*');

`sap_bics_show_cubes([search, search_in_key, search_in_text, secret])`

Convenience function to list only BW cubes. Same parameters as sap_bics_show minus obj_type.

SELECT * FROM sap_bics_show_cubes();
SELECT * FROM sap_bics_show_cubes(search='*SALES*');

`sap_bics_show_queries([search, search_in_key, search_in_text, secret])`

Convenience function to list only BW queries.

SELECT * FROM sap_bics_show_queries();

`sap_bics_show_hierarchies([search, info_object, secret])`

List available BW hierarchies.

Parameter	Type	Default	Description
`search`	VARCHAR	—	Search pattern for hierarchy names
`info_object`	VARCHAR	—	Filter by InfoObject
`secret`	VARCHAR	—	Named secret

Returns: technical_name, text, version, valid_to_date

SELECT * FROM sap_bics_show_hierarchies();
SELECT * FROM sap_bics_show_hierarchies(info_object='0COSTCENTER');

Describe

`sap_bics_describe(cube_name [, query_name, id, version, secret])`

Retrieve metadata structure for cubes or queries.

Parameter	Type	Default	Description
`cube_name`	VARCHAR	required	Cube technical name
`query_name`	VARCHAR	—	Query technical name
`id`	VARCHAR	—	State ID for persisted session
`version`	UINTEGER	—	State version
`secret`	VARCHAR	—	Named secret

-- Describe a cube
SELECT * FROM sap_bics_describe('MY_CUBE');

-- Describe a query
SELECT * FROM sap_bics_describe('MY_CUBE', 'MY_QUERY');

`sap_bics_describe_infoobject(info_object_name [, secret])`

Get technical details about a BW InfoObject.

Returns: info_object, data_type, conv_exit, output_length, length, decimals

SELECT * FROM sap_bics_describe_infoobject('0COSTCENTER');

Hierarchy Extraction

`sap_bics_hierarchy(hierarchy_name [, version, date_to, secret])`

Extract hierarchy structure with node relationships and paths.

Parameter	Type	Default	Description
`hierarchy_name`	VARCHAR	required	Hierarchy technical name
`version`	VARCHAR	`'A'`	Hierarchy version
`date_to`	VARCHAR	—	Valid-to date (`YYYYMMDD`)
`secret`	VARCHAR	—	Named secret

Returns: node_id, parent_id, child_id, next_id, info_object, node_name, node_value, date_from, date_to, level, path

SELECT * FROM sap_bics_hierarchy('ZCOSTCENTER_H01');
SELECT * FROM sap_bics_hierarchy('ZCOSTCENTER_H01', version='A', date_to='20251231');

Query Workflow (Stateful)

BICS queries use a stateful workflow: initialize a session, configure axes and filters, then fetch results.

Step 1: `sap_bics_begin(cube_name [, id, return, variables, hierarchy_variables, variant, secret])`

Initialize a BICS query session. cube_name may also be a BEx query technical name, which is resolved to its InfoProvider via RSRREPDIR.

Parameter	Type	Default	Description
`cube_name`	VARCHAR	required	Cube or BEx query technical name
`id`	VARCHAR	—	User-defined state ID
`return`	BICS_RETURN	`'DESCRIBE'`	`'DESCRIBE'` for metadata, `'RESULT'` for data
`variables`	LIST<STRUCT>	—	BEx variable values (see below)
`hierarchy_variables`	LIST<STRUCT>	—	Hierarchy-node variable values (see below)
`variant`	VARCHAR	—	Name of a saved BEx variant to fill the variables
`secret`	VARCHAR	—	Named secret

SELECT * FROM sap_bics_begin('MY_CUBE', id='my_session');

variables shape — LIST<STRUCT(NAME, SIGN, OP, LOW, HIGH)>, mapped onto SAP's BICS_PROV_STATE_INIT_VARIABLES row type and submitted with BICS_PROV_OPEN. It mirrors the ODP filters shape, with NAME naming a BEx variable instead of a field:

Field	Type	Semantics
`NAME`	VARCHAR	BEx variable technical name
`SIGN`	VARCHAR	`'I'` (include) or `'E'` (exclude); defaults to `'I'`
`OP`	VARCHAR	`'EQ'`, `'BT'`, `'GE'`, `'LE'`, `'CP'`, …; defaults to `'BT'` when `HIGH` is given, else `'EQ'`
`LOW`	VARCHAR	Value, or lower bound of an interval
`HIGH`	VARCHAR	Upper bound of an interval; empty otherwise

Repeat NAME to fill a multi-value variable — that is how multiple values travel on the wire.

SELECT * FROM sap_bics_begin('MY_QUERY', id='q1',
    variables => [
        {'NAME':'ZVAR_YEAR', 'SIGN':'I', 'OP':'EQ', 'LOW':'2026', 'HIGH':''},
        {'NAME':'ZVAR_DATE', 'SIGN':'I', 'OP':'BT', 'LOW':'20260101', 'HIGH':'20260131'}
    ]);

Values are stored with the session state and replayed whenever the session is restored, so a chained begin → rows → result workflow stays restricted.

hierarchy_variables shape — LIST<STRUCT(NAME, LOW, HIERARCHY_NAME, HIERARCHY_VERSION, HIERARCHY_DUE_DATE)>. Separate from variables so the common case does not have to spell out the hierarchy fields; HIERARCHY_DUE_DATE defaults to '99991231'.

A variable name the query does not expose as input-ready is rejected — BW would otherwise ignore it silently and return an unrestricted result.

`sap_bics_variables(info_provider [, query, id, secret])`

List the BEx variables of a query, to find out what variables has to fill.

Column	Type	Description
`name`	VARCHAR	Technical name
`text`	VARCHAR	Description
`mandatory`	BOOLEAN	BW refuses to produce a result until this one has a value
`input_enabled`	BOOLEAN	Can be filled by the caller (exit variables cannot)
`is_exit_variable`	BOOLEAN	Filled by a BW customer-exit, not by the caller
`var_type`	VARCHAR	`CHARACTERISTIC_VALUE`, `HIERARCHY`, `TEXT`, `FORMULA`, `HIERARCHY_NODE`
`selection_type`	VARCHAR	`SINGLE_VALUE`, `INTERVAL`, `SELECTION_OPTION`, `MULTIPLE_VALUES`, `PRECALCULATED_VALUE_SET`
`entry_type`	VARCHAR	`OPTIONAL`, `MANDATORY`, `MANDATORY_NOT_INITIAL`
`reference_char`	VARCHAR	Characteristic a hierarchy-node variable refers to

SELECT * FROM sap_bics_variables('MY_QUERY');

Step 2: `sap_bics_rows(state_id, char1 [, char2, ..., op, return])`

Configure row axis characteristics.

Parameter	Type	Default	Description
`state_id`	VARCHAR	required	State ID from `sap_bics_begin`
`char1, ...`	VARCHAR	required	Characteristics to add/set
`op`	BICS_OPERATION	`'ADD'`	`'SET'`, `'ADD'`, or `'REMOVE'`
`return`	BICS_RETURN	`'DESCRIBE'`	Return format

SELECT * FROM sap_bics_rows('my_session', '0CALDAY', '0MATERIAL');

Step 2b: `sap_bics_columns(state_id, char1 [, char2, ..., op, return])`

Configure column axis characteristics. Same signature as sap_bics_rows.

SELECT * FROM sap_bics_columns('my_session', '0AMOUNT', op='SET');

Step 3: `sap_bics_filter(state_id, char_name, member1 [, member2, ..., op, return])`

Restrict a characteristic to specific member values (AO "Keep Only" / "Exclude" gesture). Members are positional varargs after the characteristic name; pass zero members with op='SET' to clear the filter.

Parameter	Type	Default	Description
`state_id`	VARCHAR	required	State ID
`char_name`	VARCHAR	required	Characteristic technical name
`member1, …`	VARCHAR (varargs)	—	Member values to include (or remove with `op='REMOVE'`)
`op`	BICS_OPERATION	`'ADD'` if members supplied, else `'SET'`	`'SET'`, `'ADD'`, `'REMOVE'`
`return`	BICS_RETURN	`'DESCRIBE'`	Return format

-- "Keep Only Germany and France" on country
SELECT * FROM sap_bics_filter('q1', '0D_NW_CNTRY', 'DE', 'FR', op='SET');
-- Append USA to the existing selection
SELECT * FROM sap_bics_filter('q1', '0D_NW_CNTRY', 'US', op='ADD');
-- Clear the filter
SELECT * FROM sap_bics_filter('q1', '0D_NW_CNTRY', op='SET');

Step 4 (optional): `sap_bics_set_char_prop(state_id, char_name, prop, value [, return])`

Set an AO-style per-characteristic property (Display, Sort, or Totals visibility). Mutates /E_TH_STATE_CHARACTERISTICS/<id>/<field> on the server and the change is honoured by the next sap_bics_result call.

Parameter	Type	Default	Description
`state_id`	VARCHAR	required	State ID
`char_name`	VARCHAR	required	Characteristic technical name
`prop`	VARCHAR	required	One of `'DISPLAY'`, `'TOTALS'`, `'SORT'`
`value`	VARCHAR	required	See table below
`return`	BICS_RETURN	`'DESCRIBE'`	Return format

`prop`	Allowed `value`	Server field mutated
`DISPLAY`	`'KEY'` \| `'TEXT'` \| `'BOTH'`	`RESULT_SET_PRESENTATION` + `MEMBER_ACCESS_PRESENTATION` (bitflag: KEY=4, TEXT=32)
`TOTALS`	`'SHOW'` \| `'HIDE'`	`RESULT_VISIBILITY` ('A' / 'N')
`SORT`	`'ASC'` \| `'DESC'` \| `'NONE'`	`RESULT_SET_SORTING.DIRECTION` ('A' / 'D' / '')

-- Show member texts instead of keys
SELECT * FROM sap_bics_set_char_prop('q1', '0D_NW_CNTRY', 'DISPLAY', 'TEXT');
-- Sort products descending
SELECT * FROM sap_bics_set_char_prop('q1', '0D_NW_PROD', 'SORT', 'DESC');

The result-set parser is display-mode aware: when BICS returns both key and text presentation entries for a member, the renderer picks key / text / both according to the per-char setting. The DESCRIBE payload of state_rows, state_columns, and state_free now carries {display, totals, sort} so clients can read the current values without an extra round-trip.

Note: the grand-total "SUMME" / "Overall Result" row in sap_bics_result is not affected by per-char TOTALS='HIDE' — BICS does not expose a state field for grand-total visibility. The flag is applied to the server state (visible in DESCRIBE) but the SUMME row still appears in the result set; downstream clients can filter it out client-side. AO does the same.

Step 5: `sap_bics_result(state_id)`

Fetch result set from configured query state.

Parameter	Type	Default	Description
`state_id`	VARCHAR	required	State ID

Result shape — one VARCHAR column per row-axis element carrying the formatted member key or text, one INTEGER <name>_HIER_LEVEL column per row-axis element, then one DOUBLE column per column-axis leaf.

The number of row-axis columns follows the result set the BW server returns; when a result carries no rows at all, the session state's axis is used instead so a query's schema does not change shape just because it returned nothing.

Column names are resolved in order: the session state, then the query's design-time metadata (one extra RFC call, made only when the state cannot name the axis — the case for BEx queries whose drilldown lives in the query definition), then ROW_1, ROW_2, …. Both metadata sources are matched on the member's characteristic id, never by position, so a column is never labelled with an unrelated characteristic.

-- Complete workflow
SELECT * FROM sap_bics_begin('MY_CUBE', id='q1');
SELECT * FROM sap_bics_rows('q1', '0CALDAY', '0MATERIAL');
SELECT * FROM sap_bics_columns('q1', '0AMOUNT');
SELECT * FROM sap_bics_set_char_prop('q1', '0CALDAY', 'SORT', 'DESC');  -- optional
SELECT * FROM sap_bics_filter('q1', '0MATERIAL', 'M01', 'M02', op='SET');  -- optional
SELECT * FROM sap_bics_result('q1');

Metadata Views

Functions for extracting BW system metadata. All accept an optional secret parameter.

Function	Description
`sap_bics_meta_providers([provider_type, secret])`	List BW InfoProviders by type (CUBE, ADSO, HCPR, ODSO, ODSVIEW)
`sap_bics_meta_provider_fields(provider_name [, secret])`	Fields/characteristics for a provider
`sap_bics_meta_datasources([secret])`	List all DataSources
`sap_bics_meta_datasource_fields(datasource_name [, secret])`	Fields for a DataSource
`sap_bics_meta_transformations([secret])`	List all transformations
`sap_bics_meta_transform_fields(transformation_name [, secret])`	Field mappings and rules
`sap_bics_meta_hcpr([secret])`	List all CompositeProviders
`sap_bics_meta_hcpr_mapping(hcpr_name [, secret])`	Field mappings for CompositeProviders
`sap_bics_meta_queries([secret])`	List all BW queries
`sap_bics_meta_query_usage(query_name [, secret])`	Query usage and dependencies
`sap_bics_meta_query_elements(query_name [, secret])`	Detailed query structure elements
`sap_bics_meta_query_stats([secret])`	Query performance and usage statistics
`sap_bics_meta_objxref([secret])`	Dependencies between BW objects
`sap_bics_meta_infoobjects([secret])`	List all InfoObjects

SELECT * FROM sap_bics_meta_providers(provider_type='CUBE');
SELECT * FROM sap_bics_meta_provider_fields('MY_CUBE');
SELECT * FROM sap_bics_meta_queries();

Lineage & Impact Analysis

Functions for BW data lineage extraction. All accept an optional secret parameter.

`sap_bics_lineage_edges([scope, secret])`

Extract lineage edges between BW objects.

Returns: edge_type, src_kind, src_name, src_field, tgt_kind, tgt_name, tgt_field

SELECT * FROM sap_bics_lineage_edges();

`sap_bics_lineage_graph_json([scope, secret])`

Full lineage graph in JSON format.

SELECT * FROM sap_bics_lineage_graph_json();

`sap_bics_lineage_trace(object_name [, secret])`

Trace lineage from a specific object.

SELECT * FROM sap_bics_lineage_trace('MY_CUBE');

`sap_bics_query_lineage(query_name [, secret])`

Complete field-level lineage for a specific BW query.

SELECT * FROM sap_bics_query_lineage('MY_QUERY');

erpl_odp — SAP Operational Data Provisioning

Discovery

`sap_odp_show_contexts([secret])`

List available ODP contexts.

Returns: technical_name, text, release

SELECT * FROM sap_odp_show_contexts();
-- Typical contexts: BW, ABAP_CDS, SAPI, SLT, HANA

`sap_odp_show(odp_context [, search, secret])`

List ODP providers/sources within a context.

Parameter	Type	Default	Description
`odp_context`	VARCHAR	required	ODP context (e.g. `'BW'`, `'ABAP_CDS'`, `'SAPI'`)
`search`	VARCHAR	—	Search pattern (wildcards)
`secret`	VARCHAR	—	Named secret

Returns: technical_name, text, semantics, semantics_text

SELECT * FROM sap_odp_show('BW');
SELECT * FROM sap_odp_show('ABAP_CDS', search='*FLIGHT*');

`sap_odp_describe(odp_context, odp_name [, secret])`

Get detailed metadata about an ODP provider.

SELECT * FROM sap_odp_describe('BW', 'MY_ODP_SOURCE');

Data Extraction

`sap_odp_preview(odp_context, odp_name [, secret])`

Preview a small sample of data from an ODP provider.

SELECT * FROM sap_odp_preview('BW', 'MY_ODP_SOURCE');

`sap_odp_read_full(odp_context, odp_name [, threads, columns, filters, secret])`

Full extraction of ODP provider data with parallel processing. The cursor on the SAP side is opened in FULL mode and auto-closed when the scan ends — this is one-shot. For incremental extraction use sap_odp_read_delta.

Parameter	Type	Default	Description
`odp_context`	VARCHAR	required	ODP context
`odp_name`	VARCHAR	required	ODP provider name
`threads`	UINTEGER	5	Parallel read threads
`columns`	LIST(VARCHAR)	all	Columns to extract
`filters`	LIST(STRUCT)	—	Server-side selection filters (see below)
`secret`	VARCHAR	—	Named secret

filters shape — LIST<STRUCT(FIELDNAME, SIGN, OP, LOW, HIGH)> mapped onto SAP's RODPS_REPL_S_SELECTION row type. Predicates are OR-combined within the list:

Field	Type	Semantics
`FIELDNAME`	VARCHAR	ODP source field name
`SIGN`	VARCHAR	`'I'` (include) or `'E'` (exclude)
`OP`	VARCHAR	`'EQ'`, `'NE'`, `'GT'`, `'LT'`, `'GE'`, `'LE'`, `'BT'`, `'CP'`, ...
`LOW`	VARCHAR	Lower / single-value comparand
`HIGH`	VARCHAR	Upper comparand (only for `BT`)

-- Simple equality
SELECT * FROM sap_odp_read_full('BW', 'MY_ODP_SOURCE');
SELECT * FROM sap_odp_read_full('ABAP_CDS', 'MY_CDS_VIEW', threads=8);

-- Server-side range filter (between two business partners, inclusive)
SELECT * FROM sap_odp_read_full('ABAP_CDS', 'SEPM_IBUPA$P',
    COLUMNS=['BUSINESSPARTNER','COMPANYNAME'],
    FILTERS=[{
        'FIELDNAME': 'BUSINESSPARTNER',
        'SIGN':      'I',
        'OP':        'BT',
        'LOW':       '0100000000',
        'HIGH':      '0100000099'
    }]);

`sap_odp_read_delta(odp_context, odp_name, subscriber_process [, …])`

Incremental delta extraction. The first call with a given subscriber_process performs SAP's auto-DELTAINIT — returns the full current snapshot AND registers a server-side delta pointer keyed by the subscriber tuple. Subsequent calls with the same subscriber_process resume from the previous pointer and return only the changes since then.

The cursor persists across calls (FULL cursors auto-close on scan completion; DELTA cursors do not). Close them explicitly with PRAGMA sap_odp_close_delta_cursor when your pipeline is done.

Parameter	Type	Default	Description
`odp_context`	VARCHAR	required	ODP context (e.g. `'BW'`, `'ABAP_CDS'`)
`odp_name`	VARCHAR	required	ODP provider name
`subscriber_process`	VARCHAR	required	Stable identifier that keys the server-side delta pointer across calls. Choose a deterministic name per pipeline (e.g. `'MY_ETL_BUPA_DAILY'`).
`threads`	UINTEGER	1	Accepted for API symmetry but delta/RECOVER fetch is serialized (capped to 1). Parallel package fetch can race a multi-package delta into an under-count, and delta packets are small; use `sap_odp_read_full` `threads` for large parallel snapshots. Must be ≥ 1.
`columns`	LIST(VARCHAR)	all	Columns to extract (not allowed together with `recover=true`)
`filters`	LIST(STRUCT)	—	Server-side selection predicates (same shape as `sap_odp_read_full`; not allowed together with `recover=true`)
`recover`	BOOLEAN	`false`	When true, re-stream the last unconfirmed packet (`I_EXTRACTION_MODE='R'`) without advancing the pointer, using the original open's projection/filters. Useful after a fetch was interrupted. Cannot be combined with `columns`/`filters`.
`secret`	VARCHAR	—	Named secret

Concurrency note: do not run two sap_odp_read_delta calls with the same subscriber_process in parallel — they will race the server-side pointer.

Change semantics: for ABAP_CDS byElement-tracked sources, inserts and updates both surface as after-images (ODQ_CHANGEMODE='U'); physical deletes are not reported by the byElement annotation (the row simply leaves the snapshot).

-- First call: SAP auto-DELTAINIT — returns full current snapshot, registers
-- a server-side delta pointer under subscriber_process 'NIGHTLY_ETL'.
SELECT * FROM sap_odp_read_delta('BW', '0D_FC_C01$F', 'NIGHTLY_ETL');

-- Run the same call later (e.g. next day): returns only the rows that
-- changed in the cube since the previous call.
SELECT * FROM sap_odp_read_delta('BW', '0D_FC_C01$F', 'NIGHTLY_ETL');

-- If a previous DELTA call was interrupted, re-stream its last packet:
SELECT * FROM sap_odp_read_delta('BW', '0D_FC_C01$F', 'NIGHTLY_ETL',
                                 recover=true);

-- Release the cursor when done:
PRAGMA sap_odp_close_delta_cursor('BW', 'NIGHTLY_ETL', '0D_FC_C01$F');

Delta-capable sources are identified by supports_delta=true in sap_odp_describe. Not every CDS view is delta-capable — the underlying DDL must carry the @Analytics.dataExtraction.delta.byElement annotation; BW fact tables (*$F) are typically delta-capable.

Subscription Management

`sap_odp_show_subscriptions([secret])`

List active ODP subscriptions.

SELECT * FROM sap_odp_show_subscriptions();

`sap_odp_show_cursors([secret])`

List ODP extraction cursors for delta tracking. Cursors created by sap_odp_read_delta appear here with is_delta_extension=true; their subscriber_proc column matches the subscriber_process argument you passed.

SELECT * FROM sap_odp_show_cursors();

`sap_odp_get_last_modified(odp_context, odp_name [, secret])`

Return the last-modified UTC timestamp of an ODP object (invokes RODPS_REPL_ODP_GET_LAST_MODIF) without fetching any rows. Use it as a cheap probe before sap_odp_read_delta: if the timestamp has not advanced since your last run, there is nothing to extract.

Output columns: odp_name (VARCHAR), last_modified (DECIMAL(21,7) — a SAP UTC timestamp; comparable against the pointer column of sap_odp_show_cursors).

SELECT * FROM sap_odp_get_last_modified('ABAP_CDS', 'MY_CDS_VIEW$E');

`sap_odp_get_subscriptions(odp_context, odp_name [, subscriber_name, subscriber_process, secret])`

List the subscriptions registered for a specific ODP object (invokes RODPS_REPL_ODP_GET_SUBSCR). Unlike sap_odp_show_subscriptions (which lists all subscriptions), this is scoped to one odp_name and can be further filtered.

Output columns: subscriber_type, subscriber_name, subscriber_process, queue_name, subscription_id.

Parameter	Type	Description
`odp_context`	VARCHAR	ODP context (e.g. `'BW'`, `'ABAP_CDS'`)
`odp_name`	VARCHAR	ODP object name
`subscriber_name`	VARCHAR	(named) optional subscriber-name filter
`subscriber_process`	VARCHAR	(named) optional subscriber-process filter
`secret`	VARCHAR	(named) optional secret name

SELECT * FROM sap_odp_get_subscriptions('ABAP_CDS', 'MY_CDS_VIEW$E');

`sap_rfc_live_connections()` / `sap_rfc_connections_opened()` / `sap_rfc_connections_closed()`

Scalar functions returning how many SAP RFC connections erpl has opened and closed in this process, and how many it currently holds open (opened - closed).

Every open connection is a session and a work-process reservation on the SAP system, so sap_rfc_live_connections() is the number that matters to a Basis team. Between queries it should be 0. A non-zero value means erpl is still holding SAP sessions.

SELECT sap_rfc_live_connections();   -- 0 between queries

These are process-wide counters, not per-connection state, and they are VOLATILE so DuckDB never constant-folds them at bind time. Their intended use is asserting in tests and in the field that a scan released what it acquired — client-side timing shows nothing when a connection is never released, because the entire cost falls on the SAP system.

`PRAGMA sap_odp_close_delta_cursor(odp_context, subscriber_process, odp_name [, secret=...])`

Graceful counterpart to sap_odp_drop. Looks up the cursor for the given subscriber tuple and calls RODPS_REPL_ODP_CLOSE on its pointer. Idempotent: returns 'CLOSED' if a cursor existed (open or already closed) and 'NOT_FOUND' if no cursor of that name was found.

Prefer this over sap_odp_drop at the end of a delta pipeline — close leaves the subscription registered and resumable from its last pointer; drop wipes the subscription so the next call performs DELTAINIT again.

Parameter	Type	Description
`odp_context`	VARCHAR	ODP context (e.g. `'BW'`, `'ABAP_CDS'`)
`subscriber_process`	VARCHAR	Subscriber process identifier (the one passed to `sap_odp_read_delta`)
`odp_name`	VARCHAR	ODP object name

Named parameters: secret — optional secret name.

PRAGMA sap_odp_close_delta_cursor('BW', 'NIGHTLY_ETL', '0D_FC_C01$F');

Cleanup hygiene matters: leaked open cursors accumulate server-side. If a pipeline crashes mid-fetch the cursor may not be closeable via this pragma — fall back to sap_odp_drop to fully reset, accepting that the next call will re-snapshot via DELTAINIT.

`PRAGMA sap_odp_drop(odp_context, subscriber_name, subscriber_process, odp_name)`

Drop an ODP subscription/cursor on the SAP system (invokes RODPS_REPL_ODP_RESET). A subsequent sap_odp_read_full will re-create the subscription from scratch.

Parameter	Type	Description
`odp_context`	VARCHAR	ODP context (e.g. `'ABAP_CDS'`, `'BW'`, `'SAPI'`)
`subscriber_name`	VARCHAR	Subscriber name registered against the queue
`subscriber_process`	VARCHAR	Subscriber process identifier
`odp_name`	VARCHAR	ODP object name

Named parameters: secret — optional secret name.

-- Find the subscription to drop
SELECT queue_name, subscriber_name, subscriber_proc
  FROM sap_odp_show_subscriptions();

-- Drop it (values come from the row above)
PRAGMA sap_odp_drop(
    'ABAP_CDS',
    'ERPL_BW',
    'ERPL_ABAP_CDS_SEPM_IBUPA_4271',
    'SEPM_IBUPA$P'
);

SSH tunnelling — moved to `erpl_tunnel`

The SSH tunnel that erpl used to bundle now lives in a dedicated extension, erpl_tunnel, which does more: reverse tunnels, Tailscale and NetBird backends, and peer discovery.

INSTALL erpl_tunnel FROM 'http://get.erpl.io';
LOAD erpl_tunnel;

tunnel_create, tunnel_close, tunnel_close_all and tunnels() remain registered in erpl as stubs that raise and point here, so an old script says where the function went rather than failing with "does not exist". Loading erpl_tunnel replaces them.

Two differences from the version erpl bundled: tunnels() gained backend and direction columns, so column order changed (queries naming columns are unaffected), and tunnel_create is a deprecated alias for tunnel_import. The ssh_tunnel secret type is unchanged, so existing secrets keep working.

Reference

Secret Types

`sap_rfc` — SAP RFC Connection

All parameters are VARCHAR. Choose either direct connection or load-balanced parameters.

Parameter	Description	Direct	Load-Balanced
`ashost`	Application server host	required	—
`sysnr`	System number	required	—
`mshost`	Message server host	—	required
`msserv`	Message server service	—	optional
`sysid`	System ID	—	required
`group`	Logon group	—	required
`client`	SAP client number	required	required
`user`	Username	required	required
`passwd`	Password (redacted)	required¹	required¹
`lang`	Language (e.g. `'EN'`)	optional	optional
`snc_mode`	Activate SNC — `'1'` on, `'0'` off	optional	optional
`snc_sso`	Use the SNC identity for logon (`'1'`/`'0'`)	optional	optional
`snc_qop`	SNC quality of protection	optional	optional
`snc_myname`	SNC client name	optional	optional
`snc_partnername`	SNC server name	optional	optional
`snc_lib`	SNC library path	optional	optional
`mysapsso2`	SSO2 ticket (redacted)	optional	optional
`x509cert`	X.509 certificate for logon (redacted)	optional	optional
`saprouter`	SAProuter string, e.g. `'/H/routerhost/S/3299'`	optional	optional
`gwhost`	Gateway host	optional	optional
`gwserv`	Gateway service	optional	optional
`codepage`	Logon codepage, e.g. `'4103'`	optional	optional
`trace`	SAP RFC trace level `'0'`–`'3'`	optional	optional
`dest`	Destination in `sapnwrfc.ini`	optional	optional

¹ Not required when logging on via SNC or an SSO2 ticket.

The parameter names are passed through to RfcOpenConnection unchanged, so they follow the SAP NetWeaver RFC SDK documentation.

-- Direct connection
CREATE SECRET my_sap (
    TYPE sap_rfc,
    ASHOST 'sap.example.com', SYSNR '00', CLIENT '100',
    USER 'sapuser', PASSWD 'password', LANG 'EN'
);

-- Load-balanced connection
CREATE SECRET my_sap_lb (
    TYPE sap_rfc,
    MSHOST 'sapms.example.com', SYSID 'PRD', GROUP 'PUBLIC',
    CLIENT '100', USER 'sapuser', PASSWD 'password'
);

-- SNC / Kerberos logon, without a password
CREATE SECRET my_sap_snc (
    TYPE sap_rfc,
    ASHOST 'sap.example.com', SYSNR '00', CLIENT '100', USER 'sapuser',
    SNC_MODE '1',
    SNC_PARTNERNAME 'p:CN=SAP/sap.example.com@EXAMPLE.LOCAL',
    SNC_LIB '/usr/lib/libgsskrb5.so'
);

A secret is selected by name via the secret argument, e.g. PRAGMA sap_rfc_ping(secret='my_sap_snc') or SELECT * FROM sap_read_table('SFLIGHT', secret='my_sap_snc'). Without it, the best-matching sap_rfc secret in scope is used.

`ssh_tunnel` — SSH Tunnel Connection

Parameter	Type	Default	Description
`ssh_host`	VARCHAR	required	SSH jump host
`ssh_port`	INTEGER	22	SSH port
`ssh_user`	VARCHAR	required	SSH username
`password`	VARCHAR	—	SSH password (redacted)
`private_key_path`	VARCHAR	—	Path to SSH private key (redacted)
`passphrase`	VARCHAR	—	Private key passphrase (redacted)
`auth_method`	VARCHAR	auto	`'password'`, `'key'`, or `'agent'` (auto-detected if omitted)

-- Password authentication
CREATE SECRET my_ssh (
    TYPE ssh_tunnel,
    ssh_host 'jumphost.example.com', ssh_user 'myuser',
    password 'secret'
);

-- Key-based authentication
CREATE SECRET my_ssh_key (
    TYPE ssh_tunnel,
    ssh_host 'jumphost.example.com', ssh_user 'myuser',
    private_key_path '/home/user/.ssh/id_rsa'
);

Custom Types

Type	Values	Used By
`BICS_RETURN`	`'DESCRIBE'`, `'RESULT'`	BICS query workflow functions
`BICS_OPERATION`	`'SET'`, `'ADD'`, `'REMOVE'`	BICS axis/filter configuration
`ODP_REPLICATION_MODE`	`'FULL'`, `'DELTA'`, `'RECOVER'`	ODP extraction mode

SAP ↔ DuckDB Type Mapping

The DDIC name on the left is what sap_describe_fields() reports; the RFCTYPE shown in parentheses is the internal RFC-SDK type the ERPL scanners use.

DDIC type	RFCTYPE	DuckDB type	Notes
CHAR / CLNT / LANG / CUKY / UNIT	RFCTYPE_CHAR	VARCHAR	Fixed-length, right-trimmed
NUMC / ACCP	RFCTYPE_NUM	VARCHAR	Numeric characters; preserves leading zeros
INT4	RFCTYPE_INT	BIGINT	4-byte integer (signed)
INT1	RFCTYPE_INT1	TINYINT	1-byte integer
INT2 / PREC	RFCTYPE_INT2	SMALLINT	2-byte integer (PREC is a DDIC alias)
INT8	RFCTYPE_INT8	BIGINT	8-byte integer (signed)
FLTP	RFCTYPE_FLOAT	DOUBLE	IEEE 754 floating point
DEC / CURR / QUAN	RFCTYPE_BCD	DECIMAL(min(2N-1,38), S)	Packed-decimal; precision capped at DuckDB's max
DECF16 / D16D / D16N / D16R / D16S	RFCTYPE_DECF16	DECIMAL(16, S)	IEEE 754-2008 decimal floating point; precision is the spec-defined 16, scale clamped to it
DECF34 / D34D / D34N / D34R / D34S	RFCTYPE_DECF34	DECIMAL(34, S)	IEEE 754-2008 decimal floating point; precision is the spec-defined 34, scale clamped to it
STRING / STRG / SSTR / LCHR	RFCTYPE_STRING	VARCHAR	Variable-length character data
XMLDATA	RFCTYPE_XMLDATA	VARCHAR	XML payload (text)
RAW / LRAW	RFCTYPE_BYTE	BLOB	Fixed-length raw bytes (incl. RAW(16) UUIDs). See note below
RAWSTRING / RSTR	RFCTYPE_XSTRING	BLOB	Variable-length raw bytes. See note below
DATS	RFCTYPE_DATE	DATE	Format YYYYMMDD
TIMS	RFCTYPE_TIME	TIME	Format HHMMSS
UTCLONG / UTCL	RFCTYPE_UTCLONG	TIMESTAMP	UTC timestamp, microsecond precision
UTCS	RFCTYPE_UTCSECOND	TIMESTAMP	UTC timestamp, second precision
UTCM	RFCTYPE_UTCMINUTE	TIMESTAMP	UTC timestamp, minute precision
(n/a — RFC parameter only)	RFCTYPE_DTDAY / DTWEEK / DTMONTH	INTEGER	Date durations (days / weeks / months)
(n/a — RFC parameter only)	RFCTYPE_TSECOND / TMINUTE	INTEGER	Time durations (seconds / minutes)
(n/a — RFC parameter only)	RFCTYPE_CDAY	INTEGER	Calendar day
(n/a — RFC parameter only)	RFCTYPE_STRUCTURE	STRUCT	Nested structure
(n/a — RFC parameter only)	RFCTYPE_TABLE	LIST	Table parameter (each row is a STRUCT)
(any unrecognized RFCTYPE, with `erpl_rfc_strict_type_check=false`)	—	VARCHAR	Lenient fallback (issue #53)

Notes:

The same mapping applies to sap_read_table, sap_rfc_invoke, and sap_odp_* scanners because they share RfcType::CreateDuckDbType(). BICS characteristic axes use the equivalent bicstype2rfctype → rfctype2logicaltype path.
DDIC CLNT, LANG, CUKY, and UNIT are CHAR aliases — they surface as VARCHAR with a fixed width, not as a dedicated type.
BCD precision is capped at DuckDB's DECIMAL(38) maximum; SAP fields wider than DEC(20) will be reported with precision=38 and scale clamped to it.
Raw byte columns in sap_read_table: RFC_READ_TABLE cannot carry binary data, so it spells RAW / LRAW / RAWSTRING / RSTR columns out as hex text in its character DATA line. ERPL decodes that back into the bytes it stands for, so the BLOB holds the payload and octet_length() is the true byte count. An empty raw column arrives as the field delimiter and is reported as NULL. Before v2026.08.18 the hex text was stored verbatim, which doubled octet_length() and required a manual unhex() (#109).

Configuration Options

erpl_rfc

Option	Type	Default	Description
`erpl_telemetry_enabled`	BOOLEAN	`true`	Enable telemetry (erpl.io/telemetry)
`erpl_telemetry_key`	VARCHAR	(built-in)	Telemetry API key
`erpl_trace_enabled`	BOOLEAN	`false`	Enable ERPL tracing
`erpl_trace_level`	VARCHAR	`'INFO'`	Trace level: TRACE, DEBUG, INFO, WARN, ERROR, NONE
`erpl_trace_output`	VARCHAR	`'console'`	Output: console, file, both
`erpl_trace_file_path`	VARCHAR	`'trace'`	Trace file directory
`erpl_trace_max_file_size`	BIGINT	0 (unlimited)	Max trace file size in bytes
`erpl_trace_rotation`	BOOLEAN	`false`	Enable trace file rotation
`erpl_rfc_strict_type_check`	BOOLEAN	`false`	When true, throw an error on unsupported SAP RFC types instead of falling back to VARCHAR
`erpl_rfc_persistent_connections`	BOOLEAN	`true`	Cache one RFC connection + function descriptor per column for a `sap_read_table` scan instead of reopening per batch
`erpl_rfc_max_persistent_connections`	UINTEGER	16	Upper bound on RFC connections a scan caches concurrently (issue #67); columns past the cap use per-batch open/close
`erpl_rfc_read_table_batch_budget`	UINTEGER	1310720	Target max concurrent result rows (projected columns × per-column batch) for `sap_read_table`; bounds peak memory on wide tables (issue #69). Lower = less memory but more RFC round-trips; `0` disables the cap
`erpl_rfc_fetch_size`	UINTEGER	1310720	How much `sap_read_table` asks SAP for per round-trip, as concurrent result rows. Alias of `erpl_rfc_read_table_batch_budget`. See Tuning large reads
`erpl_rfc_max_threads`	UINTEGER	0	Default for the `threads` named parameter; `0` lets erpl choose
`erpl_rfc_partitions`	UBIGINT	0	Split a `sap_read_table` scan into this many row ranges read in parallel. `0` reads in one pass, parallelising across columns instead. See Narrow tables
`erpl_rfc_partition_window_rows`	UBIGINT	0	Rows a partition worker claims at a time; `0` uses one RFC batch per window
`erpl_rfc_pushdown_filters`	BOOLEAN	`true`	Translate SQL `WHERE` predicates into `RFC_READ_TABLE`'s `OPTIONS` table so SAP filters the rows instead of sending them all. Turning it off never changes which rows come back, only how many cross the wire. See Filter Pushdown
`erpl_rfc_backend`	VARCHAR	`'nwrfc'`	Which implementation serves RFC calls: `'nwrfc'` (SAP's NetWeaver RFC SDK) or `'proto'` (the pure-Rust erpl-proto implementation). Must be set before the first SAP call; frozen for the life of the process once resolved. Environment override: `ERPL_RFC_BACKEND`
`erpl_rfc_backend_path`	VARCHAR	`''`	Explicit path to the RFC backend shared library, overriding the search. Empty means: next to the extension, then the loader's library path. Environment override: `ERPL_RFC_BACKEND_PATH`

SET erpl_trace_enabled = TRUE;
SET erpl_trace_level = 'DEBUG';
SET erpl_trace_output = 'both';

Selecting the RFC backend

erpl does not link the SAP SDK. It resolves the RFC entry points at runtime, so the implementation behind them is a runtime choice:

-- Must come before any SAP call in this process.
SET erpl_rfc_backend = 'proto';
SELECT sap_rfc_backend();          -- 'proto'

The backend freezes at the first SAP call. Connections and function descriptors belong to one implementation, so a later SET is refused rather than allowed to hand a handle from one library to the other — start a new process to switch.

If 'proto' is selected and its library cannot be found, the call fails. erpl never falls back to the SDK, because a silent downgrade would leave you believing you had tested a backend you had not.

erpl_bics

Option	Type	Default	Description
`erpl_bics_trace`	BOOLEAN	`false`	Enable BICS trace logging
`erpl_bics_trace_dir`	VARCHAR	`'./trace'`	BICS trace directory
`erpl_bics_stream_result_tables`	BOOLEAN	`true`	Stream the large BICS response tables off the SAP SDK handle instead of materialising them
`erpl_bics_max_result_memory`	VARCHAR	`''` (DuckDB's `memory_limit`)	Memory a single BICS result set may use, e.g. `'8GB'`
`erpl_bics_max_data_cells`	BIGINT	`0` (derive from the memory budget)	Explicit cell budget (`I_MAX_DATA_CELLS`), for pinning exactly what BW receives

erpl_bics_stream_result_tables controls how the response is read. With it on (the default) the four tables that grow with the result — E_T_DATA_CELLS, E_T_ROWS, E_T_MEMBER, E_T_MEMBER_PRESENTATION — are read row by row from the SDK handle and only the fields actually needed are converted. Turning it off restores the pre-2026.08 behaviour of building the whole response as a value tree, which costs roughly 2.2 KB per data cell and 0.8 KB per row-axis element. It exists as an escape hatch; there is no reason to turn it off except to compare behaviour.

A BEx query is read in one piece: BW builds the whole result set or none of it, and there is no windowing in the BICS RFC API to page it (neither BICS_PROV_GET_RESULT_SET nor BICS_PROV_GET_RESULTSET_DETAIL accepts a row range). So the ceiling is a memory budget.

erpl_bics_max_result_memory accepts anything DuckDB's own memory settings accept ('8GB', '512MB', '80%'). Left empty it follows DuckDB's memory_limit, which defaults to 80% of physical RAM — so out of the box erpl sizes itself to the machine, and SET memory_limit moves both together.

erpl converts the budget into the cell count BW needs (I_MAX_DATA_CELLS) by dividing by ~128 bytes per data cell — the width of one BICS_PROV_RS_DATA_CELL row in the SAP RFC container. Set erpl_bics_max_data_cells to a non-zero value to bypass the conversion and pin the cell count yourself (capped at 2,147,483,647, the width of the ABAP integer BW receives it in).

When a query does not fit, it fails with the size it would have needed and a value you can paste straight back:

This BEx query is too large to read in one go: it would return 63000 rows x 31 columns
= 1953000 data cells, which needs about 238.4 MB of memory. The limit is 4.0 MB. Either
raise it with SET erpl_bics_max_result_memory = '287MiB' (make sure the machine actually
has that much), or make the query smaller with sap_bics_filter(). BW reported result
state 4.

Note the budget counts data cells — the key-figure cells — not output columns. A result with 60,000 rows and 31 output columns of which 6 are key figures is 360,000 data cells, not 1,860,000.

Notes

Filter Pushdown

sap_read_table supports both SAP-side and DuckDB-side filter pushdown:

-- SAP-side filter (via FILTER parameter, pushed to RFC_READ_TABLE)
SELECT * FROM sap_read_table('SFLIGHT', FILTER='CARRID = ''LH''');

-- DuckDB-side filter pushdown (automatically pushed to SAP when possible)
SELECT * FROM sap_read_table('SFLIGHT') WHERE CARRID = 'LH';

On a large table this is the single biggest lever available: a predicate SAP can evaluate turns a multi-million-row transfer into a few thousand rows. What reaches the server:

Predicate	Pushed
`=`, `<>`, `<`, `>`, `<=`, `>=`	yes
`AND` / `OR` over one column, including `BETWEEN`	yes, all arms or none
`IN (...)`	yes, until the generated clause exceeds ~4000 characters
`IS NULL` / `IS NOT NULL`	no — ABAP has no NULL
Predicates on the client field (`MANDT`, DDIC type `CLNT`)	no — RFC_READ_TABLE rejects a clause naming the client
Literals of type `TIMESTAMP`, `BLOB`, `FLOAT`, `DOUBLE`	no — no unambiguous ABAP spelling

Literals are rendered the way the DDIC expects them: DATE as YYYYMMDD, TIME as HHMMSS, character and numeric values quoted with embedded apostrophes doubled. A type whose ABAP spelling is not established is not pushed rather than guessed at.

Anything not pushed is still applied — erpl evaluates it after reading, so the result set is identical either way. Only the volume transferred changes.

erpl_rfc_pushdown_filters = false disables the translation entirely and makes erpl evaluate every predicate itself. It exists as an escape hatch for SAP releases that reject the generated syntax, and as a way to check that a filter is not the cause of a discrepancy: the same query must return the same rows with it on and off.

Tuning large reads

Paging and parallelism use the same two names everywhere, so what you learn on one extension applies to the others:

	Named parameter	Session setting	Meaning
Parallelism	`threads`	`erpl_<ext>_max_threads`	How many SAP calls run at once. `0` = let erpl decide
Fetch granule	`fetch_size`	`erpl_<ext>_fetch_size`	How much is asked for per round-trip, in the protocol's own unit

-- per query
SELECT * FROM sap_read_table('LARGE_TABLE', threads = 8, fetch_size = 262144);

-- or as a session default
SET erpl_rfc_fetch_size = 262144;
SET erpl_rfc_max_threads = 8;

The unit of fetch_size follows the protocol: for sap_read_table it is concurrent result rows (projected columns x per-column batch), which is what bounds the SAP SDK's own buffer on wide tables.

Both are transport settings only. Every value returns exactly the same rows; they trade memory against round-trips, nothing else. Lower fetch_size to cap memory harder on a wide table, raise it for fewer round-trips on a narrow one.

How much threads helps depends on the SAP system's capacity — work processes, application servers, database sessions — not on erpl. Raise it while watching throughput rather than setting it blindly, and check with your Basis team before running a large parallel extract against a production system.

erpl_rfc_read_table_batch_budget is the original spelling of erpl_rfc_fetch_size. Both remain supported and write the same value — they are two names for one knob, not two knobs.

Narrow tables: use `partitions`, not `threads`

threads parallelises across columns — one concurrent RFC_READ_TABLE call per projected column. That suits a wide extract and does nothing for a narrow one: a single-column scan issues one call, and threads has nothing to spread.

partitions splits the rows instead. Each worker claims a window of the table and reads it with ROWSKIPS/ROWCOUNT:

SELECT * FROM sap_read_table('DD02L', partitions = 8);

-- or as a session default
SET erpl_rfc_partitions = 8;

What to expect. Measured on a 164,664-row single-column extract, release build:

`partitions`	1	2	4	8	16
wall	2.23s	1.45s	1.11s	0.83s	0.92s

About 2.7x at eight workers, with the knee clearly visible — past it, more workers cost more than they return. Where the knee falls is a property of the SAP system's capacity, not of erpl, so find it by raising the value and watching throughput.

Rows come back unordered. An unpartitioned scan calls RFC_READ_TABLE with GET_SORTED='X' and returns rows in that order; partitioned workers finish in whatever order they finish. That is why this is opt-in rather than the default. Add an ORDER BY if you need one.

erpl_rfc_partition_window_rows sets how many rows a worker claims at a time. The default is one RFC batch per window, which shares the table evenly across workers; larger windows mean fewer, coarser hand-offs.

Two caveats worth knowing:

ROWSKIPS is a 32-bit integer, so a partitioned scan cannot start a window beyond row 2,147,483,647. erpl refuses rather than wrapping, because a wrap would silently re-read an earlier range and duplicate rows. Narrow the read with a WHERE clause.
Windows are offsets into a server-side sort, not a snapshot. If the table is written while you read it, rows can shift between windows. That is true of an unpartitioned scan too — it also pages by offset — but partitioning makes it easier to observe.

For a genuinely large extract, running several processes against SAP still scales better than any in-process approach, because SAP-side concurrency limits apply per client program. Check with your Basis team first.

SSH Tunnel + SAP Connection

Complete workflow for connecting through an SSH jump host:

-- 1. Create SSH tunnel secret
CREATE SECRET my_ssh (TYPE ssh_tunnel, ssh_host 'jump.example.com', ssh_user 'user', password 'pw');

-- 2. Create tunnel
PRAGMA tunnel_create(secret='my_ssh', remote_host='sap.internal', remote_port=3300, local_port=13300);

-- 3. Create SAP connection pointing to local tunnel port
CREATE SECRET my_sap (TYPE sap_rfc, ASHOST 'localhost', SYSNR '00', CLIENT '100', USER 'sap', PASSWD 'pw');

-- 4. Query through tunnel
SELECT * FROM sap_read_table('SFLIGHT');

-- 5. Cleanup
PRAGMA tunnel_close_all;

Last Updated: 2026-02-11

Uh oh!

FilesExpand file tree

API_REFERENCE.md

Latest commit

History

API_REFERENCE.md

File metadata and controls

ERPL Extension Suite — API Reference

Overview

Quick Start

Quick Reference

erpl_rfc — SAP RFC Connectivity

Data Access

sap_read_table(table_name [, THREADS, COLUMNS, FILTER, ...])

sap_rfc_invoke(function_name, ...args [, path, secret])

Discovery & Metadata

sap_show_tables([TABLENAME, TEXT, THREADS])

sap_describe_fields(table_name [, LANGUAGE])

sap_rfc_show_function([FUNCNAME, GROUPNAME, LANGUAGE])

sap_rfc_show_groups([GROUPNAME, LANGUAGE])

sap_rfc_describe_function(function_name)

sap_rfc_authorizations()

Connection & Diagnostics

PRAGMA sap_rfc_ping([secret])

sap_rfc_backend()

PRAGMA sap_rfc_set_trace_level(level)

PRAGMA sap_rfc_set_trace_dir(directory)

PRAGMA sap_rfc_set_maximum_trace_file_size(size, unit)

PRAGMA sap_rfc_set_maximum_stored_trace_files(count)

PRAGMA sap_rfc_set_ini_path(path)

PRAGMA sap_rfc_reload_ini_file

ATTACH — Virtual SAP Catalog

erpl_bics — SAP Business Warehouse

Discovery

sap_bics_show([obj_type, search, search_in_key, search_in_text, fetch_levels, secret])

sap_bics_show_cubes([search, search_in_key, search_in_text, secret])

sap_bics_show_queries([search, search_in_key, search_in_text, secret])

sap_bics_show_hierarchies([search, info_object, secret])

Describe

sap_bics_describe(cube_name [, query_name, id, version, secret])

sap_bics_describe_infoobject(info_object_name [, secret])

Hierarchy Extraction

sap_bics_hierarchy(hierarchy_name [, version, date_to, secret])

Query Workflow (Stateful)

Step 1: sap_bics_begin(cube_name [, id, return, variables, hierarchy_variables, variant, secret])

sap_bics_variables(info_provider [, query, id, secret])

Step 2: sap_bics_rows(state_id, char1 [, char2, ..., op, return])

Step 2b: sap_bics_columns(state_id, char1 [, char2, ..., op, return])

Step 3: sap_bics_filter(state_id, char_name, member1 [, member2, ..., op, return])

Step 4 (optional): sap_bics_set_char_prop(state_id, char_name, prop, value [, return])

Step 5: sap_bics_result(state_id)

Metadata Views

Lineage & Impact Analysis

sap_bics_lineage_edges([scope, secret])

sap_bics_lineage_graph_json([scope, secret])

sap_bics_lineage_trace(object_name [, secret])

sap_bics_query_lineage(query_name [, secret])

erpl_odp — SAP Operational Data Provisioning

Discovery

sap_odp_show_contexts([secret])

sap_odp_show(odp_context [, search, secret])

sap_odp_describe(odp_context, odp_name [, secret])

Data Extraction

sap_odp_preview(odp_context, odp_name [, secret])

sap_odp_read_full(odp_context, odp_name [, threads, columns, filters, secret])

sap_odp_read_delta(odp_context, odp_name, subscriber_process [, …])

Subscription Management

sap_odp_show_subscriptions([secret])

sap_odp_show_cursors([secret])

sap_odp_get_last_modified(odp_context, odp_name [, secret])

sap_odp_get_subscriptions(odp_context, odp_name [, subscriber_name, subscriber_process, secret])

sap_rfc_live_connections() / sap_rfc_connections_opened() / sap_rfc_connections_closed()

PRAGMA sap_odp_close_delta_cursor(odp_context, subscriber_process, odp_name [, secret=...])

PRAGMA sap_odp_drop(odp_context, subscriber_name, subscriber_process, odp_name)

SSH tunnelling — moved to erpl_tunnel

Reference

Secret Types

sap_rfc — SAP RFC Connection

ssh_tunnel — SSH Tunnel Connection

Custom Types

`sap_read_table(table_name [, THREADS, COLUMNS, FILTER, ...])`

`sap_rfc_invoke(function_name, ...args [, path, secret])`

`sap_show_tables([TABLENAME, TEXT, THREADS])`

`sap_describe_fields(table_name [, LANGUAGE])`

`sap_rfc_show_function([FUNCNAME, GROUPNAME, LANGUAGE])`

`sap_rfc_show_groups([GROUPNAME, LANGUAGE])`

`sap_rfc_describe_function(function_name)`

`sap_rfc_authorizations()`

`PRAGMA sap_rfc_ping([secret])`

`sap_rfc_backend()`

`PRAGMA sap_rfc_set_trace_level(level)`

`PRAGMA sap_rfc_set_trace_dir(directory)`

`PRAGMA sap_rfc_set_maximum_trace_file_size(size, unit)`

`PRAGMA sap_rfc_set_maximum_stored_trace_files(count)`

`PRAGMA sap_rfc_set_ini_path(path)`

`PRAGMA sap_rfc_reload_ini_file`

`sap_bics_show([obj_type, search, search_in_key, search_in_text, fetch_levels, secret])`

`sap_bics_show_cubes([search, search_in_key, search_in_text, secret])`

`sap_bics_show_queries([search, search_in_key, search_in_text, secret])`

`sap_bics_show_hierarchies([search, info_object, secret])`

`sap_bics_describe(cube_name [, query_name, id, version, secret])`

`sap_bics_describe_infoobject(info_object_name [, secret])`

`sap_bics_hierarchy(hierarchy_name [, version, date_to, secret])`

Step 1: `sap_bics_begin(cube_name [, id, return, variables, hierarchy_variables, variant, secret])`

`sap_bics_variables(info_provider [, query, id, secret])`

Step 2: `sap_bics_rows(state_id, char1 [, char2, ..., op, return])`

Step 2b: `sap_bics_columns(state_id, char1 [, char2, ..., op, return])`

Step 3: `sap_bics_filter(state_id, char_name, member1 [, member2, ..., op, return])`

Step 4 (optional): `sap_bics_set_char_prop(state_id, char_name, prop, value [, return])`

Step 5: `sap_bics_result(state_id)`

`sap_bics_lineage_edges([scope, secret])`

`sap_bics_lineage_graph_json([scope, secret])`

`sap_bics_lineage_trace(object_name [, secret])`

`sap_bics_query_lineage(query_name [, secret])`

`sap_odp_show_contexts([secret])`

`sap_odp_show(odp_context [, search, secret])`

`sap_odp_describe(odp_context, odp_name [, secret])`

`sap_odp_preview(odp_context, odp_name [, secret])`

`sap_odp_read_full(odp_context, odp_name [, threads, columns, filters, secret])`

`sap_odp_read_delta(odp_context, odp_name, subscriber_process [, …])`

`sap_odp_show_subscriptions([secret])`

`sap_odp_show_cursors([secret])`

`sap_odp_get_last_modified(odp_context, odp_name [, secret])`

`sap_odp_get_subscriptions(odp_context, odp_name [, subscriber_name, subscriber_process, secret])`

`sap_rfc_live_connections()` / `sap_rfc_connections_opened()` / `sap_rfc_connections_closed()`

`PRAGMA sap_odp_close_delta_cursor(odp_context, subscriber_process, odp_name [, secret=...])`

`PRAGMA sap_odp_drop(odp_context, subscriber_name, subscriber_process, odp_name)`

SSH tunnelling — moved to `erpl_tunnel`

`sap_rfc` — SAP RFC Connection

`ssh_tunnel` — SSH Tunnel Connection

Narrow tables: use `partitions`, not `threads`