추천 스크랩 URL복사

참조1: Oracle® Database Application Developer's Guide - Large Objects 10g Release 2 (10.2)

       http://docs.oracle.com/cd/B19306_01/appdev.102/b14249/toc.htm 

참조2: BLOB 타입을 CLOB 타입으로 변경: http://www.dator.co.kr/data/textyle/96598

대상 : Oracle PL/SQL 개발자

LOB란?

LOB는 Large OBject의 약자로,

오라클에서 일반적인 data type으로는 담을 수 없는 대량의 데이터를 저장하기 위해 고안된 타입입니다.

또한, 기존의 기술인, LONG 과  LONG RAW를 대체하는 역할을 합니다. 

(오라클에서 LONG은 하위버전 호환을 위해서만 제공하고 있으며, 사용을 권장하지 않고 있습니다.)

Pro*C/C++, Pro*COBOL, VB(OO4O) 에서는, 최대 (4Giga-1)byte 까지 저장할 수 있고,

Java(JDBC), PL/SQL(DBMS_LOB), C(OCI) 에서는, 최대 8~128 TB 까지 저장할 수 있습니다.

아래는 LOB와 LONG RAW를 비교한 표입니다.

원문: http://docs.oracle.com/cd/B19306_01/appdev.102/b14249/adlob_tables.htm#sthref152

LOB 이해하기

PL/SQL에서의 LOB는 간단하게 말하면, 프로그래밍 언어의 포인터(참조변수) 와 같습니다.

테이블이나, 외부 파일, 임시저장소 등에 LOB를 저장할 영역을 할당해 놓고,

해당 영역을 가리키도록 구성되어 있습니다.

오라클에서는 이렇게 가리키는 녀석을, LOB Locator라고 부르고 

실제 저장된 LOB 값을, LOB Value라고 부르고 있습니다.

때문에, 일반 변수타입과 달리, 타입 하나에 2가지 객체가 포함되어 있으며,

이로 인해 독특한 특성을 지니고 있습니다.

LOB 저장소 기준 분류

LOB는 실제 타입은 아니고 여러 타입을 묶어서 통칭하는 분류어입니다.

LOB value가 저장되는 위치에 따라서 각기 다른 특징을 가지는데, 다음과 같이 분류됩니다.

1. 외부 (External LOB)

   - 현재로선 파일(BFILE)이 유일한 External LOB Type 입니다.

   - 값이 DB 외부에 저장되며, Read-only입니다.

   - 복사시 Lob locator만 복사됩니다. 테이블에 insert 해도 실제 값이 복사되지 않고 locator만 복사됩니다.

2. 내부 (Internal LOB)

  2-1. 테이블 (Persistent)   - 값이 테이블에 저장됩니다.

  2-2. 임시저장소(Temporary) - 값이 변수에 저장됩니다.

   - Read-Write 둘다 가능합니다

   - Lob locator와 Lob Value가 같이 붙어다니며, 복제시 인스턴스가 새로 생성됩니다. 

     즉, 같은 대상을 여러개의 LOB 변수가 가리키지 않으며, 수정해도 원본이 변경되지 않습니다.

   - 함수 인자에 NOCOPY 옵션을 주어서, 복사에 수행되는 부담을 덜 수 있습니다.

LOB 타입 기준 분류

BFILE - External LOB를 불러올 때 사용하는 타입입니다.

         읽기 전용 타입으로, Lob Locator만 복사됩니다.

BLOB  - Binary data 저장용입니다. RAW type과 쉽게 호환되나, CLOB와의 상호변환은 character set 문제를 감안해야 합니다.

CLOB  - Text data 저장용입니다.

NCLOB - CLOB의 유니코드 지원버전입니다. national character set과 관계없이 UCS-2 호환형식으로 저장됩니다.

LOB 변수 생성 및 값 대입

1. 외부 LOB(BFILE)

  - 외부 파일을 읽어와서 locator를 지정하는 방식입니다.

     BFILENAME 함수를 이용해서 locator를 지정할 수 있습니다.

     단 파일명과 디렉토리를 지정해 주어야 하며,

     CREATE DIRECTORY로 DIRECTORY 가 미리 지정되어 있어야 합니다.

     관련 예제) 하단 Ex1

