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

parameter[11] has a value but is an output parameter. Should that be `ParameterDirection.InputOutput`? — JayGee, Jun 28 '13 at 12:57
I tried that and it still gave the same error, so I tried removing the value and just calling output and it still gave me the same error. — Bagzli, Jun 28 '13 at 13:00

Vlad Bezden · Accepted Answer · 2013-06-28T13:05:09.463

2

Have you tried:

cmd.CommandText = "CALL my_stored_procedure";

or

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

ODBC command requires CALL

edited Jun 28 '13 at 13:05

answered Jun 28 '13 at 12:58

Vlad Bezden

83,883
25
248
179

You're a genius! haha can't believe how that i was missing such an obvious thing... – Bagzli Jun 28 '13 at 13:02

score 1 · Answer 2 · answered Jun 28 '13 at 12:34

1

CommandText should contain only the name of the StoredProcedure

 cmd.CommandText = "my_stored_procedure";

answered Jun 28 '13 at 12:34

Steve

213,761
22
232
286

Can you add to your question the code of the stored procedure? – Steve Jun 28 '13 at 12:46
Unfortunately I cannot, however I can say that when I run the stored procedure in oracle itself, it runs without problems. Have a look at the parameters I am passing, my belief is that the problem lies in there somewhere. I don't know if the return parameter is not called correctly or maybe the null, or even the number fields. In oracle they are defined as "Number" I have updated the code to show the parameter types for the procedure. – Bagzli Jun 28 '13 at 12:53
Check also this [question/answer](http://stackoverflow.com/questions/2687187/what-is-the-proper-odbc-command-for-calling-oracle-stored-procedure-with-paramet) – Steve Jun 28 '13 at 13:05

score 0 · Answer 3 · answered Aug 12 '14 at 12:28

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();
    }

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

3 Answers3