How to write and execute an Oracle function

Here is an example of a function on Oracle. Then given following is a procedure that called the function. I have used them to implement the paging function. Here is the Function.

create or replace FUNCTION  "RETURN_EMPLOYEE" (argEmailAddress IN EMPLOYEE.EmailAddress%TYPE) 
 return EMPLOYEE_NESTED_TABLE
 as
 V_RET  EMPLOYEE_NESTED_TABLE;

 CURSOR C1 IS
 SELECT  EMPLOYEE.EMPLOYEEId,
         EMPLOYEE.Name,
         EMPLOYEE.Stake,
         EMPLOYEE.CreatedDate,
         EMPLOYEE.StartDate,
         EMPLOYEE.EndDate,
         EMPLOYEE.EMPLOYEEDurationId,
         EMPLOYEEMember.EMPLOYEEMemberStatusId,
         EMPLOYEEMember.UPDATENOTIFIED
 FROM  EMPLOYEE,EMPLOYEEMember
 WHERE  EMPLOYEEMember.EmailAddress = argEmailAddress AND
        EMPLOYEE.EMPLOYEEId = EMPLOYEEMember.EMPLOYEEId AND
  EMPLOYEEMember.EMPLOYEEMemberStatusId IN(2,5,6)
 ORDER BY EMPLOYEEId;

 i NUMBER := 0;
 begin
  V_RET  := EMPLOYEE_NESTED_TABLE ();
      FOR C1_REC IN C1
      LOOP
          i := i + 1;
          V_RET.EXTEND;
          V_RET(V_RET.COUNT) := EMPLOYEE_COLUMNS( C1_REC.EMPLOYEEId,
                                                  C1_REC.Name,
                                                  C1_REC.Stake,
                                                  C1_REC.CreatedDate,
                                                  C1_REC.StartDate,
                                                  C1_REC.EndDate,
                                                  C1_REC.EMPLOYEEDurationId,
                                                  C1_REC.EMPLOYEEMemberStatusId,
                                                  C1_REC.UPDATENOTIFIED);
      END LOOP;
  RETURN V_RET;
 end RETURN_EMPLOYEE;

Here is the procedure that calls the function.

PROCEDURE GetJoinedEmplyeeListByEmail( argEmailAddress IN EMPLOYEE.EmailAddress%TYPE,
                                     argPageNo IN  NUMBER,
                                     argEmployeesPerPage IN  NUMBER, 
                                     argNoOfPages OUT  NUMBER,
                                     argNoOfEmployees OUT  NUMBER,
                                     argEmployee OUT refCursorType)
IS
BEGIN
 SELECT COUNT(Employee. EmployeeId) INTO argNoOfEmployees
  FROM Employee,EmployeeMember
  WHERE  EmployeeMember.EmailAddress = argEmailAddress AND 
  Employee.EmployeeId = EmployeeMember.EmployeeId AND 
  EmployeeMember.EmployeeMemberStatusId IN(9,90,7);
 
 OPEN argEmployee FOR 
  SELECT * FROM (SELECT  EmployeeId,
                               Name,
                               Stake,
                               CreatedDate,
                               StartDate,
                               EndDate,
                               EmployeerId,
                               EmployeeDurationId,
                               EmployeeMemberStatusId,
                               rownum rn
                        FROM   (SELECT * FROM TABLE(RETURN_EMPLOYEE(argEmailAddress)))
                        WHERE  rownum <= argNoOfEmployees-(argPageNo*argEmployeesPerPage-argEmployeesPerPage))
                WHERE rn > argNoOfEmployees-(argPageNo*argEmployeesPerPage); 

  SELECT  ROUND(((COUNT(Employee.EmployeeId))/argEmployeesPerPage)+0.49) INTO argNoOfPages  
  FROM  Employee,EmployeeMember
  WHERE  EmployeeMember.EmailAddress = argEmailAddress AND 
  Employee.EmployeeId = EmployeeMember.EmployeeId AND 
  EmployeeMember.EmployeeMemberStatusId IN (9,90,7);
END GetJoinedEmployeeListByEmail;

Here the function has used a type that is created. Here are the statements for creating types.

CREATE OR REPLACE TYPE  "EMPLOYEE_COLUMNS" as object (
     EMPLOYEEID NUMBER(10,0),
     NAME VARCHAR2(500),
     STAKE VARCHAR2(1000),
     CREATEDDATE DATE,
     STARTDATE DATE,
     ENDDATE DATE,
     EMPLOYEERID NUMBER(10,0),
     EMPLOYEEDURATIONID  NUMBER(10,0),
     EMPLOYEEMEMBERSTATUSID NUMBER(10,0),
     rn NUMBER(10) );

CREATE OR REPLACE TYPE "EMPLOYEE_NESTED_TABLE" as table of EMPLOYEE_COLUMNS;

This worked perfect for my paging and if there is a better implementation for paging by oracle with an "oreder by" please do share it with me. The problem here was to do such work is because order by is executed after all the other statements.