[ ORACLE ] BLOB 작업하기

현재 진행 중인 프로젝트에서 IMAGE 나 PDF 등 (Binary Data)을 저장하고 조회하는 기능이 필요해서 오라클 DB에 ODP.NET을 이용해서 작업을 구성하였다. 
 
BLOB  작업 
 
LOBs Basic 
 
  LOBs 는 데이터베이스에 저장할 수 있는 데이터 스트림으로서의  Large OBject 를 의미한다. 최대 처리할 수 있는 크기는 4G이며 다음과 같은 형식을 제공한다. 
<ul>
<li>BLOB - 비정형 바이너리 데이터로 저장되는 형식으로 문자형식(Character set)에 상관없는 비트스트림이다.</li>
<li>CLOB - Single byte 와 Multi byte 의 문자형 데이터를 의미하고 고정/가변 길이를 제공하고 데이터베이스의 Character Set 기준을 따라서 저장된다.</li>
<li>NCLOB - CLOB 과 같지만 Unicode 데이터로 저장된다.</li>
</ul>
   LONG 이나 LONG RAW 형식을 처리하는 것과 같이 OracleDataReader를 이용해서 LOB 필드에 저정된 값을 읽을 수 있다. LOB 데이터 형식을 사용하는 방식의 다른 점은 DML 이나 PL/SQL 을 이용해서 해당 필드에 접근하는 경우에 명확하게 구분된다. BLOB 이나 CLOB 의 경우는 별도의 테이블스페이스에 저장이 되기 때문에 해당 필드는 데이터가 존재하는 위치의 포인터 정보만 가지고 있게 된다. (LOB Locator) 따라서 LONG 이나 LONG RAW 형식과는 다른 방식으로 관리가 된다. 
 
Working with BLOB 
 
  아래와 같이 기본 테이블을 구성하도록 한다. 
 
 
<div class="line number1 index0 alt2" style="background-color: #f4f4f4; border: 0px; font-variant: inherit; line-height: inherit; margin: 0px; padding: 0px 1em 0px 0em !important; vertical-align: baseline; white-space: pre !important;">
<code class="sql keyword" style="border: 0px; color: rgb(0, 102, 153) !important; font-variant: inherit; font-weight: bold !important; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">CREATE</code> <code class="sql keyword" style="border: 0px; color: rgb(0, 102, 153) !important; font-variant: inherit; font-weight: bold !important; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">TABLE </code><code class="sql keyword" style="border: 0px; font-variant: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">BLOBSAMPLE</code><code class="sql plain" style="border: 0px; font-variant: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;"> (</code></div>
<div class="line number2 index1 alt1" style="background-color: #f4f4f4; border: 0px; font-variant: inherit; line-height: inherit; margin: 0px; padding: 0px 1em 0px 0em !important; vertical-align: baseline; white-space: pre !important;">
<code class="sql spaces" style="border: 0px; font-variant: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">  </code><code class="sql plain" style="border: 0px; font-variant: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">FILE_ID VARCHAR2(32) NOT NULL,</code></div>
<div class="line number3 index2 alt2" style="background-color: #f4f4f4; border: 0px; font-variant: inherit; line-height: inherit; margin: 0px; padding: 0px 1em 0px 0em !important; vertical-align: baseline; white-space: pre !important;">
<code class="sql spaces" style="border: 0px; font-variant: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">  </code><code class="sql plain" style="border: 0px; font-variant: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">FILE_NAME VARCHAR2(200) NOT NULL,</code> 
<code class="sql plain" style="border: 0px; font-variant: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;"> FILE_EXTENSION VARCHAR2(10) NOT NULL,</code> 
<code class="sql plain" style="border: 0px; font-variant: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;"> FILE_CONTENT BLOB NOT NULL,</code> 
<code class="sql plain" style="border: 0px; font-variant: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;"> CONSTRAINT BLOBSAMPLE_PK PRIMARY KEY (</code> 
<code class="sql plain" style="border: 0px; font-variant: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;"> FILE_ID</code> 
<code class="sql plain" style="border: 0px; font-variant: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;"> )</code> 
<code class="sql plain" style="border: 0px; font-variant: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;"> ENABLE</code> 
<code class="sql plain" style="border: 0px; font-variant: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">);</code></div>
 
  위의 테이블을 생성한 후에 생성된 테이블의 스크립트를 빼서 보면 아래와 같이 BLOB 컬럼에 대한 저장 관련 정보가 생성된 것을 확인할 수 있다. 
 
 
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjSc0huCZmT-SFsieirVbnjZK5xT4PNWxKYK4Q-fDxYpejLBrEbsUv0t1JGE-xX3OuiUH0ZAvFInFywF576RehJA4RqCI5vYBNteuDP3IZxrI9mGPxtkGAsAk4bswroYfuxHnnC2D2vNwmE/s1600/%EC%9D%B4%EB%AF%B8%EC%A7%80+1.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="260" loading="lazy" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjSc0huCZmT-SFsieirVbnjZK5xT4PNWxKYK4Q-fDxYpejLBrEbsUv0t1JGE-xX3OuiUH0ZAvFInFywF576RehJA4RqCI5vYBNteuDP3IZxrI9mGPxtkGAsAk4bswroYfuxHnnC2D2vNwmE/s1600/%EC%9D%B4%EB%AF%B8%EC%A7%80+1.png" width="640" /></a></div>
 
  일반적인 SQL 문장을 통해서 충분히 처리가 가능하지만, 프로젝트에서는 패키지를 기본으로 사용하고 있기 때문에 아래와 같이 BLOB 처리를 위한 패키지를 구성하도록 한다. 
 
 
