LOB基本概念：

    LOB是一種數據類型，主要用于存儲大對象。在Oracle中存在三種LOB數據類型分別是BLOB：Binary Large Object、CLOB：Character Large Object、DBCLOB：Double-byte Character Large Object，今天主要來看一下這個BLOB和CLOB類型。

1. BLOB數據類型存儲非結構化二進制大對象，BLOB對象可以當做是沒有字符集語義的bitstreams。
2. CLOB數據類型存儲單字節和多字節數據，這兩個的固定的寬度和和不同的寬度都支持設置，兩者都可以使用數據庫字符集。
3. NCLOB數據類型存儲Unicode數據。

BLOB 數據的使用

    二進制數據一般都會表現為字節流或是緩沖區。dotConnect for Oracle允許用最方便的方式操縱BLOB數據。你可以使用OracleLob。值屬性或使用OracleLob.Read()和OracleLob.Write()方法來傳輸數據到或是來自服務器，兩種方法下面都有雨示例。值得注意的是當OracleLob.LobType屬性是OracleDbType.Blob時，這個OracleLob.Value被視為字節數組，OracleDbType.Clob以及OracleDbType.NClob將會被一字符串視為OracleLob.Value。

    下一個示例程序顯示如何從硬盤上傳文件到服務器以及下載下來。要執行這些程序，你必須創建一個表，如下：

CREATE TABLE PICTURES (

  ID NUMBER(12),

  PICNAME VARCHAR2(20),

  PICTURE BLOB

)

