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

[Chapman, S. <Sco...@pi...>]

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.