On this Page

Overview

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. 

Upcoming

Snap type:

Read

Description:


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:

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.
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: 

Without using expressions

Using expressions

  • "EMPNO=$EMPNO and ENAME=$EMPNAME"

  • email = $email 

  • emp=$emp

  • "emp='" + $emp + "'"

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


Using expressions that join strings together to create SQL queries or conditions has a potential SQL injection risk and is hence unsafe. Ensure that you understand all implications and risks involved before using concatenation of strings with '=' Expression enabled. 


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

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 resultSelect 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.


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

If the value is larger than 0, the Snap first downloads the target file into a temporary local file. If any error occurs during the download, the Snap waits for the time specified in the Retry interval and attempts to download the file again from the beginning. When the download is successful, the Snap streams the data from the temporary file to the downstream Pipeline. All temporary local files are deleted when they are no longer needed.

Ensure that the local drive has sufficient free disk space to store the temporary local file.

Default value: 0

Example: 3

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

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


Column-specific timestamp precision

Select this checkbox to display the timestamp with millisecond/microsecond/nanosecond precision in string type.

By default, the checkbox is deselected, which maintains the backward compatibility (supports only millisecond of date time type).

Default Value: Deselected

  

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:

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

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.

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:

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

 

  • 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


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

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.
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.

Download the Pipeline.

Downloads