will be helpful for those in need First, don't mess with SET ROWCOUNT - that does something very dangerous (if used incorrectly). Return values are actually handled by adding a parameter with "direction" of ReturnValue. Alternatively, SELECT the value, and use ExecuteScalar.
like below fixes the issue This is covered in the documentation. The key details to learn about are locators, associate result set locators and then allocate ... cursor for result set. After that is done, your calling procedure can treat the cursor just like any other (fetching rows , close etc).
How to insert result set returned by an oracle stored procedure into another table using second stored procedure?