DB링크를 통한 원격지 DB간 데이터 전송 시 CLOB 타입의 컬럼 오류 해결

지난주에 개발 작업을 하다가 겪었던 문제입니다. 같은 문제를 겪고 있는 분이 계실지도 몰라서 공유합니다.^^

오라클에서 DB Link를 통해서 다른 DB의 테이블에 접근하는 코드를 종종 작성하실 텐데요.

오라클의 데이터 타입 중에 LOB라는 타입이 있습니다. Large Object 라는 의미인데요. 

일반적인 수준의 데이터 크기가 아닌 대용량의 데이터를 저장하고 관리하기 위해서 오라클에서 제공하는 타입입니다.

일반적인 문자열(String), 숫자, 날짜 등은 정형적인 데이터 타입인 반면, LOB는 사진이나 영상 등 대용량의 데이터도 DB로 처리할 수 있도록 하기 위해 생겨난 타입이라고 보시면 됩니다. 예전에는 DB자체에서 이미지나 영상 데이터를 저장할 방법이 없어서 파일로 많이 관리하고 처리하였지만, 현재는 DB에서도 이를 지원합니다. (아직은 이미지나 영상은 처리(로딩) 속도나 제어가 파일이 더 편한 경우도 많기 때문에 여전히 파일 형태로 사용하는 경우도 많습니다.)

그럼 간단하게 LOB 타입에 대해서 알아보면 다음과 같습니다.

타입	의미	설명
CLOB	Character Large Object	대용량 문자열 저장 (4GB 까지 문자열 저장 가능)
BLOB	Binary Large Object	바이너리 형태로 사진, 오디오, 영상 등을 저장 (4GB 까지 저장 가능)
NCLOB	National Character Set Large Type	유니코드 데이터를 저장

이 데이터 타입들과 유사한 것으로 BFILE 이라는 타입도 있는데, 이는 조금 특이한 것이 외부 파일 시스템에 데이터가 저장되지만, 오라클을 통해서 접근 가능합니다.

이제 본론으로 들어가면.

이러한 타입들의 문제가 원격 DB로 데이터를 내보내거나 할 때, 문제가 발생합니다.

CLOB를 예로들면, 다른 원격 DB의 CLOB 데이터 타입을 바로 SELECT 한 후, 또 다른 원격 DB로 INSERT(또는 UPDATE)하려 한다고 하면 오라클은 오류를 발생시킵니다.

즉, 아래 쿼리는 오라클에서 지원하지 않는 형태의 쿼리입니다.

// 아래애서 TBL_DATA1 테이블의 REAL_DATA 타입은 CLOB 타입입니다.

// 아래에서 TBL_DATA2 테이블의 ORG_DATA 타입은 CLOB 타입입니다.

INSERT INTO TBL_DATA1@YOURDB (DATA_ID, REAL_DATA, DATA_CD, CRT_DATE)

SELECT DATA_ID, REAL_DATA, DATA_CD, SYSDATE FROM TBL_DATA2@OURDB WHERE DATA_ID = 5;

위의 코드는 원격지 데이터베이스 OURDB의 TBL_DATA2 테이블에서 특정 레코드를 조회하여 다른 원격지 데이터베이스인 YOURDB의 TBL_DATA1 으로 바로 INSERT 시키고 있는 쿼리입니다.

사실 위의 코드 자체는 문법적으로 문제가 없습니다. 하지만 실행시켜 보면 실행되지 않습니다.

오라클에서 LOB 타입에 대해 원격지 DB간의 조회-삽입(수정)을 지원하지 않기 때문입니다.

그렇다면 어떻게 문제를 해결할 수 있을까요?

바로 쿼리를 실행해야 하는 DB에 임시 테이블을 만들어 활용하는 것입니다.

데이터를 조회할 원격지DB의 데이터를 읽어(SELECT) 들여 임시 테이블에 저장한 후, 임시 테이블에 저장된 데이터를 데이터를 보낼 원격지DB에 쓰는(INSERT or UPDATE) 방법입니다.

테이블은 데이터를 임시 저장할 구조에 맞게 미리 생성해 두어도 되고, 아래 쿼리와 같이 데이터를 불러올 원격지 DB의 테이블 구조와 동일하게 생성할 수 있습니다.

CREATE TABLE TMP_DATA3 AS

SELECT * FROM TBL_DATA2@OURDB WHERE 1 = 0;

위의 쿼리에서 SELECT 구문의 WHERE 절(WHERE 1 = 0)을 조금 의아해 하실 수도 있을 것 같은데요.

WHERE 1 = 0 절은 항상 조건이 FALSE (1과 0은 서로 다른 값이죠)이기 때문에 실제 테이블에 있는 레코드는 가져오지 않고, 테이블의 구조만 그대로 가져와 TMP_DATA3 테이블을 생성시킵니다.

즉 위의 CREATE 구문이 실행되면 원격지 OURDB에 있는 TBL_DATA2와 동일하면서 레코드가 없는 빈 테이블 TMP_DATA3이 생성됩니다.

그럼 이제 이 임시 테이블을 이용하여 원격지(DBLink를 이용한)간에  CLOB 타입의 데이터를 전송해 보도록 하겠습니다.

-- 임시 테이블에 원격지1의 데이터를 불러와 저장

INSERT INTO TMP_DATA3 (DATA_ID, REAL_DATA, DATA_CD, CRT_DATE)

SELECT DATA_ID, REAL_DATA, DATA_CD, SYSDATE FROM TBL_DATA2@OURDB WHERE DATA_ID = 5;

-- 임시 테이블에 저장된 데이터를 원격지2의 DB 테이블에 저장

INSERT INTO TBL_DATA1@YOURDB (DATA_ID, REAL_DATA, DATA_CD, CRT_DATE)

SELECT DATA_ID, REAL_DATA, DATA_CD, CRT_DATE FROM TMP_DATA3 WHERE DATA_ID = 5;

특별히 코드가 어려워지지는 않았습니다. 단지 INSERT 구문이 2개로 늘어났는데요.

기존 데이터를 전송하는데에 있어서 DB Link를 통해 원격지1 -> 원격지2 DB로 바로 전송하는 대신(이 방법은 오라클에서 지원하지 않기 때문에 불가), 원격지1 -> 중간DB -> 원격지2 로 처리하는 방식으로 중간에 단계를 하나 더 두어 문제를 해결하였습니다.