Forum Discussion
Paginated Report - Measure and Recordset From Query consideration
Thansk.
But in this way , i need to make an OBJECT TYPE in Oracle. Is Right ?
"The recommended and supported approach is to convert the stored procedure into an Oracle function that returns a SYS_REFCURSOR and then call that function using a Select statement. This avoids the use of OUT parameters and does not require Oracle object types or pipelined functions."
Thanks in advance.
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
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.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