<div class="line number1 index0 alt2" style="background-color: #f4f4f4; border: 0px; margin: 0px; padding: 0px 1em 0px 0em !important; vertical-align: baseline;">
<div style="font-size: 1em !important; font-variant: inherit; line-height: inherit; white-space: pre !important;">
<code class="sql keyword" style="border: 0px; color: rgb(0, 102, 153) !important; font-size: 1em !important; font-variant: inherit; font-weight: bold !important; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">[ 패키지 HEAD ]</code></div>
<div style="font-size: 1em !important; font-variant: inherit; line-height: inherit; white-space: pre !important;">
CREATE OR REPLACE PACKAGE BLOBSAMPLE AS </div>
<code class="sql keyword" style="border: 0px; margin: 0px; padding: 0px; vertical-align: baseline; white-space: pre;"> PROCEDURE ADD_BLOB (
 I_FILE_ID IN VARCHAR2,
 I_FILE_NAME IN VARCHAR2,
 I_FILE_EXTENSION IN VARCHAR2,
 I_FILE_CONTENT IN BLOB
 );
 PROCEDURE READ_BLOBS (
 O_DATACUR OUT SYS_REFCURSOR
 );
END BLOBSAMPLE;</code> 
<div style="font-variant: inherit; line-height: inherit; white-space: pre !important;">
[ 패키지 BODY ]</div>
<div style="font-size: 1em !important; font-variant: inherit; line-height: inherit; white-space: pre !important;">
CREATE OR REPLACE PACKAGE BODY BLOBSAMPLE AS</div>
 PROCEDURE ADD_BLOB (
 I_FILE_ID IN VARCHAR2,
 I_FILE_NAME IN VARCHAR2,
 I_FILE_EXTENSION IN VARCHAR2,
 I_FILE_CONTENT IN BLOB
 ) AS
 BEGIN
 INSERT INTO LOBSAMPLE 
 VALUES(I_FILE_ID, I_FILE_NAME, I_FILE_EXTENSION, DBMS_LOB.GETLENGTH(I_FILE_CONTENT),  
 I_FILE_CONTENT);

COMMIT WORK;
 END ADD_BLOB;
 PROCEDURE READ_BLOBS (
 O_DATACUR OUT SYS_REFCURSOR
 ) AS
 BEGIN
 OPEN O_DATACUR FOR
 SELECT FILE_ID
 , FILE_NAME
 , FILE_EXTENSION
 , FILE_SIZE
 , FILE_CONTENT
 FROM LOBSAMPLE;
 END READ_BLOBS;
