WITH ABK(A_DATE,A_RNO,A_EMPYN,A_NAME) AS(
    SELECT '20150401', '301', 'Y', '' FROM DUAL UNION ALL
    SELECT '20150401', '302', 'N', '강감찬' FROM DUAL UNION ALL
    SELECT '20150401', '303', 'Y', '' FROM DUAL UNION ALL
    SELECT '20150401', '304', 'Y', '' FROM DUAL UNION ALL
    SELECT '20150401', '305', 'N', '홍길동' FROM DUAL UNION ALL
    SELECT '20150401', '306', 'Y', '' FROM DUAL UNION ALL
    SELECT '20150401', '307', 'Y', '' FROM DUAL UNION ALL
    SELECT '20150401', '308', 'Y', '' FROM DUAL UNION ALL
    SELECT '20150401', '309', 'Y', '' FROM DUAL UNION ALL
    SELECT '20150402', '301', 'N', '이순신' FROM DUAL UNION ALL
    SELECT '20150402', '302', 'Y', '' FROM DUAL UNION ALL
    SELECT '20150402', '303', 'Y', '' FROM DUAL UNION ALL
    SELECT '20150402', '304', 'Y', '' FROM DUAL UNION ALL
    SELECT '20150402', '305', 'Y', '' FROM DUAL UNION ALL
    SELECT '20150402', '306', 'N', '갈갈이' FROM DUAL UNION ALL
    SELECT '20150402', '307', 'Y', '' FROM DUAL UNION ALL
    SELECT '20150402', '308', 'Y', '' FROM DUAL UNION ALL
    SELECT '20150402', '309', 'Y', '' FROM DUAL UNION ALL
    SELECT '20150403', '301', 'Y', '' FROM DUAL UNION ALL
    SELECT '20150403', '302', 'Y', '' FROM DUAL UNION ALL
    SELECT '20150403', '303', 'N', '이경호' FROM DUAL UNION ALL
    SELECT '20150403', '304', 'Y', '' FROM DUAL UNION ALL
    SELECT '20150403', '305', 'Y', '' FROM DUAL UNION ALL
    SELECT '20150403', '306', 'Y', '' FROM DUAL UNION ALL
    SELECT '20150403', '307', 'Y', '' FROM DUAL UNION ALL
    SELECT '20150403', '308', 'Y', '' FROM DUAL UNION ALL
    SELECT '20150403', '309', 'Y', '' FROM DUAL
)
SELECT A_RNO "객실번호"
    , COUNT(*) "연박가능일수"
    , MIN(A_DATE) ||'~'|| MAX(A_DATE) "연박가능일자"
FROM (
      SELECT A_RNO, A_DATE
           , DECODE(A_EMPYN,'Y' ,
                  SUM(DECODE(A_EMPYN,'N',1,0)) OVER(PARTITION BY A_RNO ORDER BY A_DATE),
                  NULL) AS GRP
      FROM ABK
)
WHERE GRP IS NOT NULL
GROUP BY A_RNO, GRP
HAVING COUNT(*) >= 3  -- 가능일수 조회 조건
ORDER BY 1,3
;

