Chinaunix首页 | 论坛 | 博客
  • 博客访问: 103119291
  • 博文数量: 19283
  • 博客积分: 9968
  • 博客等级: 上将
  • 技术积分: 196062
  • 用 户 组: 普通用户
  • 注册时间: 2007-02-07 14:28
文章分类

全部博文(19283)

文章存档

2011年(1)

2009年(125)

2008年(19094)

2007年(63)

分类: Oracle

2008-05-23 21:10:28

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

 来源:culturenet的 Blog 作者:culturenet的 Blog  

">

 在调用存储过程时,产生如下错误:

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:inetpubwwwrootresourcedatalaydbresource.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:inetpubwwwrootresourcedatalaydbresource.cs:69
   ReSource.BusLay.BusResource.Insert() in c:inetpubwwwrootresourcebuslaybusresource.cs:119
   ReSource.WebLay.MYRESOURCEAdd.Insert(Object sender, EventArgs e) in c:inetpubwwwrootresourceweblaytjzymyresourceadd.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的情况。但是不知道为什么把处理字符串参数等于 "" 的时候得语句给注释掉了。于是乎,就更改源代码,如下:

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;
        }

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

后来,上网上看到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 行开始)

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的情况。但是不知道为什么把处理字符串参数等于 "" 的时候得语句给注释掉了。于是乎,就更改源代码,如下:

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;
        }

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

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


Oracle will treat '''''''' (empty string) as
NULL, and store as NULL anyway - better be prepared for it, and
treat it as such.

阅读(283) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~