Oracle - Insert

Overview

The Oracle - Insert Snap executes an SQL Insert statement.

Document keys are used as the columns to insert into, and their values are inserted into the column. Any missing column will insert a null value into it.

Important: JDBC Driver upgrade and behavior change

The Oracle JDBC Driver is upgraded from OJDBC6 JAR (v11.2.0.4) to OJDBC10 JAR (v19.20.0.0) in the latest distribution in October 2023 and deployed to the stable distribution in the November 2023 release (after the Snaplex upgrade). The latest JDBC driver upgrade is backward-compatible. Learn more: Oracle JDBC driver and JOOQ upgrades.

You can consume this driver upgrade with the 434patches23000 Snap Pack version.

This JDBC driver upgrade has resulted in specific behavior changes that include errors, status codes, and success and failure messages. Learn more about the behavior changes to ensure your migration to the upgraded driver is seamless.



  • This is a Write-type Snap.
  • Works in Ultra Tasks when the Batch size is set to 1.

Prerequisites

Known issues

The Oracle - Insert Snap inserts incorrect time zone data for timestamp with time zone data type. For example, when an input value is 2021-08-10 17:30:43-07:00, the value written to the target table is 2021-08-10 17:30:43+07:00.

Workaround: Use an expression Date.parse(2021-08-10 17:30:43-07:00) in Mapper Snap. The Mapper Snap converts it to a UTC timestamp string, that is, 2021-08-11 00:30:43 00:00. The Snap will now insert the value successfully.

Snap views

Type Description Examples of upstream and downstream Snaps
Input

This Snap has at least one document input view (Min: 1, Max: 2).

Requires the Schema name and Table name to process a certain number of input documents into a prepared insert query.

The second input view can be added for metadata for the table as a document so that the table is created in Oracle with a similar schema as the source table. If the table already exists, then the metadata is not used.
Output

This Snap has at most one document output view (Min: 0, Max: 1).

Snap outputs the number of documents successfully inserted into the target table in the output view document.
Learn more about Error handling.

Snap settings

Legend:
  • Expression icon (): Allows using pipeline parameters to set field values dynamically (if enabled). SnapLogic Expressions are not supported. If disabled, you can provide a static value.
  • SnapGPT (): Generates SnapLogic Expressions based on natural language using SnapGPT. Learn more.
  • Suggestion icon (): Populates a list of values dynamically based on your Snap configuration. You can select only one attribute at a time using the icon. Type into the field if it supports a comma-separated list of values.
  • Upload : Uploads files. Learn more.
Learn more about the icons in the Snap settings dialog.
Field/Field set Description

Label

String

Required. Specify a unique name for the Snap. Modify this to be more appropriate, especially if more than one of the same Snaps is in the pipeline.

Default value: Oracle - Insert

Example: Insert Emp data
Schema name

String/Expression/ Suggestion		 Specify the database schema name. The suggestions in the Schema field are populated only when at least a single table exists in the schema. If no tables exist to use that schema, only SYS, SYSTEM, and XDB are populated.

Default value: N/A

Example: SYS
Table name

String/Expression/ Suggestion		 Specify the table that the rows will be inserted into. This list is populated based on the tables associated with the selected schema.

Default value: N/A

Example: people
Create table if not present

Checkbox		 Select this checkbox to automatically create the target table if it does not exist.
  • If a second input view is configured for the Snap and it contains a document with schema (metadata) from the source table, the Snap creates the new (target) table using the same schema (metadata). However, if the schema comes from a different database, the Snap might fail with the Unable to create table: "<table_name>" error due to data type incompatibility.
  • In the absence of a second input view, the Snap creates a table based on the data types of the columns generated from the first row of the input document (first input view).

Due to implementation details, a newly created table is not visible to subsequent database Snaps during runtime validation. If you want to immediately use the newly updated data you must use a child Pipeline that is invoked through a Pipeline Execute Snap.

Learn more about table creation.

Default value: Deselected
Preserve case sensitivity

Checkbox		 Select this checkbox to preserve the case sensitivity of the column names while performing the insert operation.
  • If you deselect the Preserve case sensitivity checkbox, the input documents are loaded to the target table if the key names in the input documents match the target table column names ignoring the case.
  • If you include a second input view, selecting this checkbox has no impact on the column names of the target table, because the Snap uses the metadata from the second input view.
Number of retries

Integer/Expression		 Specify the maximum number of attempts to be made to receive a response. The request is terminated if the attempts do not result in a response.

Default value: 0

Example: 3
Retry interval (seconds)

Integer/Expression		 Specify the time interval between two successive retry requests. A retry happens only when the previous attempt resulted in an exception.

Default value: 1

Example: 10
Session parameters

Use this field set to define the session parameters. This property lets you use the National Language Support (NLS) parameters. See the Oracle documentation for detailed information about Setting NLS Parameters.

The NLS parameters enable overriding the default value (e.g., a comma) that is set for the session in the initialization parameter file or set by the client with environment variables (e.g., a decimal point).

You can add multiple parameters by using the plus (+) sign next to Session parameters.
Session parameter name

String/Expression		 Specify the session parameter name.

Default value: N/A

Example: NLS_TERRITORY
Session parameter value

String/Expression		 Specify the session parameter value.

Default value: N/A

Example: "CZECH REPUBLIC"

Snap execution

Dropdown list
Choose one of the three modes in which the Snap executes. Available options are:
  • Validate & Execute: Performs limited execution of the Snap and generates a data preview during pipeline validation. Subsequently, performs full execution of the Snap (unlimited records) during pipeline runtime.
  • Execute only: Performs full execution of the Snap during pipeline execution without generating preview data.
  • Disabled: Disables the Snap and all Snaps that are downstream from it.

Default value: Execute only

Example: Validate & execute

Auto commit

In a scenario where the Auto commit on the account is set to true, and the downstream Snap depends on the data processed on an upstream Database Bulk Load Snap, use the Script Snap to add delay for the data to be available.

For example, when performing a create, insert, and a delete function sequentially on a pipeline, using a Script Snap helps in creating a delay between the insert and delete function or otherwise it may turn out that the delete function is triggered even before inserting the records on the table.

Oracle reports failures for the entire batch even if some rows successfully inserted/updated. Because the Snap must report what is reported by the database, the Snap sends all records to the error view and no record to the output view. If you want to see accurate results for each row you must set the batch size to 1 and execute the pipeline.

Examples

  1. Insert input data into a table
  2. Error Handling of the Snap (Oracle Insert)