如何在Insert工作时更新BLOB列，错误ORA-00932

Question

using Oracle.DataAccess.Client;//needs reference to Oracle.DataAccess.dll
using Oracle.DataAccess.Types; //OracleBlob


public static bool StoreBlobImage(OracleConnection conn, string ArtNr, byte[] bImageJpg)
{
    bool Ok = false;
#if true // this is what I need, but does not work
    string Sql = "update MyTable set Image = :Image where ArtNr = :ArtNr";
#else // this works
    string Sql = "insert into MyTable (ArtNr, Image) values (:ArtNr, :Image)";
#endif
    using (OracleCommand cmd = new OracleCommand(Sql, conn))
    {
        //cmd.Connection = conn;
        //cmd.CommandType = CommandType.Text;
        cmd.Parameters.Add("ArtNr", OracleDbType.Varchar2, 8).Value = ArtNr;
#if false // tried method 1
        cmd.Parameters.Add("Image", OracleDbType.Blob).Value = bImageJpg;
#else // now trying method 2
        OracleParameter blobParameter = new OracleParameter();
        blobParameter.OracleDbType = OracleDbType.Blob;
        blobParameter.ParameterName = "Image";
        blobParameter.Value = bImageJpg;
        blobParameter.Direction = ParameterDirection.Input;
        blobParameter.IsNullable = true;
        cmd.Parameters.Add(blobParameter);
#endif
        try
        {
            conn.Open();
            cmd.ExecuteNonQuery();  // ORA-00932: inconsistent datatypes: expected - got BLOB
        }
        catch (Exception TheException)
        {
        }// debug breakpoint
    }
    return Ok;
}

Answer 1

当我读到你的帖子时，我真的觉得你在胡思乱想。出于好奇，我试了一下，惊讶于这个错误真的发生了。

有个好消息。我到处找了找，发现了这个：

如何使用>>准备好的查询<<使用ODP（oracle.dataaccess）更新CLOB字段中的数据？

public static bool StoreBlobImage(OracleConnection conn, string ArtNr, byte[] bImageJpg)
{
    bool Ok = false;
    string Sql = "update MyTable set Image = :Image where ArtNr = :ArtNr";

    using (OracleCommand cmd = new OracleCommand(Sql, conn))
    {
        cmd.Parameters.Add("Image", OracleDbType.Blob).Value = bImageJpg;
        cmd.Parameters.Add("ArtNr", OracleDbType.Varchar2, 8).Value = ArtNr;

        try
        {
            cmd.ExecuteNonQuery();
        }
        catch (Exception TheException)
        {
        }
    }
    return Ok;
}

--编辑--

根据OP的建议，有另一个修复，根据上面提到的同一线程，可以防止重新排列参数的必要性。我猜想，如果您正在更新多个LOB，这可能也会派上用场。

切换bindbyname属性似乎也可以解决此问题：

cmd.BindByName = true;