Re: [cx-oracle-users] cx_Oracle and array in and out

[Anthony T. <ant...@gm...>]

Hi,

The answer to both of your questions is due to the fact that varray is
a "SQL" object whereas the arrays that are mentioned are only
available in PL/SQL. The ability to create SQL objects (like varray)
is still in the works. I was working on it a few months ago but then
things got way too busy at work and I haven't worked on it since.
Perhaps sometime over the break I'll get a chance to work on it as I
would very much like to get it done!

The other problem is also due to fact that varrays are "SQL" objects
and different code is in place. That code only converts to Python
floats since it occurs internally (getting an object attribute) and
not directly from a cursor. Still, things should be consistent. I
appreciate you pointing this out and I'll pay attention to that when I
get a chance to look at this a bit more closely.

Anthony

On Thu, Dec 16, 2010 at 3:13 PM, Chapman, Scott <Sco...@pi...> wrote:
> I have two questions regarding this situation:
>
>
>
> import cx_Oracle
>
>
>
> from decimal import Decimal
>
>
>
> ml = [Decimal("1.4"), Decimal("2.0"), Decimal("0.5")]
>
>
>
> def NumbersAsDecimal(cursor, name, defaultType, size, precision, scale):
>
>     if defaultType == cx_Oracle.NUMBER:
>
>         return cursor.var(str, 100, cursor.arraysize, outconverter =
> Decimal)
>
>
>
> ora_dbh = cx_Oracle.Connection("user","pass","")
>
> ora_dbh.outputtypehandler = NumbersAsDecimal
>
> ora_c = ora_dbh.cursor()
>
>
>
> try:
>
>     ora_c.execute("DROP TABLE VECTORS")
>
> except:
>
>     pass
>
> try:
>
>     ora_c.execute("DROP TYPE VECTOR")
>
> except:
>
>     pass
>
> ora_c.execute("CREATE TYPE VECTOR AS VARRAY(360) OF NUMBER")
>
> ora_c.execute("CREATE TABLE VECTORS ( NAME VARCHAR2(10), VEC VECTOR)")
>
> ora_c.execute("insert into VECTORS values ('v1' ,  VECTOR(:value1))", value1
> = ml)
>
> ora_c.execute("select * from VECTORS")
>
> foo = ora_c.fetchall()
>
> ora_dbh.close()
>
> print foo
>
> print type(foo[0][1][0])
>
>
>
> When I run it I get:
>
>
>
> $ python test_cx.py
>
> Traceback (most recent call last):
>
>   File "test_cx.py", line 25, in ?
>
>     ora_c.execute("insert into VECTORS values ('v1' ,  VECTOR(:value1))",
> value1 = ml)
>
> cx_Oracle.DatabaseError: ORA-01484: arrays can only be bound to PL/SQL
> statements
>
>
>
> How to bind this correctly?
>
>
>
> Also, when I do this with
>
>    ora_c.execute("insert into VECTORS values ('v1' ,  VECTOR(1.4, 2.0,
> 0.5))")
>
> The values are inserted Ok and the fetchall  returns a list of floats.
>
>
>
> How do I make it return a list of Decimals?
>
>
>
> Thanks,
>
> Scott
>
>
>
>
>
>
>
> This message contains confidential information and is intended only for the
> individual named. If you are not the named addressee, you should not
> disseminate, distribute, alter or copy this e-mail. Please notify the sender
> immediately by e-mail if you have received this e-mail by mistake and delete
> this e-mail from your system. E-mail transmissions cannot be guaranteed to
> be secure or without error as information could be intercepted, corrupted,
> lost, destroyed, arrive late or incomplete, or contain viruses. The sender,
> therefore, does not accept liability for any errors or omissions in the
> contents of this message which arise during or as a result of e-mail
> transmission. If verification is required, please request a hard-copy
> version. This message is provided for information purposes and should not be
> construed as a solicitation or offer to buy or sell any securities or
> related financial instruments in any jurisdiction.
> ------------------------------------------------------------------------------
> Lotusphere 2011
> Register now for Lotusphere 2011 and learn how
> to connect the dots, take your collaborative environment
> to the next level, and enter the era of Social Business.
> http://p.sf.net/sfu/lotusphere-d2d
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>
>