OracleClient, Blob, 32K, ORA-01460

使用OracleClient处理clob/blob时可以有三种方法:
1.  OracleDataAdapter

public void writeDataWithDA()
{

           FileInfo fi = new FileInfo("c:/temp/testfile.txt");
           StreamReader sr = new StreamReader(fi.FullName);
           String clob = sr.ReadToEnd();
           sr.Close();
 
           OracleDataAdapter da = new OracleDataAdapter("SELECT ID, TEXT FROM CLOBTEST",ConnectionString);

           DataTable dt = new DataTable();

           // get the schema
           da.FillSchema(dt, SchemaType.Source);

           OracleCommandBuilder cb = new OracleCommandBuilder(da);

           int id = 2;

           // create a row containing the data
           DataRow row = dt.NewRow();
           row["ID"] = id;
           row["TEXT"] = clob;

           dt.Rows.Add(row);
 
           // update the table
           da.Update(dt);
}

2. OracleCommand

public void writeDataWithCommand()
{
           FileInfo fi = new FileInfo("c:/temp/testfile.txt");
           StreamReader sr = new StreamReader(fi.FullName);
           String tempBuff = sr.ReadToEnd();
           sr.Close();

           using(OracleConnection conn = new OracleConnection(ConnectionString))
           {
                      conn.Open();
                      Console.WriteLine("Connected") ;
                      String strSQL = "INSERT INTO CLOBTEST (ID,TEXT) VALUES (1,:TEXT_DATA) ";

                      OracleParameter parmData = new OracleParameter();
                      parmData.Direction = ParameterDirection.Input;
                      parmData.OracleType = OracleType.Clob;
                      parmData.ParameterName = "TEXT_DATA";
                      parmData.Value = tempBuff;
 
                      OracleCommand cm = new OracleCommand();
                      cm.Connection = conn;
                      cm.Parameters.Add(parmData);
                      cm.CommandText = strSQL;
                      cm.ExecuteNonQuery(); 

                      conn.Close();
           } 

           Console.WriteLine("Done!") ;
}

3. 使用 temporery LOB

public void writeWithTempBlob()
{
           FileInfo fi = new FileInfo("c:/temp/testfile.txt");
           StreamReader sr = new StreamReader(fi.FullName);
           String tempBuff = sr.ReadToEnd();
           sr.Close();
 
           using(OracleConnection conn = new OracleConnection(ConnectionString))
           {
                      conn.Open();
                      Console.WriteLine("Connected") ;
                      OracleTransaction tx = conn.BeginTransaction();

                      OracleCommand tempcmd = conn.CreateCommand();
                      tempcmd.Transaction = tx;
                      tempcmd.CommandText = "declare xx clob; begin dbms_lob.createtemporary(xx, false, 0); :tempclob := xx; end;";

                      tempcmd.Parameters.Add(new OracleParameter("tempclob",
                                 OracleType.Clob)).Direction = ParameterDirection.Output;
                      tempcmd.ExecuteNonQuery();

                      //get the temp lob object
                      OracleLob tempLob = (OracleLob)tempcmd.Parameters[0].Value;

                      //transform into byte array
                      System.Text.Encoding enc = Encoding.Unicode;          //MUST be unicode encoded!
                      Byte[] b = enc.GetBytes(tempBuff);

                      tempLob.BeginBatch(OracleLobOpenMode.ReadWrite);
                      tempLob.Write(b,0,b.Length);
                      tempLob.EndBatch();

                      OracleCommand cmd = conn.CreateCommand();
                      cmd.Transaction = tx;
                      cmd.CommandText = "INSERT INTO CLOBTEST (ID, TEXT) VALUES (:ID, :TEXT)";
                      cmd.Parameters.Add("ID", 3);
                      cmd.Parameters.Add("TEXT", OracleType.Clob).Value = tempLob;           //insert the temp lob
                      cmd.ExecuteNonQuery();

                      tx.Commit(); 

           }

           Console.WriteLine("Done!") ;
}

当使用1,2 处理的blob大于32K时会拋出
ORA-01460: unimplemented or unreasonable conversion
requested

google到的解決法子
http://p2p.wrox.com/topic.asp?TOPIC_ID=7743

OracleLob 相關
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdataoracleclientoraclelobclasswritetopic.asphttp://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdataoracleclientoraclelobclasswritetopic.asp