/* # CLOB 형 데이타 SELECT DBMS_LOB.SUBSTR( CLOB 타입 컬럼 명 , 추출할 문자열 길이(숫자) OR 전체 문자길이( DBMS_LOB.GETLENGTH(CONTENTS) ) , 전체 값 출력 : 1 ) 전체 문자길이( DBMS_LOB.GETLENGTH(CONTENTS) ) 사용시 아래와 같은 메세지 발생할 수 있음. -- ORA-06502: PL/SQL: 수치 또는 값 오류: 문자열 버퍼가 너무 작습니다
==> DBMS_LOB.GETLENGTH(CONTENTS)의 길이가 4000이상인 경우 발생하는 듯함. 원인은 아래 내용 참조
*/SELECT DBMS_LOB.SUBSTR(CONTENTS, 1000, 1) AS CONT_SUB -- 1000 개까지 출력(문자열) , DBMS_LOB.INSTR(CONTENTS, 'TEST', 1, 1) AS CONT_INS -- 검색 문구의 위치 출력(숫자) FROM CLOB_TABLE WHERE DBMS_LOB.INSTR(CONTENTS, 'TEST', 1, 1) > 0 -- 검색 문구가 존재할때;
* ORA-06502: PL/SQL: 수치 또는 값 오류: 문자열 버퍼가 너무 작습니다 의 원인
DBMS_LOB.SUBSTR (
lob_loc IN BLOB,
amount IN INTEGER := 32767,
offset IN INTEGER := 1)
RETURN RAW;
DBMS_LOB.SUBSTR (
lob_loc IN CLOB CHARACTER SET ANY_CS,
amount IN INTEGER := 32767,
offset IN INTEGER := 1)
RETURN VARCHAR2 CHARACTER SET lob_loc%CHARSET;
DBMS_LOB.SUBSTR (
file_loc IN BFILE,
amount IN INTEGER := 32767,
offset IN INTEGER := 1)
RETURN RAW;
Parameters
Table 52-52 SUBSTR Function Parameters
Parameter
Description
lob_loc
Locator for the LOB to be read. For more information, see Operational Notes.
file_loc
The file locator for the LOB to be examined.
amount
Number of bytes (for BLOBs) or characters (for CLOBs) to be read.
offset
Offset in bytes (for BLOBs) or characters (for CLOBs) from the start of the LOB (origin: 1).
(출처: http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_lob.htm#i999349)
CLOB일 경우, Byte가 아니라 문자 갯수가 4000자로 잘려진다고 합니다.
즉, 문자셋이 한글이나 UTF-8 등으로 되어 있을 경우, 4000자를 가져오면 4000 Byte를 넘기 때문에,
VARCHAR2 에 저장할 수 있는 한계에 걸려, 해당 오류가 발생합니다.
또한, DBMS_LOB.SUBSTR(...) 함수의 리턴 값이 VARCHAR2로 지정되어 있어서,
이 함수를 SQL 문장에서 직접 사용할 경우, 함수가 리턴될 때 바로 4000Byte 제한이 걸리게 됩니다.
때문에, 위에서도 INSERT가 아닌 DBMS_LOB.SUBSTR(...) 함수가 수행된 바로 직후에 에러가 발생할 것입니다.
질문하신 내용도, 2000 Byte 까지만 저장된다는 걸로 보아, 같은 상황인 것 같습니다.
해결 방법은, 여러 가지가 있을 것 같은데요,
1. 1000 또는 2000 Byte씩 나누어 받은 후 합치는 방법
- 번거롭지만, 어쩔 수 없는 경우엔 쓸 만합니다.
TO_CLOB(DBMS_LOB.substr(...)) || TO_CLOB(DBMS_LOB.substr(...)) 이런 식으로 사용하면 됩니다.
2. 글자수가 아닌 Byte로 잘라오는 방법을 쓸 수도 있습니다
- CLOB는 기본적으로 Byte로 자르는 방법을 지원하지 않는 것 같습니다.
- LOB등의 다른 형식으로 변환한 후 DBMS_LOB.substr(...) 를 사용하면 byte로 잘라집니다.
3. SUBSTR(...)로도 CLOB를 처리할 수 있습니다.
즉, DBMS_LOB.SUBSTR(..4000, 1) 대신 SUBSTR(..,1,4000) 을 사용하는 식입니다.
4. PL/SQL의 VARCHAR2 크기는 4000 Byte가 아닌, 32767 Byte 까지 확장됩니다.
PL/SQL로 프로시저 내부에서 DBMS_LOB.SUBSTR(..,4000,.)를 이용해 결과를 얻어오는 것도 방법이 될 수 있습니다.
저는 1,2,3 번으로 해보았는데요,
2번은 문자가 깨질 가능성이 있고, 1,3번은 시스템 환경에 따라 안먹히는 경우가 있습니다.
현재로선 번거롭더라도 PL/SQL을 직접 작성하는 것이 제일 안전해 보입니다.
혹시, 더 좋은 방법이 있다면 공유 부탁드려요,
저도, 나중에라도 좋은 방법을 찾으면 업데이트 하겠습니다.
'Database > Oracle' 카테고리의 다른 글
| [펌] Data Pump 사용방법 (0) | 2015.01.24 |
|---|---|
| [펌] Partition Table(파티션 테이블) (0) | 2014.11.26 |
| java.sql.SQLException: 결과 집합을 종료했음 (0) | 2013.09.27 |
| [펌] SQLException : 결과집합을 모두 소모했음 (0) | 2013.09.27 |
| [펌] java.sql.SQLException: ORA-01000: 최대 열기 커서 수를 초과했습니다 (0) | 2013.09.27 |
