• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4943
  • Last Modified:

Prepared Statements, Oracle & C#


I'm simply trying to use Prepared Statements with Oracle and c#.

I tried several code snippets from all over the web and my brain (It works with java&oracle or .NET&MSSQL...)

Here's my current test-code-snippet:

                sqlStatement = "INSERT INTO tmp (ID, NAME) VALUES(@ID, @NAME)";
                OracleCommand cmd = new OracleCommand(sqlStatement, this.dbCon);

                cmd.Parameters.Add(new OracleParameter("@ID",   OracleDbType.Varchar2, 15));
                cmd.Parameters.Add(new OracleParameter("@NAME", OracleDbType.Varchar2, 15));


                cmd.Parameters["@ID"].Value = "3wfwef";
                cmd.Parameters["@NAME"].Value = "lyxfggh";


Whenever I try to Execute the Statement I get the following execption:
Oracle.DataAccess.Client.OracleException ORA-00936: missing expression
The exeption is not the problem, it is clear: The db always tries to insert the dummy parameters (eg.: @ID)  to the table, and throws an error because (in our case because of VARCHARs) of the missing apostrophs.

It seems that it does not even try to precompile the SQL Statement.

Any suggestions?

Best Regards Gernot

1 Solution
slightwv (䄆 Netminder) Commented:
I've never used 'Prepare' but I don't think that's the problem.  Bind variables in Oracle use the colon not @.

Give this a try:
sqlStatement = "INSERT INTO tmp (ID, NAME) VALUES(:ID, :NAME)";

securityresearchAuthor Commented:
Thanks a lot, this was the problem.

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now