4,000 바이트 제한이 문제가 되십니까? LOB를 활용하십시오...


Downloads for this article:
Oracle Database 10g
Zend Core for Oracle
Apache HTTP Server 1.3 (및 이후 버전)


VARCHAR2와 같은 오라클 데이터 타입은 유용하게 활용됩니다. 하지만 4,000 바이트 이상의 데이터를 저장하려면 어떻게 해야 할까요? 바로 오라클이 지원하는 Long Object (LOB) 데이터 타입이 필요합니다. 또 LOB와 호환하는 PHP API의 사용법을 알고 있어야 합니다. 이에 필요한 지식을 갖고 있지 않은 개발자에게는 무척 까다로운 과제가 될 수 있습니다


이번 “Oracle+PHP Cookbook” 시리즈 연재에서는, LOB 데이터 타입과 PHP에서 LOB를 다루는 방법을 예제를 통해 알아보기로 합니다.

오라클의 Long Object

오라클은 다음과 같은 LOB 데이터 타입을 지원합니다:
BLOB - 바이너리 데이터의 저장에 사용
CLOB - 데이터베이스 문자 셋 인코딩을 이용한 문자 데이터 저장에 사용
NCLOB - 국가별 문자 셋을 이용한 유니코드 문자 데이터 저장에 사용. (본 문서에서 사용되는 PHP OCI8 익스텐션은 NCLOB을 지원하지 않음을 참고하십시오.)
BFILE - 운영체제 파일시스템에 저장된 외부 파일의 참조에 사용
그 밖에도 temporary LOB가 있습니다. temporary LOB는 BLOB, CLOB 또는 NCLOB 등의 형태를 취할 수 있으며, 해제되기 전까지 임시 테이블스페이스에 저장됩니다.

이전 버전의 오라클에서는 문자 및 바이너리 데이터의 저장을 위해 LONG, LONG RAW 타입을 지원했습니다. 이 두 가지 타입은 Oracle9i에서 LOB로 대체되었습니다.

