How do I solve the ORA-01704: string literal too long error in Python cx_oracle?

Question

I am trying to update an entry in a table usinig Python cx_oracle. The column is named "template" and it has a data type of CLOB.

This is my code:

dsn = cx_Oracle.makedsn(hostname, port, sid)
orcl = cx_Oracle.connect(username + '/' + password + '@' + dsn)
curs = orcl.cursor()
sql = "update mytable set template='" + template + "' where id='6';"
curs.execute(sql)
orcl.close()

When I do this, I get an error saying the string literal too long. The template variable contains about 26000 characters. How do I solve this?

Edit:

I found this: http://osdir.com/ml/python.db.cx-oracle/2005-04/msg00003.html
So I tried this:

curs.setinputsizes(value = cx_Oracle.CLOB)
sql = "update mytable set template='values(:value)' where id='6';"
curs.execute(sql, value = template)

and I get a "ORA-01036: illegal variable name/number error"

Edit2:

So this is my code now:

    curs.setinputsizes(template = cx_Oracle.CLOB)
    sql = "update mytable set template= :template where id='6';"
    print sql, template
    curs.execute(sql, template=template)

I get an ORA-00911: invalid character error now.

Answer 1

Inserting values in sql statements is a very bad practice. You should use parameters instead:

dsn = cx_Oracle.makedsn(hostname, port, sid)
orcl = cx_Oracle.connect(username + '/' + password + '@' + dsn)
curs = orcl.cursor()
curs.setinputsizes(template = cx_Oracle.CLOB)
sql = "update mytable set template= :template where id='6'"
curs.execute(sql, template=template)
orcl.close()

Answer 2

Change your table definition. A varchar2 field can store up to 32767 bytes; so, if you're using an 8-bit encoding, you have a bit of room left to play with before resorting to LOBs.

Answer 3

Use IronPython

import sys
sys.path.append(r"...\Oracle\odp.net.11g.64bit")
import clr
clr.AddReference("Oracle.DataAccess")
from Oracle.DataAccess.Client import OracleConnection, OracleCommand,   OracleDataAdapter

connection = OracleConnection('userid=user;password=hello;datasource=database_1')
connection.Open()

command = OracleCommand()
command.Connection = connection
command.CommandText = "SQL goes here"
command.ExecuteNonQuery()