I am a little baffled by this error - meaning I am likely overlooking something that is likely obvious.
I am executing an update command configured as the following:
Dim comm As OracleCommand = conn.CreateCommand
comm.CommandType = CommandType.Text
comm.CommandText = String.Format("UPDATE {0} SET DESCRIPTION=:description, " _
& "LAST_UPDATED=systimestamp WHERE TYPE_ID=:typeId", TABLE_NAME)
comm.Parameters.Add(":typeId", OracleDbType.Int32)
comm.Parameters(":typeId").Value = orgType.TYPE_ID
comm.Parameters.Add(":description", OracleDbType.Varchar2)
comm.Parameters(":description").Value = orgType.DESCRIPTION
For my test query I am sending this method an OrgType object with TYPE_ID = 10 and DESCRIPTION = "Advisory"
The table consists of the follwing columns:
TYPE_ID NUMBER(9,0)
DESCRIPTION VARCHAR2(200 BYTE)
LAST_UPDATED DATE
Can somebody explain to me why I would be getting the following Oracle error when I attempt to execute this command?
ORA-01722:
invalid number
I removed the LAST_UPDATED column from the update and executed the command and received this same error.
I removed the parameter for DESCRIPTION and hard-coded the update value in as "DESCRIPTION = 'Advisory.' The command executed properly when doing this.
I have determined it is an issue with the DESCRIPTION column and it's parameter configuration. However, I cannot figure out why it is trying to convert anything to a number. The OracleDbType is set to "VARCHAR2." The value of the orgType.DESCRIPTION object
is a String set equal to "Advisory."
According to this discussion - it seems like Oracle is attempting to convert the string that you are passing in to a number (or vice versa), which would be invalid for your VARCHAR2 field.
Have you placed a breakpoint within the code that you listed and stepped through it during execution to see what the values that are actually being stored are? (Sorry I haven't worked with an Oracle Database in a very long time.)
The .ToString() method should certainly be working, perhaps create a variable prior to it being used to check what the results of the .ToString() are creating?
Yes; I have stepped through the code and verified that orgType.DESCRIPTION is a String value equal to "Advisory" in my test code.
I tried using the TO_CHAR oracle function in my query:
comm.CommandText = String.Format("UPDATE {0} SET DESCRIPTION=TO_CHAR(:description), " _
& "LAST_UPDATED=systimestamp WHERE TYPE_ID=:typeId", TABLE_NAME)
Didn't make a difference. So far the only way I have gotten the query to run succeffully is to concatenate the description value into the query string:
comm.CommandText = String.Format("UPDATE {0} SET DESCRIPTION='{1}', " _
& "LAST_UPDATED=systimestamp WHERE TYPE_ID=:typeId", TABLE_NAME, orgType.DESCRIPTION.ToString)
I'll keep looking although that was the only difference that I could think of between your hard-coded 'Advisory' string and your example.
(And you are sure that this is related to just the Description field and not another one of the fields? Such as Timestamp?)
KJAK
Participant
1673 Points
478 Posts
Database transaction - invalid number on String (VARCHAR2) column
Jan 18, 2013 08:22 PM|LINK
I am a little baffled by this error - meaning I am likely overlooking something that is likely obvious.
I am executing an update command configured as the following:
Dim comm As OracleCommand = conn.CreateCommand comm.CommandType = CommandType.Text comm.CommandText = String.Format("UPDATE {0} SET DESCRIPTION=:description, " _ & "LAST_UPDATED=systimestamp WHERE TYPE_ID=:typeId", TABLE_NAME) comm.Parameters.Add(":typeId", OracleDbType.Int32) comm.Parameters(":typeId").Value = orgType.TYPE_ID comm.Parameters.Add(":description", OracleDbType.Varchar2) comm.Parameters(":description").Value = orgType.DESCRIPTIONFor my test query I am sending this method an OrgType object with TYPE_ID = 10 and DESCRIPTION = "Advisory"
The table consists of the follwing columns:
TYPE_ID NUMBER(9,0)
DESCRIPTION VARCHAR2(200 BYTE)
LAST_UPDATED DATE
Can somebody explain to me why I would be getting the following Oracle error when I attempt to execute this command?
I removed the LAST_UPDATED column from the update and executed the command and received this same error.
I removed the parameter for DESCRIPTION and hard-coded the update value in as "DESCRIPTION = 'Advisory.' The command executed properly when doing this.
I have determined it is an issue with the DESCRIPTION column and it's parameter configuration. However, I cannot figure out why it is trying to convert anything to a number. The OracleDbType is set to "VARCHAR2." The value of the orgType.DESCRIPTION object is a String set equal to "Advisory."
Help me out here guys. What am I missing?
KJAK
Rion William...
All-Star
27100 Points
4486 Posts
Re: Database transaction - invalid number on String (VARCHAR2) column
Jan 18, 2013 08:40 PM|LINK
Have you tried explicitly sending it in as a string (using the .ToString() method)?
comm.Parameters(":description").Value = orgType.DESCRIPTION.ToString()According to this discussion - it seems like Oracle is attempting to convert the string that you are passing in to a number (or vice versa), which would be invalid for your VARCHAR2 field.
You can take a look at the Oracle Implicit Conversion Table here, which may give some insight into this issue.
KJAK
Participant
1673 Points
478 Posts
Re: Database transaction - invalid number on String (VARCHAR2) column
Jan 22, 2013 02:10 PM|LINK
Tried your suggestion. It didn't make a difference.
Viewed your resources. None provided a solution to my issue.
KJAK
Rion William...
All-Star
27100 Points
4486 Posts
Re: Database transaction - invalid number on String (VARCHAR2) column
Jan 22, 2013 02:30 PM|LINK
Have you placed a breakpoint within the code that you listed and stepped through it during execution to see what the values that are actually being stored are? (Sorry I haven't worked with an Oracle Database in a very long time.)
The .ToString() method should certainly be working, perhaps create a variable prior to it being used to check what the results of the .ToString() are creating?
priyankmtr
Contributor
2626 Points
526 Posts
Re: Database transaction - invalid number on String (VARCHAR2) column
Jan 22, 2013 02:43 PM|LINK
i hope this will help youin figuring out the problem and solution as well.
http://ora-01722.ora-code.com/
(Mark as Answer If you find helpful)
KJAK
Participant
1673 Points
478 Posts
Re: Database transaction - invalid number on String (VARCHAR2) column
Jan 22, 2013 02:52 PM|LINK
Yes; I have stepped through the code and verified that orgType.DESCRIPTION is a String value equal to "Advisory" in my test code.
I tried using the TO_CHAR oracle function in my query:
comm.CommandText = String.Format("UPDATE {0} SET DESCRIPTION=TO_CHAR(:description), " _ & "LAST_UPDATED=systimestamp WHERE TYPE_ID=:typeId", TABLE_NAME)Didn't make a difference. So far the only way I have gotten the query to run succeffully is to concatenate the description value into the query string:
comm.CommandText = String.Format("UPDATE {0} SET DESCRIPTION='{1}', " _ & "LAST_UPDATED=systimestamp WHERE TYPE_ID=:typeId", TABLE_NAME, orgType.DESCRIPTION.ToString)KJAK
KJAK
Participant
1673 Points
478 Posts
Re: Database transaction - invalid number on String (VARCHAR2) column
Jan 22, 2013 02:53 PM|LINK
That was one of the first things I did. The information there doesn't resolve my issue.
KJAK
Rion William...
All-Star
27100 Points
4486 Posts
Re: Database transaction - invalid number on String (VARCHAR2) column
Jan 22, 2013 02:57 PM|LINK
I know it may be silly, but have you tried wrapping the value in single-quotes?
comm.Parameters(":description").Value = String.Format("'{0}'",orgType.DESCRIPTION.ToString())KJAK
Participant
1673 Points
478 Posts
Re: Database transaction - invalid number on String (VARCHAR2) column
Jan 22, 2013 03:04 PM|LINK
Tried it.. No Success
- Invalid Number error
KJAK
Rion William...
All-Star
27100 Points
4486 Posts
Re: Database transaction - invalid number on String (VARCHAR2) column
Jan 22, 2013 03:10 PM|LINK
Hmmm. I really thought that might fix it.
I'll keep looking although that was the only difference that I could think of between your hard-coded 'Advisory' string and your example. (And you are sure that this is related to just the Description field and not another one of the fields? Such as Timestamp?)