使用cx_Oracle插入CLOB [英] Inserting a CLOB using cx_Oracle

问题描述

我正在尝试使用以下代码插入CLOB.

I am trying to insert a CLOB using the following code.

cursor = connection.cursor()
cursor.setinputsizes(HERP = cx_Oracle.CLOB)

cursor.execute("INSERT INTO myTable (FOO, BAR) VALUES (:FOO, :BAR)", FOO=val1, BAR=val2)
cursor.execute("INSERT INTO myTable2 (HERP) VALUES (:HERP)", HERP=val3) 
#len(HERP) 39097

当我在不使用cursor.setinputsizes(HERP = cx_Oracle.CLOB)的情况下运行脚本时，它在使用ValueError: string data too large的第二个查询中失败，而当我在cursor.setinputsizes(HERP = cx_Oracle.CLOB)的情况下运行脚本时，在使用DatabaseError: ORA-01036: illegal variable name/number的第一个查询中失败了.我尝试插入的CLOB包含一个代码段(即它有很多分号，逗号和括号)，"string".decode("ascii")返回u'string'，所以unicode并不是问题...对吗?我不知道这两个问题是否有问题.数据库中的字段当前是一个CLOB，但是我已经使用NCLOB进行了尝试，并且行为没有改变.

When I run the script WITHOUT cursor.setinputsizes(HERP = cx_Oracle.CLOB) it fails on the second query WITH ValueError: string data too large, when I run the script with cursor.setinputsizes(HERP = cx_Oracle.CLOB) it fails on the first query with DatabaseError: ORA-01036: illegal variable name/number. The CLOB I am trying to insert contains a code snippet (i.e. it has a lot of semi colons, commas and parenthesis), "string".decode("ascii") returns u'string', so unicode isn't the problem... right? I don't know if either of these things are problems. The field in the database is a currently a CLOB, however I have tried it with an NCLOB and the behavior did not change.

我也尝试将字段作为BLOB使用，然后对要插入的值使用.encode("hex")，再次具有相同的行为.

I have also tried the field as a BLOB and then used .encode("hex") on the value I was inserting, again same behavior.

我也尝试了HERP = cursor.var(cx_Oracle.CLOB)而不是cursor.setinputsizes(HERP = cx_Oracle.CLOB)，同样的问题.

I have also tried HERP = cursor.var(cx_Oracle.CLOB) instead of cursor.setinputsizes(HERP = cx_Oracle.CLOB), same issues.

我一直在通过 cx- oracle-users 列表，但还没有运气.

I've been going through the discussions on the cx-oracle-users list, but no luck yet.

如果我使用此行cursor.execute("INSERT INTO myTable2 (HERP) VALUES (:HERP)", HERP="".join(set(val3)).encode("hex"))，它会起作用，所以我认为这与数据的内容(这与BLOB有关)无关.

It works if I use this line cursor.execute("INSERT INTO myTable2 (HERP) VALUES (:HERP)", HERP="".join(set(val3)).encode("hex")), so I don't think it's an issue with the data's content (This is with the BLOB).

如何使用cx_Oracle将CLOB插入Oracle数据库?

How can I use cx_Oracle to insert a CLOB into an Oracle database?

推荐答案

有几种可能的解决方案:

There are a few possible solutions:

1. 升级cx_Oracle.我不确定您使用的是哪个版本，但是我将python 2.7.2与cx_Oracle 5.1一起使用，并且在不使用setinputsizes的情况下将150,000个字符插入到CLOB列中时不会出现任何错误.
2. 由于setinputsizes适用于以后的每次游标使用，因此只需在这些不同的cursor.execute语句之间进行更改.

例如:

cursor = connection.cursor()
cursor.setinputsizes(FOO=None, BAR=None)
cursor.execute("INSERT INTO myTable (FOO, BAR) VALUES (:FOO, :BAR)", 
FOO=val1,  BAR=val2)
cursor.setinputsizes(HERP = cx_Oracle.CLOB)
cursor.execute("INSERT INTO myTable2 (HERP) VALUES (:HERP)", HERP=val3)

这篇关于使用cx_Oracle插入CLOB的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！