Using C# to insert data into an Oracle BLOB Field

[Chris Fink]

The following code is returning an Oracle excpetion {"ORA-12704: character
set mismatch" } System.Exception.

The runtime SQL is as follows:

INSERT INTO INBOUNDWEBSERVICES (STRUCTUREID, STATUS, LASTUPDATEDBY, MSGIN)
VALUES 1,:2,:3,:4)
In narrowing down the SQL, I have determined that the field that is not
working is the BLOB, placeholder :4

Following is my method. I am using ODP.NET dataprovider to communicate with
Oracle 8i system. I have hardcoded some of the params to point out that the
problem is the BLOB.

I appreciate any assistance in this, I have exhausted all my options.

using System;
using System.Text;
using System.IO;
using System.Data;
using Oracle.DataAccess;
using Oracle.DataAccess.Client;
using Oracle.DataAccess.Types;


// test binary data insert
string img = "c:/temp/test.jpg";
FileStream fs = new FileStream(img, FileMode.Open, FileAccess.Read);
byte[] imageData = new byte[fs.Length];
fs.Read(imageData,0,System.Convert.ToInt32(fs.Length));
fs.Close();
// // TODO: convert msgIn to byte array to insert into Oracle BLOB field
// string asciiString = "Test String";
// // Create two different encodings.
// Encoding ascii = Encoding.ASCII;
// Encoding unicode = Encoding.Unicode;
// // Convert the string into a byte[].
// byte[] asciiBytes = ascii.GetBytes(asciiString);
// byte[] baMsgIn = asciiBytes;
OracleConnection cn = new OracleConnection(DATASOURCE8I);

string sql = "INSERT INTO INBOUNDWEBSERVICES (STRUCTUREID, STATUS,
LASTUPDATEDBY, MSGIN) VALUES 1,:2,:3,:4)";
OracleCommand cmd = new OracleCommand();
cmd.CommandText = sql;
cmd.Connection = cn;
cmd.CommandType = CommandType.Text;
OracleParameter p1 = cmd.Parameters.Add("sid",OracleDbType.NVarchar2,50);
p1.Direction = ParameterDirection.Input;
p1.Value = "structureID";
OracleParameter p2 = cmd.Parameters.Add("STATUS",OracleDbType.NVarchar2,50);
p2.Direction = ParameterDirection.Input;
p2.Value = "status";
OracleParameter p3 =
cmd.Parameters.Add("lastUpdatedBy",OracleDbType.NVarchar2,50);
p3.Direction = ParameterDirection.Input;
p3.Value = "lastUpdatedBy";
OracleParameter p4 =
cmd.Parameters.Add("blob",OracleDbType.Blob,imageData.Length);
p4.Direction = ParameterDirection.Input;
p4.Value = imageData; // binary
//p4.Value = "4";
try
{
cn.Open();
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
throw new Exception(ex.Message.ToString());
}
cn.Close();