현재 진행 중인 프로젝트에서 IMAGE 나 PDF 등 (Binary Data)을 저장하고 조회하는 기능이 필요해서 오라클 DB에 ODP.NET을 이용해서 작업을 구성하였다.
BLOB 작업
LOBs Basic
LOBs 는 데이터베이스에 저장할 수 있는 데이터 스트림으로서의 Large OBject 를 의미한다. 최대 처리할 수 있는 크기는 4G이며 다음과 같은 형식을 제공한다.
- BLOB - 비정형 바이너리 데이터로 저장되는 형식으로 문자형식(Character set)에 상관없는 비트스트림이다.
- CLOB - Single byte 와 Multi byte 의 문자형 데이터를 의미하고 고정/가변 길이를 제공하고 데이터베이스의 Character Set 기준을 따라서 저장된다.
- NCLOB - CLOB 과 같지만 Unicode 데이터로 저장된다.
Working with BLOB
아래와 같이 기본 테이블을 구성하도록 한다.
CREATE TABLE BLOBSAMPLE ( FILE_ID VARCHAR2(32) NOT NULL, FILE_NAME VARCHAR2(200) NOT NULL, FILE_EXTENSION VARCHAR2(10) NOT NULL, FILE_CONTENT BLOB NOT NULL, CONSTRAINT BLOBSAMPLE_PK PRIMARY KEY ( FILE_ID ) ENABLE);위의 테이블을 생성한 후에 생성된 테이블의 스크립트를 빼서 보면 아래와 같이 BLOB 컬럼에 대한 저장 관련 정보가 생성된 것을 확인할 수 있다.
일반적인 SQL 문장을 통해서 충분히 처리가 가능하지만, 프로젝트에서는 패키지를 기본으로 사용하고 있기 때문에 아래와 같이 BLOB 처리를 위한 패키지를 구성하도록 한다.
[ 패키지 HEAD ] 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;VALUES(I_FILE_ID, I_FILE_NAME, I_FILE_EXTENSION, DBMS_LOB.GETLENGTH(I_FILE_CONTENT),
I_FILE_CONTENT);
<span style="color: blue;">COMMIT WORK;</span>
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;
위의 코드는 일반적인 패키지 구성 코드이며, 별다른 특이사항은 없지만 BLOB 이기 때문에 별도로 함수를 사용해야 하는 부분이 존재한다. 전달된 BLOB 파라미터는 Lob Locator 이기 때문에 실제 데이터나 데이터의 길이를 구하기 위해서는 “DBMS_LOB” 로 시작되는 패키지를 사용해야 한다. 위에서는 데이터 길이를 구하기 위해서 “GETLENGTH” 함수를 사용하였다.
이제 서버에서 처리할 작업이 완료되었으므로 아래와 같이 클라이언트의 샘플 코드를 작성하도록 한다.
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();
<span style="color: red;"><b>oda.Fill(ds);</b></span></span></span><br />
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; }
LOBs 가 포함된 정보를 조회하는 경우는 ODP.NET Command가 LOBs Locator 만을 처리하는 것이 아니라 실제 데이터를 모두 조회할 수 있도록 "InitialLOBFetchSize" 를 -1 로 설정해 주는 부분만 조심하면 된다.
아래의 그림은 실행해서 BLOB 데이터를 처리한 결과이다. 기본 제공되는 DataGridView 컨트롤은 바인딩이 되는 컬럼의 형식이 BLOB (byte[]) 인 경우에는 자동으로 Image 로 전환하려고 하기 때문에 오류가 발생하게 된다. 오류를 표시하지 않기 위해서는 DataError 이벤트로 오류를 잡아주어야 한다. 그럼 Image로 변환이 불가한 바이너리 데이터는 표시되지 않는다. 당연히 Image 로 변환 가능한 것은 Image 가 표시된다.
Inserting LOBs on Oracle Server 8.0.5 and Olders
이전 버전의 오라클에서는 현재와 같은 방식으로 LOBs 를 지원하지 않는다. 따라서 매뉴얼로 EMPTY_BLOB or EMPTY_CLOB 함수를 사용해서 Lob Locator를 생성하고 초기화 해주어야 한다. 그리고 RETURNING 명령을 통해서 초기화된 Locator를 반환해 주어야 한다.
SET NAME = :Name
, VALUE = EMPTY_CLOB()
WHERE ID = :Id
RETUNING VALUE
INTO :Value
따라서 클라이언트 코드도 이에 맞춰서 파라미터를 구성해 주어야 한다.
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();
...
이전 버전에 대한 처리를 오라클 버전을 구해서 설치하고 테스트할 수 없었기 때문에 실제 검증이 되지는 않았지만, 인터넷에 정리된 내용을 기준으로 한 것이기 때문에 큰 문제는 없을 듯 하다.
댓글
댓글 쓰기