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

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

On Thu, 10 Feb 2005 07:50:11 -0700, Anthony Tuininga
<an...@co...> wrote:
> > 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 :-(
> 
> Not certain. Since I've only dumped it for future import into an Oracle
> database that hasn't been an issue for me. You'll have to try -- you can
> find out what happens by simply issuing "to_char(number)" on any number
> you would care to and looking at the resulting string.

I tried it out, and it works perfectly. Thanks for the pointer. But
(see below) the result isn't always the same as to_char() gives, in
the presence of NLS parameters...

> > 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
[...]
> > 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...
> 
> I do and I've considered something similar. Perhaps a
> cursor.define(position, data_type) which would allow you to override the
> default retrieve definition. In your example the call would be
> "c.define(1, cx_Oracle.STRING)". I suspect it would have to take place
> before the execute() call, though. Comments, anyone?

That looks fine. In practice, though, for my requirement,
OPT_NumbersAsStrings is perfectly adequate. As things stand, I don't
have a use for anything finer grained, and the only example I can
think of is using a string to allow me to construct a Decimal without
loss of precision. It would probably be worth having some additional
use cases before expending much effort on this :-)

One question that does come up is that of format. My application was
basically like yours - dump the numbers to strings so that you can
later load the strings back into the database. As this is
Oracle->Oracle, no format issue arises. However, if you want to use
the results elsewhere, you'd need to define the format. A check of the
Oracle OCI documentation wasn't clear to me so I did some
experimenting. It *looks* like the format is

    <digits>.<digits>E<sign><digits>

with the .<digits> and the E<sign><digits> parts optional. But it's
not affected by NLS_NUMERIC_CHARACTERS, which is probably good...

OK, I'm overdoing this now. But the point remains that ultimately use
of OPT_NumbersAsStrings and any column-level equivalent is going to
hit a need to be sure what the valid formats are. Oracle's
documentation isn't very clear on this, and expecting Python
programmers to have a good understanding of OCI is probably expecting
a bit too much.

Hmm, actually it may not even be roundtrip-safe. Try this:

>>> cn = cx_Oracle.connect("scott/tiger")
>>> cx_Oracle.OPT_NumbersAsStrings = 1
>>> c = cn.cursor()
>>> c.execute("select 123456/100 from dual")
[<NumberVar object at 0x00973ED0>]
>>> n, = c.fetchone()
>>> n
'1234.56'
>>> c.execute("create table t (n number)")
>>> c.execute("insert into t values (:a)", a=n)
>>> c.execute("alter session set NLS_NUMERIC_CHARACTERS=',.'")
>>> c.execute("select 123456/100 from dual")
[<NumberVar object at 0x00973ED0>]
>>> n, = c.fetchone()
>>> n
'1234.56'
>>> c.execute("insert into t values (:a)", a=n)
Traceback (most recent call last):
  File "<stdin>", line 1, in ?
cx_Oracle.DatabaseError: ORA-01722: invalid number

>>>

Ick. This is getting complicated...

Well, it works for me. So this is not something I'm going to lose sleep over :-)

Paul.