Oracle Execute Snap Behavior when no data returned for a SQL Query

[sponnada]

Hi All,

While we are using Oracle Execute snap, we are unable to propagate ports / column names to next snaps like mapper where there is no data in source table which is resulting pipeline failure since column names are not propagated. To mitigate this issue, we are adding always a union query to pull a dummy record to get at least 1 record from query. Is there any easy way or setting we need to do as every time we have to add this dummy record in SQL Query and need to add filter to eliminate to avoid data loads into target table.

Regards,
Srini Ponnada

[skatpally]

Hello Srini,

I use Oracle execute snap when we have complex queries to execute. Any reason why you are not using Oracle Select query where you have an option in the Settings tab. Oracle Execute also has the same setting

From Documentation

Ignore empty result If selected, no document will be written to the output view when a SELECT operation does not produce any result. If this property is not selected and the Pass through the property is selected, the input document will be passed through to the output view.
Default value: Not selected

[sponnada]

Oracle execute is allowing us to pull data by joining more than one table to apply SQL Query level joins and filters. This way we are leveraging the dw appliances power to get changed data in a faster way so I am using Oracle execute than oracle select.

[skatpally]

You can add a mapper to propagate the column names to the input view of the Oracle Execute Snap and enable the pass through button so that you will have the original object flowing from the Execute snap. You can provide an example.