How to return recordset from SELECT statement in Oracle?

In Oracle, returning a recordset from a stored procedure is not as straight forward as simply write a SELECT statement in MSSQL stored procedure. We will need to make use of a reference cursor to do that.

-- First, declare the reference cursor.
Type YourCursorType Is Ref Cursor;

-- Then, write the SELECT statement as usual by assign the cursor.
Open YourCursorType For
Select Field_Names From Table_Name
Where Related_Conditions
-- Group By Field_Names
-- Order By Field_Names

-- Note that the Group By and Order By are optional statements.

For more information, please refer to: http://decipherinfosys.wordpress.com/2007/08/06/oracle-returning-a-recordset-from-a-stored-procedure/