In this article
You can use this Snap to execute an Oracle statement or query. This is a Write Snap type. This Snap works only with single queries.
This Snap:
|
|
None.
Works in Ultra Pipelines.
When the SQL statement property is an expression, the Pipeline parameters are shown in the Suggest, but not the input schema.
main23721
(because of the JOOQ upgrade) previously, when you used a stored procedure in the Oracle-Execute Snap, the Snap displayed a Message:Success
in the output. Now, the Snap displays $UPDATE_COUNT=-1
.As part of 436patches25696
, when you use a stored procedure in the Oracle - Execute Snap, the Snap displays Message: Success
and $UPDATE_COUNT=-1, 0, or 1
(based on the Snap Pack behavior) in the output.
Input/Output | Type of view | Number of views | Examples of Upstream/Downstream Snap | Description | |
---|---|---|---|---|---|
Input | Document |
|
| This Snap has at most one document input view. If the input view is defined, then the where clause can substitute incoming values for a given expression. | |
Output | Document |
|
| This Snap has at most one document output view. If an output view is available and an update/insert/merge/delete statement was executed, then the original document that was used to create the statement will be output with the status of the statement executed.
|
Field | Field Type | Description | ||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
Label* | String | Enter a unique 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. Default Value: Oracle Execute | ||||||||||||||||
SQL statement* | String | Specify the SQL statement to execute on the server.
Default Value: [None] | ||||||||||||||||
Query type | Dropdown list/Expression | Select the type of query for your SQL statement (Read or Write). When Auto is selected, the Snap tries to determine the query type automatically. Default Value: Auto | ||||||||||||||||
Pass through | Checkbox | Select this checkbox to pass the input document to the output view under the key ' Default Value: Selected | ||||||||||||||||
Ignore empty result | Checkbox | Select this checkbox to not write any document to the output view when a SELECT operation does not produce any result. Default Value: Not selected | ||||||||||||||||
Auto commit | Dropdown list | Choose one of the options from the list to override the state of the Auto commit on the account. The available options are:
Default Value: Use account setting
| ||||||||||||||||
Number of retries | Integer | 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 | ||||||||||||||||
Retry interval (seconds) | Integer | Specify the time interval between two successive retry requests. A retry happens only when the previous attempt resulted in an exception. Default Value: 1 | ||||||||||||||||
Column-specific timestamp precision | Checkbox | 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 | Dropdown list |
In this example, we have a table named STUDENT that stores students' scores. It has 3 columns: ID, NAME and SCORE. We will get the record of a student named "Brace" with this pipeline:
The JSON Generator Snap passes the student's name to Oracle-Execute Snap:
Here is the running result of the pipeline: |
This example will show the error handling of the Snap. We use the same pipeline as the one in example #1 and route error messages to error view:
The Oracle - Execute Snap tries to select AGE column that doesn't exist in table STUDENT. This should cause a SQL execution error and the error message will be routed to error view: Here is the error message that is routed to the error view: |