오라클 CLOB select - olakeul CLOB select

Question

Oracle에서 CLOB 조회(쿼리)하는 방법

# CLOB컬럼의 전체길이 반환

DBMS_LOB.GETLENGTH(CLOB컬럼명)

# CLOB컬럼의 값 조회

DBMS_LOB.SUBSTR(CLOB컬럼명, 가져올 길이, 오프셋)

예시)

MyTable의 CLOB으로 정의된 col1 이름을 가진 컬럼을 조회해보자.

SELECT DBMS_LOB.SUBSTR(col1, DBMS_LOB.GETLENGTH(col1)) FROM MyTable

[ORA-06502, ORA-06512 오류 발생 시 조치 방법]

지정된 Buffer 크기보다 더 큰 데이터를 읽을 때 발생한다.

SUBSTR함수의 Offset를 이용하여 여러 번 호출하여 해결 가능하다.

DBMS_LOB.SUBSTR (lob_loc IN CLOB, amount IN INTEGER:=32767, offset IN INTEGER:= 1)

SELECT DBMS_LOB.SUBSTR(col1, 3000, 1) as C1, DBMS_LOB.SUBSTR(col1, 3000, 3001) as C2 FROM MyTable

amount와 offset은 자신의 DBMS설정 값에 따라 변경 가능하다.

끝.

If it's a CLOB why can't we to_char the column and then search normally ?

Create a table

CREATE TABLE MY_TABLE(Id integer PRIMARY KEY, Name varchar2(20), message clob);

Create few records in this table

INSERT INTO MY_TABLE VALUES(1,'Tom','Hi This is Row one');
INSERT INTO MY_TABLE VALUES(2,'Lucy', 'Hi This is Row two');
INSERT INTO MY_TABLE VALUES(3,'Frank', 'Hi This is Row three');
INSERT INTO MY_TABLE VALUES(4,'Jane', 'Hi This is Row four');
INSERT INTO MY_TABLE VALUES(5,'Robert', 'Hi This is Row five');
COMMIT;

Search in the clob column

SELECT * FROM MY_TABLE where to_char(message) like '%e%';

Results

ID   NAME    MESSAGE   
===============================  
1    Tom     Hi This is Row one         
3    Frank   Hi This is Row three
5    Robert  Hi This is Row five

/*

# CLOB 형 데이타 SELECT

DBMS_LOB.SUBSTR(

CLOB 타입 컬럼 명

, 추출할 문자열 길이(숫자) OR 전체 문자길이( DBMS_LOB.GETLENGTH(CONTENTS) )

, 전체 값 출력 : 1

)

전체 문자길이( DBMS_LOB.GETLENGTH(CONTENTS) ) 사용시 아래와 같은 메세지 발생할 수 있음.

-- ORA-06502: PL/SQL: 수치 또는 값 오류: 문자열 버퍼가 너무 작습니다

*/

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 -- 검색 문구가 존재할때

출처:http://develop.sunshiny.co.kr/841

Oracle CLOB 컬럼 Select 시 주의사항 (ORA-06502)

2017.01.24 23:40
데이터 관리/Database

Oracle CLOB 컬럼은 Oracle 버전 10g 이후로 지원되는 LOB 타입의 종류 중 하나로, 문자열이나 이미지 데이터를 4GB까지 저장할 수 있습니다.

어느 날 화면에서 처리한 데이터가 DB에 정상적으로 들어가있는지 확인하기 위해 Orange에서 CLOB 컬럼을 조회해봤습니다.

서비스 Log나 화면 IO를 보면 데이터를 정상적으로 DB에 넣는 것 처럼 보이는데, Orange에서 Select로 조회해보면 일부가 깨져서 보이거나 데이터가 잘리는 현상이 있었습니다.

[현상]

입력한 값 : 안녕하세요? 테스트 데이터입니다.

Orange에서 조회한 CLOB 컬럼의 값 : 안녕하세요? 테스트

[원인]

프로그램 오류라 생각하고 몇 시간 동안 로그도 뒤져보고, 비슷한 유형의 다른 프로그램도 테스트해보고 하다가 원인을 결국 발견했습니다.

CLOB 컬럼 특성 상 4GB 용량의 데이터를 저장할 수 있기 때문에, 최대 4GB의 데이터가 존재할 수 있는 컬럼을 쿼리로 조회할 경우 성능적인 면에서 큰 부하가 걸릴 수 있습니다.

1000 rows 만 있어도.. 최대 1000*4GB 인 셈이지요.

때문에 CLOB 컬럼 조회 시 부하 발생의 위험을 최소화하고 효율적인 쿼리를 위해서 컬럼이 지니고 있는 전체 데이터가 아닌 로케이터만 조회해옵니다.

