My first "technical" post is about appending data to Oracle blob chunk-by-chunk.
Such kind of tasks often arises when you are going to store large file in database.
BLOBs can store binary data (up to 4 GB) in database tables (see http://download.oracle.com/docs/cd/B10501_01/appdev.920/a96591/adl01int.htm#119741 to deep into the BLOB details).
Often you must upload this file chunk-by-chunk to gain complete control over the process.
I am using Devart dotConnect component for Oracle (http://www.devart.com/dotconnect/oracle/). Component looks very nice, but I must admit that sometimes strange errors happen with this component. But updates are regular so I think if you find a bug you can report it and you will have it fixed in next version.
Ok, let's start with database definition and insert a record with empty BLOB into the table:
CREATE TABLE DOCUMENTS.FILES
(
FID NUMBER(10) NOT NULL,
FDATA BLOB NOT NULL
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING
/
SET DEFINE OFF;
INSERT INTO DOCUMENTS.FILES
(
FID,
FDATA
)
VALUES
(
1,
EMPTY_BLOB()
)
/
-- (Here we are working with tablespace DOCUMENTS).
Okay, then we need to create a stored procedure that will take BLOB as a parameter and append it to BLOB stored in the table.
There is Oracle package dbms_lob. You can read more about its functions here http://www.psoug.org/reference/dbms_lob.html In the procedure we will use dbms_lob.append function that appends source BLOB to the end of destination BLOB:
CREATE OR REPLACE PROCEDURE clobtableappend (p_Value BLOB) is dest_lob BLOB; begin SELECT FDATA INTO dest_lob FROM DOCUMENTS.FILES WHERE FID = 1 FOR UPDATE; dbms_lob.append(dest_lob, p_Value); COMMIT; end; /
Ok, then download latest version of dotConnect (http://www.devart.com/dotconnect/oracle/download.html), install it, create a project in Visual Studio, reference Devart.Data, Devart.Data.Oracle assemblies from GAC, and we are ready to write the code. I created a console application:
string strConn = "User Id=***;Password=***;Server=***;Persist Security Info=True;direct=True;Unicode=true;sid=***;";
OracleConnection conn = new OracleConnection(strConn);
conn.Open();
DirectoryInfo directoryInfo = new DirectoryInfo(@"D:\temp");
OracleCommand command;
var files = directoryInfo.GetFiles();
for (int i = 0; i < files.Length; i++)
{
var stream = files[i].OpenRead();
byte[] buf = new byte[stream.Length];
stream.Read(buf, 0, buf.Length);
command = new OracleCommand("DOCUMENTS.ClobTableAppend", conn);
command.CommandType = CommandType.StoredProcedure;
var param = command.Parameters.Add("p_Value", OracleDbType.Blob);
param.Direction = ParameterDirection.Input;
param.Value = buf;
command.ExecuteNonQuery();
}
command = conn.CreateCommand();
command.CommandText = "SELECT FDATA FROM DOCUMENTS.FILES WHERE FID = 1";
using (var reader = command.ExecuteReader())
{
reader.Read();
var bb = reader.GetOracleLob(0);
byte[] b = new byte[bb.Length];
bb.Read(b, 0, b.Length);
File.WriteAllBytes("C:\\blob.txt", b);
}
The algorithm is simple:
1. We enumerate all files in D:\temp directory.
2. For each file:
3. We read it.
4. We append it into database BLOB by calling the stored procedure.
5. After all files are appended, we read them back and write in c:\blob.txt.
I recommend you to use text files to control correctness of the program.