LOB 데이터의 저장. Oracle Database 10g는 BLOB, CLOB, NCLOB 타입에 대해 단일 레코드 당 최대 128TB의 용량을 지원합니다 (실제 최대 용량은 데이터베이스 블록 사이즈와 LOB의 “chunk” 설정에 따라 달라질 수 있습니다.

LOB는 LOB 컨텐트와 LOB 로케이터(locator)의 두 가지 요소로 구성됩니다. LOB 로케이터는 LOB에 대한 “포인터”로 활용됩니다. LOB 로케이터는 LOB 데이터를 효과적으로 저장하고 관리하기 위한 목적에서 설계되었으며, PHP API의 INSERT, UPDATE, SELECT 에도 반영되어 있습니다 (아래 설명 참고)..

오라클은 LOB 레코드의 크기가 4KB를 넘지 않는 경우 LOB 컨텐트를 테이블 내부에 “인라인(in-line)” 형태로 저장합니다. 4KB를 초과하는 LOB는 테이블의 테이블스페이스 내에 아웃오브라인(out-of-line)” 형태로 저장됩니다. 이러한 방법으로 작은 크기의 LOB를 신속하게 조회하는 한편, 큰 LOB 때문에 테이블 스캔 성능이 저하되는 것을 방지할 수 있습니다.

LOB 저장 및 액세스를 위해 메모리 캐싱, 버퍼링과 같은 테크닉을 사용하여 애플리케이션의 성능을 개선할 수 있습니다. 자세한 정보는 오라클 제품문서의 LOB Performance Guidelines와 Oracle Database Application Developer's Guide - Large Objects를 참고하시기 바랍니다.

LOB 관련 제약사항. LOB 타입의 활용에 관련한 몇 가지 제약사항이 존재합니다. 특히 SQL 구문과 관련한 제약사항은 주의할 필요가 있습니다. 아래와 같은 쿼리에서는 LOB 타입을 사용할 수 없습니다.
SELECT DISTINCT
ORDER BY
GROUP BY

또 테이블 JOIN,UNION, INTERSECTION, MINUS 등의 구문에서도 LOB 타입 컬럼을 사용하는 것이 금지되어 있습니다.

그 밖에도 LOB를 프라이머리 키 컬럼으로 사용할 수 없는 등의 여러 가지 제약사항이 존재합니다. 자세한 정보는 Oracle Database Application Developer's Guide - Large Objects를 참고하십시오.

CLOB과 문자 셋

데이터베이스의 디폴트 문자 셋(character set)은 NLS_CHARACTERSET 매개변수를 통해 설정되며, CLOB 타입으로 저장된 텍스트는 이 문자 셋을 이용하여 인코딩 됩니다. 아래 SQL 구문을 이용하여 데이터베이스 문자 셋을 설정하시기 바랍니다:
SELECT value FROM nls_database_parameters WHERE parameter = 'NLS_CHARACTERSET'

현재로서는 PHP가 NCLOB을 지원하지 않으므로, 아래와 같은 방법으로 UTF-8과 같은 유니코드 인코딩을 기본 데이터베이스 문자 셋으로 설정하는 것을 고려해 볼 수 있습니다:
ALTER DATABASE CHARACTER SET UTF8

참고: 기존 데이터 또는 애플리케이션 코드가 다른 문자 셋을 사용하고 있는 경우, 변경 작업이 전체 환경에 미치는 영향을 미리 감안하여야 합니다. 자세한 정보는 Oracle Globalization Support Guide와 An Overview on Globalizing Oracle PHP Applications를 참고하시기 바랍니다.

LOB 데이터의 처리

본 문서에서는 PHP의 OCI8 익스텐션에 초점을 맞추어 설명합니다. 또 오라클에 포함된 DBMS_LOB 패키지는 PL/SQL을 이용한 LOB 처리를 위한 병렬 프로시저 및 함수를 제공하고 있음을 참고하시기 바랍니다.

PHP OCI8 익스텐션은 글로벌 PHP 네임스페이스에 “OCI-Lob”이라 불리는 PHP 클래스를 등록합니다. LOB 타입의 컬럼이 포함된 SELECT 구문이 실행된 경우, PHP는 이 구문을 OCI-Lob 오브젝트 인스턴스에 자동으로 바인딩합니다. OCI-Lob 오브젝트에 대한 참조가 확보되면 load(), save()등의 메소드를 이용하여 LOB 컨텐트를 조회, 수정할 수 있습니다.

사용 가능한 OCI-Lob 메소드는 PHP 버전에 따라 달라집니다. PHP5는 read(), seek(), 그리고 append()등의 메소드를 지원합니다. 이에 대한 PHP Manual 의 설명이 다소 불충분한 감이 있으므로, 지원되는 버전 넘버가 궁금한 경우 아래와 같은 스크립트를 사용해서 검증해 보시기 바랍니다.
<?php
foreach (get_class_methods('OCI-Lob') as $method ) {
print "OCI-Lob::$method()\n";
}
?>

PHP 5.0.5가 실행 중인 필자의 시스템에서는 아래와 같은 메소드 리스트가 출력되었습니다:
OCI-Lob::load()
OCI-Lob::tell()
OCI-Lob::truncate()
OCI-Lob::erase()
OCI-Lob::flush()
OCI-Lob::setbuffering()
OCI-Lob::getbuffering()
OCI-Lob::rewind()
OCI-Lob::read()
OCI-Lob::eof()
OCI-Lob::seek()
OCI-Lob::write()
OCI-Lob::append()
OCI-Lob::size()
OCI-Lob::writetofile()
OCI-Lob::writetemporary()
OCI-Lob::close()
OCI-Lob::save()
OCI-Lob::savefile()
OCI-Lob::free()

PHP 4.x OCI8 익스텐션은 전체 LOB의 일괄적인 읽기 및 쓰기만을 지원합니다. PHP5에서는 LOB의 일부 “청크(chung)”만을 읽거나 쓸 수 있으며 setBuffering(),getBuffering() 메소드를 이용한 LOB 버퍼링을 지원합니다. 또 PHP5는 스탠드얼론 함수로 oci_lob_is_equal(), oci_lob_copy()를 지원합니다.

본 문서에서 사용된 예제들은 새로운 PHP5 OCI 함수명을 사용하고 있습니다 (예: oci_parse 대신 OCIParse 사용)예제에서 공통적으로 사용되는 시퀀스와 테이블이 아래와 같습니다:
CREATE SEQUENCE mylobs_id_seq
NOMINVALUE
NOMAXVALUE
NOCYCLE
CACHE 20
NOORDER
INCREMENT BY 1;

CREATE TABLE mylobs (
id NUMBER PRIMARY KEY,
mylob CLOB
)

본 문서의 예제는 대부분 CLOB을 사용하고 있지만, 동일한 로직이 BLOB에도 적용 가능함을 참고하시기 바랍니다.

LOB의 INSERT

LOB의 INSERT 작업을 수행하려면, 먼저 오라클의 EMPTY_BLOB 또는 EMPTY_CLOB 함수를 이용하여 LOB를 초기화해야 합니다. NULL 값을 포함한 LOB는 업데이트할 수 없습니다.

초기화가 완료되면, 컬럼을 PHP OCI-Lob 오브젝트에 바인딩하고 오브젝트의 save() 메소드를 이용하여 LOB 컨텐트를 업데이트합니다.

아래 스크립트는 LOB 타입 데이터의 INSERT 쿼리 수행을 위한 예를 보여주고 있습니다:
<?php
// connect to DB etc...

$sql = "INSERT INTO
mylobs
(
id,
mylob
)
VALUES
(
mylobs_id_seq.NEXTVAL,
--Initialize as an empty CLOB
EMPTY_CLOB()
)
RETURNING
--Return the LOB locator
mylob INTO :mylob_loc";

$stmt = oci_parse($conn, $sql);

// Creates an "empty" OCI-Lob object to bind to the locator
$myLOB = oci_new_descriptor($conn, OCI_D_LOB);

// Bind the returned Oracle LOB locator to the PHP LOB object
oci_bind_by_name($stmt, ":mylob_loc", $myLOB, -1, OCI_B_CLOB);

// Execute the statement using , OCI_DEFAULT - as a transaction
oci_execute($stmt, OCI_DEFAULT)
or die ("Unable to execute query\n");

// Now save a value to the LOB
if ( !$myLOB->save('INSERT: '.date('H:i:s',time())) ) {

// On error, rollback the transaction
oci_rollback($conn);

} else {

// On success, commit the transaction
oci_commit($conn);

}

// Free resources
oci_free_statement($stmt);
$myLOB->free();


// disconnect from DB etc.
?>

위 예제에서 트랜잭션을 사용하고 있으며, oci_execute 의 실행과정에서 OCI_DEFAULT 상수를 통해 an oci_commit 또는 oci_rollback를 대기할 것을 지시하고 있습니다. 이는 INSERT 작업이 두 단계(로우의 생성, LOB의 업데이트)로 실행되기 때문입니다.
참고 위에서는 BLOB 타입이 사용되고 있으므로oci_bind_by_name 함수를 호출하는 작업만이 필요합니다:

oci_bind_by_name($stmt, ":mylob_loc", $myLOB, -1, OCI_B_BLOB);

마찬가지로, LOB 타입을 명시하지 않고 문자열을 바인딩할 수도 있습니다;
<?php
// etc.

$sql = "INSERT INTO
mylobs
(
id,
mylob
)
VALUES
(
mylobs_id_seq.NEXTVAL,
:string
)
";

$stmt = oci_parse($conn, $sql);

$string = 'INSERT: '.date('H:i:s',time());

oci_bind_by_name($stmt, ':string', $string);

oci_execute($stmt)
or die ("Unable to execute query\n");

// etc.
?>

위와 같은 방법을 사용하면 코드를 대폭적으로 단순화할 수 있습니다. 이러한 방식은 LOB에 기록되는 데이터의 크기가 비교적 작은 경우에 유용합니다. 큰 용량의 파일 컨텐트를 LOB에 스트리밍하고자 하는 경우에는, PHP LOB 오브젝트에 대해 write(), flush() 함수를 호출하고 (전체 파일을 하나의 인스턴스로 메모리에 보관하는 대신) 작은 청크(chunk) 단위로 루프를 돌려 컨텐트를 처리하는 것이 좋습니다.

LOB의 SELECT

LOB 컬럼을 포함하는 데이터를 SELECT 쿼리로 조회하는 경우, PHP는 해당 컬럼을 OCI-Lob 오브젝트에 자동으로 바인딩합니다. 그 예가 다음과 같습니다:
<?php
// etc.

$sql = "SELECT
*
FROM
mylobs
ORDER BY
Id
";

$stmt = oci_parse($conn, $sql);

oci_execute($stmt)
or die ("Unable to execute query\n");

while ( $row = oci_fetch_assoc($stmt) ) {
print "ID: {$row['ID']}, ";

// Call the load() method to get the contents of the LOB
print $row['MYLOB']->load()."\n";
}

// etc.
?>

OCI_RETURN_LOBS 상수와 oci_fetch_array()를 함께 사용하여 LOB 오브젝트를 그 값으로 대치하도록 함으로써 작업을 한층 단순화할 수 있습니다:
while ( $row = oci_fetch_array($stmt, OCI_ASSOC+OCI_RETURN_LOBS) ) {
print "ID: {$row['ID']}, {$row['MYLOB']}\n";
}

LOB의 UPDATE

LOB를 업데이트하는 경우, 위의 INSERT 구문 예와 마찬가지로 "RETURNING" 커맨드를 사용할 수도 있습니다. 하지만 “SELECT ... FOR UPDATE” 구문을 사용하는 편이 훨씬 간단합니다:
<?php
// etc.

$sql = "SELECT
mylob
FROM
mylobs
WHERE
id = 3
FOR UPDATE /* locks the row */
";

$stmt = oci_parse($conn, $sql);

// Execute the statement using OCI_DEFAULT (begin a transaction)
oci_execute($stmt, OCI_DEFAULT)
or die ("Unable to execute query\n");

// Fetch the SELECTed row
if ( FALSE === ($row = oci_fetch_assoc($stmt) ) ) {
oci_rollback($conn);
die ("Unable to fetch row\n");
}

// Discard the existing LOB contents
if ( !$row['MYLOB']->truncate() ) {
oci_rollback($conn);
die ("Failed to truncate LOB\n");
}

// Now save a value to the LOB
if ( !$row['MYLOB']->save('UPDATE: '.date('H:i:s',time()) ) ) {

// On error, rollback the transaction
oci_rollback($conn);

} else {

// On success, commit the transaction
oci_commit($conn);

}

// Free resources
oci_free_statement($stmt);
$row['MYLOB']->free();


// etc.
?>

INSERT의 경우와 마찬가지로, 위 코드는 UPDATE 수행을 위해 트랜잭션을 이용하고 있습니다. 특히 주목해야 할 부분이 truncate()함수를 호출하는 부분입니다. save()를 이용해서 LOB를 업데이트하는 경우, save() 함수는 LOB 컨텐트의 시작 부분에서부터 새로운 데이터의 길이에 해당되는 부분까지만을 업데이트합니다. 따라서 이전의 컨텐트가 여전히 LOB에 남아 있을 수도 있습니다.
PHP 4.x, truncate()함수가 지원되지 않습니다. 따라서 새로운 데이터를 업데이트하기 전에 LOB의 기존 컨텐트를 지우려면 오라클의 EMPTY_CLOB() 함수를 사용해야 합니다.

$sql = "UPDATE
mylobs
SET
mylob = EMPTY_CLOB()
WHERE
id = 2403
RETURNING
mylob INTO :mylob
";

$stmt = OCIParse($conn, $sql);

$mylob = OCINewDescriptor($conn,OCI_D_LOB);

OCIBindByName($stmt,':mylob',$mylob, -1, OCI_B_CLOB);

// Execute the statement using OCI_DEFAULT (begin a transaction)
OCIExecute($stmt, OCI_DEFAULT)
or die ("Unable to execute query\n");

if ( !$mylob->save( 'UPDATE: '.date('H:i:s',time()) ) ) {

OCIRollback($conn);
die("Unable to update lob\n");

}

OCICommit($conn);
$mylob->free();
OCIFreeStatement($stmt);

BFILE에 대한 작업 수행

BFILE 타입에 대해 INSERT 또는 UPDATE를 수행하는 경우, 오라클은 (웹 서버가 아닌) 데이터베이스 서버 운영체제의 파일시스템에서 저장된 파일의 위치를 지정하는 작업을 수행하게 됩니다. 또 SELECT 구문을 이용하여 BILE의 컨텐트를 읽어 들이거나, 필요한 경우 DBMS_LOB 패키지의 함수와 프로시저를 호출하여 파일에 대한 정보만을 확인할 수도 있습니다.

BFILE은 파일시스템에 저장된 파일을 직접 액세스하는 한편으로 SQL 구문을 이용하여 파일의 위치를 확인할 수 있게 한다는 장점을 제공합니다. 예를 들어 웹 서버에서 이미지를 직접 제공하면서, BFILE을 포함한 테이블과 다른 테이블과의 관계 정보를 추적하는 것이 가능합니다 (한 예로, 어떤 사용자가 어떤 파일을 업로드했는지 확인할 수 있습니다).

위에서 사용된 테이블 스키마를 먼저 업데이트해 보겠습니다;
ALTER TABLE mylobs ADD( mybfile BFILE )

그런 다음, 오라클에 디렉토리 앨리어스(alias)를 등록하고(이 과정에서 관리자 권한이 필요합니다), 테이블에 대한 읽기 권한을 할당합니다:
CREATE DIRECTORY IMAGES_DIR AS '/home/harryf/public_html/images'
GRANT READ ON DIRECTORY IMAGES_DIR TO scott

이제 아래와 같이 BFILE의 INSERT 작업을 수행할 수 있습니다:
<?php
// etc.

// Build an INSERT for the BFILE names
$sql = "INSERT INTO
mylobs
(
id,
mybfile
)
VALUES
(
mylobs_id_seq.NEXTVAL,
/*
Pass the file name using the Oracle directory reference
I created called IMAGES_DIR
*/
BFILENAME('IMAGES_DIR',:filename)
)";

$stmt = oci_parse($conn, $sql);

// Open the directory
$dir = '/home/harryf/public_html/images';
$dh = opendir($dir)
or die("Unable to open $dir");

// Loop through the contents of the directory
while (false !== ( $entry = readdir($dh) ) ) {

// Match only files with the extension .jpg, .gif or .png
if ( is_file($dir.'/'.$entry) && preg_match('/\.(jpg|gif|png)$/',$entry) ) {

// Bind the filename of the statement
oci_bind_by_name($stmt, ":filename", $entry);

// Execute the statement
if ( oci_execute($stmt) ) {
print "$entry added\n";
}
}

}

필요한 경우, CLOB을 조회한 것과 동일한 방법으로 오라클에서 BFILE을 읽어 들일 수 있습니다. 또는 아래와 같이 DBMS_LOB.FILEGETNAME 프로시저를 통해 파일 이름만을 확인한 후 파일시스템에서 직접 액세스하는 방법을 사용할 수도 있습니다:
<?php
// etc.

$sql = "SELECT
id
FROM
mylobs
WHERE
-- Select only BFILES which are not null
mybfile IS NOT NULL;

$stmt1 = oci_parse($conn, $sql);

oci_execute($stmt1)
or die ("Unable to execute query\n");

$sql = "DECLARE
locator BFILE;
diralias VARCHAR2(30);
filename VARCHAR2(30);

BEGIN

SELECT
mybfile INTO locator
FROM
mylobs
WHERE
id = :id;

-- Get the filename from the BFILE
DBMS_LOB.FILEGETNAME(locator, diralias, filename);

-- Assign OUT params to bind parameters
:diralias:=diralias;
:filename:=filename;

END;";

$stmt2 = oci_parse($conn, $sql);

while ( $row = oci_fetch_assoc ($stmt1) ) {

oci_bind_by_name($stmt2, ":id", $row['ID']);
oci_bind_by_name ($stmt2, ":diralias", $diralias,30);
oci_bind_by_name ($stmt2, ":filename", $filename,30);

oci_execute($stmt2);
print "{$row['ID']}: $diralias/$filename\n";

}
// etc.
?>

또, DBMS_LOB.FILEEXISTS 함수를 사용하면 운영체제에서 삭제되었음에도 불구하고 여전히 데이터베이스에서 참조되고 있는 파일이 무엇인지 확인할 수 있습니다.

결론

이번 연재에서는 Oracle Database 10g에서 사용되는 다양한 종류의 LOB에 대해 설명했습니다. 이제 데이터베이스에 대용량의 데이터를 효과적으로 저장하기 위해 각각의 LOB 타입이 어떻게 활용되는지 이해하셨을 것입니다. 또 PHP OCI8 API를 이용하여 LOB 데이터를 처리하는 방법과, Oracle/PHP 환경에서 자주 발생하는 개발 관련 이슈에 대해서 설명을 드렸습니다.