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

[Frank v. L. <fvl...@ca...>]

Thanks,

This was very helpful. It was indeed the ";" and also the size of the 
binary. I can now write and read CLOB objects.

I guess MySQL is a little bit more forgiving in entering this type of data.

Frank.
Amaury Forgeot d'Arc wrote:
> 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,
> 


-- 
--------------------------------------
Frank van Lingen
California Institute of Technology
CA 91125 Pasadena
United States
--------------------------------------
Mail Code:356-48
bld      :340 Lauritsen
email    :fvl...@ca...
im(aim)  :marcellus0872
tel      :(+1) 626 395 3862
mobile   :(+1) 310 968 5584
url      :http://www.van-lingen.name/
--------------------------------------