LOB基本概念：

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

1. BLOB數(shù)據(jù)類型存儲(chǔ)非結(jié)構(gòu)化二進(jìn)制大對(duì)象，BLOB對(duì)象可以當(dāng)做是沒有字符集語義的bitstreams。
2. CLOB數(shù)據(jù)類型存儲(chǔ)單字節(jié)和多字節(jié)數(shù)據(jù)，這兩個(gè)的固定的寬度和和不同的寬度都支持設(shè)置，兩者都可以使用數(shù)據(jù)庫(kù)字符集。
3. NCLOB數(shù)據(jù)類型存儲(chǔ)Unicode數(shù)據(jù)。

BLOB 數(shù)據(jù)的使用

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

    下一個(gè)示例程序顯示如何從硬盤上傳文件到服務(wù)器以及下載下來。要執(zhí)行這些程序，你必須創(chuàng)建一個(gè)表，如下：

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 數(shù)據(jù)使用

    CLOB 數(shù)據(jù)的使用和BLOB數(shù)據(jù)是一樣的，區(qū)別就在于Value屬性的表現(xiàn)。當(dāng)從OracleLob.Value讀取時(shí)，CLOB和NCLO數(shù)據(jù)類型，會(huì)得到字符串。數(shù)據(jù)是透明的解碼，所以你不必?fù)?dān)心字符集。如果需要原始數(shù)據(jù)，可以使用 OracleLob的里流媒體功能，那是OracleLob.Read 和OracleLob.Write的方法。請(qǐng)注意，由于OracleLob.Value是一個(gè)只讀屬性，你可以只用OracleLob.Write方法將數(shù)據(jù)寫入到的LOB中，但是并沒有執(zhí)行編碼。

For example consider this table definition.

CREATE TABLE ClobTable (

  Id NUMBER,

  Name VARCHAR2(30),

  Value CLOB

)

    如果您需要執(zhí)行簡(jiǎn)單的任務(wù)，如獲取記錄集，在大多數(shù)情況下，你不需要知道oraclelob。當(dāng)在fetch 上調(diào)用OracleDataReader.GetValue時(shí)，將會(huì)得到一組字節(jié)用于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對(duì)象也可以達(dá)到一樣的效果：

[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
...