In this article

1 Overview
2 Upcoming
- 2.1 Snap Type
- 2.2 Limitations and Known Issues
- 2.3 Support for Ultra Pipelines
- 2.4 Snap Views
3 Snap Settings
- 3.1 Auto commit
4 Examples
5 Snap Pack History

Overview

You can use the Oracle - Execute Snap to execute an SQL update query with the specified properties. Document keys are used as the columns to update, and their values are used as the updated value.

Updates will be batched up until the account's batch size property or until the structure of the update statement changes. An update statement will change if an incoming document contains different keys than a previous document.

Upcoming

Snap Type

Write

Limitations and Known Issues

None.

Support for Ultra Pipelines

Works in Ultra Pipelines.

Snap Views

Type	Format	Number of Views	Examples of upstream and downstream Snaps	Description

Type

Format

Number of Views

Examples of upstream and downstream Snaps

Description

Input

Document

Min: 1
Max: 1

Mapper
Oracle Execute

Values to be updated in the target columns.

Output

Document

Min: 0
Max: 1

File Writer
Mapper

Updated records.

Error

Error handling is a generic way to handle errors without losing data or failing the Snap execution. You can handle the errors that the Snap might encounter while running the Pipeline by choosing one of the following options from the When errors occur list under the Views tab. The available options are:

Stop Pipeline Execution: Stops the current pipeline execution when the Snap encounters an error.
Discard Error Data and Continue: Ignores the error, discards that record, and continues with the rest of the records.
Route Error Data to Error View: Routes the error data to an error view without stopping the Snap execution.

Learn more about Error handling in Pipelines.

Database Write Snaps output all records of a batch (as configured in your account settings) to the error view if the write fails during batch processing.

Snap Settings

Asterisk (*): Indicates a mandatory field.
Suggestion icon ( ): Indicates a list that is dynamically populated based on the configuration.
Expression icon (): Indicates the value is an expression (if enabled) or a static value (if disabled). Learn more about Using Expressions in SnapLogic.
Add icon ( ): Indicates that you can add fields in the field set.
Remove icon (): Indicates that you can remove fields from the field set.

Session parameters	Use this fieldset to set the National Language Support (NLS) parameters. Learn more about Setting NLS Parameters. The NLS parameters override the default value, for example, comma (,) set for the session in the initialization parameter file or set by the client with environment variables, such as a decimal point.
Field	Field Type	Description
Label* Default Value: Oracle - Update Example: Oracle - Update	String	The name for the Snap. You can modify this to be more specific, especially if you have more than one of the same Snap in your Pipeline.
Schema Name Default Value: None Example: SYS	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.
Table name* Default Value: None Example: people	String/Expression/Suggestion	Specify the name of the table to execute the update operation on. This list is populated based on the tables associated with the selected schema.
Update condition* Default value: None Examples: Without using expressions `EmpId = 12` `email = 'you@example.com'` Using expressions `"EMPNO=$EMPNO and ENAME=$EMPNAME"` `email = $email` `emp=$emp` `"emp='" + $emp + "'"` `"EMPNO=" + $EMPNO + " and ENAME='" + $EMPNAME+ "'"`	String/Expression	Specify the SQL WHERE clause of the update statement. You can define specific values or columns to update (Set condition) in the upstream Snap, such as Mapper Snap, and then use the WHERE clause to apply these conditions on the columns sourced from the upstream Snap. For instance, here is a sample of an Update SQL query: `UPDATE table_name` `SET column1 = value1, column2 = value2,` `WHERE condition;` Refer to the example to understand how the Update Condition works.
Number of Retries Default Value: 0 Example: 3	Integer/Expression	Specifies 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.
Retry Interval (seconds) Default value: 1 Example: 10	Integer/Expression	Specifies the time interval between two successive retry requests. A retry happens only when the previous attempt resulted in an exception.
Session parameter name Default value: None Example: NLS_TERRITORY	String/Expression	Specify the name of the NLS parameter. This Snap supports the Enable, Disable, and Force commands in session parameters. This field supports only pipelines parameters, and not upstream values.
Session parameter value Default value: None Example: CZECH REPUBLIC	String/Expression	Specify the value for the NLS parameter. This field supports only pipelines parameters, and not upstream values.
Default value: Execute only Example: Validate & Execute	Dropdown list

