DATOR

LOB 활용법 (PL/SQL)

Document URL : http://www.dator.co.kr/381212
Oracle DB | Posted on December 18th, 2012 at 01:10 by 밀오 | 조회수 : 48589

참조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 개발자

 

이 글에서는 PL/SQL Temporary LOB 이용법을 주요 주제로 다룹니다.

 

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를 비교한 표입니다.

 

Table 4-1 LOBs Vs. LONG RAW

LOB Data Type

LONG and LONG RAW Data Type

하나의 row 에 여러개의 LOB들을 저장할 수 있다.

각 row에 LONG 이나 LONG RAW를 오직 한 개만 저장할 수 있다.

LOB들은 사용자 지정 타입의 속성으로 사용할 수 있다.

LONG 과 LONG RAW 둘다 불가능하다.

테이블의 열에는 LOB locator만 저장된다. 

BLOB와 CLOB의 데이터는 별도의 table space에 저장되기도 하며, BFILE은 외부에 파일로 저장된다.

Inline LOB의 경우, 테이블의 열에 약 4000 바이트 이하의 데이터를 갖는 LOB를 저장하게 된다.

LONG 과 LONG RAW의 경우, 테이블의 컬럼에 전체 데이터가 저장된다.

LOB류의 열에 접근할 때엔, locator와 data중 어떤 것을 fetch할 것인지 선택할 수 있다.

LONG 이나 LONG RAW 에 접근할 때엔, 전체 값을 통째로 받아온다.

LOB류의 최대 크기는, 블록 크기 설정에 따라서 8 TB나 그 이상으로 커질 수 있다.

LONG 과 LONG RAW의 최대 크기는 2GB 로 제한된다.

데이터를 random, piece-wise 방식으로 조작하는 데에 매우 유연하게 대처한다.

LOB들은 데이터를 임의의 지점(offset) 부터 접근할 수 있다.

데이터를 random, piece-wise 방식으로 조작하는 데에 덜 유연하게 대처한다.

LONG 류는 데이터를, 시작점부터 원하는 지점을 찾아가는 방식으로 접근한다.

local과 distributed 양쪽 환경 모두, LOB들을 복제할 수 있다.

Local과 Distributed 양쪽 환경 모두, LONG이나 LONG RAW를 복제할 수 없다.

(참고 Oracle Database Advanced Replication)


원문: 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만 복사됩니다.

   - Lob 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

 

 

LOB 사용 후기

대부분의 데이터타입이 최대 4000 바이트 까지만 지원하기 때문에,
LOB를 사용하지 않고, 그 보다 큰 데이터를 다루기 위해서는, 타입에 맞게 잘게 쪼개서 작업해야 합니다.

쪼개는 것도 일이고, 이를 다시 합치는 것도 여러가지로 골치아픈 일이 많습니다.
사용하는 문자셋에 따라서 최대 가용 문자수가 변하기 때문입니다.

LOB는 잘 다뤄보지 않던 데이터타입 이라,
부담도 크고, 다른 문제가 없을지, 오류는 발생하지 않을지... 걱정이 되는 것이 사실입니다.

하지만, 충분히 연구해서 잘 사용한다면,
막혔던 곳이 뻥 뚫린 기분을 만끽할 수 있습니다.
 

 
예제

Ex1 : BFILE 사용, BFILE -> CLOB / temporary LOB 사용, DBMS_LOB.CREATETEMPORARY(...)


사용예제)


--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;
/



 

Ex2 : persistent LOB 사용, NOCOPY, BLOB <-> CLOB, VARCHAR2 <-> CLOB, RAW <->BLOB(암시적)


사용예제)


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; 



111.PNG 

Tagged :
       

Comments : 0