Forum Stats

  • 3,826,772 Users
  • 2,260,706 Discussions
  • 7,897,072 Comments

Discussions

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

683536
683536 Member Posts: 3
edited Nov 23, 2010 5:20PM in Oracle Provider for OLE DB
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

Best Answer

  • gdarling - oracle
    gdarling - oracle Member Posts: 2,034
    edited Feb 5, 2009 6:39PM Answer ✓
    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();
                }
            }
        }
    }

Answers

  • gdarling - oracle
    gdarling - oracle Member Posts: 2,034
    edited Feb 5, 2009 6:39PM Answer ✓
    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
    683536 Member Posts: 3
    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
    gdarling - oracle Member Posts: 2,034
    edited Feb 6, 2009 11:10AM
    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
    683536 Member Posts: 3
    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
    818083 Member Posts: 1
    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.
This discussion has been closed.