你好,我在使用C#将字符串数据插入Oracle的blob时遇到了问题。我在“Toad”中的SQL编辑器中使用了这个语句,它可以正常地插入行,所以这就是代码来源。
INSERT INTO STORAGE ( STRG_ID, APPL_ID, TASK_ID, PARM_TYPE_ID, PARM_VAL_TXT,APPL_ITEM_TXT) VALUES (STRG_ID_SEQ.nextval, 2, 1, 1, 'cow',utl_raw.cast_to_raw('some string'))
表格内容如下:
strg_id 数字,
Appl_id 数字,
task_id 数字 ,
Parm_val_text varchar2(250),
Appl_item_txt blob
字符串数据将会是一个约42k的json文件,但目前我甚至无法使一个5字符的字符串正常工作。我已经尝试了我所能想到的所有方法,但我对Oracle还很陌生,主要使用Sql Server。我在网上找到了一些资料,下面是我找到的内容。我尝试过:
try
{
using (connection)
{
using (var cmd = connection.CreateCommand())
{
cmd.CommandType = CommandType.Text;
cmd.BindByName = true;
cmd.CommandText =
"INSERT INTO CDP.STORAGE (STRG_ID, APPL_ID, TASK_ID, PARM_TYPE_ID, PARM_VAL_TXT,APPL_ITEM_TXT) " +
" VALUES (:parmStorageId, :parmApplId, :parmTaskId, :parmTypeId, :ParmValTxt, :parmBlob)";
cmd.Parameters.Add("parmStorageId", "CDP.STRG_ID_SEQ.nextval");
cmd.Parameters.Add("parmApplId", appId);
cmd.Parameters.Add("parmTaskId", taskId);
cmd.Parameters.Add("parmTypeId", parmTypeId);
cmd.Parameters.Add("ParmValTxt", parmValue);
OracleParameter param = cmd.Parameters.Add("parmBlob", OracleDbType.Blob); //Add the parameter for the blobcolumn
string tim = "this is some very large string 42k characters";
// byte[] bytes = new byte[tim.ToCharArray().Length * sizeof(char)];
byte[] bytes = System.Text.Encoding.UTF8.GetBytes(tim);
System.Buffer.BlockCopy(tim.ToCharArray(), 0, bytes, 0, bytes.Length);
param.Value = bytes; //Asign the Byte Array to the parameter
cmd.ExecuteNonQuery();
}
connection.Close();
}
}
catch (Exception ex)
{
Log.Error(ex);
return false;
}
我遇到了以下错误:
Oracle.DataAccess.Client.OracleException ORA-01722: invalid number at Oracle.DataAccess.Client.OracleException.HandleErrorHelper(Int32 errCode, OracleConnection conn, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src, String procedure, Boolean bCheck, Int32 isRecoverable)
at Oracle.DataAccess.Client.OracleException.HandleError(Int32 errCode, OracleConnection conn, String procedure, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src, Boolean bCheck)
at Oracle.DataAccess.Client.OracleCommand.ExecuteNonQuery()
at SeasonsSvc.Models.SeasonsOracleRepository.BlobInsert(Int32 appId, Int32 taskId, Int32 parmTypeId, String parmValue, SeasonsReturnData myDataSet) in c:\Users\e048014\Documents\Visual Studio 2012\Projects\SeasonsSvc\SeasonsSvc\Models\SeasonsOracleRepository.cs:line 174
然后我尝试了以下方法:
public bool BlobInsert(int appId, int taskId, int parmTypeId, string parmValue, SeasonsReturnData myDataSet)
{
var connection = _dataUtility.GetOracleConnection("OracleConn");
StringBuilder sb = new StringBuilder();
var serializer = new JavaScriptSerializer();
serializer.Serialize(myDataSet,sb);
try
{
using (connection)
{
using (var cmd = connection.CreateCommand())
{
cmd.CommandType = CommandType.Text;
cmd.BindByName = true;
cmd.CommandText =
"INSERT INTO STORAGE (STRG_ID, APPL_ID, TASK_ID, PARM_TYPE_ID, PARM_VAL_TXT,APPL_ITEM_TXT) " +
" VALUES (STRG_ID_SEQ.nextval,2, 1, 1, 'cows', :parmBlob)";
cmd.Parameters.Add("parmBlob", "utl_raw.cast_to_raw('" + sb.ToString() + "')");
cmd.ExecuteNonQuery();
}
connection.Close();
}
}
catch (Exception ex)
{
Log.Error(ex);
return false;
}
return true;
}
这给我带来了以下错误:
{Oracle.DataAccess.Client.OracleException ORA-01461: can bind a LONG value only for insert into a LONG column at Oracle.DataAccess.Client.OracleException.HandleErrorHelper(Int32 errCode, OracleConnection conn, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src, String procedure, Boolean bCheck, Int32 isRecoverable)
at Oracle.DataAccess.Client.OracleException.HandleError(Int32 errCode, OracleConnection conn, String procedure, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src, Boolean bCheck)
at Oracle.DataAccess.Client.OracleCommand.ExecuteNonQuery()
at SeasonsSvc.Models.SeasonsOracleRepository.BlobInsert(Int32 appId, Int32 taskId, Int32 parmTypeId, String parmValue, SeasonsReturnData myDataSet) in c:\Users\e048014\Documents\Visual Studio 2012\Projects\SeasonsSvc\SeasonsSvc\Models\SeasonsOracleRepository.cs:line 118}
我遇到了困难,我可以在Toad上将utl_raw.cast_to_raw('some string')用于上面的插入语句,但如果我复制json到“some string”的位置,无论是在Toad还是Visual Studio中都会出现错误,指出字符串太长。 我猜这就是4000个字符限制的原因,这就是为什么我正在尝试绑定变量。
谢谢任何帮助、想法等。
serializer.Serialize(myDataSet,sb);
的时候,变量sb
的值是多少? - MethodManBLOB
中,而不是用于字符数据的CLOB
? - Justin CaveCDP.STRG_ID_SEQ.nextval
的调用放入SQL语句中,而不是尝试将CDP.STRG_ID_SEQ.nextval
作为参数值传递。同样,在第二个示例中,将对utl_raw.cast_to_raw
的调用放入SQL本身中,而不是尝试让参数以某种方式调用过程调用,我从未见过这样做。祝你好运。 - Bob Jarvis - Слава Україні