Auto commit

In a scenario where the Auto commit on the account is set to true, and the downstream Snap does 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.

Examples

In this example, we will update a record in a table.

This example updates a row of data in table TEST_EMPLOYEE which is defined as below:

CREATE TABLE "TECTONIC"."TEST_EMPLOYEE" (    
    "Title" VARCHAR2(4000 BYTE), "Employee_Name" VARCHAR2(4000 BYTE), "Employee_ID" VARCHAR2(4000 BYTE)
);

We need to change the Title column of the row that has "Cooper" as Employee_Name to "Senior Software Engineer". First, we use a JSON Generator Snap to pass the new data:

Then we set up the table name and the update condition:

This is an example output of the pipeline:

This example pipeline demonstrates how error handling works. We use the same pipeline as the one used in Example #1. The difference is this time we try to update the column "Titlee" which doesn't exist in TEST_EMPLOYEE table:

And this is the error message routed to the error view:

In this example, we will use the well-known Northwind sample database Following is a selection from the "Customers" table:

CustomerID	CustomerName	ContactName	Address	City	PostalCode	Country

CustomerID	CustomerName	ContactName	Address	City	PostalCode	Country
1	Alfreds Futterkiste	Maria Anders	Obere Str. 57	Berlin	12209	Germany
2	Ana Trujillo Emparedados y helados	Ana Trujillo	Avda. de la Constitución 2222	México D.F.	05021	Mexico
3	Antonio Moreno Taquería	Antonio Moreno	Mataderos 2312	México D.F.	05023	Mexico
4	Around the Horn	Thomas Hardy	120 Hanover Sq.	London	WA1 1DP	UK
5	Berglunds snabbköp	Christina Berglund	Berguvsvägen 8	Luleå	S-958 22	Sweden

Assume we wish to update the customer "Alfreds Futterkiste" with a new contact person and city.

Enter Customers in the Table name property and CustomerName='Alfreds Futterkiste' in the Update condition property.

Assume that the Update Snap receives a Map data in the input view as following:

{ "ContactName" : "Alfred Schmidt", "City" : "Hamburg" }

UPDATE Customers SET ContactName='Alfred Schmidt', City='Hamburg' WHERE CustomerName='Alfreds Futterkiste';

and submit the request to the database server.

The selection from the "Customers" table will now look like this:

CustomerID	CustomerName	ContactName	Address	City	PostalCode	Country

CustomerID	CustomerName	ContactName	Address	City	PostalCode	Country
1	Alfreds Futterkiste	Alfred Schmidt	Obere Str. 57	Hamburg	12209	Germany
2	Ana Trujillo Emparedados y helados	Ana Trujillo	Avda. de la Constitución 2222	México D.F.	05021	Mexico
3	Antonio Moreno Taquería	Antonio Moreno	Mataderos 2312	México D.F.	05023	Mexico
4	Around the Horn	Thomas Hardy	120 Hanover Sq.	London	WA1 1DP	UK
5	Berglunds snabbköp	Christina Berglund	Berguvsvägen 8	Luleå	S-958 22	Sweden

Be careful when updating records. If we had omitted the Update condition property in the example above, the "Customers" table would have looked like this:

CustomerID	CustomerName	ContactName	Address	City	PostalCode	Country

CustomerID	CustomerName	ContactName	Address	City	PostalCode	Country
1	Alfreds Futterkiste	Alfred Schmidt	Obere Str. 57	Hamburg	12209	Germany
2	Ana Trujillo Emparedados y helados	Alfred Schmidt	Avda. de la Constitución 2222	Hamburg	05021	Mexico
3	Antonio Moreno Taquería	Alfred Schmidt	Mataderos 2312	Hamburg	05023	Mexico
4	Around the Horn	Alfred Schmidt	120 Hanover Sq.	Hamburg	WA1 1DP	UK
5	Berglunds snabbköp	Alfred Schmidt	Berguvsvägen 8	Hamburg	S-958 22	Sweden

Oracle - Update