How to handle empty recordset from Stored Procedure

[JackieM]

I have a job that calls an Oracle Stored Procedure so I have no ability to make any updates to the Stored Procedure. The Stored Procedure returns data in a cursor. My flow works great if the cursor contains data:

tDBSP -> tParseRecordSet -> rest of processing

The issue is that I need to take a specific action if there are no records returned from the SP, i.e. the recordset is empty.  But in this situation, I just get 0 records out of the tParseRecordSet and the flow is done.

How can I handle this situation?

[Rahul_Kale]

Hello JackieM,

Thank you for reaching out to the Qlik community.
 

Use a tFlowMeter or tDBSP → tDBRow with a COUNT check, then branch the flow.
 

Common pattern:-

1. tDBSP → tParseRecordSet
2. Add tFlowMeter after tParseRecordSet
3. Use Run If (or OnComponentOk) with condition:tFlowMeter_1.NB_LINE == 0
4. Route this to your “no data returned” action.
5. Normal flow continues when NB_LINE > 0.

Alternative (simpler):-

• Use tDBRow before/after the SP to call a SELECT COUNT(*) wrapper query and branch based on the result.

Below are the related Talend documentation that can help you:-

• tParseRecordSet:
  https://help.talend.com/r/en-US/8.0/studio-user-guide/tparserecordset
• tFlowMeter:
  https://help.talend.com/r/en-US/8.0/studio-user-guide/tflowmeter
• Triggers & Run If conditions:
  https://help.talend.com/r/en-US/8.0/studio-user-guide/using-triggers-and-links

[JackieM]

Hi @Rahul_Kale - Thank you for the reply.  The issue is that when the cursor is empty, 0 rows come out of tParseRecordSet so it doesn't matter what components I have after tParseRecordSet as the flow dies with tParseRecordSet.  

Your simpler suggestion is great but this is a vendor SP and I don't have access to see the code.  So I can't write a select statement based on the query in the SP since I can't see what it is.

[JackieM]

Hi @Rahul_Kale - I tried adding a tFlowMeter and the flow does seem to continue through it (with 0 rows):

However, I don't have a NB_LINE available to me from tFlowMeter to work with. I only have ERROR_MESSAGE:

[JackieM]

I was ultimately able to resolve this issue by writing code in a tJavaRow component linked from tDBSP that determines if cursor is empty and takes appropriate action.

tDBSP -> tJavaRow -> (if cursor has data) ->  tParseRecordSet 

                                 -> (if cursor doesn't have data) -> processing to handle empty cursor

[Rahul_Kale]

Good to know that JackieM & Thank you for updating us.