[C#]

static void UploadBlob(OracleConnection myConnection)
{
  //Open file on disk
  FileStream fs = new FileStream("D:\\Tmp\\test.bmp", FileMode.Open, FileAccess.Read);
  BinaryReader r = new BinaryReader(fs);
  myConnection.Open();
  //Create temporary BLOB
  OracleLob myLob = new OracleLob(myConnection,OracleDbType.Blob);
  int streamLength = (int)fs.Length;
  //Transfer data to server
  myLob.Write(r.ReadBytes(streamLength), 0, streamLength);
  //Perform INSERT
  OracleCommand myCommand = new OracleCommand(
   "INSERT INTO Pictures (ID, PicName, Picture) VALUES(1,'pict1',:Pictures)", myConnection);
  OracleParameter myParam = myCommand.Parameters.Add("Pictures", OracleDbType.Blob);
  myParam.OracleValue = myLob;
  try
  {
    Console.WriteLine(myCommand.ExecuteNonQuery() + " rows affected.");
  }
  finally
  {
    myConnection.Close();
    r.Close();
    fs.Close();
  }
}

static void DownloadBlob(OracleConnection myConnection)
{
  OracleCommand myCommand = new OracleCommand("SELECT * FROM Pictures", myConnection);
  myConnection.Open();
  OracleDataReader myReader = myCommand.ExecuteReader(System.Data.CommandBehavior.Default);
  try
  {
    while (myReader.Read())
    {
      //Obtain OracleLob directly from OracleDataReader
      OracleLob myLob = myReader.GetOracleLob(myReader.GetOrdinal("Picture"));
      if (!myLob.IsNull)
      {
        string FN = myReader.GetString(myReader.GetOrdinal("PicName"));
        //Create file on disk
        FileStream fs = new FileStream("D:\\Tmp\\" + FN + ".bmp", FileMode.Create);
        //Use buffer to transfer data
        byte[] b = new byte[myLob.Length];
        //Read data from database
        myLob.Read(b,0,(int)myLob.Length);
        //Write data to file
        fs.Write(b,0,(int)myLob.Length);
        fs.Close();
        Console.WriteLine(FN + " downloaded.");
      }
    }
  }
  finally
  {
    myReader.Close();
    myConnection.Close();
  }
}

[Visual Basic]

Public Sub UploadBlob(ByVal myConnection As OracleConnection)
  'Open file on disk
  Dim fs As FileStream = New FileStream("D:\Tmp\test.bmp", FileMode.Open, FileAccess.Read)
  Dim r As BinaryReader = New BinaryReader(fs)
  myConnection.Open()
  'Create temporary BLOB
  Dim myLob As OracleLob = New OracleLob(myConnection, OracleDbType.Blob)
  Dim streamLength As Int32 = fs.Length
  'Transfer data to server
  myLob.Write(r.ReadBytes(streamLength), 0, streamLength)
  'Perform INSERT
  Dim myCommand As OracleCommand = New OracleCommand(
    "INSERT INTO Pictures (ID, PicName, Picture) VALUES(1,'pict1',:Pictures)", myConnection)
  Dim myParam As OracleParameter = myCommand.Parameters.Add("Pictures", OracleDbType.Blob)
  myParam.OracleValue = myLob
  Try
    Console.WriteLine(myCommand.ExecuteNonQuery() & " rows affected.")
  Finally
    myConnection.Close()
    r.Close()
    fs.Close()
  End Try
End Sub

Public Sub DownloadBlob(ByVal myConnection As OracleConnection)
  Dim myCommand As New OracleCommand("SELECT * FROM Pictures", myConnection)
  myConnection.Open()
  Dim myReader As OracleDataReader = & _
      myCommand.ExecuteReader(System.Data.CommandBehavior.Default)
  Try
    While myReader.Read()
      Dim myLob As OracleLob = myReader.GetOracleLob(myReader.GetOrdinal("Picture"))
      If Not myLob.IsNull Then
        Dim FN As String = myReader.GetString(myReader.GetOrdinal("PicName"))
        Dim fs As FileStream = New FileStream("D:\Tmp\" + FN + ".bmp", FileMode.Create)
        Dim w As BinaryWriter = New BinaryWriter(fs)
        w.Write(myLob.Value)
        w.Close()
        fs.Close()
        Console.WriteLine(String.Concat(FN, " downloaded."))
      End If
    End While
  Finally
    myReader.Close()
    myConnection.Close()
  End Try
End Sub

CLOB 數據使用

    CLOB 數據的使用和BLOB數據是一樣的，區別就在于Value屬性的表現。當從OracleLob.Value讀取時，CLOB和NCLO數據類型，會得到字符串。數據是透明的解碼，所以你不必擔心字符集。如果需要原始數據，可以使用 OracleLob的里流媒體功能，那是OracleLob.Read 和OracleLob.Write的方法。請注意，由于OracleLob.Value是一個只讀屬性，你可以只用OracleLob.Write方法將數據寫入到的LOB中，但是并沒有執行編碼。

For example consider this table definition.

CREATE TABLE ClobTable (

  Id NUMBER,

  Name VARCHAR2(30),

  Value CLOB

)

    如果您需要執行簡單的任務，如獲取記錄集，在大多數情況下，你不需要知道oraclelob。當在fetch 上調用OracleDataReader.GetValue時，將會得到一組字節用于BLOB列以及用于CLOB one的字符串。此外你可以使用OracleDataReader.GetChars、 OracleDataReader.GetBytes得到LOB值塊。示例如下：

[C#]

...
OracleCommand oraCommand = new OracleCommand("SELECT * FROM ClobTable", oraConnection);
OracleDataReader reader = oraCommand.ExecuteReader();
while (reader.Read())
  Console.WriteLine(reader.GetValue(2));
...

[Visual Basic]

...
Dim oraCommand As OracleCommand = new OracleCommand( _
    "SELECT * FROM ClobTable", oraConnection)
Dim reader As OracleDataReader = oraCommand.ExecuteReader()
while (reader.Read())
  Console.WriteLine(reader.GetValue(2))
end while
...

    使用OracleLob對象也可以達到一樣的效果：

[C#]

...
OracleCommand oraCommand = new OracleCommand("SELECT * FROM ClobTable", oraConnection);
OracleDataReader reader = oraCommand.ExecuteReader();
OracleLob clob;
while (reader.Read()) {
  clob = reader.GetOracleLob(2);
  Console.WriteLine(clob.Value);
}
...

[Visual Basic]

...
Dim oraCommand As OracleCommand = new OracleCommand( _
    "SELECT * FROM ClobTable", oraConnection)
Dim reader As OracleDataReader = oraCommand.ExecuteReader()
Dim clob As OracleLob
while (reader.Read())
  clob = reader.GetOracleLob(2)
  Console.WriteLine(clob.Value)
end while
...