|
java.sql.SQLException: Data size bigger than max size for this type: 3042 |
1. setCharacterStream을 사용
2. oracle.jdbc.ttc7.NonPlsqlTTCDataSet.class의 marshalRow 요놈을 수정.
출처: http://www.javaservice.net/~java/bbs/read.cgi?m=devtip&b=jdbc&c=r_p&n=1152605190
varchar2(4000)에 PreparedStatement로 insert를 하는데 고생을 좀 심하게 하여
제 경험을 공유할 겸 해서 글을 남깁니다.
환경
OS : Windows 2003 Server
Oracle Database version : 9.2.0.1.0
NLS_LANG : KOREAN_KOREA.KO16MSWIN949
JDBC Driver version : 9.2.0.5.0 (ojdbc14.jar)
Java version : 1.4.2_12
테스트 테이블
CREATE TABLE BIG_TEST
(
K VARCHAR2(10) NOT NULL,
A VARCHAR2(4000),
B VARCHAR2(4000),
C VARCHAR2(4000),
PRIMARY KEY (K)
);
위와 같은 테이블에 Excel로 만들어진 자료를 insert하는 프로그램을 개발하여
테스트하는데 아래와 같은 에러가 발생했습니다.
pstmt = conn.prepareStatement(
"insert into BIG_TEST \n" +
"(K, A, B, C) values \n" +
"(?, ?, ?, ?) "
);
pstmt.setString(1, k);
pstmt.setString(2, a); // 여기서 에러
pstmt.setString(3, b);
pstmt.setString(4, c);
java.sql.SQLException: 데이터 크기가 해당 유형의 최대 크기보다 큽니다: 2001
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:179)
at oracle.jdbc.ttc7.TTCItem.setArrayData(TTCItem.java:147)
at oracle.jdbc.dbaccess.DBDataSetImpl.setBytesBindItem(DBDataSetImpl.java:2492)
at oracle.jdbc.driver.OraclePreparedStatement.setItem(OraclePreparedStatement.java:1194)
at oracle.jdbc.driver.OraclePreparedStatement.setString(OraclePreparedStatement.java:1614)
at oracle.BigTest.start(BigTest.java:144)
at oracle.BigTest.main(BigTest.java:247)
a의 바이트 길이를 찍어봤습니다. 그냥 getBytes().length = 1583 일반적으로 생각하는 영문1, 한글2의 숫자가 나옵니다. 그런데 위에 Exception에 나온 2001과 같지 않아서 getBytes("UTF8").length로 찍어보니 Exception과 같은 숫자가 나오네요.
System.out.println("A : bytes=" + a.getBytes().length + ", UTF8 bytes=" + a.getBytes("UTF8").length);
A : bytes=1583, UTF8 bytes=2001
여러 길이의 테스트 결과 UTF8로 2000이 넘어가면 위와 같은 Exception이 발생합니다.
ORA-xxxxx같은 코드가 없고 해서 혹시나 해서 oracle.jdbc.ttc7.TTCItem.class를 역컴파일 해봤습니다.
public void setArrayData(boolean flag, String s, int i)
throws SQLException
{
is_null = flag;
if(s != null)
{
byte_value = StringToBytes(s, i);
data_size = byte_value.length;
if((type == 96 || type == 1) && data_size > max_out_size)
DBError.throwSqlException(70, new Integer(data_size));
} else
{
byte_value = new byte[0];
data_size = 0;
}
}
if((type == 96 || type == 1) && data_size > max_out_size)
여기 if문에서 max_out_size가 2000이고 data_size가 2001로 나옵니다.
다른 길이도 테스트 해봤는데 max_out_size는 항상 2000이네요.
저런 상태면 setString으로는 그냥 byte길이도 아닌 UTF8로 2000이 넘는 데이터는 넣을 수가 없다는
얘긴데.. 당황스럽네요. DB에러도 아니고 JDBC에서 UTF8 2000으로 길이 체크를 하는건 오바라고
생각되는데 4000이라면 또 모를까, 그래서 주석처리하고 테스트 해 봤습니다.
insert 잘만 되는군요. getBytes().length 4000까지 잘 들어가고 40001부터
"java.sql.SQLException: ORA-01461: LONG 값은 LONG 열에만 입력할 수 있습니다"로 DB에러도
잘 나옵니다.
해결했다고 좋아하며 다시 Excel로 insert하는거 테스트 하는데
다른 문제가 생기더군요. 이번에는 컬럼 순서가 바뀌어서 들어가는 겁니다.
상태를 보니 UTF8로 길이가 2000이 넘지 않는것 앞에 2000이 넘는게 오면 위치가 바뀌더군요.
1. a가 2000이 넘을때
A B C
c a b
2. b가 2000이 넘을때
A B C
a c b
3. c가 2000이 넘을때(정상)
A B C
a b c
4. a, b가 2000이 넘을때
A B C
b c a
5. a, c가 2000이 넘을때
A B C
b a c
6. b, c가 2000이 넘을때(정상)
A B C
a b c
7. a, b, c가 2000이 넘을때(정상)
A B C
a b c
8. a, b, c가 2000이 넘지 않을때(정상)
A B C
a b c
이제서야 열심히 게시판을 뒤져보니 데이터가 많을때 setString()을 사용하지 말고
setCharacterStream()을 사용하라고 되어 있어서 원래 드라이버를 사용하고 아래 코드로 변경했습니다.
pstmt.setString(1, k);
pstmt.setCharacterStream(2, new StringReader(a), a.length());
pstmt.setCharacterStream(3, new StringReader(b), b.length());
pstmt.setCharacterStream(4, new StringReader(c), c.length());
Exception없이 insert는 잘 되는데 순서가 뒤 바뀌는건 여전하더군요.
이번에는 현상이 좀 다릅니다. 일정길이 이상이면 컬럼이 무조건 맨 뒤로 가버리더군요.
일정길이라고 한건 딱히 같은 byte길이를 찾을 수 없었고 내부적으로 변환되는 byte 길이가 있는것 같더군요.
1. a가 큼
A B C
c b a
2. B가 큼
A B C
a c b
3. C가 큼(정상)
A B C
a b c
4. a, b가 큼(1과 동일)
A B C
c b a
또 게시판을 열심히 뒤졌습니다. 저 처럼 컬럼 순서가 바뀌는 경우가 있다고 하는데 Statement로 하면
잘 들어간다더군요. 아래처럼 고쳐서 테스트 해봤습니다.
pstmt = conn.prepareStatement(
"insert into BIG_TEST \n" +
"(K, A, B, C) values \n" +
"('" + k + "', '" + a + "', '" + b + "', '" + c + "') "
);
insert 잘 됩니다. 순서도 안 바뀌고...
그럼 PreparedStatement는 쓰면 안되나? 그냥 Statement를 쓰면 ' 같은거 escape처리 해야돼서
변수마다 함수 둘러줘야되는데... varchar2(4000) 짜리가 한두군데가 아닌데...
고민 끝에 드라이버 소스를 뒤져보기로 했습니다. 왜 순서가 뒤바뀌는지...
결국 찾은 파일이 NonPlsqlTTCDataSet.class입니다.
oracle.jdbc.ttc7.NonPlsqlTTCDataSet.class의 marshalRow함수가 아래와 같습니다.
protected void marshalRow() throws SQLException, IOException
{
int i = -1;
for(int j = 0; j < columns.length; j++)
if(!columns[j].sizeExceeded())
{
if(columns[j].type.isStream())
columns[j].pushStream();
else
columns[j].marshal();
} else
if(i != -1)
{
if(columns[j].type.isStream())
columns[j].pushStream();
else
columns[j].marshal();
} else
{
i = j;
}
if(i != -1)
if(columns[i].type.isStream())
columns[i].pushStream();
else
columns[i].marshal();
marshaledRows++;
}
여기서 ?가 4개니까 for문은 4번 루프를 돕니다. a, b, c가 모두 큰 경우로 테스트하여 j에 대해 찍어봤습니다.
0, 2, 3 이 나오고 맨 밑에 if(i != -1)에서 i가 1이네요.
k일때 !columns[j].sizeExceeded() true (크기가 작으니까)
if(!columns[j].sizeExceeded())
{
if(columns[j].type.isStream())
columns[j].pushStream();
else
columns[j].marshal();
로 타고,
a에 대해 돌때 !columns[j].sizeExceeded() == false, i != -1 false 여서
} else
{
i = j;
}
로 타고,
b, c일때
if(i != -1)
{
if(columns[j].type.isStream())
columns[j].pushStream();
else
columns[j].marshal();
루프가 끝나고 루프 바깥에서 i = 1로 되서 a에 대한 처리가 이루어집니다.
그래서 a가 맨 뒤쪽 c 컬럼으로 insert되는 현상이 생기더군요.
코드로 봐선 큰게(sizeExceeded) 나오면 맨 나중에 처리하겠다는 의도인데..
그것도 처음 나오는 것만. 왜 저런 코딩이 필요한지 모르겠습니다.
위쪽이 max_out_size가 2000으로 체크하듯 예전 오라클의 LONG타입이 테이블당 1개만 올수 있어서
저런 코드가 필요했는데 JDBC코드가 그대로 유지되어 온게 아닌지...
함수를 아래와 같이 고쳐서 setCharacterStream()으로 테스트 해봤습니다.
protected void marshalRow() throws SQLException, IOException
{
int i = -1;
for(int j = 0; j < columns.length; j++)
if(!columns[j].sizeExceeded())
{
if(columns[j].type.isStream())
columns[j].pushStream();
else
columns[j].marshal();
} else
//if(i != -1)
{
if(columns[j].type.isStream())
columns[j].pushStream();
else
columns[j].marshal();
//} else
//{
// i = j;
}
//if(i != -1)
// if(columns[i].type.isStream())
// columns[i].pushStream();
// else
// columns[i].marshal();
marshaledRows++;
}
루프는 순서대로 0, 1, 2, 3으로 타고 DB에 데이터도 순서대로 잘 insert되는군요.
이번에는 맨 처음의 oracle.jdbc.ttc7.TTCItem.class도 아래 코드를 주석처리해서
setString()으로 테스트 해봤습니다. 잘 됩니다.
//if((type == 96 || type == 1) && data_size > max_out_size)
// DBError.throwSqlException(70, new Integer(data_size));
한글포함 4000bytes 꽉 채워서 a, b, c insert하는데 잘 들어가는군요. 순서도 맞구요.
setString(), setCharaterStream() 둘다 잘 됩니다.
참고로, setString()을 쓰면 marshal()을 타고, setCharaterStream()을 쓰면 pushStream()을 탑니다.
=================================================================================
결론입니다.
1. Orcale JDBC 드라이버에서는 UTF8 bytes길이가 2000이 넘어가면 아래 Exception이 발생하여
setString()을 사용하지 못합니다.
java.sql.SQLException: 데이터 크기가 해당 유형의 최대 크기보다 큽니다: UTF8bytes길이
2. UTF8 bytes길이가 2000이 넘어갈때 setCharacterStream()을 사용해야 하는데
컬럼이 1개 일때만 해당 컬럼을 맨 뒤로 빼서 사용하는게 안전합니다.
여러개의 setCharacterStream()을 사용하면 순서가 뒤바뀔수 있습니다.
3. 여기까지의 경우로 해결이 안될 상황이면 그냥 Statement를 사용하세요.
※ Statement를 사용할때는 '만 ''로 escape하세요. Toad에서 쿼리 실행할때 처럼 &를
\&로 escape하면 값이 \로 인해 4000바이트가 넘어가면 아래 Exception이 발생합니다.
java.sql.SQLException: ORA-01704: 문자열이 너무 깁니다
4. 컬럼을 CLOB으로 변경하는걸 고려해 보세요.
전 테스트 해보지 않았지만 다른 게시물에 CLOB에 대한 조언도 있더군요.
CLOB으로 변경하면 setClob()을 사용하고, 조회 쪽도 고쳐져야 되는걸로 알고 있습니다.
CLOB이 여러개일때도 순서 안바뀌는지 테스트가 필요하겠습니다.
5. CLOB으로 변경도, 코드 수정도 싫다면 JDBC드라이버를 수정하세요.
1) oracle.jdbc.ttc7.TTCItem.class의 public void setArrayData(boolean flag, String s, int i)의
아래 코드를 제거합니다.
//if((type == 96 || type == 1) && data_size > max_out_size)
// DBError.throwSqlException(70, new Integer(data_size));
2) oracle.jdbc.ttc7.NonPlsqlTTCDataSet.class의 protected void marshalRow()를
아래 코드와 같이 수정합니다.
protected void marshalRow() throws SQLException, IOException
{
int i = -1;
for(int j = 0; j < columns.length; j++)
if(!columns[j].sizeExceeded())
{
if(columns[j].type.isStream())
columns[j].pushStream();
else
columns[j].marshal();
} else
//if(i != -1)
{
if(columns[j].type.isStream())
columns[j].pushStream();
else
columns[j].marshal();
//} else
//{
// i = j;
}
//if(i != -1)
// if(columns[i].type.isStream())
// columns[i].pushStream();
// else
// columns[i].marshal();
marshaledRows++;
}
끝으로, 저는 iBATIS를 사용하는 환경이라 JDBC 드라이버를 고쳐서 해결했으나,
다양한 경우를 테스트 해보진 않아 고친부분이 다른 곳에 문제를 일으키지 않는지는 잘 모르겠습니다.
현재 제가 개발하는 사이트에 올려 돌려보고 있는 중이며 아직까진 특별한 상황은 일어나지 않았고,
제 생각으로는 딱히 문제가 될것 같진 않으나 고치실 분들은 주의하시기 바랍니다.
첨부파일 설명입니다.
BigTest.java <-- 테스트용 클래스.
ojdbc14_patch.jar <-- 위에 언급한 2개 클래스만 수정하여 들어있는 jar파일.
기존 ojdbc14.jar 앞에 classpath를 잡아주면 고쳐진걸로 실행됩니다.
ojdbc14_patch_full.jar <-- 2개 클래스만 수정된 전체 jdbc드라이버입니다.
classpath의 순서를 잡기 힘든 분들은 드라이버를 이걸로 바꾸세요.
BigTest.java
ojdbc14_patch.zip
'IT_Programming > Java' 카테고리의 다른 글
| jsoup: Java HTML Parser (0) | 2013.07.21 |
|---|---|
| [펌] 파일 감시 데몬 만들기 (0) | 2013.06.27 |
| Serializable 를 이용한 Clone (0) | 2011.11.26 |
| [JSON-LIB 사용] JSON 형태의 Data 사용하기 (0) | 2011.07.04 |
| Java 에서의 Object Pool 기법 (0) | 2011.07.04 |