0

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
Improve this question
2
  • parameter[11] has a value but is an output parameter. Should that be ParameterDirection.InputOutput? Commented Jun 28, 2013 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. Commented Jun 28, 2013 at 13:00

3 Answers 3

Reset to default
2

Have you tried:

cmd.CommandText = "CALL my_stored_procedure";

or 

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

ODBC command requires CALL

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

You're a genius! haha can't believe how that i was missing such an obvious thing...
1

CommandText should contain only the name of the StoredProcedure

 cmd.CommandText = "my_stored_procedure";
Improve this answer

3 Comments

Can you add to your question the code of the stored procedure?
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.
Check also this question/answer
0

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

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.