데이터베이스 질문과 답변
공기업에서 si개발을 하고 있습니다.
DB는 oracle 11g를 쓰고 있고, 전자정부프레임웍을 기반으로 개발 중입니다.
다름이 아니오라 결제처리 업무를 개발하면서 데이터 타입이 clob컬럼을 쓰고 있습니다..
(웹라이트나 나모웹에디터를 이용하여 게시글을 올리면 이 글을 html로 읽어와서 DB에 html형식으로 저장, 이 후 PPT나 PDF로 변환하여 보고자료로 쓸 수 있도록 해달라는 요구사항에 의해 개발하다보니 varchar2(2000)을 훌쩍 넘어가서 어쩔수 없이 CLOB을 사용함.)
그런데 보고라인이 각 팀에서 상위부서로 보고를 하면 그 상위부서에서는 보고내용을 필터링도하고 취합하여 다시 하나의 파일로 다시 상위부서에 보고하는 형식입니다.
처음에 CLOB컬럼으로 인서트 하는 것은 CLOB컬럼이기 때문에 4,000byte이상도 충분히 인서트 되는데
중간에 CLOB컬럼을 합치는 과정이 녹녹치 않습니다.
그래서 해봤던 것이
첫번째로 wm_concat과 listagg를 사용하여 인서트 하는 방법인데 이것은 CLOB컬럼이라도 varchar2(4000)까지 밖에 안되기 때문에 쿼리로는 어떻게 할 수 없다는 판단을 내리게 되었고,
두번째는 자바단으로 각 내용을 셀렉트 해와서 String변수에 담은 다음 다시 DB에 넣어주는데
처음 글을 작성할 때 4,000byte이상도 들어갔던 CLOB 컬럼이 이렇게 String 타입으로 내용을 합쳐서 넣으니
"LONG 값은 LONG 열에 삽입할 때만 바인드할 수 있습니다." 라는 에러를 확인할 수 있었습니다.
그래서 마지막으로 프로시져를 이용해서 넣었는데
다중행 처리를 단일행으로 바꾸는데 어려움을 겪고 있습니다. (다수의 건을 합치기 때문....)
결론은 clob타입으로 된 다수(n)의 데이터를 하나로 합치는 것인데,
경험이 있으신 분들이나 쿼리에 조예가 깊으신 분들의 도움을 부탁드립니다.
아래는 제가 작성한 쿼리와 프로시져입니다.
SELECT LISTAGG(SUM_CONTENT) WITHIN GROUP(ORDER BY SUM_CONTENT) AS NEW_CONTENT
FROM TABLE
WHERE pkValue IN('2014022702001','2014022802001');
-----------------------------------------------------------------------------------------------------------------------------------------------------
CREATE OR REPLACE PROCEDURE KPX_INTRANET.SUMMARY_DATA
(
V_SUM_GUBUN IN CHAR
, V_SUM_REG_NO IN VARCHAR2
, V_CHARGE_REG_NO IN VARCHAR2
, V_REPORT_DATE IN VARCHAR2
, V_DEPT_CD IN VARCHAR2
)
IS
V_SUM_DATE KPX_INTRANET.SCEP_SUMMARY_APP.SUM_DATE%TYPE;
V_UP_DEPT_CD KPX_INTRANET.SCEP_SUMMARY_APP.UP_DEPT_CD%TYPE;
V_POINT_GUBUN KPX_INTRANET.SCEP_SUMMARY_APP.POINT_GUBUN%TYPE;
V_FILE_MNG_CODE KPX_INTRANET.SCEP_SUMMARY_APP.FILE_MNG_CODE%TYPE;
V_SUM_CONTENT KPX_INTRANET.SCEP_SUMMARY_APP.SUM_CONTENT%TYPE;
V_CLOB_SUMMARY CLOB;
BEGIN
<<<채번과 구분코드값등을 가져오는 과정은 생략>>>
SELECT LISTAGG(CONF_MNG_WRITE_TITLE, '<br>') WITHIN GROUP (ORDER BY CONF_MNG_WRITE_TITLE)
INTO V_FILE_MNG_CODE
FROM SCEP_WRITE_APP
WHERE 1=1
AND STATE_CODE = '2'
AND DEPT_CD IN
(SELECT DEPT_CODE
FROM KPXJOJIK
WHERE 1=1
AND UP_CODE =
(SELECT UP_CODE
FROM KPXJOJIK
WHERE 1=1
AND DEPT_CODE = V_DEPT_CD
)
);
<<<글제목 합치기>>>
DBMS_OUTPUT.PUT_LINE('V_FILE_MNG_CODE>>>'||V_FILE_MNG_CODE||'<<<');
DBMS_OUTPUT.PUT_LINE('>>> CLOB SELECT <<<');
SELECT CONTENT
INTO V_SUM_CONTENT
FROM SCEP_WRITE_APP
WHERE 1=1
AND STATE_CODE = '2'
AND DEPT_CD IN (SELECT DEPT_CODE
FROM KPXJOJIK
WHERE 1=1
AND UP_CODE = (SELECT UP_CODE
FROM KPXJOJIK
WHERE 1=1
AND DEPT_CODE = V_DEPT_CD
)
);
<<< CLOB컬럼내용 select >>>
DBMS_OUTPUT.PUT_LINE('V_SUM_CONTENT>>>'||V_SUM_CONTENT||'<<<');
SELECT CONCAT(V_SUM_CONTENT,'<br>')
INTO V_CLOB_SUMMARY
FROM DUAL;
<<< CLOB컬럼 합치기하려 함 >>>
DBMS_OUTPUT.PUT_LINE('V_CLOB_SUMMARY>>>'||V_CLOB_SUMMARY||'<<<');
INSERT INTO SCEP_SUMMARY_APP
(
SUM_DATE
, UP_DEPT_CD
, POINT_GUBUN
, FILE_MNG_CODE
, SUM_GUBUN
, SUM_REG_NO
, M_CHARGE_REG_NO
, M_REPORT_DATE
, SUM_CONTENT
)
VALUES
(
V_SUM_DATE
, V_UP_DEPT_CD
, V_POINT_GUBUN
, V_FILE_MNG_CODE
, V_SUM_GUBUN
, V_SUM_REG_NO
, V_CHARGE_REG_NO
, V_REPORT_DATE
, V_CLOB_SUMMARY
);
<<익셉션 생략<>>>
END SUMMARY_DATA;
꽤나 긴 질문은 끝까지 읽어주셔서 감사합니다.
-- XMLAgg 사용시에는 GetStringVal() 대신 GetCLOBVal() 을 사용하시면 됩니다. - 단, 태그 출력값 주의
XMLAgg(XMLElement(x, sum_content) ORDER BY pkValue).Extract('//text()').GetCLOBVal() AS new_content
http://www.gurubee.net/article/58819
http://www.gurubee.net/article/55512
합쳐지는 데이터가 몇건 안된고 형식이 고정적이라면?
그냥 문자열 연결함수(Concat 또는 ||)을 이용하는게 좋을 듯 합니다.
SELECT (SELECT sum_content FROM table WHERE pkValue = '2014022702001')
|| (SELECT sum_content FROM table WHERE pkValue = '2014022802001')
AS new_content
마농님 감사합니다. 전에 wm_concat과 listagg가 안되어서 xmlagg도 해보았는데 그 땐 문법이 틀렸던 것 같아 행을 열로 바꾸는 함수는 안되는가보다라고 생각했었거든요.... 쿼리 한방에 되는걸 자바단으로도 해보고 프로시져도 만들어보고... 평생 못잊을 공부한 것 같습니다. 그런데 xmlagg에 getCLOBVal() 이거는 데이터타입에 따라 varchar2s나 char타입에 getStringVal()라고 해주는 것인가요?!
) 버튼을 클릭하여 작성 하시면 됩니다.