Enterprise Library- Data Block使用oracle存储过程，字符串参数传入值为""时出现问题的解决

王朝c#·作者佚名 2006-12-17

Enterprise Library- Data Block使用oracle存储过程，字符串参数传入值为""时出现问题的解决在调用存储过程时，产生如下错误：

Parameter 'p_R_URL': No size set for variable length data type: String. 说明: 执行当前 Web 请求期间，出现未处理的异常。请检查堆栈跟踪信息，以了解有关该错误以及代码中导致错误的出处的详细信息。

异常详细信息: System.Exception: Parameter 'p_R_URL': No size set for variable length data type: String.

源错误:

行 67： catch(Exception ex)行 68： {行 69： throw new Exception(ex.Message);行 70： //return false;行 71： }

源文件: c:\inetpub\wwwroot\resource\datalay\dbresource.cs 行: 69

堆栈跟踪:

[Exception: Parameter 'p_R_URL': No size set for variable length data type: String.] ReSource.DataLay.DbResource.Insert(String R_TITLE, String R_USE, String R_XKML, String R_GRLB, String R_WJLB, DateTime R_ADDTIME, String R_URL, String R_PRIVATE, String R_COMMONT) in c:\inetpub\wwwroot\resource\datalay\dbresource.cs:69 ReSource.BusLay.BusResource.Insert() in c:\inetpub\wwwroot\resource\buslay\busresource.cs:119 ReSource.WebLay.MYRESOURCEAdd.Insert(Object sender, EventArgs e) in c:\inetpub\wwwroot\resource\weblay\tjzy\myresourceadd.aspx.cs:80 System.EventHandler.Invoke(Object sender, EventArgs e) +0 System.Web.UI.WebControls.LinkButton.OnClick(EventArgs e) +108 System.Web.UI.WebControls.LinkButton.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +57 System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +18 System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +138 System.Web.UI.Page.ProcessRequestMain() +1292

版本信息: Microsoft .NET Framework 版本:1.1.4322.2300; ASP.NET 版本:1.1.4322.2300

很是郁闷，于是就打开Enterprise Library的源代码进行调试。发现问题原来出现在 OracleCommandWrapper.cs文件中的如下函数：（从345 行开始）

private OracleParameter CreateParameter(string name, DbType dbType, int size, ParameterDirection direction, bool nullable, byte precision, byte scale, string sourceColumn, DataRowVersion sourceVersion, object value)

{

OracleParameter param = this.command.CreateParameter();

param.ParameterName = name;

param.DbType = dbType;

param.Size = size;

param.Value = (value == null) ? DBNull.Value : value;

// modify parameter type and value for special cases

switch (dbType)

{

// for Guid, change to value to byte array

case DbType.Guid:

guidParameters.Add(param.ParameterName, 'System.Guid');

param.OracleType = OracleType.Raw;

param.Size = 16;

// convert Guid value to byte array only if not null

if ((value is DBNull) || (value == null))

{

param.Value = Convert.DBNull;

}

else

{

param.Value = ((Guid)value).ToByteArray();

}

break;

// case DbType.AnsiString:

// case DbType.AnsiStringFixedLength:

// case DbType.String:

// case DbType.StringFixedLength:

// // for empty string, set it to DBNull

// if ((value == null) || (!(value is DBNull)) && ((string)value).Length == 0)

// {

// param.Value = Convert.DBNull;

// }

// break;

default:

break;

}

param.Direction = direction;

param.IsNullable = nullable;

param.Precision = precision;

param.Scale = scale;

param.SourceColumn = sourceColumn;

param.SourceVersion = sourceVersion;

return param;

}

可以看到，代码中处理了传入值为 NULL的情况。但是不知道为什么把处理字符串参数等于 '' 的时候得语句给注释掉了。于是乎，就更改源代码，如下：

{

OracleParameter param = this.command.CreateParameter();

param.ParameterName = name;

param.DbType = dbType;

param.Size = size;

param.Value = (value == null) ? DBNull.Value : value;

// modify parameter type and value for special cases

switch (dbType)

{

// for Guid, change to value to byte array

case DbType.Guid:

guidParameters.Add(param.ParameterName, 'System.Guid');

param.OracleType = OracleType.Raw;

param.Size = 16;

// convert Guid value to byte array only if not null

if ((value is DBNull) || (value == null))

{

param.Value = Convert.DBNull;

}

else

{

param.Value = ((Guid)value).ToByteArray();

}

break;

case DbType.AnsiString:

case DbType.AnsiStringFixedLength:

case DbType.String:

case DbType.StringFixedLength:

// for empty string, set it to DBNull

if ((value == null) || (!(value is DBNull)) && ((string)value).Length == 0)

{

param.Value = Convert.DBNull;

}

break;

default:

break;

}

param.Direction = direction;

param.IsNullable = nullable;

param.Precision = precision;

param.Scale = scale;

param.SourceColumn = sourceColumn;

param.SourceVersion = sourceVersion;

return param;

}

问题的到了解决。但是不知道微软的代码为什么把这个给注释掉了。

后来，上网上看到Oracle 的一个论坛上的帖子上有如下的描述：

Oracle will treat '' (empty string) as

NULL, and store as NULL anyway - better be prepared for it, and

treat it as such.

版本信息: Microsoft .NET Framework 版本:1.1.4322.2300; ASP.NET 版本:1.1.4322.2300

很是郁闷，于是就打开Enterprise Library的源代码进行调试。发现问题原来出现在 OracleCommandWrapper.cs文件中的如下函数：（从345 行开始）

{

OracleParameter param = this.command.CreateParameter();

param.ParameterName = name;

param.DbType = dbType;

param.Size = size;

param.Value = (value == null) ? DBNull.Value : value;

// modify parameter type and value for special cases

switch (dbType)

{

// for Guid, change to value to byte array

case DbType.Guid:

guidParameters.Add(param.ParameterName, 'System.Guid');

param.OracleType = OracleType.Raw;

param.Size = 16;

// convert Guid value to byte array only if not null

if ((value is DBNull) || (value == null))

{

param.Value = Convert.DBNull;

}

else

{

param.Value = ((Guid)value).ToByteArray();

}

break;

// case DbType.AnsiString:

// case DbType.AnsiStringFixedLength:

// case DbType.String:

// case DbType.StringFixedLength:

// // for empty string, set it to DBNull

// if ((value == null) || (!(value is DBNull)) && ((string)value).Length == 0)

// {

// param.Value = Convert.DBNull;

// }

// break;

default:

break;

}

param.Direction = direction;

param.IsNullable = nullable;

param.Precision = precision;

param.Scale = scale;

param.SourceColumn = sourceColumn;

param.SourceVersion = sourceVersion;

return param;

}

可以看到，代码中处理了传入值为 NULL的情况。但是不知道为什么把处理字符串参数等于 '' 的时候得语句给注释掉了。于是乎，就更改源代码，如下：

{

OracleParameter param = this.command.CreateParameter();

param.ParameterName = name;

param.DbType = dbType;

param.Size = size;

param.Value = (value == null) ? DBNull.Value : value;

// modify parameter type and value for special cases

switch (dbType)

{

// for Guid, change to value to byte array

case DbType.Guid:

guidParameters.Add(param.ParameterName, 'System.Guid');

param.OracleType = OracleType.Raw;

param.Size = 16;

// convert Guid value to byte array only if not null

if ((value is DBNull) || (value == null))

{

param.Value = Convert.DBNull;

}

else

{

param.Value = ((Guid)value).ToByteArray();

}

break;

case DbType.AnsiString:

case DbType.AnsiStringFixedLength:

case DbType.String:

case DbType.StringFixedLength:

// for empty string, set it to DBNull

if ((value == null) || (!(value is DBNull)) && ((string)value).Length == 0)

{

param.Value = Convert.DBNull;

}

break;

default:

break;

}

param.Direction = direction;

param.IsNullable = nullable;

param.Precision = precision;

param.Scale = scale;

param.SourceColumn = sourceColumn;

param.SourceVersion = sourceVersion;

return param;

}

问题的到了解决。但是不知道微软的代码为什么把这个给注释掉了。

后来，上网上看到Oracle 的一个论坛上的帖子上有如下的描述：

Oracle will treat '' (empty string) as

NULL, and store as NULL anyway - better be prepared for it, and

treat it as such.

Enterprise Library- Data Block使用oracle存储过程，字符串参数传入值为&quot;&quot;时出现问题的解决

Enterprise Library- Data Block使用oracle存储过程，字符串参数传入值为""时出现问题的解决