즉, CLOB 컬럼 조회 시 부하 발생의 위험을 줄이고 쿼리 효율을 위해 데이터의 일부분만 보여집니다.

CLOB 컬럼의 전체 값을 조회하고 싶을 경우, 아래와 같이 dbms_lob 을 이용하도록 합시다!

(Orange툴을 사용하는 경우라면 dbms_lob 을 사용하지 않아도 Text Output 탭에서 전체값을 확인할 수 있습니다)

[이용 예시]

SELECT dbms_lob.substr(컬럼, dbms_lob.getlength(컬럼))

WHERE PK컬럼 = 'PK값'; -- 반드시 한 건 씩 조회해야한다.

cs

위와 같은 현상은 외부 시스템과 인터페이스 연동 테스트를 하던 중 발견한 것입니다.

현재 개발하고 있는 시스템에서는 EAI에 인터페이스를 전송한 후, 연동된 내용과 결과를 이력으로 관리하는 테이블이 있습니다.

이상하게도 EAI와 외부시스템에서는 정상적인 데이터를 받은 것을 확인하였는데, 인터페이스 연동 이력을 관리하는 내부 테이블에만 데이터가 깨져서 들어가는 겁니다.

프로그램 버그인 줄 알고 오전 동안 헤맸는데 Oracle CLOB 컬럼의 특성을 몰라서 생긴 삽질이었네요.

역시 모르는게 있을 때는 주변에 계신 똑똑이들에게 여쭤보는게 최고인듯..

(2021.01.24 내용추가) ORA-06502, 문자열 버퍼가 작다는 오류 발생

[오류]

ORA-06502: "Numeric Or Value Error: Character String Buffer Too Small"

[원인]

dbms_lob.substr() 함수의 return값은 varchar2 타입인데, varchar2 타입에 담을 수 있는 데이터의 최대 길이가 정해져있습니다.

varchar2 타입의 길이는 보통은 4,000 bytes 이며, PL/SQL에서는 32,767 bytes 까지 가능합니다.

clob으로 조회하는 데이터가 varchar2 타입에 담을 수 없는 경우 버퍼가 적다는 오류가 발생하게 됩니다.

이 오류는 오래전부터 존재하던 이슈로, 보통 두 가지 방식으로 접근하여 해결하는 것 같습니다.

[해결방법]

1. 최대 버퍼의 사이즈를 늘린다.

2. 4,000 bytes 내로 데이터를 잘라서 조회한다. (한글의 경우 글자가 깨질 위험이 있음)

참고 링크

- (한글) 오류 원인과 해결방법 안내 : https://tomining.tistory.com/95

- 4,000 bytes별로 잘라서 조회 : https://stackoverflow.com/questions/30507997/migration-script-with-dbms-lob-substr-gets-character-string-buffer-too-small

- dbms_lob.substr() 함수 설명 : https://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_lob.htm#i999349

오라클 CLOB 조회 CLOB select 오라클 CLOB 출력 CLOB 데이터 추출

오라클 CLOB select - olakeul CLOB select

Oracle CLOB 컬럼 Select 시 주의사항 (ORA-06502)

관련 게시물

파리바게트 기프티콘 배달 - palibageteu gipeutikon baedal

체납 세액 조회 - chenab seaeg johoe

벤처투자조합 행위제한 - bencheotujajohab haeng-wijehan

BC TOP 포인트 전환 - BC TOP pointeu jeonhwan

한진택배 해외 배송 속도 - hanjintaegbae haeoe baesong sogdo

고등학교 생기부 보는법 - godeunghaggyo saeng-gibu boneunbeob

계좌 사기 신고 - gyejwa sagi singo

나이스 생활기록부 조회 - naiseu saenghwalgilogbu johoe

오라클 클라이언트 리스너 - olakeul keullaieonteu liseuneo

한진택배 배송조회 업데이트 - hanjintaegbae baesongjohoe eobdeiteu

광고하는

최근 소식

몬헌 월드 대검 세팅 - monheon woldeu daegeom seting

비 로 시작하는 단어 - bi lo sijaghaneun dan-eo

카톡 전체 멘션 방법 - katog jeonche mensyeon bangbeob

원신 프레임 올리기 - wonsin peuleim olligi

일러스트 png 테두리 - illeoseuteu png teduli

세탁기 통세척 시간 - setaggi tongsecheog sigan

4음보 율격 예시 - 4eumbo yulgyeog yesi

경기도 예비군 훈련장 - gyeong-gido yebigun hunlyeonjang

개구쟁이 뽀로로 가사 - gaegujaeng-i ppololo gasa

시 놀로지 하드 소리 - si nolloji hadeu soli

광고하는

포퓰러

광고하는

에 대한

합법적인

돕다

사회의