Oggetti LOB OracleOracle LOBs

Il Provider di dati .NET Framework per Oracle include la OracleLob (classe), che viene utilizzato per operare con Oracle LOB tipi di dati.The .NET Framework Data Provider for Oracle includes the OracleLob class, which is used to work with Oracle LOB data types.

Un OracleLob può essere uno di questi OracleType tipi di dati:An OracleLob may be one of these OracleType data types:

Tipo di datiData type DescrizioneDescription
BLOBBlob Oracle BLOB tipo di dati che contiene dati binari con dimensioni massime di 4 GB.An Oracle BLOB data type that contains binary data with a maximum size of 4 gigabytes. Esegue il mapping a un matrice di tipo Byte.This maps to an Array of type Byte.
Oggetto CLOBClob Oracle CLOB tipo di dati che contiene i dati di tipo carattere, in base al carattere predefinito impostato nel server, con dimensioni massime di 4 GB.An Oracle CLOB data type that contains character data, based on the default character set on the server, with a maximum size of 4 gigabytes. Esegue il mapping a stringa.This maps to String.
NClobNClob Oracle NCLOB tipo di dati che contiene i dati di tipo carattere, basato sul set nel server con una dimensione massima di 4 GB di caratteri nazionali.An Oracle NCLOB data type that contains character data, based on the national character set on the server with a maximum size of 4 gigabytes. Esegue il mapping a stringa.This maps to String.

Un OracleLob differisce da un OracleBFile in quanto i dati vengono archiviati nel server anziché in un file fisico nel sistema operativo.An OracleLob differs from an OracleBFile in that the data is stored on the server instead of in a physical file in the operating system. Può anche essere un oggetto di lettura / scrittura, a differenza di un OracleBFile, che è sempre di sola lettura.It can also be a read-write object, unlike an OracleBFile, which is always read-only.

Creazione, recupero e scrittura di un LOBCreating, Retrieving, and Writing to a LOB

Nell'esempio c# seguente viene illustrato come creare LOB in una tabella Oracle e quindi recuperare e scriverli sotto forma di OracleLob oggetti.The following C# example demonstrates how you can create LOBs in an Oracle table, and then retrieve and write to them in the form of OracleLob objects. Viene illustrato l'utilizzo di OracleDataReader oggetto e OracleLob lettura e scrivere metodi.The example demonstrates using the OracleDataReader object and the OracleLob Read and Write methods. Nell'esempio viene utilizzata Oracle BLOB, CLOB, e NCLOB tipi di dati.The example uses Oracle BLOB, CLOB, and NCLOB data types.

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();  
   }  
}  

Creazione di un LOB temporaneoCreating a Temporary LOB

Nell'esempio C# seguente viene illustrato come creare un LOB temporaneo.The following C# example demonstrates how to create a temporary LOB.

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();  

Vedere ancheSee Also

Oracle e ADO.NETOracle and ADO.NET
Provider gestiti ADO.NET e Centro per sviluppatori di set di datiADO.NET Managed Providers and DataSet Developer Center