locked
Getting the right type for Oracle parameters passed into a stored proc RRS feed

  • Question

  • User-1992252904 posted

    Hello there.  I have a stored proc that takes like 28 parameters.  Those parameters are divided into at least 3 different types of variables for the stored proc.  My application pulls these variables out of an xml file programatically (at runtime).  I heard on the grapevine somewhere that all you had to do for this to work was input the variables in the correct order into the stored proc and Oracle would convert the data into the right type...  Well, I trusted that info, and now I am getting Oracle errors...  "ORA-00900: invalid SQL statement" returned to me... I am guessing it is because I am attempting to pass in strings...  First question, can you actually just pass in strings and have Oracle translate them into the correct data type(I know, it sounds to good to be true), second question builds on the first.  IF you can do that, how would you go about it?

    Here is kindof what I did...

    using (OracleConnection conn = new OracleConnection(ConfigurationManager.ConnectionStrings["ACODBConnectionString"].ConnectionString))
                {
    
                    using (OracleCommand cmd = new OracleCommand(sProc, conn))
                    {
                        int i = 0;
                        foreach (string path in paths)
                        {
                            string OracleParam;
                            xAttribute = AcoXMLDoc.SelectSingleNode(string.Format("//dataTemplateSpecification/templates/template/elements/element[@name='{0}']", path)).Attributes["value"];
                            if ((xAttribute.Value == null))
                            {
                                OracleParam = "";
                                cmd.Parameters.Add(colName[i], OracleParam);
                            }
                            else
                            {
                                OracleParam = xAttribute.Value;
                                cmd.Parameters.Add(colName[i], OracleParam);
                            }
                            i++;
                        }
                        conn.Open();
                        outcome = cmd.ExecuteNonQuery();
                    }
                }
    


    By the way, I am using ODP.NET
    Monday, August 1, 2011 7:48 AM

Answers

  • User-1952482173 posted

    Most likely, you passed null value in, and your sql statement generated like WHERE id = 10 AND name = AND Store = 'Test'.

    So, even no value provided for name, for example, you need to pass '' in, so that the statement will be name = ''.

    Can you try to change "OracleParam = ""; " to OracleParam = "''"; 

    to see want happen?
     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, August 1, 2011 11:19 AM