Inserting a CLOB using cx_Oracle
cx-oracle, oracle, python
Solution
There are a few possible solutions:
- Upgrade cx_Oracle. I'm not sure which version you're using, but I'm using python 2.7.2 with cx_Oracle 5.1 and I don't get any errors when inserting 150,000 chars into a CLOB column without using setinputsizes at all.
- Since setinputsizes applies to every subsequent cursor use, just change it between these different cursor.execute statements.
eg:
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)
Problem
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 ``` 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. I have also tried the field as a BLOB and then used `.encode("hex")` on the value I was inserting, again same behavior. I have also tried `HERP = cursor.var(cx_Oracle.CLOB)` instead of `cursor.setinputsizes(HERP = cx_Oracle.CLOB)`, same issues. I've been going through the discussions on the cx-oracle-users list, but no luck yet. 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). How can I use `cx_Oracle` to insert a CLOB into an Oracle database?