Displaying A Recordset from a REFCURSOR from an Oracle Procedure

[JGCPQ]

Greetings everyone,

I am new to the forums so, please bare with me for providing the necessary details for getting the appropriate response(s).  I am trying to use Power Query to call a stored Oracle procedure.  This procedure has two parameters to pass.  A SYS_REFCURSOR and an integer.  The SYS_REFCURSOR is holding a recordset.  I need to be able to display that recordset so I can finish transforming the data from Power Query.  I appear to have made a successful call to the procedure by using the following approach:

declare

   io_cur SYS_REFCURSOR;

begin

   dbname.procedure(io_cur,123456);

end;

Power Query shows me a table with one column labeled "Records Affected" and one row with the value of 1.  From what I have been seing online, it appears I need to loop through the recordset to display all columns and rows.  Is that accurate?  If so, how do I do that? I'm still a beginner with SQL and am new to Oracle so, forgive me, if you feel I should know the answer to that question after getting this far.  😛   Examples I have seen of people looping through recordsets in a refcursor are confusing to me and/or appear to be a solution that will be vary labor intensive for me, as the data output could have as many as 100 columns.  Is there no way to just "select" (if you will) all of the data from the refcursor and be done with it?  

Any assistance someone can provide would be greatly appreciated.  Thank whomever attempts to help in advance for your prompt attention to this.

[dax]

Hi @JGCPQ , 

It seems that you want to get loop result, right? Could you get this from Oracle query? If not, I suggest you could try to write loop query by Oracle query, then you could execute this in power query which will return all results. I suggest you could post this in Oracle forum where you will get more professional suggestions in query part.

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[JGCPQ]

Hi @dax ,

Thanks for responding.  Can you confirm whether or not that is my only solution at this point?  To loop through the contents of the refcursor in order to get my desired output?  Might there be another way?  The thought crossed my mind to reach out to the Oracle community (which I will, at this point), but I was interested in whether or not there may be another way that I am missing.

JGC