On Wednesday, September 28, 2016 at 10:16:20 AM UTC-4, Mike Bayer wrote: > > > So illustrating VARRAY round trip on cx_oracle is the first step. >
It looks like cx_Oracle supports reading varrays, but supports writing them only as column literals, not as bound parameters. The following code tests a bunch of plausible formats for bound parameters, none of which work. This is consistent with https://sourceforge.net/p/cx-oracle/mailman/message/26769899/ . if __name__ == '__main__': import logging logging.basicConfig(level='DEBUG') def format_args(args): return ', '.join(['"%s"' % arg if isinstance(arg, str) else str(arg) for arg in execute_args]) import cx_Oracle with cx_Oracle.Connection(user, password, host) as connection: cursor = connection.cursor() for drop_object in ("TABLE tb_aaa", "TYPE tp_str_vec", "TYPE tp_num_vec"): try: cursor.execute("DROP " + drop_object) except: pass for execute_args in [("CREATE OR REPLACE TYPE tp_str_vec as VARRAY(3) of VARCHAR2(8)",), ("CREATE OR REPLACE TYPE tp_num_vec as VARRAY(3) of NUMBER",), ("CREATE TABLE tb_aaa ( strvec tp_str_vec, numvec tp_num_vec )",),]: logging.info(format_args(execute_args) + "\n") cursor.execute(*execute_args) for i, execute_args in enumerate([ (("INSERT INTO tb_aaa VALUES ( tp_str_vec(%(strvec1)s), tp_num_vec(%(numvec1)s) )" % {"strvec1": str(['A1', 'A2', 'A3'])[1:-1], "numvec1": str([1.1, 1.2, 1.3])[1:-1]}),), ("INSERT INTO tb_aaa VALUES ( tp_str_vec(:strvec1), tp_num_vec(:numvec1) )", {"strvec1": ['B1', 'B2', 'B3'], "numvec1": [2.1, 2.2, 2.3]}), ("INSERT INTO tb_aaa VALUES ( tp_str_vec(:strvec1), tp_num_vec(:numvec1) )", {"strvec1": str(['C1', 'C2', 'C3'])[1:-1], "numvec1": str([3.1, 3.2, 3.3])[1:-1]}), ("INSERT INTO tb_aaa VALUES ( tp_str_vec(:strvec1), tp_num_vec(:numvec1) )", {"strvec1": str(['D1', 'D2', 'D3']), "numvec1": str([4.1, 4.2, 4.3])}), ("INSERT INTO tb_aaa VALUES ( tp_str_vec(:strvec1), tp_num_vec(:numvec1) )", {"strvec1": '(%s)' % str(['E1', 'E2', 'E3'])[1:-1], "numvec1": '(%s)' % str([5.1, 5.2, 5.3])[1:-1]}), ("INSERT INTO tb_aaa VALUES ( :strvec1, :numvec1 )", {"strvec1": ['F1', 'F2', 'F3'], "numvec1": [6.1, 6.2, 6.3]}), (("INSERT INTO tb_aaa VALUES ( (%(strvec1)s), (%(numvec1)s) )" % {"strvec1": str(['G1', 'G2', 'G3'])[1:-1], "numvec1": str([7.1, 7.2, 7.3])[1:-1]}),), (("INSERT INTO tb_aaa VALUES ( %(strvec1)s, %(numvec1)s )" % {"strvec1": str(['H1', 'H2', 'H3']), "numvec1": str([8.1, 8.2, 8.3])}),), ]): try: logging.info("INSERT #%d: %s" % (i, format_args(execute_args))) cursor.execute(*execute_args) logging.info("INSERT #%d succeeded.\n" % i) except cx_Oracle.DatabaseError as e: logging.info("INSERT #%d failed: " % i + str(e)) cursor.execute("SELECT * FROM tb_aaa") result = cursor.fetchall() logging.info("SELECT returns:\n" + str(result)) INFO:root:"CREATE OR REPLACE TYPE tp_str_vec as VARRAY(3) of VARCHAR2(8)" INFO:root:"CREATE OR REPLACE TYPE tp_num_vec as VARRAY(3) of NUMBER" INFO:root:"CREATE TABLE tb_aaa ( strvec tp_str_vec, numvec tp_num_vec )" INFO:root:INSERT #0: "INSERT INTO tb_aaa VALUES ( tp_str_vec('A1', 'A2', 'A3'), tp_num_vec(1.1, 1.2, 1.3) )" INFO:root:INSERT #0 succeeded. INFO:root:INSERT #1: "INSERT INTO tb_aaa VALUES ( tp_str_vec(:strvec1), tp_num_vec(:numvec1) )", {'numvec1': [2.1, 2.2, 2.3], 'strvec1': ['B1', 'B2', 'B3']} INFO:root:INSERT #1 failed: ORA-01484: arrays can only be bound to PL/SQL statements INFO:root:INSERT #2: "INSERT INTO tb_aaa VALUES ( tp_str_vec(:strvec1), tp_num_vec(:numvec1) )", {'numvec1': '3.1, 3.2, 3.3', 'strvec1': "'C1', 'C2', 'C3'"} INFO:root:INSERT #2 failed: ORA-22814: attribute or element value is larger than specified in type INFO:root:INSERT #3: "INSERT INTO tb_aaa VALUES ( tp_str_vec(:strvec1), tp_num_vec(:numvec1) )", {'numvec1': '[4.1, 4.2, 4.3]', 'strvec1': "['D1', 'D2', 'D3']"} INFO:root:INSERT #3 failed: ORA-22814: attribute or element value is larger than specified in type INFO:root:INSERT #4: "INSERT INTO tb_aaa VALUES ( tp_str_vec(:strvec1), tp_num_vec(:numvec1) )", {'numvec1': '(5.1, 5.2, 5.3)', 'strvec1': "('E1', 'E2', 'E3')"} INFO:root:INSERT #4 failed: ORA-22814: attribute or element value is larger than specified in type INFO:root:INSERT #5: "INSERT INTO tb_aaa VALUES ( :strvec1, :numvec1 )", {'numvec1': [6.1, 6.2, 6.3], 'strvec1': ['F1', 'F2', 'F3']} INFO:root:INSERT #5 failed: ORA-01484: arrays can only be bound to PL/SQL statements INFO:root:INSERT #6: "INSERT INTO tb_aaa VALUES ( ('G1', 'G2', 'G3'), (7.1, 7.2, 7.3) )" INFO:root:INSERT #6 failed: ORA-00907: missing right parenthesis INFO:root:INSERT #7: "INSERT INTO tb_aaa VALUES ( ['H1', 'H2', 'H3'], [8.1, 8.2, 8.3] )" INFO:root:INSERT #7 failed: ORA-00936: missing expression INFO:root:SELECT returns: [(['A1', 'A2', 'A3'], [1.1, 1.2, 1.3])] -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
