Re: [cx-oracle-users] Types used for values returned from a query

[Paul M. <p.f...@gm...>]

On Wed, 09 Feb 2005 11:08:36 -0700, Anthony Tuininga
<an...@co...> wrote:
> This is true in Python 2.4 and up. Python 2.3 and earlier do use an
> internal date representation which is less powerful than the Python
> datetime data type. I'm assuming here that you are using Python 2.4.

Correct - sorry, I didn't mention this explicitly.

> The way it currently works is as follows: if the Oracle data type has no
> decimal places, the value is converted to a long/integer value;
> otherwise, the value is converted to a float with all of its loss of
> precision.

That's pretty much what I'd determined by experiment.

> When faced with this problem myself (for a program which
> dumps the contents of arbitrary queries in a format suitable for later
> importing) I dumped all numbers as strings.

That's pretty much the same requirement as I have and so the same
solution should apply. The only issue I have is that my loader is
written as a Java stored procedure inside the database, so my dump
format needs to be Java-readable.

> Oracle is quite happy to accept the string as input to a numeric column. I'm
> not sure if that will work for you but it is an option.

Quite possibly. I can just bind the variable as a String in Java, and
then load it into a Number column, I guess. Could there be any format
issues (exponential formats, commas in numbers, etc)? I only ask
because the environemnt makes it annoyingly hard to report errors, so
when things go wrong it's a real pain to debug :-(

> If you are interested in pursuing that option, you should take a look at the
> OPT_NumbersAsStrings option in cx_Oracle.

That sounds great. I'll look at that in the morning.

> > Alternatively, is there any likelihood of cxOracle using Python 2.4's
> > Decimal type in the near future?
> 
> Theoretically this wouldn't be all that difficult to accomplish but
> since there is no C implementation this would mean a significant
> performance penalty.
> Having used the Decimal type myself in pure Python
> I know that it is the most efficiently coded module, either. I suppose
> this type could be returned __only__ if the data is not an integer and
> that would mitigate the problem somewhat. Comments?

That may be an option, but frankly, this is pretty specialised use
where an exact representation is crucial. Maybe just having a way of
saying that a specific column in a query must be returned as a string,
and then doing string->Decimal in Python would be suitable. Something
like

   c = cn.cursor()
   c.execute("select...")
   c.col_as_string(1)
   row = c.fetchone()
   decval = Decimal(row[1])

All I really care about is having some way of being sure that there's
no precision loss - using strings does it, the rest is just
convenience (only using strings where it's really needed).

Sorry - must dash, but I hope you get the idea...

Paul