pythonoracle-databasepyodbccloblob

Update oracle CLOB column with large string


I have a table systesttab that contains a few columns. One of those columns is of type CLOB and it's supposed to hold a string of a base64 encoded image.

CREATE TABLE systesttab(
    ...
    f_picture     CLOB       DEFAULT ' ' NOT NULL,
    ...
)

However, when I try to update the table with a large base64 string (over 100k character), it fails and my python application crashes (even when put in a try...except block).

UPDATE systesttab SET f_picture = 'data:image/png;base64,iVBORw0KGgoASU ...'

I have even tried to cast the value to clob:

UPDATE systesttab SET f_picture = TO_CLOB('data:image/png;base64,iVBORw0KGgoASU ...')

But all I get is this error:

Input string too long, limit 8192

Now, I guess that this is trying to tell me something about the chunk size, but it's not really helpful to me.

enter image description here

How can I update the table with a single statement?

Do I have to declare the table's create statement differently?

If there is a way to get this done in a single statement, it should also work when updating multiple columns on the same table.

Environment: python 3.4 & pyodbc


Solution

  • I have solved this by using SQL Bindings. It seems the character limit does not apply then.

    In python the statement now looks like this:

    pic = 'data:image/png;base64,iVBORw0KGgoASU ...'
    sql = "UPDATE systesttab SET f_picture = ?"
    cursor.execute( sql, [pic] )
    

    This also works fine when updating multiple fields at the same time.