Re: [cx-oracle-users] How to insert a Blob or Clob using cx_oracle?

[Amaury F. d'A. <ama...@gm...>]

Hello,

Frank van Lingen wrote:
> I am trying to insert a file into oracle. I first do a
> base64.encodestring and then insert it. The sql string for it looks like
> the one below. If I copy this string to a sqlplus command line it is
> being inserted. However if I do it within Python using cx_oracle it
> gives me an error:
> cx_Oracle.DatabaseError: ORA-00911: invalid character

First try to remove the semicolon at the end of your query.
The semicolon is not part of the SQL language; it is used by sqlplus
and pl/sql to delimit statements.

Otherwise your code seems correct. I suspect there will be a limit
(65000?) to the length of the string you can insert, though.

To pass bigger values you could use something like:
curs = orcl.cursor()
curs.setinputsizes(
    workflow_spec=cx_Oracle.CLOB,
    policies=cx_Oracle.CLOB,
    parameters=cx_Oracle.CLOB)
sqlStr="INSERT INTO
ws_request(string_id,owner,workflow_spec,policies,parameters,request_type)
VALUES ('myOwnRequestID0','fvlingen',:workflow_spec,:policies,:parameters,'event')"
curs.execute(sqlStr,
    workflow_spec="<long text>",
    policies="<another long text>",
    parameters="<another very long text>")

Hope this helps,

-- 
Amaury Forgeot d'Arc