• How to know what date it is today

SELECT TO_CHAR(SYSDATE,'D') INTO W_D FROM DUAL;
 or
 SELECT TO_CHAR(SYSDATE,'DAY') INTO W_D FROM DUAL;

1 means Sunday
7 means Saturday

• How to get Weekends from a period of time.

DROP TABLE WEEKEND;
CREATE TABLE WEEKEND (WEEK_END DATE);

DECLARE
  W DATE := TO_DATE('2016-12-01','YYYY-MM-DD');
  W_D CHAR;
  BEGIN LOOP SELECT TO_CHAR(W,'D') INTO W_D FROM DUAL;
      IF W_D = 1 OR W_D = 7 THEN
        INSERT INTO WEEKEND VALUES (W);
      END IF;
      W := W + 1;
      IF TO_CHAR(W,'YYYY-MM-DD') = '2016-12-30' THEN
        EXIT;
      END IF;
    END LOOP;
  END;
/