Page Comparison

On this Page

Table of Contents

maxLevel	2
exclude	Older Versions\|Additional Resources\|Related Links\|Related Information

Snap type:

Read

Description:

This Snap allows you to fetch data from an Oracle database by providing a table name and configuring the connection. The Snap produces the records from the database on its output view which can then be processed by a downstream Snap.

JSON paths can be used in a query and will have values from an incoming document substituted into the query. However, documents missing values for a given JSON path will be written to the Snap's error view. After a query is executed, the query's results are merged into the incoming document overwriting any existing keys' values. The original document is output if there are no results from the query.

Queries produced by the Snap have an equivalent format:

Code Block
SELECT * FROM [table] WHERE [where clause] ORDER BY [ordering] LIMIT [limit] OFFSET [offset]

If more powerful functionality is desired, then the Execute Snap should be used.

Expected upstream Snaps: A dynamic where a clause can be defined by providing values upstream, such as the Mapper Snap can provide constants, pipeline parameters mapped into variables, which then can be used in the where clause or even in the table/schema properties.
Expected downstream Snaps: The Snap will output one document for every record retrieved, hence any document processing Snap can be used downstream.
Expected input: Document that provides values for one or more properties of the Snap or simply for pass through purpose.
Expected output: Document for each record retrieved. Special types such as TIMESTAMP, TIMESTAMPTZ and TIMESTAMPLTZ are converted into SnapLogic internal date type representations which then can be consumed by downstream Snaps just like any other data type.

Info
The where clause can only use variables, not constants or pipeline parameters.

A good example of a where clause is: SALARY =$SALARY (here we use the SALARY variable of the input document).

Prerequisites:

None

Support and limitations: Works in Ultra Task Pipelines.

Account:

This Snap uses account references created on the Accounts page of SnapLogic Manager to handle access to this endpoint. See Oracle Account for information on setting up this type of account.

Views:

Input	This Snap allows up to one input view. If the input view is defined, then the where clause can substitute incoming values for a given expression, such as a table name or as a variable as part of the where clause.
Output	A second view can be added to show the metadata for the table as a Document. The metadata document can then be fed into the second input view of Oracle - Insert or Bulk Load Snap so that the table is created in Oracle with a similar schema as the source table.
Error	This Snap has at most one error view and produces zero or more documents in the view.

Settings

Label*

Specify 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

Specify the database schema name. Selecting a schema filters the Table name list to show only those tables within the selected schema.

Default value: None
Example: SYS

Table name*

Enter or select the name of the table to execute the select on.

Default value: None
Example: people

Where clause

WHERE clause of SELECT statement. The value to be used in the WHERE clause will be used here. Document value substitutions (such as $person.firstname with the value found in the incoming document at the path) can also be used as needed. The "=" will not need to be checked when using document value substitutions unless using expression language.

Default value: None
Examples:

Multiexcerpt include macro

name	ME_DB_Snaps_Query_Examples
page	Oracle - Update

Note
The Where clause property does not support passing Pipeline parameters or passing upstream parameters.

Order by: Column names

Enter in the columns in the order in which you want to order by. The default database sort order will be used.

Default value: None
Example: name, email

Limit offset

Specify the first row for the query.

Default value: None
Example: 0

Limit rows

Specify the number of rows to return from the query.

Default value: None
Example: 10

Output fields*

Enter or select output field names for SQL SELECT statement. To select all fields, leave it at default.

Default value: None
Example: email, address, first, last

Fetch Output Fields In Schema

Insert excerpt

	Cassandra - Select
	Cassandra - Select
nopanel	true

Default value: Not selected

Pass through

Select this checkbox to pass the input document to the output view under the key 'original'.

Default value: Selected

Ignore empty result Select this checkbox to ignore empty results, that is no document will be written to the output view when a SELECT operation does not produce any result.
If you do not select this checkbox and select the Pass through checkbox, the input document will be passed through to the output view.

Default value: Not selected

Auto commit

Select one of the options for this property to override the state of the Auto commit property on the account. The Auto commit at the Snap-level has three values: True, False, and Use account setting. The expected functionality for these modes are:

True - The Snap will execute with auto-commit enabled regardless of the value set for Auto commit in the Account used by the Snap.
False - The Snap will execute with auto-commit disabled regardless of the value set for Auto commit in the Account used by the Snap.
Use account setting - The Snap will execute with Auto commit property value inherited by the Account used by the Snap.

Default value: False

Match data types

Conditional. If you select this checkbox, the Snap matches the output data types same as when the Output fields property is empty (SELECT * FROM ...). The output preview would be in the same format as the one when SELECT * FROM is implied and all the contents of the table are displayed.

