Calling Oracle Stored Procedure from ASP.NET(C#)

Question

I have a stored procedure on an oracle server and I am trying to run it, however I can't figure out what I am doing wrong. When I call it directly from the server it works fine, however when I try to do it from a web application it does not work.

Here is the stored procedure with its parameters:

my_stored_procedure ( '1111' , '01 AUGUST 2011',  '22','abc' ,
          'abc' , SYSDATE , 'abc' , 1 ,'abc' , NULL, 7, returnValue) ;

If I run this on oracle server then it works without any problems and it does what it suppose to. Now here is the c# code that I am running to try and make it work:

OdbcConnection conn = getConnection();  //method that gets the connection
        OdbcParameter[] parameter = new OdbcParameter[12];

        parameter[0] = new OdbcParameter("@P_1", OdbcType.VarChar);
        parameter[0].Value = "0085";
        parameter[1] = new OdbcParameter("@P_2", OdbcType.DateTime);
        parameter[1].Value = new DateTime(2013, 04, 15);
        parameter[2] = new OdbcParameter("@P_3", OdbcType.VarChar);
        parameter[2].Value = "72";
        parameter[3] = new OdbcParameter("@P_4", OdbcType.VarChar);
        parameter[3].Value = "SANDBOX2";
        parameter[4] = new OdbcParameter("@P_5", OdbcType.VarChar);
        parameter[4].Value = "BATAR";
        parameter[5] = new OdbcParameter("@P_6", OdbcType.DateTime);
        parameter[5].Value = new DateTime();
        parameter[6] = new OdbcParameter("@P_7", OdbcType.VarChar);
        parameter[6].Value = "MRD";
        parameter[7] = new OdbcParameter("@P_8", OdbcType.Double);
        parameter[7].Value = 1;
        parameter[8] = new OdbcParameter("@P_9", OdbcType.VarChar);
        parameter[8].Value = "ORG70000";
        parameter[9] = new OdbcParameter("@P_10", OdbcType.VarChar);
        parameter[9].Value = System.DBNull.Value;
        parameter[10] = new OdbcParameter("@P_11", OdbcType.Double);
        parameter[10].Value = 1;
        parameter[11] = new OdbcParameter("@P_12", OdbcType.Int);
        parameter[11].Value = 1;
        parameter[11].Direction = ParameterDirection.Output;

        cmd.Connection = conn;
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddRange(parameter);
        cmd.CommandText = "my_stored_procedure ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ;";
        cmd.ExecuteNonQuery();
        foreach (OdbcParameter p in parameter)
        {
            if (p.Direction == ParameterDirection.Output)
                returnParameter = p.Value.ToString();
        }

        cmd.Connection.Close();
        cmd.Dispose();

Error that I get is: + $exception {"ERROR [42000] [Oracle][ODBC][Ora]ORA-00900: invalid SQL statement\n"} System.Exception {System.Data.Odbc.OdbcException}

I can't figure out what i am doing wrong.

Parameters for the procedure:

var1 VARCHAR2, var1 DATE, var3 VARCHAR2,
     var4 VARCHAR2, var5 VARCHAR2, var6 DATE, var7 VARCHAR2, var8 NUMBER,
     var9 VARCHAR2, var10 VARCHAR2 DEFAULT NULL, var11 NUMBER, var12 OUT NUMBER

Answer 1

This is the procedure definition in oracle:

string connStr = "Data Source=datasource;Persist Security Info=True;User ID=user;Password=pass;Unicode=True"; DataSet dataset = new DataSet();

string connStr = ConfigurationManager.ConnectionStrings["OracleConn"].ToString();

    using (OracleConnection objConn = new OracleConnection(connStr))
    {
        OracleCommand objCmd = new OracleCommand();
        objCmd.Connection = objConn;
        objCmd.CommandText = "Oracle_PkrName.Stored_Proc_Name";
        objCmd.CommandType = CommandType.StoredProcedure;
        objCmd.Parameters.Add("Emp_id", OracleType.Int32).Value = 3; // Input id
        objCmd.Parameters.Add("Emp_out", OracleType.Cursor).Direction = ParameterDirection.Output;

        try
        {
            objConn.Open();
            objCmd.ExecuteNonQuery();
            OracleDataAdapter da = new OracleDataAdapter(objCmd);
            da.Fill(dataset);                   
        }
        catch (Exception ex)
        {
            System.Console.WriteLine("Exception: {0}", ex.ToString());
        }
        objConn.Close();
    }

Answer 2

Have you tried:

cmd.CommandText = "CALL my_stored_procedure";

or

cmd.CommandText = "CALL my_stored_procedure (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

ODBC command requires CALL

Answer 3

CommandText should contain only the name of the StoredProcedure

 cmd.CommandText = "my_stored_procedure";