C#执行Oracle存储过程 报Oracle-06550错误解决方法

用代码说明解决方法.

在Oracle有一个存储过程,在用Oracle工具(Quest Toad)调试执行时没有任何问题,当在C#中调用此过程报Oracle-06550错误.

一般的此存储过程具有调用参数.

具体代码:

Oracle后台的存储过程代码:

Create Or Replace Procedure Lungu.Pro_Alert_Lunguid(In_LunguID  varchar2)
Is
---Local Var定义区  
 Var_Sequence   Number;
 N                       Number;
 N1                     Number;
 N2                     Number;
 NN                     number;

/******************************************************************************/

Begin

    If(In_Lunguid <>' ') Then
   
        Select Count(*) Into N From Robot_Alart_R2_Lunguid  Where Lunguid=In_Lunguid; 
           
        If(N>=1) Then
           
            Update Robot_Alart_R2_Lunguid  Set Alert_Flag = '1' Where Lunguid=In_Lunguid;
       
        End If;
       
        select count(*) into NN From R2_Plan
                     Where  State_Of_Order<>'4' ;
                
        if(NN>=1) then    
        
            Var_Sequence := To_Number(Substr(In_Lunguid,5));
          
            For Cc In
                    (
                Select Plan_Order_Id,Prodord_Id,Acual_Output,State_Of_Order From R2_Plan
                         Where  State_Of_Order<>'4' Order By Start_Date Desc
                )
            Loop
              
                N1:=To_Number(Substr(Cc.Prodord_Id,5,5));
                   
                N2:=N1+Cc.Acual_Output;
                   
                If(Var_Sequence>=N1) And (Var_Sequence<=N2) Then
                   
                    Select Count(*) Into N From R2_Plan  Where   Prodord_Id=Cc.Prodord_Id;
                                         
                    If(N>=1) Then
                                                   
                        If(Cc.State_Of_Order='2') Then
                                  
                        Update R2_Plan Set State_Of_Order = '4'
                               Where Prodord_Id<Cc.Prodord_Id
                               And State_Of_Order='3'; 
                                                      
                        Update R2_Plan Set State_Of_Order = '3'
                               Where Prodord_Id=Cc.Prodord_Id;
                       
                              
                        End If;
                       
                    End If;
                                       
                  End If;
                       
                   
            End Loop ; 
   
       end if;
  
   End If;
  
   Exception
     When Others Then
       -- Consider Logging The Error And Then Re-Raise
       Raise;
      
End Pro_Alert_Lunguid;

此过程在Oracle中执行没有任何问题.

在C#中的调用代码:                   

            using (OracleConnection tmpOraConn = new OracleConnection(this.oraConn.ConnectionString))
                    {
                        tmpOraConn.Open();
                        OracleCommand tmpCmd = tmpOraConn.CreateCommand();
                        tmpCmd.CommandText = "Pro_ALERT_LUNGUID";
                        tmpCmd.CommandType = CommandType.StoredProcedure;
                        tmpCmd.Parameters.Add("LunguID", OracleType.NVarChar);
                        tmpCmd.Parameters["LunguID"].Direction = ParameterDirection.Input;
                        tmpCmd.Parameters["LunguID"].Value = this.initialLunguID;
                        try
                        {
                            tmpCmd.ExecuteNonQuery();
                        }
                        catch (OracleException oe)
                        {
                            string errorMsg = oe.Message;
                        }
                        finally
                        {
                            tmpOraConn.Close();
                            tmpCmd = null;
                        }

 

执行此段代码就会报Oracle-06650错误.

解决办法:

注意到Oracle存储过程Pro_Alert_Lunguid(In_lunguId in varchar2)中的参数名为in_lunguID.

而在C#中                        tmpCmd.Parameters.Add("LunguID", OracleType.NVarChar);中添加的参数名为LunguID.问题就出在这儿.  要保持C#中添加的参数名和Oracle中后台存储过程名一致(也就是两个的参数名一样)

只要将上面的C#中的代码 tmpCmd.Parameters.Add("LunguID", OracleType.NVarChar);改为

 tmpCmd.Parameters.Add("In_LunguID", OracleType.NVarChar);即可.当然也可以将Oracle的存储过程的参数从

in_Lunguid改为--->lunguID.