Getting an ORA-01426: Numeric Overflow in .NET 4.0, C#, using the Oracle DataAccess .dll

Question

I am trying to write a very simple webpage using Visual Studio 2010, .Net 4, C#. I am using the Oracle DataAccess .DLL and have made a connection to the database just fine. When I run this code, I get the exception 'ORA-01426: Numeric Overflow." I am not doing any calculations, and I put the whole thing into a string before I submit the query. Below is my code sample:

string sql = "SELECT * from users.training WHERE per_id_no = " + strIdNo;
        OracleCommand cmd = new OracleCommand(sql, conn);
        cmd.CommandType = System.Data.CommandType.Text;

        try
        {
            Label1.Visible = false;
            //Read data from the database
            OracleDataReader dr = cmd.ExecuteReader(); // C#
            dr.Read();

score 2 · Answer 1 · answered Jul 05 '11 at 17:56

This is not an answer, but your code is vulnerable to SQL injection. Never include parameters directly into the SQL statement. Use named arguments instead:

    string sql = "SELECT * from users.training WHERE per_id_no = @id";
    using (var cmd = conn.CreateCommand()) 
    {
        cmd.CommandText = sql;

        //syntax differs between different providers
        cmd.Parameters.Add("id", strIdNo); 

        try
        {
            Label1.Visible = false;
            OracleDataReader dr = cmd.ExecuteReader();
            dr.Read();
        }
     }

Thanks for the tip! I am brand new to using Oracle with .NET, so once I get this working I will look at implementing your suggestion. — Dbloom, Jul 05 '11 at 18:24

score 1 · Accepted Answer · answered Jul 05 '11 at 18:55

1

If per_id_no is a varchar2 data type then you probably need to put the value in single quotes.
string sql = "SELECT * from users.training WHERE per_id_no = '" + strIdNo + "'";

Of course, I entirely agree with @jgauffin, in which case I think the query should look like this
string sql = "SELECT * from users.training WHERE per_id_no = '@id'";
But I am pretty sure in Oracle the paramter should be :id and not @id which I believe is SQL Server syntax.

Anyway, HTH
Harv

answered Jul 05 '11 at 18:55

Harv

523
3
8

This worked! Thank you very much, I have been looking for days for an answer to this, and I knew it would come down to my ignorance of basic Oracle/SQL. – Dbloom Jul 05 '11 at 19:55

score 0 · Answer 3 · answered Jul 05 '11 at 17:52

strIdNo may be too large for the type that would contain it. Check to be sure it doesn't need to be quoted (to convert it into a string). Also check the type of the per_id_no column to make sure it is the type you expect (and that strIdNo can fit in it).

Getting an ORA-01426: Numeric Overflow in .NET 4.0, C#, using the Oracle DataAccess .dll

3 Answers3