END BLOBSAMPLE;</div>
 
  위의 코드는 일반적인 패키지 구성 코드이며, 별다른 특이사항은 없지만 BLOB 이기 때문에 별도로 함수를 사용해야 하는 부분이 존재한다. 전달된 BLOB 파라미터는 Lob Locator 이기 때문에 실제 데이터나 데이터의 길이를 구하기 위해서는 "DBMS_LOB" 로 시작되는 패키지를 사용해야 한다. 위에서는 데이터 길이를 구하기 위해서 "GETLENGTH" 함수를 사용하였다. 
 
  이제 서버에서 처리할 작업이 완료되었으므로 아래와 같이 클라이언트의 샘플 코드를 작성하도록 한다. 
 
 
<div class="line number1 index0 alt2" style="background-color: #f4f4f4; border: 0px; margin: 0px; padding: 0px 1em 0px 0em !important; vertical-align: baseline;">
<div>
private void btnAdd_Click(object sender, EventArgs e) {
 FileInfo fi = new FileInfo(this.txtFile.Text);
 try {
 using (OracleConnection conn = new OracleConnection(ConfigurationManager.ConnectionStrings["LOCALDB"].ConnectionString)) {
 conn.Open();
 try { 
 using (OracleCommand cmd = new OracleCommand()) {
 cmd.Connection = conn;
 cmd.CommandText = "BLOBSAMPLE.ADD_BLOB";
 cmd.CommandType = CommandType.StoredProcedure;
 cmd.Parameters.Add(new OracleParameter() {
 ParameterName = "I_FILE_ID",
 OracleDbType = OracleDbType.Varchar2,
 Value = Guid.NewGuid().ToString("N"),
 Direction = ParameterDirection.Input
 });
 cmd.Parameters.Add(new OracleParameter() {
 ParameterName = "I_FILE_NAME",
 OracleDbType = OracleDbType.Varchar2,
 Value = fi.FullName,
 Direction = ParameterDirection.Input
 });
 cmd.Parameters.Add(new OracleParameter() {
 ParameterName = "I_FILE_EXTENSION",
 OracleDbType = OracleDbType.Varchar2,
 Value = fi.Extension,
 Direction = ParameterDirection.Input
 });
 cmd.Parameters.Add(new OracleParameter() {
 ParameterName = "I_FILE_CONTENT",
 OracleDbType = OracleDbType.Blob,
 Value = File.ReadAllBytes(fi.FullName),
 Direction = ParameterDirection.Input
 });

cmd.ExecuteNonQuery();
        }
      } finally {
        conn.Close(); 
      }
    }
  } catch (Exception ex) {
    MessageBox.Show(ex.Message);
  }
}

private void btnFile_Click(object sender, EventArgs e) {
  OpenFileDialog dlg = new OpenFileDialog();
  if (dlg.ShowDialog() == DialogResult.OK) {
    this.txtFile.Text = dlg.FileName;
  }
}

private void btnQuery_Click(object sender, EventArgs e) {
 try {
 using (OracleConnection conn = new OracleConnection(ConfigurationManager.ConnectionStrings["LOCALDB"].ConnectionString)) {
 conn.Open();
 try {
 using (OracleCommand cmd = new OracleCommand()) {
 cmd.Connection = conn;
 cmd.CommandText = "BLOBSAMPLE.READ_BLOBS";
 cmd.CommandType = CommandType.StoredProcedure;
 cmd.InitialLOBFetchSize = -1;
 cmd.Parameters.Add(new OracleParameter() {
 ParameterName = "O_DATACUR",
 OracleDbType = OracleDbType.RefCursor,
 Value = null,
 Direction = ParameterDirection.Output
 });

using (OracleDataAdapter oda = new OracleDataAdapter(cmd)) {
 DataSet ds = new DataSet();
 oda.Fill(ds); 
 this.dataGridView1.DataSource = ds.Tables[0];
 }
 }
 } finally {
 conn.Close(); 
 }
 }
 } catch (Exception ex) {
 MessageBox.Show(ex.Message);
 }
}

