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
<mailto:sqlalchemy+unsubscr...@googlegroups.com>.
To post to this group, send email to sqlalchemy@googlegroups.com
<mailto:sqlalchemy@googlegroups.com>.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
