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")
{
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
{
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);
}
}
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
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