Forum Discussion

FrancoCNR's avatar
FrancoCNR
Frequent Visitor
6 months ago

Paginated Report - Measure and Recordset From Query consideration

Hallo, i'me newbie in Power BI and i've 2 questions: I've PRO License. 1. Is possibile with a Paginated Report (.rdl) obtain data from Oracle  Store Procedure that return SYS_REFCURSOR passing one ...
Need Help
v-prasare's avatar
v-prasare
Community Support
6 months ago

Hi FrancoCNR,

Converting a stored procedure into a function that returns SYS_REFCURSOR works without OBJECT TYPEs because SYS_REFCURSOR is a predefined Oracle type.
OBJECT TYPEs are only required when using pipelined or table functions, which return collections and are queried using TABLE(). Please refer to below documentation and let me know if this helps resolve your query

 

Using Ref Cursors To Return Recordsets - ORACLE-BASE

 

 

Thanks,

Prashanth

FrancoCNR's avatar
FrancoCNR
Frequent Visitor
5 months ago

Ok. It means that my call in RDL DataSet is something like this

{call ? := MyPackage.MyFunction( :param1, :param2 )}

where :param1 and :param2 are IN parameters and 
? = is SYS_REFCURSOR RETURNED BACK.
Thanks again.






  • v-prasare's avatar
    v-prasare
    Community Support
    5 months ago

    Hi FrancoCNR,

     

    This approach aligns with how RDL datasets are intended to work. In Paginated Reports, a dataset query must return a tabular result set, and an Oracle function that returns a SYS_REFCURSOR meets this requirement. The report engine automatically treats the returned cursor as the dataset’s rows and columns.
    Stored procedures that expose a SYS_REFCURSOR through OUT parameters aren’t supported for dataset binding when using OLE DB or ODP.NET, which is why refactoring the logic into a function is the recommended and supported pattern. That said, behavior can still vary by Oracle provider and environment, so it’s a good idea to validate this in your setup and share any issues you encounter.

     

    Refer to below:

    oracle database - PL/SQL print out ref cursor returned by a stored procedure - Stack Overflow

     

     

     

     

    Thanks,

    prashanth