Can some one give me a small explanation about how and when should someone use sys_refcursor ?
A cursor is a pointer to a result set for a query. By returning a
A cursor can allow more flexibility than writing a PL/SQL function that returns an array as it's completely up to the client how many rows to fetch and when to stop. That said, I've not found many cases where this additional flexibility is useful.
It's worth noting that the
This enables you to write functions which return different queries, like so:
However, if you're using
As an example of the possibilities: because it's pl/sql in back, one can define an object to represent a row, define a pl/sql table of those objects,
and end with
So rather than constructing mongo, often dense and/or cryptic direct queries on a database table, one can create an internal table and have the whole power of pl/sql to populate it. And the client collecting the result-set is none the wiser. And changing the definition of the internal table is easier from a management pov than changing a database table.
Also when using report-generators like Jasper, you can push the SQL out of the report and into the database, and just call the procedure to get the recordset, leaving the report-side to focus on the formatting.