CREATE OR REPLACE
type APPTDEPT_TY is object (appt_department varchar2(11)); 

CREATE OR REPLACE
type APPTDEPT_TBL_TY is table of apptdept_ty; 

CREATE FUNCTION q_apptdept (p_appointment_id varchar2, p_date date)
      RETURN apptdept_tbl_ty
   IS
      CURSOR cur (c_appt_id varchar2, c_date date)
      IS
         SELECT   d.appt_department
           FROM   apptdept d
          WHERE   D.APPOINTMENT_ID = c_appt_id
              AND D.DEPT_EFF_DATE <= c_date
              AND (D.DEPT_END_DATE IS NULL
                OR D.DEPT_END_DATE >= c_date);

      ty             apptdept_ty;
      tbl            apptdept_tbl_ty := apptdept_tbl_ty ();
   BEGIN
      FOR rec IN cur (p_appointment_id, p_date) LOOP
         ty := apptdept_ty (rec.appt_department);
         tbl.EXTEND;
         tbl (tbl.LAST) := ty;
      END LOOP;

      RETURN tbl;
   END;

Now, we can execute the query

SELECT *
FROM TABLE(Q_APPTDEPT('00123456',SYSDATE));

   SELECT *
   FROM TABLE(Q_APPTDEPT(:dept, :user_date));