Thread: [cx-oracle-users] Integers in oracle

SourceForge Headquarters 225 Broadway Suite 1600 San Diego, CA 92101 +1 (858) 422-6466

=BFWhy is it that I get 1.0 when I ask for=20

select cast(1 as int) from dual
?

Is this Oracle?, Python?, me?
--=20
gpg --recv-keys --keyserver wwwkeys.pgp.net B9AD9B1B

Unfortunately, Oracle declares the "integer" to be without scale and
precision. Internally, cx_Oracle checks the scale and precision to
determine whether or not an integer ought to be returned. Specifically,
if the precision is between 1 and 9 and the scale is 0, an integer is
returned; otherwise, a float is returned.

This has always annoyed me but I don't know a good way around it. Oracle
has a numeric precision exceeding floating point by a great deal so a
while ago I added a "switch" OPT_NumbersAsStrings which, if set, will
return all numbers as strings instead of integers or floating point
numbers. This is useful for retaining the precision of the Oracle
numbers. You could use that, if you wish but it still requires you to
run int() on the result.

I have considered a method for telling cx_Oracle that the values ought
to be returned as integers, ignoring the precision and scale, but I
haven't come up with anything useful yet. Got any suggestions? One
possibility is the following:

vars = cursor.execute("select cast(1 as int) from dual")
vars[0].returntype = cx_Oracle.INTEGER
result, = cursor.fetchone()

Another is:

cursor.define(1, cx_Oracle.INTEGER)
cursor.execute("select cast(1 as int) from dual")
result, = cursor.fetchone()

At this point, result would be an integer. Neither is particularly
pleasant but perhaps better than the current situation. Any thoughts?

On Thu, 2003-08-28 at 04:05, Marcos Sánchez Provencio wrote:
> Why is it that I get 1.0 when I ask for 
> 
> select cast(1 as int) from dual
> ?
> 
> Is this Oracle?, Python?, me?
-- 
Anthony Tuininga
an...@co...

Computronix
Distinctive Software. Real People.
Suite 200, 10216 - 124 Street NW
Edmonton, AB, Canada  T5N 4A3
Phone:	(780) 454-3700
Fax:	(780) 454-3838
http://www.computronix.com

I think that the right way is to have a decimal type, with the same
features as the oracle number.

We are using Tim Peters' fixedpoint module for that.

http://fixedpoint.sourceforge.net/

El jue, 28-08-2003 a las 15:59, Anthony Tuininga escribi=F3:
> Unfortunately, Oracle declares the "integer" to be without scale and
> precision. Internally, cx_Oracle checks the scale and precision to
> determine whether or not an integer ought to be returned. Specifically,
> if the precision is between 1 and 9 and the scale is 0, an integer is
> returned; otherwise, a float is returned.
>=20
> This has always annoyed me but I don't know a good way around it. Oracle
> has a numeric precision exceeding floating point by a great deal so a
> while ago I added a "switch" OPT_NumbersAsStrings which, if set, will
> return all numbers as strings instead of integers or floating point
> numbers. This is useful for retaining the precision of the Oracle
> numbers. You could use that, if you wish but it still requires you to
> run int() on the result.
>=20
> I have considered a method for telling cx_Oracle that the values ought
> to be returned as integers, ignoring the precision and scale, but I
> haven't come up with anything useful yet. Got any suggestions? One
> possibility is the following:
>=20
> vars =3D cursor.execute("select cast(1 as int) from dual")
> vars[0].returntype =3D cx_Oracle.INTEGER
> result, =3D cursor.fetchone()
>=20
> Another is:
>=20
> cursor.define(1, cx_Oracle.INTEGER)
> cursor.execute("select cast(1 as int) from dual")
> result, =3D cursor.fetchone()
>=20
> At this point, result would be an integer. Neither is particularly
> pleasant but perhaps better than the current situation. Any thoughts?
>=20
> On Thu, 2003-08-28 at 04:05, Marcos S=E1nchez Provencio wrote:
> > Why is it that I get 1.0 when I ask for=20
> >=20
> > select cast(1 as int) from dual
> > ?
> >=20
> > Is this Oracle?, Python?, me?
--=20
gpg --recv-keys --keyserver wwwkeys.pgp.net B9AD9B1B

Unfortunately, in the example that started this thread, the result would
be "1." when printed (in other words it doesn't work as well for
integers as it does for "floating point" numbers) and the use of this
module would also induce considerable overhead. Worse still, I'd have to
"port" this to C since cx_Oracle uses C. I could (I suppose) include
fixedpoint.py in the distribution of cx_Oracle and import it directly
but that is less palatable. I most definitely would not want to do this
as the default but perhaps as an option like OPT_NumbersAsFixedPoint or
something like that. If there is enough interest in this, I am willing
to pursue this. Anyone else got any comments or ideas?

On Thu, 2003-08-28 at 08:12, Marcos Sánchez Provencio wrote:
> I think that the right way is to have a decimal type, with the same
> features as the oracle number.
> 
> We are using Tim Peters' fixedpoint module for that.
> 
> http://fixedpoint.sourceforge.net/
> 
> 
> 
> El jue, 28-08-2003 a las 15:59, Anthony Tuininga escribi:
> > Unfortunately, Oracle declares the "integer" to be without scale and
> > precision. Internally, cx_Oracle checks the scale and precision to
> > determine whether or not an integer ought to be returned.
> Specifically,
> > if the precision is between 1 and 9 and the scale is 0, an integer is
> > returned; otherwise, a float is returned.
> > 
> > This has always annoyed me but I don't know a good way around it.
> Oracle
> > has a numeric precision exceeding floating point by a great deal so a
> > while ago I added a "switch" OPT_NumbersAsStrings which, if set, will
> > return all numbers as strings instead of integers or floating point
> > numbers. This is useful for retaining the precision of the Oracle
> > numbers. You could use that, if you wish but it still requires you to
> > run int() on the result.
> > 
> > I have considered a method for telling cx_Oracle that the values ought
> > to be returned as integers, ignoring the precision and scale, but I
> > haven't come up with anything useful yet. Got any suggestions? One
> > possibility is the following:
> > 
> > vars = cursor.execute("select cast(1 as int) from dual")
> > vars[0].returntype = cx_Oracle.INTEGER
> > result, = cursor.fetchone()
> > 
> > Another is:
> > 
> > cursor.define(1, cx_Oracle.INTEGER)
> > cursor.execute("select cast(1 as int) from dual")
> > result, = cursor.fetchone()
> > 
> > At this point, result would be an integer. Neither is particularly
> > pleasant but perhaps better than the current situation. Any thoughts?
> > 
> > On Thu, 2003-08-28 at 04:05, Marcos Snchez Provencio wrote:
> > > Why is it that I get 1.0 when I ask for 
> > > 
> > > select cast(1 as int) from dual
> > > ?
> > > 
> > > Is this Oracle?, Python?, me?
-- 
Anthony Tuininga
an...@co...

Computronix
Distinctive Software. Real People.
Suite 200, 10216 - 124 Street NW
Edmonton, AB, Canada  T5N 4A3
Phone:	(780) 454-3700
Fax:	(780) 454-3838
http://www.computronix.com