아래쿼리는 2000바이트까지 밖에 합쳐지지가 않네요
4000바이트까지 들어가야하는데 방법 없을까요? ㅜㅜ
SELECT TO_CHAR(SYSDATE, 'YYYYMMDD') AS TRSC_DT
, '000' AS INST_CD
, 'FL2213' AS BACH_FILE_NM
, BLK_NO
, SEQ_NO
, REC_CNT
, LENGTHB(DETL_DATE) AS DETL_DATE_LEN
, DETL_DATE AS DETL_DATE
FROM
(
SELECT CEIL(SEQ_NO/100) BLK_NO
, DECODE(CEIL(SEQ_NO/100),'1', SEQ_NO, (SEQ_NO - 100*(CEIL(SEQ_NO/100)-1))) AS SEQ_NO
, MAX(rn) REC_CNT
, MAX(REPLACE(SYS_CONNECT_BY_PATH(DETL_DATE,','),',','')) DETL_DATE
FROM
(
SELECT CEIL(ROWNUM/25) AS SEQ_NO
, TT AS DETL_DATE
, ROW_NUMBER() OVER(PARTITION BY CEIL(ROWNUM/25) ORDER BY ROWNUM) rn
FROM
(
SELECT Rpad('HEADER',80,' ') AS TT FROM DUAL
UNION ALL
SELECT lpad('DATA',80,' ') AS TT FROM DUAL CONNECT BY LEVEL <= 1000
UNION ALL
SELECT lpad('TRAILER',80,' ') AS TT FROM DUAL
)
)
START WITH rn = 1
CONNECT BY PRIOR SEQ_NO = SEQ_NO
AND PRIOR rn = rn - 1
GROUP BY SEQ_NO
)