오라클[Oracle] CLOB

내 채팅사이트 조이팅스토리에 CLOB를 사용해야할 상황이 생겼따...ㅡ,.ㅡb
html source를 저장해둬야하는 상황이 발생된 것이지비.... 물론 4000byte가 넘어간다! 썅!!!.... ㅠ.ㅜ
해결방법은 당근 있다... 기억이 가물가물하던차.... 인터넷을 뒤져보니 누군가가 잘~ 정리해두었더군... ㅋㅋ
땡스얼랏해요~ ^0^/



Oracle 을 사용하다 보면 가장 귀찮은 것 중의 하나가 CLOB 사용이다.

꼭 Clob가 아니더래도 1000자 이상의 Varchar를 사용하면

PreparedStatement 의 setString()을 사용하면 안되고, setCharacterStream()을 사용하여 처리하는 좀 귀찮은 과정을 거쳐야 한다.

업데이트 시에는 기존 CLOB데이터를 없애고 다시 넣어주어야 하는 등 불편하다.

게다가 몇몇 JDBC 버전은 CLOB 부분에 버그를 가지고 있어 clob insert시 17410 error code를 만나기도 한다.

이런경우 Hibernate와 같은 라이브러리를 사용하면 Oracle JDBC의 버그(?) 에 무방비 상태로 노출된다.

아무튼.. 이런 Oracle JDBC의 불편한 점을 개선해 보고자 어떤 멋진넘이 JdbcOraWrapper 라는걸 만들었다.
http://l3x.net/imwiki/Wiki.jsp?page=Main

사용법은 매우 간단하다. 일단 oracle jdbc 의 jar함께 JdbcOraWrapper.jar를 classpath에 넣구

Conncetion URL을 jdbc:oracle_clob:thin:... 로 변경해 주면 된다.

이넘이 해주는 내용은 간단한데, 기본적으로 Oracle JDBC를 전부 Wrapping  한다 .

그중 핵심은 JdbcOraWrapperPreparedStatement 인데, PreparedStatement 를 구현하여 기본적인 PreparedStatement 의 기능을 모두 수행하고,

setString(int index, String str) 내에서 str의 length()를 비교하여 4000 이상이면 setClob 를 사용하고, 아니면 setString을 사용하도록 되어 있다.

아무튼... 오라클 컬럼이 clob 더라도 걍 setString 으로 데이터가 자~알 들어간다.

근데 문제가 하나 있다.

내부적으로 길이 체크를 str.length() 로 하는데, 이게 한글도 길이를 1로 체크하므로,

str이 4000보다 작아도 실제 데이터 크기는 4kb를 넘을 수 있다는 것이다.

이런 케이스가 발생하면, 4000보다 적다고 판단되어 oracle jdbc의 setString을 호출하는데

실제 oracle jdbc의 preparedStatement에서는 크기 오버 에러를 내버리는 것이다.

이 부분을 str.getBytes().length 로 변경해야 다국어를 사용하는 경우 문제가 없어진다.

이런 문제점을 수정하여 jar파일을 다시 만들었다.

oracle jdbc를 wrapping만 할뿐, 다른처리를 하지 않아서, 이넘과 함께 원하는 oracle jdbc 버전을 사용하면 clob를 매우 편하게 사용할 수 있을 것이다.

[출처] Oracle JDBC Clob Wrapper|작성자 참치신랑

----------------------------------------------------------------------------------------------------------

주의할점

