This Snap has at most one document input view.

If the input view is defined, 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.

Expected input: A document that provides values for one or more properties of the Snap or simply for pass-through purposes.

• Mapper

This Snap has one or two document output views.

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 Snap or Oracle - Bulk Load Snap so that the table is created in Oracle with a similar schema as the source table.

Expected output: A document for each record retrieved. Special types such as TIMESTAMP, TIMESTAMPTZ, and TIMESTAMPLTZ are converted into SnapLogic internal date type representations which can then be consumed by downstream Snaps.

• Mapper

Label

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

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

Example: SYS

Required. Specify the table from which the rows will be fetched. This list is populated based on the tables associated with the selected schema.

Default value: None

Example: people

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.

Without using expressions:

• EmpId = 12
• email = '[email protected]'

Using expressions:

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

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

Default value: None

Enter 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

Specify the first row for the query.

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

Select this checkbox to include only the selected fields or columns in the Output Schema (second output view). If you do not provide any Output fields, all the columns are visible in the output. If you provide output fields, we recommend you select the Fetch Output Fields In Schema checkbox.

Default value: Not selected

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

Default value: Selected

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

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.

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.

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

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

Snap execution

Default value: Validate & execute

Example: Execute only

Set the following URL property in the Oracle account:

oracle.jdbc.timezoneAsRegion=false

or JVM option in the global properties:

-Doracle.jdbc.timezoneAsRegion=false