Append data to Oracle BLOB

Hello all, since now I am going to use English in my blog.
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.