2. 내부 LOB

2-1. 테이블(Persistent)

  - Select나 DML 문을 이용해서 locator를 지정할 수 있습니다

     관련 예제) 하단 Ex2

2-2. 임시(Temporary)

  - DBMS_LOB.CREATETEMPORARY(...) 를 이용해서 locator를 지정할 수 있습니다.

  - 또는 다른 LOB를 복사해서 생성하는 경우도 있습니다.

  - 둘 중 한가지가 아니라면, invalid locator 어쩌구 하는 에러가 발생합니다.

     관련 예제) 하단 Ex1

LOB 타입 간 변환

1. BFILE -> BLOB, CLOB

DBMS_LOB 패키지의 LOADBLOBFROMFILE 을 이용하면 BFILE -> BLOB 로 변환할 수 있고,

                  LOADCLOBFROMFILE 을 이용하면 BFILE -> CLOB 로 변환할 수 있습니다.

UTL_FILE 등의 파일 지원 패키지를 이용하면, file로 저장도 가능합니다. (LOB 와는 거리가 멀어져서 이글에서는 다루지 않겠습니다.)

     관련 예제) 하단 Ex1

2. BLOB <-> CLOB

DBMS_LOB 패키지의 CONVERTTOBLOB 와 CONVERTTOCLOB 함수를 이용해서 상호 변환이 가능합니다.

     관련 예제) 하단 Ex2

LOB 외 타입과의 변환

1. VARCHAR2 <-> CLOB

DBMS_LOB 패키지의 SUBSTR 함수를 이용하면 CLOB -> VARCHAR 변환이 가능합니다.

다만 글자가 잘리는 건 감안해야 합니다.

TO_CLOB 함수를 사용하면 VARCHAR2 -> CLOB 변환이 가능합니다.

     관련 예제) 하단 Ex2

2. RAW <-> BLOB

암시적으로 자동 변환됩니다.

이를 응용해서, UTL_RAW 패키지 함수들에 BLOB를 사용하면 다양한 변환을 수행할 수 있습니다.

     관련 예제) 하단 Ex2

추가 고려사항

성능 관련

 - OUT NOCOPY

큰 사이즈의 데이터를 다루다 보니, 데이터가 이동할 때의 부담이 큽니다.

함수나 프로시저 호출시, OUT 파라미터(IN OUT 포함) 에,

NOCOPY 옵션을 주면, 데이터를 복사하지 않고 원본을 바로 접근할 수 있게 합니다. (참조 전달)

NOCOPY 옵션을 주지 않으면, 값을 통째로 복사해서 넘기게 됩니다. (값 전달)

이 둘의 차이는, 4G 정도의 파일을 복사하는 것과 바로가기를 생성하는 것의 차이입니다.

대신, NOCOPY 는 변경값이 바로바로 적용되기 때문에, side-effect가 발생할 가능성이 높으니 주의!!

(함수의 두 인자에 동일 변수를 넣거나 하면, 두 값이 동시에 바뀝니다.)

(IN 파라미터는 NOCOPY 옵션을 주지 않아도 기본으로 NOCOPY로 동작합니다.)

관련 예제) 하단 Ex2

invalid locator 관련 오류해결

 - DBMS_LOB.CREATETEMPORARY(...)

LOB에 대해 잘 몰랐을 때, 저 에러를 자주 봤었습니다.

PL/SQL에서는 보통 LOB 변수를 만들어서 작업하게 되는데요,

이 변수는 temporary LOB 입니다.

일반 변수와 다르게, 여기다가 직접 값을 대입하면 저 에러가 발생합니다.

temporary LOB의 경우, 값을 저장할 공간이 자동으로 할당되지 않기 때문인데요,

CREATETEMPORARY를 호출하면 공간이 할당되고,

FREETEMPORARY 를 호출하면 할당된 공간이 해제됩니다. (직접 해제하지 않으면 접속이 끊길때(혹은 함수 호출 종료) 까지 메모리를 잡고 있습니다.)

가끔 에러가 나지 않는 경우가 있는데,

