ORA-01722: invalid number on INSERT in C# - Oracle 11g

Question

I have this Oracle 11g table

CREATE TABLE "DBNAME"."CANDIDATES" 
(
    "ID" NUMBER(24,0), 
    "USRINS" VARCHAR2(30 CHAR), 
    "DATINS" DATE, 
    "USRUPD" VARCHAR2(30 CHAR), 
    "DATUPD" DATE, 
    "EXM_ID" NUMBER(24,0), 
    "TYPE" NUMBER(3,0), 
    "PSN_ID" NUMBER(24,0), 
    "KOD" NUMBER(20,0), 
    "STATUS" NUMBER(20,0), 
    "PRICINA" VARCHAR2(200 CHAR)
)

Now i have this command in C#

string insertIntoCandidates = "INSERT INTO CANDIDATES " &
  "(USRINS, DATINS, PSN_ID, KOD, STATUS, PRICINA) " &
  values ("
                     + ":usrins, "
                     + ":datins, "
                     + ":psn_id, "
                     + ":kod, "
                     + ":status, "
                     + ":pricina"
                     + ") ";

command.Parameters.Add(":usrins", null);
command.Parameters.Add(":datins", DateTime.Now);
command.Parameters.Add(":psn_id", getPsnIDByEMBG(result.embg));
command.Parameters.Add(":kod", result.kod_kandidat);
if (result.status)
{
    command.Parameters.Add(":status", 1);
}
else
{
    command.Parameters.Add(":status", 0);
}

command.Parameters.Add(":pricina", result.pricina);

int res = command.ExecuteNonQuery();

The columns for which I don't insert a value, can get null values. After executing the last line, I get am exception ORA-01722: invalid number. I tried looking for an answer, but without any luck. Could you help me out? Thanks

score 4 · Answer 1 · answered Aug 11 '16 at 17:31

4

add

command.BindByName=true;

apparently Oracle defaults to positional binding instead of name binding.

answered Aug 11 '16 at 17:31

Darryl

41
1

1

Had this issue with Oracle.ManagedDataAccess. Very unintuitive default. – jcox Jan 02 '19 at 20:00

score 2 · Accepted Answer · edited Jun 20 '20 at 09:12

2

An ORA-01722 ("invalid number") error occurs when an attempt is made to convert a character string into a number, and the string cannot be converted into a valid number. So, check the parameters for correct data types.

And also see:

C# parameterized queries for Oracle - serious & dangerous bug!

and

Why am I getting an ORA-01722 (invalid number)?

edited Jun 20 '20 at 09:12

Community

1
1

answered Feb 11 '15 at 10:00

michaos

121
5

1

Yeah, my mistake, sorry. I was passing a string to the column KOD by mistake. – mkdavor Feb 11 '15 at 14:22

score 2 · Answer 3 · answered Jul 24 '15 at 06:22

Further to michaos's answer, also note that it doesn't matter what you name your parameters, they have to be added in the order in which they appear in the query. If not, then you can get misleading ORA-01722 (and other) errors. Yes this is a horrible bug!

score 0 · Answer 4 · answered Feb 11 '15 at 09:13

0

Instead of null you have to use DBNull.Value

answered Feb 11 '15 at 09:13

Wernfried Domscheit

54,457
9
76
110

No, it is not that. Btw I use null in the other queries and it works with just null. – mkdavor Feb 11 '15 at 09:28

ORA-01722: invalid number on INSERT in C# - Oracle 11g

4 Answers4