存储过程失败ORA-01036：非法变量名称/编号 [英] Stored Procedure Fails ORA-01036: illegal variable name/number

问题描述

我已经为MSSQL编写了存储过程，我从C＃程序中调用它。它工作正常。



我为Oracle创建了相同的代码并抛出异常ORA-01036：非法变量名称/号码，



这是我的C＃代码

I have written stored procedure for MSSQL and I call it from a C# program. it works fine.

I have created the same code for Oracle and it throws the exception ORA-01036: illegal variable name/number,

Here is my C# code

 if (DataBase == "MSSQL") // this gets executed if using MSSQL
        {
            SqlConnection conn = new SqlConnection("Data Source=SRVORDERS;Initial Catalog=ADLPRO2;User ID=OPTF;Password=#####;");
            SqlCommand cmd = new SqlCommand("OGEN.VALIDATE_PATIENT_NEW", conn);
            try
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@LAST_NAME", LAST_NAME);
                cmd.Parameters.AddWithValue("@FIRST_NAME", FIRST_NAME);
                cmd.Parameters.AddWithValue("@DOB", DOB);
                cmd.Parameters.Add("@PAT_NUMBER", SqlDbType.Int);
                cmd.Parameters.Add("@FACILITY_KEY", SqlDbType.NChar, 4);
                cmd.Parameters["@PAT_NUMBER"].Direction = ParameterDirection.Output;
                cmd.Parameters["@FACILITY_KEY"].Direction = ParameterDirection.Output;
                cmd.Parameters.Add("@VALIDATED", SqlDbType.Int);
                cmd.Parameters["@VALIDATED"].Direction = ParameterDirection.Output;
                conn.Open();
                cmd.ExecuteNonQuery();
                Validated = Convert.ToInt32(cmd.Parameters["@VALIDATED"].Value);
                if (Validated > 0)
                {
                    patnumber = Convert.ToInt32(cmd.Parameters["@PAT_NUMBER"].Value);
                    FACILITY_KEY = (string)cmd.Parameters["@FACILITY_KEY"].Value;
                }
            }
            catch (Exception e)
            {
                conn.Close();
                using (StreamWriter sw = File.AppendText(log))
                {
                    sw.WriteLine(System.DateTime.Now + " OGEN.VALIDATE_PATIENT_NEW " + Success + "  PATNUM= " + patnumber + " Order No " + orderno);
                }
            }
        }

        else  //this gets executed if using Oracle 
        {
              OracleConnection conn = new OracleConnection("Data Source=SRVORDERS;User ID=OGEN;Password=#####;Unicode=True;");
              OracleCommand cmd = new OracleCommand("OGEN.VALIDATE_PATIENT_NEW", conn);
            try
            {
...

                cmd.Parameters.Add("@VALIDATED", OracleType.Int32);
                cmd.Parameters.Add("@PAT_NUMBER", OracleType.Int32);
                cmd.Parameters.Add("@FACILITY_KEY", OracleType.Char, 4);
           }
       } 

这是我的MSSQL存储过程：

Here is my MSSQL Stored Procedure:

ALTER PROCEDURE [OGEN].[VALIDATE_PATIENT_NEW]
  (@VALIDATED INT OUTPUT,@LAST_NAME VARCHAR(30), @FIRST_NAME VARCHAR(30), @DOB datetime,@PAT_NUMBER INT OUTPUT,@FACILITY_KEY CHAR(4) OUTPUT)
AS
BEGIN
  SELECT @FACILITY_KEY = FACILITY_KEY,@PAT_NUMBER = PAT_NUMBER
  FROM OGEN.GEN_M_PATIENT_MAST WHERE UPPER(@LAST_NAME) = LAST_NAME AND UPPER(@FIRST_NAME) = FIRST_NAME AND @DOB = BIRTH_DATE
 SET @VALIDATED = @@ROWCOUNT
 RETURN
END

这是我的Oracle存储过程：

Here is my Oracle Stored Procedure:

CREATE OR REPLACE PROCEDURE VALIDATE_PATIENT_NEW
(
  VALIDATED                  IN  OUT   int,
  LAST_NAME                  IN        VARCHAR2 DEFAULT NULL,
  FIRST_NAME                 IN        VARCHAR2 DEFAULT NULL,
  DOB                        IN        timestamp DEFAULT NULL,
  PAT_NUMBER                 IN  OUT   int,
  FACILITY_KEY               IN  OUT   CHAR
)
AS  
  BEGIN 
    BEGIN
      FOR REC IN ( SELECT
         FACILITY_KEY,
         PAT_NUMBER FROM  OGEN.GEN_M_PATIENT_MAST 
    WHERE   UPPER(VALIDATE_PATIENT_NEW.LAST_NAME)  = LAST_NAME
     AND  UPPER(VALIDATE_PATIENT_NEW.FIRST_NAME)  = FIRST_NAME
     AND  VALIDATE_PATIENT_NEW.DOB  = BIRTH_DATE
     )
      LOOP
        VALIDATE_PATIENT_NEW.FACILITY_KEY := REC.FACILITY_KEY;
        VALIDATE_PATIENT_NEW.PAT_NUMBER := REC.PAT_NUMBER;
      END LOOP;
    END;   
    VALIDATED  :=  SQL%ROWCOUNT; 
    RETURN;
  END;

我执行

As soon as I execute

cmd.ExecuteNonQuery();

抛出异常



任何建议都将不胜感激。



谢谢

The exception is thrown

Any advice will be greatly appreciated.

Thank you

推荐答案

参数名称中的@是导致问题的。 @是MS特定的。删除它们。

AND，您应该按照它们在存储过程中的顺序添加参数。

It''s the @ in the parameter names that''s causing the problems. @ is MS specific. Drop them.
AND, You should add the parameters in the same order as they are in the stored procedure.

这篇关于存储过程失败ORA-01036：非法变量名称/编号的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！