Oracle Invalid number error from C#

Question

I run the below code in my C# .Net application, but I keep getting error ORA-01722: invalid number. When running the same SQL directly in PL/SQL Developer it does exactly what I ask it to without complaining.

What could be causing this?

OracleCommand command = connection.CreateCommand();
command.CommandType = CommandType.Text;
command.CommandText = "insert into my_table ( select i.*, null from my_view i where i.usr_id is not null )";
command.ExecuteNonQuery();

Note: I am using Oracle.DataAccess.Client

Answer 1

I'm not sure whether this could cause different behaviour between Developer and your client, but you're making an assumption that the column order is the same in the view and the table. It's possible, though unlikely, that it's trying to insert char data from a column in the view into a number field in the table just from sequencing them differently. Try specifying the column names in both the insert and select and see if it helps. I also wondered if there was a date format mismatch but I don't see how that would cause a problem within one statement. If this is a simplified version, does the real one have any parameters?

Answer 2

I found the problem. One of the number columns in the view used a comma (,) as a decimal seperator, while the table expected a point (.).

Thanks for the help people. :)