private void dataGridView1_DataError(object sender, DataGridViewDataErrorEventArgs e) {
 e.Cancel = true;
}</div>
</div>
 
  LOBs 가 포함된 정보를 조회하는 경우는 ODP.NET Command가 LOBs Locator 만을 처리하는 것이 아니라 실제 데이터를 모두 조회할 수 있도록 "InitialLOBFetchSize" 를 -1 로 설정해 주는 부분만 조심하면 된다. 
 
  아래의 그림은 실행해서 BLOB 데이터를 처리한 결과이다. 기본 제공되는 DataGridView 컨트롤은 바인딩이 되는 컬럼의 형식이 BLOB (byte[]) 인 경우에는 자동으로 Image 로 전환하려고 하기 때문에 오류가 발생하게 된다. 오류를 표시하지 않기 위해서는 DataError 이벤트로 오류를 잡아주어야 한다. 그럼 Image로 변환이 불가한 바이너리 데이터는 표시되지 않는다. 당연히 Image 로 변환 가능한 것은 Image 가 표시된다. 
 
 
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEizDP65l0UbALorrmmgdEkOWIwoHCaw_tmfKxgf4oRoO-IWjt1ybfDdOSoU8URWMNCCP6PZU0qiir4CXR5Zio-TthgFgNJEidWPNUToEcsRxfoYSfZvWdIAajYRd0ZJGYADz_6OYIPm4MMt/s1600/%EC%9D%B4%EB%AF%B8%EC%A7%80+2.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="470" loading="lazy" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEizDP65l0UbALorrmmgdEkOWIwoHCaw_tmfKxgf4oRoO-IWjt1ybfDdOSoU8URWMNCCP6PZU0qiir4CXR5Zio-TthgFgNJEidWPNUToEcsRxfoYSfZvWdIAajYRd0ZJGYADz_6OYIPm4MMt/s1600/%EC%9D%B4%EB%AF%B8%EC%A7%80+2.png" width="640" /></a></div>
 
Inserting LOBs on Oracle Server 8.0.5 and Olders 
 
  이전 버전의 오라클에서는 현재와 같은 방식으로 LOBs 를 지원하지 않는다. 따라서 매뉴얼로 EMPTY_BLOB or EMPTY_CLOB 함수를 사용해서 Lob Locator를 생성하고 초기화 해주어야 한다. 그리고 RETURNING 명령을 통해서 초기화된 Locator를 반환해 주어야 한다. 
 
 
<div class="line number1 index0 alt2" style="background-color: #f4f4f4; border: 0px; font-variant: inherit; line-height: inherit; margin: 0px; padding: 0px 1em 0px 0em !important; vertical-align: baseline; white-space: pre !important;">
UPDATE CLOBTABLE 
SET NAME = :Name 
, VALUE = EMPTY_CLOB() 
WHERE ID = :Id 
RETUNING VALUE 
INTO :Value</div>
 
  따라서 클라이언트 코드도 이에 맞춰서 파라미터를 구성해 주어야 한다. 
 
 
<div class="line number1 index0 alt2" style="background-color: #f4f4f4; border: 0px; font-variant: inherit; line-height: inherit; margin: 0px; padding: 0px 1em 0px 0em !important; vertical-align: baseline; white-space: pre !important;">
... 
string sql = "UPDATE CLOBTABLE SET NAME = :Name, VALUE = EMPTY_CLOB()" + 
 "WHERE ID = :Id RETURNING VALUE INTO :Value"; 
OracleCommand cmd = new OracleCommand(sql, conn); 
cmd.Parameters.Add("Id", 1); 
cmd.Parameters.Add("Name", "First"); 
OracleParameter param = cmd.Parameters.Add("Value", OracleDbType.Clob); 
param.Direction = ParameterDirection.Input; 
param.Value = "The CLOB Test Value"; 
cmd.ExecuteNonQuery(); 
...</div>
 
  이전 버전에 대한 처리를 오라클 버전을 구해서 설치하고 테스트할 수 없었기 때문에 실제 검증이 되지는 않았지만, 인터넷에 정리된 내용을 기준으로 한 것이기 때문에 큰 문제는 없을 듯 하다.

MSFL :: 모리스 소프트웨어 공작소

이 블로그 검색

[ ORACLE ] BLOB 작업하기

태그

댓글

댓글 쓰기