Note
This field applies only when the Output fields field is provided with any values.

Default value: Not selected

Number of retries

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

Multiexcerpt include macro

name	retries
page	File Reader

Retry interval (seconds)

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

Staging mode

Required when the value in the Number of retries field is greater than 0.

Specify the location from the following options to store input documents between retries:

In memory: The query results are stored in the Snaplex memory. If the query is too large to fit in the memory space, it may cause the Snap to fail, choose the On disk option.
On disk: The query results are stored on the disk in a temporary (tmp) directory that is managed by the SnapLogic platform. This directory is deleted automatically when the Snap terminates.

Default value: In memory

Note
To disable staging, enter 0 in the Number of retries field.

Multiexcerpt include macro

name	Snap Execution
page	Anaplan Read

Multiexcerpt include macro

name	Snap_Execution_Introduced
page	Anaplan Read

Note

For the 'Suggest' in the Order by columns and the Output fields properties, the value of the Table name property should be an actual table name instead of an expression. If it is an expression, it will display an error message "Could not evaluate accessor: ..." when the 'Suggest' button is clicked. This is because, at the time the "Suggest" button is clicked, the input document is not available for the Snap to evaluate the expression in the Table name property. The input document is available to the Snap only during the preview or execution time.

Examples

Using the Where clause with expressions enabled:

Image Modified

The values are passed upstream for the Oracle Select Snap to fetch the data from a table:
Image Modified

The Oracle Select Snap retrieves the records from the EMPTABLE. The values passed from the upstream for the Where clause in different ways is displayed below:

Without expressions enabled: The values are directly passed for the Select Snap to retrieve the specified records.
Image Modified

With expressions enabled:

The queries can be passed in either of the ways as below for the above upstream values (using the Mapper Snap):

"EMPNO=$EMPNO and ENAME=$EMPNAME"
"EMPNO=" + $EMPNO + " and ENAME='" + $EMPNAME+ "'"

Successful execution of the Snap displays the below output preview, with the field names as provided from the upstream:
Image Modified

Following are the two examples. The first one using a where clause for a conditional retrieval. The second one will showcase error handling.

Expand

title	1. Using a where clause for a conditional retrieval

Image Modified

The first Oracle Execute Snap will drop the existing table. We use an error view to handle the case where the table does not exist yet. Both the error and the output view then are funneled into a Union Snap which triggers the actual insertion data provided by a JSON Generator Snap.
Then we use a Mapper Snap to map back the original input data and use a Tail to use the last record (having the color black).
Next, we select on the table using the where clause: "color"=$color, which will retrieve the color black from the table and write it to the output view.
Hint: The table COLOR1 was created on the fly, and the color column is created lowercase. We quote the color column in the merge condition since it is defined as a lower case column on the table.
Hint: Multiple columns can be used in an expression such as "color"=$color and "value"=$value

Expand

title	2. Error view on the Oracle Select Snap

In the second example, we use an error view on the Oracle Select Snap.
Image Modified

Here we mis-typed the variable in the condition, which will trigger an error. The error will be written to the error view of the Snap, which then can be used for debugging purpose or further processing.
An example of the error view is shown below.

Error is the message provided by SnapLogic.
Reason is defined as the underlying reason of the error, here the path $colo does not exist in the input view.
Original is defined as the data from the input view.

Code Block

invalidValuePath: "$" error: "Unable to read path: $.colo" reason: "Field not found in JSON object: colo" invalidSubPath: "$.colo" original: color: "black" value: "#000" resolution: "Check the path syntax" stacktrace: "com.Snaplogic.jsonpath.FieldNotFoundException: Field not found in JSON object: colo\n ... invalidValue: color: "black" value: "#000"

Getting Data For Columns Using Fetch Output Fields In Schema

In this example Pipeline, we use an Oracle Select Snap to get data for only the fields that we provide in the Output fields list.

We configure the Oracle Select Snap to get data from TECTONIC.EMPLOYEE table, and fetch EMPLOYEE_ID and EMPLOYEE_NAME columns in the output.

On validation, we see that only the selected output fields (EMPLOYEE_ID and EMPLOYEE_NAME) appear in the preview and column schema.

Image Modified

Download the Pipeline.

Downloads

Multiexcerpt include macro

name	download_instructions
page	OpenAPI

Attachments

old	false
patterns	.slp, .zip

Insert excerpt

	Oracle Snap Pack
	Oracle Snap Pack
nopanel	true

Versions Compared

Old Version 51

New Version 52

Key

Settings

Examples

Getting Data For Columns Using Fetch Output Fields In Schema

Downloads