¶ Description
Insert, Update or Delete row in an Oracle database.
¶ Parameters
¶ Standard Tab
Parameters:
Connection
- Connection name — choose a connection defined in Global parameters → Services → Databases with Driver = Oracle OCI (see screenshots below).
- Inspect database — opens the Oracle browser to confirm accessible schemas/tables.
Table Name
- Target Oracle table. You can type a literal name (e.g.,
HR.EMPLOYEES) or pick a Constant from Global parameters → Definitions → Constants (the chooser shows items likefirstSource_const_1,firstSource_const_2).
Operation
insert|update|delete— the action to perform on the Table Name.
Write all columns
- ON: every incoming column that matches a target field is written.
- OFF: use Columns selection to explicitly choose which columns to send.
Columns selection
- Opens the column selector to pick a subset of input columns for the write operation (visible when Write all columns = OFF).
Columns with Date/Time/Timestamp
- Format for Dates columns — Oracle date format model for DATE columns (e.g.,
dd/MM/yy, presets list shows items likedateFormat.0…). - Format for TimeStamp columns — Oracle format for TIMESTAMP columns (e.g.,
YYYYMMDD). - Time columns — column selector to list fields that are TIME-like in your input (if you carry pure time-of-day as a separate value).
- Format for Time columns — Oracle format for TIME values (e.g.,
HH24:MI:SS).
¶ Advanced Tab
Parameters:
- BULK operation: Update number of Rows in one go
- Allow 'strange' chracters in Column's Names.
- Error Management
- Status Name
- In case of Error
- Write all rows
- Only rows where
- equals
- Custom bindings
- Minimum size for one cell of type String bytes
- Automatic SQL command generation
¶ About
Writes rows from the pipeline into Oracle using the OCI driver. Supports insert, update, and delete. You can write all columns automatically or explicitly choose which columns to send. The box also provides precise control over date/time/timestamp formats, bulk size, error management, custom bindings, and custom SQL when needed.
¶ When to Use
- Load data from your pipeline into Oracle tables (staging or production).
- Maintain an existing table using
updateordelete. - Handle Oracle-specific date/time formats without upstream conversions.
- Push high volumes efficiently via bulk operations.
- Execute a hand-crafted SQL statement while still benefiting from the box’s connection and bindings.
¶ How It Works
-
Resolves Connection name to an Oracle OCI entry from Global parameters → Services.
-
Determines target Table Name (literal or constant).
-
Builds the statement according to Operation:
- insert — constructs an
INSERTusing all or selected columns. - update — constructs an
UPDATEusing matching keys/columns; provide the columns necessary to identify and set values as per your table design. - delete — constructs a
DELETEfor the matching key columns.
(If you need exact SQL shape, enable Use a custom SQL query and provide it.)
- insert — constructs an
-
Applies Date/Time/Timestamp formats to map text values into Oracle types correctly.
-
Executes in bulk batches using the configured size.
-
Records row status if Status Name is configured; follows the selected In case of Error strategy.
¶ Minimal Setup (Quickstart)
-
Define the Oracle connection
-
Toolbar → Global parameters → Services → Databases → Add
- Driver =
Oracle OCI - Server =
<hostname or IP> - Port =
5432(example in screenshot; use your Oracle listener port, typically1521) - DB =
<service/subservice>(e.g.,orclpdb1orservice/subservice) - User =
<schema owner or user> - Password = (store in Definitions → Secrets and reference here)
- Others = optional connection string options or session settings.
- Driver =
-
-
Place the box and open Standard
- Connection name = your Oracle connection from step 1
- Table Name = your target table (e.g.,
HR.EMPLOYEES) - Operation =
insert - Write all columns = ON (for a first test)
-
Handle date/time (only if needed)
- Set Format for Dates columns, Format for TimeStamp columns, and Format for Time columns to match your incoming text.
-
Run
- Check the log for processed rows and any Oracle messages.
Validate
- Count target rows before/after; spot-check a few records (especially date/time fields).
- If you added a Status Name, verify the status values after execution.
¶ Example Workflow
-
CSV Read → Clean types → upsertOCI (insert)
Start with Write all columns = ON and BULK = 1000. If you have dates asYYYYMMDD, select that in Format for TimeStamp columns. -
Diff/Join → upsertOCI (update)
After computing changes upstream, set Write all columns = OFF and pick exactly the columns to update in Columns selection. Provide key columns required by your table’sUPDATEpredicate (or switch to Use a custom SQL query with yourMERGE). -
Filter → upsertOCI (delete)
Selectdeleteand pass only the rows that identify records to remove. Consider smaller BULK sizes if you expect many row-level locks.
¶ Compatibility & Limits
-
Requires an Oracle OCI connection defined in Global parameters → Services.
-
Works with standard Oracle format models (e.g.,
DD/MM/YY,YYYYMMDD,HH24:MI:SS). -
Be mindful of:
- NLS/session settings that may affect implicit conversions.
- Required keys/constraints for
update/delete. - Statement cache and bind sizes when sending large strings or many columns.
¶ Performance & Tuning
- BULK operation: start at 1000; increase gradually for throughput; decrease if you hit timeouts or contention.
- Custom bindings: set a Minimum size when sending long strings to reduce buffer resizing.
- Select fewer columns (turn Write all columns = OFF) to lower network and parsing overhead.
- Consider custom SQL (e.g.,
MERGE /*+ APPEND */) for complex upsert logic or when you need optimizer hints.
¶ Error Handling & Troubleshooting
- ORA-errors (auth, network, table not found): confirm Connection name, User, DB (service/subservice) and privileges.
- Date/time parsing errors: align input text with the chosen Format for Dates/TimeStamp/Time columns.
- Constraint violations (PK/UK): review which columns you update/insert and your key strategy.
- Locking/timeout: reduce BULK size; run during low-traffic windows; ensure indexes exist for
update/deletepredicates. - “Strange characters” in headers: enable Allow "strange" characters in column's names. or rename columns upstream.
¶ Security & Credentials
- Store secrets in Global parameters → Definitions → Secrets; reference them from Services → Databases.
- Grant the Oracle user only the privileges required on the target table (INSERT/UPDATE/DELETE).
- Avoid embedding credentials directly in pipelines or documentation.
