Click here to Skip to main content
15,888,286 members
Please Sign up or sign in to vote.
4.00/5 (1 vote)
See more:
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);
           }
       } 


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
C#
cmd.ExecuteNonQuery();

The exception is thrown

Any advice will be greatly appreciated.

Thank you
Posted
Updated 13-Mar-13 6:35am
v2
Comments
CHill60 13-Mar-13 12:56pm    
If you change VALIDATED to something else like ISVALID do you still get the error? Sorry to be vague - I don't have an oracle environment handy just now

1 solution

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.
 
Share this answer
 
Comments
stevenandler 13-Mar-13 17:25pm    
Changing VALIDATED didn't help snd thre are no @ in my Oracle stored procedure.

I added Debug to my stored procedure:

CREATE OR REPLACE PROCEDURE VALIDATE_PATIENT_NEW
(
VALIDATED OUT int,
LAST_NAME IN VARCHAR2 DEFAULT NULL,
FIRST_NAME IN VARCHAR2 DEFAULT NULL,
DOB date DEFAULT NULL,
PAT_NUMBER OUT int,
FACILITY_KEY OUT CHAR
)
AS
BEGIN
/* SELECT * */
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;

When my program hits the second BEGIN, execution jumps to END; I suspect a logic error since my stored produre compiles with out any errors.
Member 9410081 7-Oct-13 8:02am    
Even i receive same exception.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900