On this Page
On this Page
Table of Contents | ||||
---|---|---|---|---|
|
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
Multiexcerpt include macro | ||||||||
---|---|---|---|---|---|---|---|---|
|
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.
If more powerful functionality is desired, then the Execute Snap should be used.
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: | 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
| |||||||||||
Settings | ||||||||||||||
| ||||||||||||||
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. | |||||||||||||
Table name* | Enter or select the name of the table to execute the select on. Default value: None | |||||||||||||
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
| |||||||||||||
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 | |||||||||||||
Limit offset | Specify the first row for the query. | SYSTable name* | Enter or select the name of the table to execute the select on 0 | |||||||||||
Limit rows | Specify the number of rows to return from the query. | 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 | people10 | ||||||||||
Output fields* | Enter or select output field names for SQL SELECT statement. To select all fields, leave it at default. Default value: | None | Examples:
Multiexcerpt include macro | ||||
---|---|---|---|---|
|
Note |
---|
The Where clause property does not support passing Pipeline parameters or passing upstream parameters. |
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
Default value: None
Example: 0
Specify the number of rows to return from the query.
Default value: None
Example: 10
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
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
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
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
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
Example: email, address, first, last
Insert excerpt | ||||||
---|---|---|---|---|---|---|
|
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
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
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
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
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
Info |
---|
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
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
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. |
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
Multiexcerpt include macro | ||||
---|---|---|---|---|
|
Multiexcerpt include macro | ||||
---|---|---|---|---|
|
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:
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.
Expand | ||
---|---|---|
| ||
|
Expand | ||
---|---|---|
| ||
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.
|
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.
Downloads
Multiexcerpt include macro | ||||
---|---|---|---|---|
|
Attachments | ||||
---|---|---|---|---|
|
Insert excerpt | ||||||
---|---|---|---|---|---|---|
|