with t as
(
select '20150401' a_date,  '301' a_rno, 'Y' a_empyn,  NULL     a_name from dual union all
select '20150401' a_date,  '302' a_rno, 'N' a_empyn,  '강감찬' a_name from dual union all
select '20150401' a_date,  '303' a_rno, 'Y' a_empyn,  NULL     a_name from dual union all
select '20150401' a_date,  '304' a_rno, 'Y' a_empyn,  NULL     a_name from dual union all
select '20150401' a_date,  '305' a_rno, 'N' a_empyn,  '홍길동' a_name from dual union all
select '20150401' a_date,  '306' a_rno, 'Y' a_empyn,  NULL     a_name from dual union all
select '20150401' a_date,  '307' a_rno, 'Y' a_empyn,  NULL     a_name from dual union all
select '20150401' a_date,  '308' a_rno, 'Y' a_empyn,  NULL     a_name from dual union all
select '20150401' a_date,  '309' a_rno, 'Y' a_empyn,  NULL     a_name from dual union all
select '20150402' a_date,  '301' a_rno, 'N' a_empyn,  '이순신' a_name from dual union all
select '20150402' a_date,  '302' a_rno, 'Y' a_empyn,  NULL     a_name from dual union all
select '20150402' a_date,  '303' a_rno, 'Y' a_empyn,  NULL     a_name from dual union all
select '20150402' a_date,  '304' a_rno, 'Y' a_empyn,  NULL     a_name from dual union all
select '20150402' a_date,  '305' a_rno, 'Y' a_empyn,  NULL     a_name from dual union all
select '20150402' a_date,  '306' a_rno, 'N' a_empyn,  '갈갈이' a_name from dual union all
select '20150402' a_date,  '307' a_rno, 'Y' a_empyn,  NULL     a_name from dual union all
select '20150402' a_date,  '308' a_rno, 'Y' a_empyn,  NULL     a_name from dual union all
select '20150402' a_date,  '309' a_rno, 'Y' a_empyn,  NULL     a_name from dual union all
select '20150403' a_date,  '301' a_rno, 'Y' a_empyn,  NULL     a_name from dual union all
select '20150403' a_date,  '302' a_rno, 'Y' a_empyn,  NULL     a_name from dual union all
select '20150403' a_date,  '303' a_rno, 'N' a_empyn,  '이경호' a_name from dual union all
select '20150403' a_date,  '304' a_rno, 'Y' a_empyn,  NULL     a_name from dual union all
select '20150403' a_date,  '305' a_rno, 'Y' a_empyn,  NULL     a_name from dual union all
select '20150403' a_date,  '306' a_rno, 'Y' a_empyn,  NULL     a_name from dual union all
select '20150403' a_date,  '307' a_rno, 'Y' a_empyn,  NULL     a_name from dual union all
select '20150403' a_date,  '308' a_rno, 'Y' a_empyn,  NULL     a_name from dual union all
select '20150403' a_date,  '309' a_rno, 'Y' a_empyn,  NULL     a_name from dual
)
SELECT *
  FROM (SELECT a.*, b.cnt
          FROM t a,
               (SELECT a_rno, COUNT (1) cnt
                  FROM t
                 WHERE a_date IN (SELECT TO_CHAR (TO_DATE (:in_date, 'yyyy-mm-dd') + LEVEL - 1, 'yyyymmdd')
                                    FROM DUAL
                                 CONNECT BY LEVEL <= :in_days)
                   AND a_empyn = 'Y'
                GROUP BY a_rno) b
         WHERE a.a_date = :in_date
           AND a.a_rno = b.a_rno)
 WHERE cnt = :in_days

in_date에 예약할 날짜를 입력하고

in_days에 연박일수를 입력하는 식으로 만들었습니다.

SELECT a_rno
     , MIN ( a_date ) 
     , MAX ( a_date )
  FROM (
          SELECT a.a_date
               , a_rno
               , a_empyn
            FROM t a
           WHERE a_empyn = 'Y'
           ORDER BY a_rno, a_date
) 
 GROUP BY a_rno, TO_DATE ( a_date ) - ROWNUM
 HAVING COUNT (*) >= 3

-- ○ 입력조건
--   1. 투숙시작일 : v_sdt varchar2(8) yyyymmdd 형식
--   2. 연박일수 : v_cnt number
SELECT a_rno
  FROM abk
 WHERE a_empyn = 'Y'
   AND a_date >= :v_sdt
   AND a_date <= TO_CHAR(TO_DATE(:v_sdt, 'yyyymmdd') + :v_cnt - 1, 'yyyymmdd')
 GROUP BY a_rno
 HAVING COUNT(*) = :v_cnt
 ORDER BY a_rno
;

답변 주신분 정말 감사합니다.

그리고 마농님 답변은 정말 뒤통수 맞은 느낌 입니다. ㅎ

몇일간 머리짜매고 고민했던게 한방에 해결되었습니다.