Paper or plastic I mean, REF CURSOR or Collection

Question:
Do you prefer to use REF CURSOR or collection type when PL/SQL returns a data set, and why?
Answer:
I use REF CURSORs in addition to collections to pass a data set (usually, but now always, the result set of a query) from the Oracle Database to a calling program. Of course, I don't use them both at the same time! I choose the technique based on my requirements.
PL/SQL is an embedded language, which means it doesn't stand on its own but is executed from within a "host environment" such as SQL*Plus, Oracle Forms, a Visual Basic or Dephi front end, and so on. Lots of PL/SQL functionality is executed within the Oracle Database, but sometimes those PL/SQL programs need to communicate with the host environment. A Delphi application might, for example, need to retrieve rows of data from Oracle via a PL/SQL program.
I say all this because your question of how to best return a dataset almost always arises when a programmer is trying to figure out how to return data from the Oracle Database back to the host environment. The determining factor in this case is which Oracle data structures that host environment recognizes and supports.
Here is how I make my decision on which technique to use:
• If my PL/SQL program is returning multiple rows of data to another PL/SQL program, I tend to use collections. I can then take advantage of BULK COLLECT to quickly retrieve data from the database, populate my collection and then manipulate the contents of that collection.
• If I am returning data to a non-PL/SQL host environment, I usually pass back a cursor variable because this mechanism is supported by virtually all host environments. The front-end code, such as a Delphi application, can then fetch the rows of the data set through the cursor variable.
Often I will implement both approaches in a package, so that those who need the data can simply pick the one that meets their needs more closely.
Let me give you an example. I am currently creating a new tool called Qnxo. It uses an Oracle backend (surprise!) and a Delphi front end. The rule I have given my Delphi developers is simple: no SQL in the front end. If they need data, they call a packaged program (generated by Qnxo, oddly enough) to retrieve that data. Here is a portion of the specification for the package that retrieves data from the SG_SCRIPT table:
CREATE OR REPLACE PACKAGE sg_script_qp
IS
-- Ref cursor returning a row from SG_SCRIPT
TYPE sg_script_rc IS REF CURSOR
RETURN sg_script%ROWTYPE;

-- "Table Collection" declarations against SG_SCRIPT
TYPE sg_script_tc IS TABLE OF sg_script%ROWTYPE
INDEX BY BINARY_INTEGER;

-- Return all rows in a collection
FUNCTION allrows
RETURN sg_script_tc;

-- Return all rows in table via ref cursor
FUNCTION allrows_cv
RETURN sg_script_rc;

-- Return a collection of all rows for FK_SCR_DT value
FUNCTION ar_fk_scr_dt (driver_type_id_in IN sg_script.driver_type_id%TYPE)
RETURN sg_script_tc;

-- Return a cursor variable of all rows for FK_SCR_DT value
FUNCTION ar_fk_scr_dt_cv (
driver_type_id_in IN sg_script.driver_type_id%TYPE
)
RETURN sg_script_tp.sg_script_rc;
END sg_script_qp;
/
Notice that I have defined a function (allrows_cv) to return all the rows from the table. I have also created a function (ar_fk_scr_dt_cv) to return all the rows for a specific "driver type"—a foreign key. I use the _CV suffix so that I can easily identify functions that return cursor variables, and then generate Delphi code to help me leverage this data structure inside Delphi.
To summarize: both techniques are valid, both useful. You just have to match up their strengths and weaknesses with your particular circumstances.