아래같은 함수에서 java.sql.Clob 을 oracle.sql.CLOB으로 casting하는부분에서 class casting exception이 계속 발생합니다. 뭐가 문제일까요 public void writeClob(Clob clob, char[] buffer, int nSize) throws SQLException { // Get Output stream from Clob locator Writer clobWriter = (Writer)((oracle.sql.CLOB) clob).getCharacterOutputStream(); try{ // Open a stream to read Clob Data clobWriter.write(buffer,0,nSize); clobWriter.close(); }catch(Exception e){ e.printStackTrace(); } }

제목 : Re: 당연한듯... 글쓴이: 이원영(javaservice) 2005/11/03 11:55:48 조회수:324 줄수:17

java.sql.Clob는 oracle.sql.CLOB으로 당연히 캐스팅 될 수 없습니다. Oracle 10g JDBC Driver부터는 표준 JDBC 2.0 API인 java.sql.Clob를 지원합니다. 따라서, 10g라면 가급적 오라클 종속적인 oracle.sql.CLOB를 사용치 마시고 java.sql.Clob만 사용하세요. 그 이하의 버전이라면 어쩔 수 없겠지만,... PS: 굳이 oracle.sql.CLOB를 사용하려면 꺼내올 때부터 CLOB을 꺼내와야지요. JDBC 2.0 표준(Oracle 10g 이상) java.sql.Clob clob = rs.getClob(...); Oracle 종속적인 CLOB 타입 oracle.sql.CLOB clob = ((oracle.sql.OralceResultSet)rs).getCLOB(...);

끝으로 프로시저로 사용하는 법...

오라클의 VARCHAR 타입은 4000이 MAX이다.
그나마도 실제로 올려보믄 1500자 내외에서 짤려버린다.
방법은 있지만 일반적으로 파일이나 이미지 업뎃할때 사용하는 CLOB 타입으로 해결두 가능하다.
4GB까정...
간단하게 예제만으로도 충분하리라 본다.


[INSERT 예제..]
-- PLSQL 프로시저로............
CREATE OR REPLACE PROCEDURE TCNWEB.p_upt_freeboard
(
      p_subject   IN  WB_FREEBOARD.FREE_SUBJECT%TYPE       -- 제목
    , p_contents  IN  WB_FREEBOARD.FREE_CONTENTS%TYPE      -- 내용
    , p_parents   IN  WB_FREEBOARD.FREE_PARENTS%TYPE       -- 댓글일 경우
    , p_user      IN  WB_FREEBOARD.FREE_USER%TYPE
)
IS
   v_clob  CLOB;
  
BEGIN
 
  IF p_contents IS NOT NULL THEN
 
    INSERT INTO WB_FREEBOARD(FREE_SUBJECT, FREE_CONTENTS, FREE_PARENTS, FREE_USER)
                      VALUES(p_subject, EMPTY_CLOB(), p_parents, p_user)
                   
    RETURNING FREE_CONTENTS INTO v_clob;
 
    DBMS_LOB.WRITE(v_clob, LENGTH(p_contents), 1, p_contents);
   
    COMMIT;
   
  END IF;
 
END p_upt_freeboard;


SQL에서 사용한다믄...
EXEC p_upt_freeboard('테스트6', '다시한번2', '', 'aaaa');


[SELECT 예제..]

SELECT SEQ_NUM
     , FREE_SUBJECT
     , DBMS_LOB.SUBSTR(FREE_CONTENTS, DBMS_LOB.GETLENGTH(FREE_CONTENTS), 1)
  FROM WB_FREEBOARD

뽀나스..........

JDBC를 이용해서 CLOB 데이터를 쓰실 때 아래 예제처럼 하세요.
Connection 얻어오는 방법은 일반적인 JDBC 사용법을 가정했습니다. 혹시라도 모르시겠으면 웹에서 검색을 해보세요. 예제가 많이 나옵니다.

String sql = "insert into some_table values (?, ?, ?)";
PreparedStatement pstmt = connection.prepareStatement(sql);

pstmt.setInt(1, 100);
pstmt.setCharacterStream(2, new StringReader(some_string, some_string.length());
pstmt.setString(3, "aaa");
pstmt.execute();

읽을 때에는 아래 함수를 이용하세요. 편하게 쓰느라 제가 만들어둔 함수입니다.

    public static String readClobData(Reader reader) throws IOException {
        StringBuffer data = new StringBuffer();
        char[] buf = new char[1024];
        int cnt = 0;
        if (null != reader) {
            while ( (cnt = reader.read(buf)) != -1) {
                data.append(buf, 0, cnt);
            }
        }
        return data.toString();
    }

CLOB 데이터를 읽는 방법은 아래와 같습니다.

ResultSet rs = some select result;
String clobString = readClobData(rs.getCharacterStream("clob_column"));

http://www.dude.co.kr

이경철님의 파란블로그에서 발행된 글입니다.