You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user-cs@ibatis.apache.org by Dustin Aleksiuk <Du...@huskyenergy.ca> on 2007/01/19 17:16:15 UTC
Oracle/Ibatis Decimal error
Hi folks,
This may or may not be an iBatis issue.
When I try to insert a .net Decimal value into an Oracle decimal
column, I get this error: "numeric precision specifier is out of range
(1 to 38)". I googled it and found lots of advice to increase the
precision on the database. I've been fiddling with it for a bit but I
can't get it to work. These aren't currently big numbers either. My
test data for now is stuff like "1.23".
Anyone know why? Seems like an Oracle driver issue to me. Something to
do with the .NET Decimal datatype being too much precision for the table
even though the data isn't?
Some data:
- I can insert a simple value (1.23) using SQL directly.
- When I change my .NET datatype to a double it works
- I made iBatis output the SQL it's using, and everything looks fine.
The SQL runs.
Also note that I have no idea if I really need to use a Decimal type in
my .NET code, but it seems appropriate since I'll be doing a lot of
calcuations using this data and I don't want all of the precision and
rounding issues that come with float or double. Advice?
Much thanks,
Dustin
Re: Oracle/Ibatis Decimal error
Posted by Clinton Begin <cl...@gmail.com>.
Nah....Gilles is the BG of iBATIS.NET. I'm just his Ballmer lackey.
On 1/19/07, Dustin Aleksiuk <Du...@huskyenergy.ca> wrote:
>
>
>
> >>> "Clinton Begin" <cl...@gmail.com> 01/19/07 10:14 AM >>>
>
> > I'm actually surprised that worked....Microsoft deprecated the
> > Precision and Scale properties of the OracleParameter type.
>
> Yeah, that's interesting all right, because without those two parameters it
> doesn't work. We're using Oracle 9, if that matters. It looks like I've got
> something called Oracle.DataAccess version 9.2.0.700 in the GAC.
>
>
>
>
> Problem is, now that it works I have to get back to adding value for clients
> or whatever it is I do and let the mystery be. By the way, getting your
> iBatis email answered by THE Clinton Begin is kind of like getting your
> Microsoft support call answered by Bill Gates: something to tell the
> grandkids about.
>
> Thanks,
> Dustin
Re: Oracle/Ibatis Decimal error
Posted by Roberto R <ro...@apache.org>.
It sounds like you're using the Oracle's provider (vs the MS provider for
Oracle), and you figured out how to make it work. :-)
Roberto
On 1/19/07, Dustin Aleksiuk <Du...@huskyenergy.ca> wrote:
>
>
>
> >>> "Clinton Begin" <cl...@gmail.com> 01/19/07 10:14 AM >>>
> > I'm actually surprised that worked....Microsoft deprecated the
> > Precision and Scale properties of the OracleParameter type.
> Yeah, that's interesting all right, because without those two parameters
> it doesn't work. We're using Oracle 9, if that matters. It looks like I've
> got something called Oracle.DataAccess version 9.2.0.700 in the GAC.
>
> Problem is, now that it works I have to get back to adding value for
> clients or whatever it is I do and let the mystery be. By the way, getting
> your iBatis email answered by THE Clinton Begin is kind of like getting your
> Microsoft support call answered by Bill Gates: something to tell the
> grandkids about.
>
> Thanks,
> Dustin
>
Re: Oracle/Ibatis Decimal error
Posted by Dustin Aleksiuk <Du...@huskyenergy.ca>.
>>> "Clinton Begin" <cl...@gmail.com> 01/19/07 10:14 AM >>>
> I'm actually surprised that worked....Microsoft deprecated the
> Precision and Scale properties of the OracleParameter type.
Yeah, that's interesting all right, because without those two
parameters it doesn't work. We're using Oracle 9, if that matters. It
looks like I've got something called Oracle.DataAccess version 9.2.0.700
in the GAC.
Problem is, now that it works I have to get back to adding value for
clients or whatever it is I do and let the mystery be. By the way,
getting your iBatis email answered by THE Clinton Begin is kind of like
getting your Microsoft support call answered by Bill Gates: something to
tell the grandkids about.
Thanks,
Dustin
Re: Oracle/Ibatis Decimal error
Posted by Clinton Begin <cl...@gmail.com>.
I'm actually surprised that worked....Microsoft deprecated the
Precision and Scale properties of the OracleParameter type.
http://msdn2.microsoft.com/en-us/library/system.data.oracleclient.oracleparameter.precision.aspx
They suggest just using the Math classes to round:
http://msdn2.microsoft.com/en-us/library/ms131275.aspx
But maybe that's exactly what iBATIS.NET is doing.
Still weird how 1.23 blew the NUMERIC(38,?) limit upon creating the
parameter...perhaps there were trailing 0's? But decimal can only
have a precision of 28? Weird. I'd be interested if anyone knew the
answer, just out of curiosity.
Clinton
On 1/19/07, Dustin Aleksiuk <Du...@huskyenergy.ca> wrote:
>
>
>
>
>
> > When I try to insert a .net Decimal value into an Oracle decimal column, I
> get this error: "numeric precision specifier is out of range (1 to 38)". I
> googled it and found lots of advice to > > increase the precision on the
> database. I've been fiddling with it for a bit but I can't get it to work.
> These aren't currently big numbers either. My test data for now is stuff
> like "1.23".
>
> I figured it out. For future posterity, I used the "precision" and "scale"
> XML attributes.
>
> For example:
>
> <parameter property="SedimentAndWaterPercentage"
> precision="22" scale="8" column="sed_water_percentage"/>
>
> Sorry to bother you,
> Dustin
Re: Oracle/Ibatis Decimal error
Posted by Dustin Aleksiuk <Du...@huskyenergy.ca>.
> When I try to insert a .net Decimal value into an Oracle decimal
column, I get this error: "numeric precision specifier is out of range
(1 to 38)". I googled it and found lots of advice to > > increase the
precision on the database. I've been fiddling with it for a bit but I
can't get it to work. These aren't currently big numbers either. My
test data for now is stuff like "1.23".
I figured it out. For future posterity, I used the "precision" and
"scale" XML attributes.
For example:
<parameter property="SedimentAndWaterPercentage" precision="22"
scale="8" column="sed_water_percentage"/>
Sorry to bother you,
Dustin
Re: Oracle/Ibatis Decimal error
Posted by Gilles Bayon <ib...@gmail.com>.
For calculation, decimal is more appropriate in .NET code.
The Oracle driver are a bit messed in .NET world.
The unit test have been done against an Oracle Database by Roberto.
As I see, a .NET decimal type is map to a number in oracle.
The dbType are different if you used the MS or Oracle driver.
You can look at configuration LineIntem.xml file (property Price, column
LINEITEM_PRICE)
for Oracle driver at
http://svn.apache.org/repos/asf/ibatis/trunk/cs/mapper/IBatisNet.DataMapper.Test/Maps/Oracle/OracleClient/
for MS Driver at
http://svn.apache.org/repos/asf/ibatis/trunk/cs/mapper/IBatisNet.DataMapper.Test/Maps/Oracle/ODP/
--
Cheers,
Gilles
<a href="http://www.amazon.com/gp/registry/6JCP7AORB0LE">Wish List</a>