Re: [cx-oracle-users] insert a well known binary object (WKB)

[John C. - N. F. <joh...@no...>]

Thanks for your reply Anthony.  I seem to have found a workaround by using
"cursor.setinputsizes()" but I don't completely understand how that works.
See code snippet below and note that I changed to using the WKB in the
SDO_Geometry constructor rather than the SDO_UTIL.FROM_WKBGEOMETRY
utility.  Does what I'm doing seem reasonable to you?

feature = data['features'][0]
geom = arcgis.geometry.Geometry(feature['geometry'])
wkb = geom.WKB
objectid = get_next_objectid()
cursor.setinputsizes(wkb=cx_Oracle.BLOB)
cursor.execute("""insert into SURVEY(OBJECTID,SURVEY,SHAPE) values
(:objectid, :survey, MDSYS.SDO_GEOMETRY(:wkb, 8265))""",
  objectid=objectid, survey='H13195', wkb=wkb)

On Fri, Jul 3, 2020 at 9:20 AM Anthony Tuininga <ant...@gm...>
wrote:

> At a guess: you're trying to build an object which makes use of PL/SQL.
> PL/SQL doesn't allow strings longer than 32,767 bytes to be used. So you
> have two possibilities:
>
> (1) convert :wkb into a temporary CLOB and bind that instead of the string
> (2) make the entire statement an anonymous PL/SQL block which will cause
> cx_Oracle to turn :wkb into a temporary CLOB internally
>
> If neither of those suggestions work for you, let me suggest that you post
> an issue here: https://github.com/oracle/python-cx_Oracle/issues with a
> standalone test case that demonstrates the issue. I can then look into it
> in more detail.
>
> Anthony
>
> On Tue, Jun 30, 2020 at 7:04 PM John Cartwright - NOAA Federal via
> cx-oracle-users <cx-...@li...> wrote:
>
>> Hello All,
>>
>> I have a WKB that I'm trying to insert into a SDO_GEOMETRY column.
>> Something like this:
>>
>> cursor.execute("""
>>     insert into SURVEY(OBJECTID,SURVEY,SHAPE)
>>     values (:objectid, :survey, SDO_UTIL.FROM_WKBGEOMETRY(:wkb))""",
>>     objectid=objectid, survey='H13195', wkb=wkb
>> )
>>
>> However, I keep getting the error:
>>
>> cx_Oracle.DatabaseError: ORA-01461: can bind a LONG value only for insert
>> into a LONG column
>>
>>
>> Can someone please point me in the right direction?
>>
>> Thanks!
>>
>> --john
>>
>> _______________________________________________
>> cx-oracle-users mailing list
>> cx-...@li...
>> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>>
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>