System.Data.OracleClient调用带blob等大字段类型参数的存储过程

    System.Data.OracleClient在插入大字段类型的时候有32K大小限制，据网络收集的一些方法，整理了一下如下(微软企业库示例):

必须在获取临时 LOB 之前开始事务。否则，OracleDataReader 将不能获取后面的数据。

还可以通过调用 DBMS_LOB.CREATETEMPORARY 系统存储过程并绑定 LOB 输出参数打开 Oracle 中的临时 LOB。在客户端，临时 LOB 的行为很像基于表的 LOB。例如，要更新临时 LOB，它必须包含在事务中。

 

OracleConnection conn = Db.CreateConnection() as OracleConnection;
            conn.Open();
            OracleTransaction trans = conn.BeginTransaction() as OracleTransaction;
            OracleCommand cmd = Db.DbProviderFactory.CreateCommand() as OracleCommand;

            try
            {
                cmd.Transaction = trans;
                cmd.Connection = conn;
                cmd.CommandText = "GetTempBlob";
                //存储过程：GetTempBlob
                //"declare dpBlob blob; begin dbms_lob.createtemporary(dpBlob, false, 0); :tempblob := dpBlob; end;";
                //
                cmd.Parameters.Add(new OracleParameter("tmpBlob", OracleType.Blob)).Direction = ParameterDirection.Output;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.ExecuteNonQuery();
                OracleLob tmpBlob = (OracleLob)cmd.Parameters[0].Value;
                tmpBlob.BeginBatch(OracleLobOpenMode.ReadWrite);
                tmpBlob.Write(bytNR, 0, bytNR.Length);
                tmpBlob.EndBatch();

                //执行插入存储过程
                cmd.Parameters.Clear();
                cmd.CommandType = CommandType.StoredProcedure;
                //cmd.Transaction = trans;
                cmd.CommandText = "pkg_ManoeuvreScheme.Pro_SaveManoeuvreScheme";
                Db.AddInParameter(cmd, "v_guid", DbType.String, "1");
                Db.AddInParameter(cmd, "v_yxfamc", DbType.String, strYXFAMC);
                Db.AddInParameter(cmd, "v_dy", DbType.String, strDY);
                Db.AddInParameter(cmd, "v_bxsj", DbType.DateTime, dtBXSJ);
                Db.AddInParameter(cmd, "v_yxfanr", DbType.String, strYXFANR);
                Db.AddInParameter(cmd, "v_pj", DbType.String, strPJ);
                Db.AddInParameter(cmd, "v_bz", DbType.String, strBZ);
                Db.AddInParameter(cmd, "v_wdmc", DbType.String, strWDMC);
                Db.AddParameter(cmd, "v_nr", OracleType.Blob, bytNR.Length,
                    ParameterDirection.Input, true, 0, 0, "", DataRowVersion.Current, tmpBlob);
                int ret = cmd.ExecuteNonQuery();

                trans.Commit();

                return ret;

            }
            catch (Exception ex)
            {
                trans.Rollback();
                Logger.Error(ex);
                throw ex;
            }
            finally
            {
                conn.Close();
            } 

转载于:https://www.cnblogs.com/lae/archive/2009/11/25/1610681.html