Oracle Batch
CertifiedBulk insert rows into Oracle Database using prepared statements
Oracle Batch
Bulk insert rows into Oracle Database using prepared statements
Reads ION-formatted data from Kestra internal storage and performs high-performance batch inserts using JDBC batch operations. Data is processed in chunks (default 1,000 rows) to optimize memory and performance. Supports auto-commit for databases without transaction support. Handles NULL values explicitly to avoid ORA-17041 errors.
type: io.kestra.plugin.jdbc.oracle.BatchExamples
Fetch rows from a table and bulk insert to another one
id: oracle_batch
namespace: company.team
tasks:
- id: query
type: io.kestra.plugin.jdbc.oracle.Query
url: jdbc:oracle:thin:@dev:49161:XE
username: oracle
password: "{{ secret('ORACLE_PASSWORD') }}"
sql: |
SELECT *
FROM xref
FETCH FIRST 1500 ROWS ONLY;
fetchType: STORE
- id: update
type: io.kestra.plugin.jdbc.oracle.Batch
from: "{{ outputs.query.uri }}"
url: jdbc:oracle:thin:@prod:49161:XE
username: oracle
password: "{{ secret('ORACLE_PASSWORD') }}"
sql: |
insert into xref values( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )
Fetch rows from a table and bulk insert to another one, without using sql query
id: oracle_batch
namespace: company.team
tasks:
- id: query
type: io.kestra.plugin.jdbc.oracle.Query
url: jdbc:oracle:thin:@dev:49161:XE
username: oracle
password: "{{ secret('ORACLE_PASSWORD') }}"
sql: |
SELECT *
FROM xref
FETCH FIRST 1500 ROWS ONLY;
fetchType: STORE
- id: update
type: io.kestra.plugin.jdbc.oracle.Batch
from: "{{ outputs.query.uri }}"
url: jdbc:oracle:thin:@prod:49161:XE
username: oracle
password: "{{ secret('ORACLE_PASSWORD') }}"
table: XREF
Properties
from *Requiredstring
Input file from internal storage
URI of the source file (kestra://) containing rows to insert
Pebble expression referencing an Internal Storage URI e.g. {{ outputs.mytask.uri }}.
url *Requiredstring
The JDBC URL to connect to the database
chunk integerstring
1000Batch size per executeBatch call
Number of rows sent per JDBC batch before commit; default 1,000
columns array
Columns bound to placeholders
Ordered column names matching ? placeholders; if omitted, placeholder count must match all columns in the input row
connectionPoolSize integerstring
10Maximum number of pooled connections
Maximum connections held in the pool for a given URL and credentials. Default 10. Increase for flows that run many concurrent queries against the same database to avoid waiting for an available connection. Ignored when connectionPooling is false or for embedded drivers.
connectionPooling booleanstring
trueReuse database connections via a connection pool
When true (default), connections are pooled and reused across executions, keyed by URL and credentials, removing the connect and TLS-handshake cost on each run. Set to false if your SQL relies on session state persisting on the connection (for example SET search_path, session-scoped temp tables or variables), since pooled connections are reused. Embedded drivers (DuckDB, SQLite, MS Access) never pool regardless of this setting.
inputHandling string
AUTOAUTOSTREAMLOCALInput handling strategy
Controls how input is read during processing and retries.
AUTO buffers small files locally (<= localBufferMaxBytes) and streams large files.
STREAM always streams from internal storage.
LOCAL always buffers input to a local temporary file before processing.
localBufferMaxBytes integerstring
104857600Maximum number of bytes buffered locally
Used by AUTO and LOCAL input handling.
In AUTO, files larger than this threshold are streamed.
In LOCAL, files larger than this threshold fail fast.
maxRetries integerstring
3Maximum number of retries for transient failures
Retries are attempted only for transient failures such as temporary I/O and recoverable SQL errors.
password string
The database user's password
pluginDefaultsRef Non-dynamicstring
Reference (ref) of the pluginDefaults to apply to this task.
resumeOnRetry booleanstring
trueResume from the last successfully committed chunk on retry
retryBackoff string
PT1SDelay between retry attempts
Uses ISO-8601 duration format, for example PT1S.
retryScope string
INPUTNONEINPUTALLControls which failures are retried
INPUT retries input handling failures, ALL retries all retryable failures.
sql string
Parameterized INSERT statement to execute
Prepared INSERT with ? placeholders for each bound column.
Example: INSERT INTO
table string
Table used to auto-discover columns
Retrieves column names from the given table when columns is empty.
If sql is also omitted, an INSERT statement is generated automatically using the discovered columns
timeZoneId string
The time zone id to use for date/time manipulation. Default value is the worker's default time zone id
username string
The database user
Outputs
rowCount integer
Total rows read
updatedCount integer
Rows inserted or updated
Metrics
query counter
queriesThe number of batch queries executed.
records counter
recordsThe number of records processed.
updated counter
recordsThe number of records updated.
