Lost scale value when insert NUMBER(15,2) type

683536

Hi,

I'm trying to insert a value 253.65 in a NUMBER(15,2) collum table. Below is my code:

{color:#3366ff}
cmd.Parameters.Add(BuildOracleParam("vNF", OracleDbType.Decimal, "253.65",15,2));{color}

where BuildOracleParam(name, type, value, precision, scale) is simple a method that create and return a OracleParamenter.

but when select the colllum I get the value 253.00.

How to resolve this situation? some advice?

tnks

gdarling - oracle

Hi,

I'm assuming you're actually using ODP.NET and not OLEDB, since you're using OracleDbType?

Do you have a small complete testcase? This worked fine for me. Does it fail for you?

What version of provider, client, database are you using? I'm testing 11.1.0.7.

Cheers,
Greg

//create table numtab1(col1 number(15,2));
using System;
using System.Data;
using Oracle.DataAccess.Client;

class Program
{
    static void Main(string[] args)
    {
        using (OracleConnection con = new OracleConnection("user id=scott;password=tiger;data source=orcl"))
        {
            con.Open();
            using (OracleCommand cmd = new OracleCommand("", con))
            {
                cmd.CommandText = "insert into numtab1 values(:1)";
                OracleParameter p1 = new OracleParameter("", OracleDbType.Decimal);
                p1.Precision = 15;
                p1.Scale = 2;
                p1.Value = 253.65;
                cmd.Parameters.Add(p1);
               cmd.ExecuteNonQuery();
            }
        }
    }
}

gdarling - oracle

Hi,

I'm assuming you're actually using ODP.NET and not OLEDB, since you're using OracleDbType?

Do you have a small complete testcase? This worked fine for me. Does it fail for you?

What version of provider, client, database are you using? I'm testing 11.1.0.7.

Cheers,
Greg

//create table numtab1(col1 number(15,2));
using System;
using System.Data;
using Oracle.DataAccess.Client;

class Program
{
    static void Main(string[] args)
    {
        using (OracleConnection con = new OracleConnection("user id=scott;password=tiger;data source=orcl"))
        {
            con.Open();
            using (OracleCommand cmd = new OracleCommand("", con))
            {
                cmd.CommandText = "insert into numtab1 values(:1)";
                OracleParameter p1 = new OracleParameter("", OracleDbType.Decimal);
                p1.Precision = 15;
                p1.Scale = 2;
                p1.Value = 253.65;
                cmd.Parameters.Add(p1);
               cmd.ExecuteNonQuery();
            }
        }
    }
}

683536

thks gdarling !

the problem was that 'im inserting a string instead of decimal value.
i'm getting the value from xml document.

i'll try to correct it.


--

Frederico Pranto

gdarling - oracle

Hi Frederico,

For the record, it works fine for me still even when I supply value as a string.

Since you're relying on implicit conversion, perhaps this probably has somethign to do with the NLS settings on the client or database.

However, if you're saying it works with the above code when you supply a number instead of a string, I'd think the better solution is simply to use explicit conversion anyway instead of relying on implicit conversion (remember y2k! )

Hope it helps,
Greg

683536

Hi,

It's already works for me.

I think the problem was the culture configuration of data base or/and the input data format.

Probably, the oracle is configurated for "pt-BR" format, so the convertion will work if the input data was 465,45.
But I get 456.45. So, in .Net code I do the folow:

Decimal.Parse(pValor.ToString(), new CultureInfo("en-US").NumberFormat);

and works!

thks for the help!

818083

gdarling wrote:

Hi,




I'm assuming you're actually using ODP.NET and not OLEDB, since you're using OracleDbType?




Do you have a small complete testcase? This worked fine for me. Does it fail for you?




What <font face="tahoma,verdana,sans-serif" size="1" color="#000">version</font> of provider, client, database are you using? I'm testing 11.1.0.7.




Cheers,



Greg



<p>





</p><pre class="jive-pre"><code class="jive-code jive-java"><font color="darkgreen">//create table numtab1(col1 number(15,2));</font>

using System;

using System.Data;

using Oracle.DataAccess.Client;

&nbsp;

<font color="navy"><b>class</b></font> Program

<font color="navy">{</font>

<font color="navy"><b>static</b></font> <font color="navy"><b>void</b></font> Main(string[] args)

<font color="navy">{</font>

using (OracleConnection con = <font color="navy"><b>new</b></font> OracleConnection(<font color="red">"user id=scott;password=tiger;data source=orcl"</font>))

<font color="navy">{</font>

con.Open();

using (OracleCommand cmd = <font color="navy"><b>new</b></font> OracleCommand(<font color="red">""</font>, con))

<font color="navy">{</font>

cmd.CommandText = <font color="red">"insert into numtab1 values(:1)"</font>;

OracleParameter p1 = <font color="navy"><b>new</b></font> OracleParameter(<font color="red">""</font>, OracleDbType.Decimal);

p1.Precision = 15;

p1.Scale = 2;

p1.Value = 253.65;

cmd.Parameters.Add(p1);

cmd.ExecuteNonQuery();

<font color="navy">}</font>

<font color="navy">}</font>

<font color="navy">}</font>

<font color="navy">}</font>

</code></pre>

Your effort is appreciated! Here is also the same case, Finally learn how to solve it.