Oracle stored procedure to return multiple rows

create or replace procedure employee_sel(
   cv_results in out sys_refcursor)
   is
   begin
     open cv_results for
       select first_name, last_name
        from employee;
end;

Oracle8i introduced native dynamic SQL, an alternative to DBMS_SQL. Using native dynamic SQL, you can place dynamic SQL statements directly into PL/SQL blocks. In most situations, native dynamic SQL can replace DBMS_SQL. Native dynamic SQL is easier to use. For more info on dynamic SQL statements see Note:198306.1 at metalink.oracle.com.