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

最新推荐文章于 2023-12-22 14:28:18 发布

Sunlihgt_love

最新推荐文章于 2023-12-22 14:28:18 发布

阅读量8.9k

点赞数

文章标签： oracle 存储 c# exception output logging

本文链接：https://blog.csdn.net/Sunlihgt_love/article/details/4058743

版权

用代码说明解决方法.

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