Oracle LOB

更新 : November 2007

.NET Framework Data Provider for Oracle には、OracleLob クラスが含まれています。このクラスは、Oracle の LOB データ型を操作するために使用されます。

OracleLob は、これらの OracleType データ型の 1 つです。

OracleLob は、データがオペレーティング システムの物理ファイルにではなくサーバーに格納されるという点で、OracleBFile とは異なります。また、常に読み取り専用になっている OracleBFile とは異なり、読み書き可能なオブジェクトにもなります。

LOB の作成、取得、および書き込み

次の C# の例では、Oracle テーブルに LOB を作成し、それらを取得して OracleLob オブジェクトの形式で書き込む方法について説明します。この例では、OracleDataReader オブジェクトと OracleLob の Read および Write メソッドを使用する方法について説明します。Oracle の BLOB、CLOB、および NCLOB データ型を使用します。

[C#]

using System;
using System.IO;            
using System.Text;           
using System.Data;            
using System.Data.OracleClient;

// LobExample
public class LobExample
{
   public static int Main(string[] args)
   {
      //Create a connection.
      OracleConnection conn = new OracleConnection(
         "Data Source=Oracle8i;Integrated Security=yes");
      using(conn)
      {
         //Open a connection.
         conn.Open();
         OracleCommand cmd = conn.CreateCommand();

         //Create the table and schema.
         CreateTable(cmd);

         //Read example.
         ReadLobExample(cmd);

         //Write example
         WriteLobExample(cmd);
      }

      return 1;
   }

   // ReadLobExample
   public static void ReadLobExample(OracleCommand cmd)
   {
      int actual = 0;

      // Table Schema:
      // "CREATE TABLE tablewithlobs (a int, b BLOB, c CLOB, d NCLOB)";
      // "INSERT INTO tablewithlobs values (1, 'AA', 'AAA', N'AAAA')";
      // Select some data.
      cmd.CommandText = "SELECT * FROM tablewithlobs";
      OracleDataReader reader = cmd.ExecuteReader();
      using(reader)
      {
         //Obtain the first row of data.
         reader.Read();
         
         //Obtain the LOBs (all 3 varieties).
         OracleLob blob = reader.GetOracleLob(1);
         OracleLob clob = reader.GetOracleLob(2);
         OracleLob nclob = reader.GetOracleLob(3);

         //Example - Reading binary data (in chunks).
         byte[] buffer = new byte[100];
         while((actual = blob.Read(buffer, 0, buffer.Length)) >0)
            Console.WriteLine(blob.LobType + ".Read(" + buffer + ", " + 
              buffer.Length + ") => " + actual);

         // Example - Reading CLOB/NCLOB data (in chunks).
         // Note: You can read character data as raw Unicode bytes 
         // (using OracleLob.Read as in the above example).
         // However, because the OracleLob object inherits directly 
         // from the .Net stream object, 
         // all the existing classes that manipluate streams can 
         // also be used. For example, the 
         // .Net StreamReader makes it easier to convert the raw bytes 
         // into actual characters.
         StreamReader streamreader = 
           new StreamReader(clob, Encoding.Unicode);
         char[] cbuffer = new char[100];
         while((actual = streamreader.Read(cbuffer, 
           0, cbuffer.Length)) >0)
            Console.WriteLine(clob.LobType + ".Read(
              " + new string(cbuffer, 0, actual) + ", " + 
              cbuffer.Length + ") => " + actual);

         // Example - Reading data (all at once).
         // You could use StreamReader.ReadToEnd to obtain 
         // all the string data, or simply
         // call OracleLob.Value to obtain a contiguous allocation 
         // of all the data.
         Console.WriteLine(nclob.LobType + ".Value => " + nclob.Value);
      }
   }

   // WriteLobExample
   public static void WriteLobExample(OracleCommand cmd)
   {
      //Note: Updating LOB data requires a transaction.
      cmd.Transaction = cmd.Connection.BeginTransaction();
      
      // Select some data.
      // Table Schema:
      // "CREATE TABLE tablewithlobs (a int, b BLOB, c CLOB, d NCLOB)";
      // "INSERT INTO tablewithlobs values (1, 'AA', 'AAA', N'AAAA')";
      cmd.CommandText = "SELECT * FROM tablewithlobs FOR UPDATE";
      OracleDataReader reader = cmd.ExecuteReader();
      using(reader)
      {
         // Obtain the first row of data.
         reader.Read();

         // Obtain a LOB.
         OracleLob blob = reader.GetOracleLob(1/*0:based ordinal*/);

         // Perform any desired operations on the LOB 
         // (read, position, and so on).

         // Example - Writing binary data (directly to the backend).
         // To write, you can use any of the stream classes, or write
         // raw binary data using 
         // the OracleLob write method. Writing character vs. binary 
         // is the same;
         // however note that character is always in terms of 
         // Unicode byte counts
         // (for example, even number of bytes - 2 bytes for every
         // Unicode character).
         byte[] buffer = new byte[100];
         buffer[0] = 0xCC;
         buffer[1] = 0xDD;
         blob.Write(buffer, 0, 2);
         blob.Position = 0;
         Console.WriteLine(blob.LobType + ".Write(
           " + buffer + ", 0, 2) => " + blob.Value);
         
         // Example - Obtaining a temp LOB and copying data 
         // into it from another LOB.
         OracleLob templob = CreateTempLob(cmd, blob.LobType);
         long actual = blob.CopyTo(templob);
         Console.WriteLine(blob.LobType + ".CopyTo(
            " + templob.Value + ") => " + actual);

         // Commit the transaction now that everything succeeded.
         // Note: On error, Transaction.Dispose is called 
         // (from the using statement)
         // and will automatically roll back the pending transaction.
         cmd.Transaction.Commit();
      }
   }

   // CreateTempLob
   public static OracleLob CreateTempLob(
     OracleCommand cmd, OracleType lobtype)
   {
      //Oracle server syntax to obtain a temporary LOB.
      cmd.CommandText = "DECLARE A " + lobtype + "; "+
                     "BEGIN "+
                        "DBMS_LOB.CREATETEMPORARY(A, FALSE); "+
                        ":LOC := A; "+
                     "END;";
      
      //Bind the LOB as an output parameter.
      OracleParameter p = cmd.Parameters.Add("LOC", lobtype);
      p.Direction = ParameterDirection.Output;

      //Execute (to receive the output temporary LOB).
      cmd.ExecuteNonQuery();

      //Return the temporary LOB.
      return (OracleLob)p.Value;
   }

   // CreateTable
   public static void CreateTable(OracleCommand cmd)
   {
      // Table Schema:
      // "CREATE TABLE tablewithlobs (a int, b BLOB, c CLOB, d NCLOB)";
      // "INSERT INTO tablewithlobs VALUES (1, 'AA', 'AAA', N'AAAA')";
      try
      {
         cmd.CommandText   = "DROP TABLE tablewithlobs";
         cmd.ExecuteNonQuery();
      }
      catch(Exception)
      {
      }
      
      cmd.CommandText = 
        "CREATE TABLE tablewithlobs (a int, b BLOB, c CLOB, d NCLOB)";
      cmd.ExecuteNonQuery();
      cmd.CommandText = 
        "INSERT INTO tablewithlobs VALUES (1, 'AA', 'AAA', N'AAAA')";
      cmd.ExecuteNonQuery();
   }
}

一時 LOB の作成

次の C# の例では、一時 LOB の作成方法について説明します。

[C#]

OracleConnection conn = new OracleConnection(
  "server=test8172; integrated security=yes;");
conn.Open();

OracleTransaction tx = conn.BeginTransaction();

OracleCommand cmd = conn.CreateCommand();
cmd.Transaction = tx;
cmd.CommandText = 
  "declare xx blob; begin dbms_lob.createtemporary(
  xx, false, 0); :tempblob := xx; end;";
cmd.Parameters.Add(new OracleParameter("tempblob",
  OracleType.Blob)).Direction = ParameterDirection.Output;
cmd.ExecuteNonQuery();
OracleLob tempLob = (OracleLob)cmd.Parameters[0].Value;
tempLob.BeginBatch(OracleLobOpenMode.ReadWrite);
tempLob.Write(tempbuff,0,tempbuff.Length);
tempLob.EndBatch();
cmd.Parameters.Clear();
cmd.CommandText = "myTable.myProc";
cmd.CommandType = CommandType.StoredProcedure;  
cmd.Parameters.Add(new OracleParameter(
  "ImportDoc", OracleType.Blob)).Value = tempLob;
cmd.ExecuteNonQuery();

tx.Commit();

参照

その他の技術情報

Oracle および ADO.NET