함수나 프로시져의 RETURN 값이 LOB 인 경우가 대부분으로,

내부에서 이미 공간을 할당해놓고 반환하기 때문입니다.

관련 예제) 하단 Ex1

--GRANT CREATE ANY DIRECTORY TO <test user>
CREATE DIRECTORY TEST AS 'C:\Users\Temp\Test';

DECLARE
    v_testfile   BFILE;
    v_lob_loc    CLOB;
    v_srcoffset  INTEGER := 1;
    v_destoffset INTEGER := 1;
    v_csid       INTEGER;
    v_lang_ctx   INTEGER := DBMS_LOB.DEFAULT_LANG_CTX;
    v_warn       INTEGER := 0;
BEGIN

    DBMS_LOB.CREATETEMPORARY(v_lob_loc, FALSE, DBMS_LOB.CALL);

    v_testfile := BFILENAME('TEST', 'TEST.txt');

    DBMS_LOB.OPEN (v_lob_loc, DBMS_LOB.LOB_READWRITE);

    SELECT NLS_CHARSET_ID('AL32UTF8') INTO v_csid FROM DUAL;

    DBMS_LOB.FILEOPEN (v_testfile);
    DBMS_LOB.LOADCLOBFROMFILE(v_lob_loc, v_testfile, DBMS_LOB.LOBMAXSIZE, v_destoffset, v_srcoffset, v_csid, v_lang_ctx, v_warn);
    DBMS_LOB.FILECLOSE(v_testfile);

    DBMS_OUTPUT.PUT_LINE(DBMS_LOB.SUBSTR(v_lob_loc));
    DBMS_LOB.CLOSE(v_lob_loc);

    DBMS_LOB.FREETEMPORARY(v_lob_loc);
END;
/

CREATE TABLE test_lob ( 
  my_blob BLOB,  
  my_clob CLOB
);

INSERT into test_lob values('12345', '12345');

CREATE OR REPLACE PROCEDURE TEST_LOB_CONV (p_blob IN OUT NOCOPY BLOB)
IS
  v_test_clob  CLOB;
  v_test_raw   RAW(2000);
  v_test_vchar VARCHAR2(2000);

  v_srcoffset  INTEGER;
  v_destoffset INTEGER;

  v_csid       INTEGER;
  v_lang_ctx   INTEGER := DBMS_LOB.DEFAULT_LANG_CTX;
  v_warn       INTEGER := 0;

BEGIN

  SELECT
    my_clob into v_test_clob
  FROM test_lob
  WHERE rownum = 1
  FOR UPDATE NOWAIT;

  SELECT NLS_CHARSET_ID('AL32UTF8') INTO v_csid FROM DUAL;

  v_test_vchar := DBMS_LOB.SUBSTR(v_test_clob);

  v_srcoffset  := 1;
  v_destoffset := 1;

  DBMS_LOB.CONVERTTOBLOB(
   p_blob,
   v_test_clob,
   DBMS_LOB.LOBMAXSIZE, v_destoffset, v_srcoffset, v_csid, v_lang_ctx, v_warn);

  DBMS_OUTPUT.PUT_LINE(UTL_RAW.CAST_TO_VARCHAR2(p_blob));

  v_srcoffset  := 1;
  v_destoffset := 1;

  DBMS_LOB.CONVERTTOCLOB(
   v_test_clob,
   p_blob,
   DBMS_LOB.LOBMAXSIZE, v_destoffset, v_srcoffset, v_csid, v_lang_ctx, v_warn);

  DBMS_OUTPUT.PUT_LINE(v_test_clob);

  v_test_clob := TO_CLOB(v_test_vchar);
  DBMS_OUTPUT.PUT_LINE(v_test_clob);

END;
/

CREATE OR REPLACE PROCEDURE RUN_TEST_LOB_CONV
IS
  v_test_blob BLOB;
BEGIN
  DBMS_LOB.CREATETEMPORARY(v_test_blob, FALSE, DBMS_LOB.CALL);

  TEST_LOB_CONV(v_test_blob);

  DBMS_LOB.FREETEMPORARY(v_test_blob);
END;
/

exec RUN_TEST_LOB_CONV; 

추천 스크랩 URL복사