How can I test for no rows back from Oracle query

[vincenr]

I am trying to figure out how to route to a ‘no data’ path when nothing is returned from my select query. Can someone help me out?

What I’ve tried is (in ‘Is there data?’ snap): $retval != null (then go to data path); otherwise, go to nodata path. But I’m guessing since the select didn’t return anything, there is no $retval to look at (catch22).

I also tried:

[koryknick]

Here is an example pipeline that performs the step that I had described, but I hadn’t tested it until now to see that there are two Mappers that need to be introduced to get the behavior I described.

Basically, the “Map dummy value” snap just creates an input document for the Oracle - Execute snap to enable the Passthrough so it will generate something on the output even if no records are present from the query. The “Remove $original” strips that dummy element off the output records - finally, the “Has data?” Router snap can check to see if the input document is empty using the syntax I had provided earlier.

Pipeline attached: Community 13057 - Oracle Execute with no data_2022_08_04.slp (6.8 KB)

[vincenr]

I am revisiting this issue because I still can’t get your $={} comparison to work when there is data vs. when there isn’t? Can you assist with this?

[smudassir]

After your Oracle snap, put an Exit snap with threshold set to zero.
And run the above pipline as a child via PipeExec with error view. If pipeexec’s error view gets populated then there is output of query. If only output view of the pipeexec gets populated then there is no output from the query. HTH