ORA-01722 with ODP and Command Parameters

March 3, 2006 at 2:52 am 12 comments

This week I was working on a section of code using C#, the Oracle .NET Provider (ODP) and some good old parameters in a SQL statement.  The code was pretty straight forward (this is from memory without the assistance of intellisense– so apologies for any syntax errors):

string sql = “INSERT INTO MyTable (TextField1, NumberField2) :param1 :param2”;

OracleCommand cmd = new OracleCommand(sql, this.conn);

cmd.Parameters.Add(“param2”, OracleDbType.Int32);
cmd.Parameters.Add(“param1”, OracleDbType.nVarChar2);

cmd.Parameters[“param2”].value = 12;
cmd.Parameters[“param1”].value = “My Text”;

cmd.ExecuteNonQuery();

Everything seemed good to go, but when I executed the query, I got ORA-01722: Invalid number

When you check out the appropriate page on  http://www.ora-code.com it reads, “The attempted conversion of a character string to a number failed because the character string was not a valid numeric literal.” 

Well, my parameter value was obviously a number, so I thought I was picking the wrong field type for the parameter.  I tried all sorts of things– Int64, Int16, Long, etc.  I always got the same error.   Then I learned a helpful little tidbit about the OracleCommand object in ODP:

Named parameters are turned off by default.

So even though I’m referring to my parameters by name and setting their values by name– when the SQL command was running, it was plugging the parameters in by the order in which you add them to the command object.  So in the above example, my value for param2 was what was getting plugged in for param1 and vice versa.  As a result, my SQL was trying to insert “My Text” into a number field.  Yup, I’d say that’s an invalid number!

Luckily, ODP does support named parameters (don’t believe news group postings from 2003 that say otherwise)– you have to turn it on with a cmd.BindByName = true; call. 

 

Advertisements

Entry filed under: ORA-01722, Oracle, Oracle .NET Provider.

Dad Story: Hershey Kisses

12 Comments Add your own

  • 1. Griffin  |  August 25, 2006 at 11:16 am

    This was some great advice! I’ve just spent hours trying to figure out why my code wouldn’t work and all I was missing was the cmd.BindByName parameter.

    I was even plugging the the parameters in the correct order, but it still wouldn’t work.

    Anyway, just thought I’d show a little appreciation for the advice. So thanks alot 🙂

    Reply
  • 2. tgaw  |  August 25, 2006 at 12:42 pm

    Thanks! I’m glad the post helped!

    Reply
  • 3. Reply  |  September 15, 2006 at 1:54 pm

    THANK YOU SO MUCH! I also just spent hours trying to find out why the procedure would run but not get the parameters. No other examples even mentioned this command property, lame!

    rob lingstuyl

    Reply
  • 4. tgaw  |  September 15, 2006 at 4:18 pm

    You’re welcome! Glad it helped!

    Reply
  • 5. Donna  |  August 9, 2007 at 4:06 pm

    This had me stumped as well! Awesome stuff!

    Reply
  • 6. mutenroid  |  October 29, 2007 at 5:29 am

    Thank you very much guy!!

    Now works fine.

    Reply
  • 7. 3 Year Blogiversary « TGAW  |  March 3, 2009 at 1:03 am

    […] Well I could just let it remain empty, now could I? So I did a post. It was riveting. Brace yourself– ORA-01722 with ODP and Command Parameters. […]

    Reply
  • 8. rorfun  |  September 14, 2009 at 1:23 pm

    Thanks a lot for this essay. It solved my problem!

    Reply
  • 9. tw13  |  May 10, 2011 at 11:43 am

    I will second the many thanks on this. I was banging my head against the wall! Now I’m scared about past projects where it “worked”!

    Reply
  • 10. Bryan  |  January 26, 2012 at 2:43 pm

    nice post….I struggled for hours with this issue, finally tried to order the parameters and it worked. I thought…surely this is NOT how Orcale works…this is like MS Access! I appreciate the tip of the .BindByName method, it really helped.

    Reply
  • 11. MiNG  |  November 20, 2012 at 3:26 am

    nice post! you figure out my problem which block me a whole afternoon!

    Reply
  • 12. ahoba  |  March 10, 2019 at 6:44 am

    Many many many thanks

    Reply

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Trackback this post  |  Subscribe to the comments via RSS Feed


Flickr Photos

3D Printed Products

Tweets


%d bloggers like this: