Re: insert a well known binary object (WKB)

--===============6396188798811550622==
Content-Type: multipart/alternative; boundary="000000000000c35eb305a9c9065e"

--000000000000c35eb305a9c9065e
Content-Type: text/plain; charset="UTF-8"

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 <[email protected]>
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-oracle-users-5NWGOfrQmneRv+LV9MX5uipxlwaOVQ5f@public.gmane.org> 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-oracle-users-5NWGOfrQmneRv+LV9MX5uipxlwaOVQ5f@public.gmane.org
>> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>>
> _______________________________________________
> cx-oracle-users mailing list
> cx-oracle-users-5NWGOfrQmneRv+LV9MX5uipxlwaOVQ5f@public.gmane.org
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>

--000000000000c35eb305a9c9065e
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div class=3D"gmail_default" style=3D"font-size:small">Tha=
nks for your reply Anthony.=C2=A0 I seem to have found a workaround by usin=
g &quot;cursor.setinputsizes()&quot; but I don&#39;t completely understand =
how that works.=C2=A0 See code snippet below and note that I changed to usi=
ng=C2=A0the WKB in the SDO_Geometry constructor rather than the SDO_UTIL.FR=
OM_WKBGEOMETRY utility.=C2=A0 Does what I&#39;m doing seem reasonable to yo=
u?</div><div class=3D"gmail_default" style=3D"font-size:small"><br></div><d=
iv class=3D"gmail_default" style=3D"font-size:small">feature =3D data[&#39;=
features&#39;][0]<br>geom =3D arcgis.geometry.Geometry(feature[&#39;geometr=
y&#39;])<br>wkb =3D geom.WKB<br>objectid =3D get_next_objectid()<br>cursor.=
setinputsizes(wkb=3Dcx_Oracle.BLOB)<br>cursor.execute(&quot;&quot;&quot;ins=
ert into SURVEY(OBJECTID,SURVEY,SHAPE) values (:objectid, :survey, MDSYS.SD=
O_GEOMETRY(:wkb, 8265))&quot;&quot;&quot;,=C2=A0</div><div class=3D"gmail_d=
efault" style=3D"font-size:small">=C2=A0 objectid=3Dobjectid, survey=3D&#39=
;H13195&#39;, wkb=3Dwkb)</div></div><br><div class=3D"gmail_quote"><div dir=
=3D"ltr" class=3D"gmail_attr">On Fri, Jul 3, 2020 at 9:20 AM Anthony Tuinin=
ga &lt;<a href=3D"mailto:[email protected]">anthony.tuininga@gmail=
.com</a>&gt; wrote:<br></div><blockquote class=3D"gmail_quote" style=3D"mar=
gin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1=
ex"><div dir=3D"ltr">At a guess: you&#39;re trying to build an object which=
 makes use of PL/SQL. PL/SQL doesn&#39;t allow strings longer than 32,767 b=
ytes to be used. So you have two possibilities:<div><br></div><div>(1) conv=
ert :wkb into a temporary CLOB and bind that instead of the string</div><di=
v>(2) make the entire statement an anonymous PL/SQL block which will cause =
cx_Oracle to turn :wkb into a temporary CLOB internally</div><div><br></div=
><div>If neither of those suggestions=C2=A0work for you, let me suggest tha=
t you post an issue here:=C2=A0<a href=3D"https://github.com/oracle/python-=
cx_Oracle/issues" target=3D"_blank">https://github.com/oracle/python-cx_Ora=
cle/issues</a> with a standalone test case that demonstrates the issue. I c=
an then look into it in more detail.</div><div><br></div><div>Anthony</div>=
</div><br><div class=3D"gmail_quote"><div dir=3D"ltr" class=3D"gmail_attr">=
On Tue, Jun 30, 2020 at 7:04 PM John Cartwright - NOAA Federal via cx-oracl=
e-users &lt;<a href=3D"mailto:cx-oracle-users-5NWGOfrQmneRv+LV9MX5uipxlwaOVQ5f@public.gmane.org" target=
=3D"_blank">cx-oracle-users-5NWGOfrQmneRv+LV9MX5uipxlwaOVQ5f@public.gmane.org</a>&gt; wrote:<br></div><=
blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-l=
eft:1px solid rgb(204,204,204);padding-left:1ex"><div dir=3D"ltr"><div clas=
s=3D"gmail_default" style=3D"font-size:small">Hello All,</div><div class=3D=
"gmail_default" style=3D"font-size:small"><br></div><div class=3D"gmail_def=
ault" style=3D"font-size:small">I have a WKB that I&#39;m trying to insert =
into a SDO_GEOMETRY column.=C2=A0 Something like this:</div><div class=3D"g=
mail_default" style=3D"font-size:small"><br></div><div class=3D"gmail_defau=
lt" style=3D"font-size:small">cursor.execute(&quot;&quot;&quot;<br>=C2=A0 =
=C2=A0 insert into SURVEY(OBJECTID,SURVEY,SHAPE)<br>=C2=A0 =C2=A0 values (:=
objectid, :survey, SDO_UTIL.FROM_WKBGEOMETRY(:wkb))&quot;&quot;&quot;,<br>=
=C2=A0 =C2=A0 objectid=3Dobjectid, survey=3D&#39;H13195&#39;, wkb=3Dwkb</di=
v><div class=3D"gmail_default" style=3D"font-size:small">)<br></div><div cl=
ass=3D"gmail_default" style=3D"font-size:small"><br></div><div class=3D"gma=
il_default" style=3D"font-size:small">However, I keep getting the error:</d=
iv><div class=3D"gmail_default" style=3D"font-size:small"><br></div><div cl=
ass=3D"gmail_default" style=3D"font-size:small">cx_Oracle.DatabaseError: OR=
A-01461: can bind a LONG value only for insert into a LONG column<br></div>=
<div class=3D"gmail_default" style=3D"font-size:small"><br></div><div class=
=3D"gmail_default" style=3D"font-size:small"><br></div><div class=3D"gmail_=
default" style=3D"font-size:small">Can someone please point me in the right=
 direction?</div><div class=3D"gmail_default" style=3D"font-size:small"><br=
></div><div class=3D"gmail_default" style=3D"font-size:small">Thanks!</div>=
<div class=3D"gmail_default" style=3D"font-size:small"><br></div><div class=
=3D"gmail_default" style=3D"font-size:small">--john</div><div class=3D"gmai=
l_default" style=3D"font-size:small"><br></div></div>
_______________________________________________<br>
cx-oracle-users mailing list<br>
<a href=3D"mailto:cx-oracle-users-5NWGOfrQmneRv+LV9MX5uipxlwaOVQ5f@public.gmane.org" target=3D"_blank">=
cx-oracle-users-5NWGOfrQmneRv+LV9MX5uipxlwaOVQ5f@public.gmane.org</a><br>
<a href=3D"https://lists.sourceforge.net/lists/listinfo/cx-oracle-users" re=
l=3D"noreferrer" target=3D"_blank">https://lists.sourceforge.net/lists/list=
info/cx-oracle-users</a><br>
</blockquote></div>
_______________________________________________<br>
cx-oracle-users mailing list<br>
<a href=3D"mailto:cx-oracle-users-5NWGOfrQmneRv+LV9MX5uipxlwaOVQ5f@public.gmane.org" target=3D"_blank">=
cx-oracle-users-5NWGOfrQmneRv+LV9MX5uipxlwaOVQ5f@public.gmane.org</a><br>
<a href=3D"https://lists.sourceforge.net/lists/listinfo/cx-oracle-users" re=
l=3D"noreferrer" target=3D"_blank">https://lists.sourceforge.net/lists/list=
info/cx-oracle-users</a><br>
</blockquote></div>

--000000000000c35eb305a9c9065e--


--===============6396188798811550622==
Content-Type: text/plain; charset="us-ascii"
MIME-Version: 1.0
Content-Transfer-Encoding: 7bit
Content-Disposition: inline


--===============6396188798811550622==
Content-Type: text/plain; charset="us-ascii"
MIME-Version: 1.0
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

_______________________________________________
cx-oracle-users mailing list
cx-oracle-users-5NWGOfrQmneRv+LV9MX5uipxlwaOVQ5f@public.gmane.org
https://lists.sourceforge.net/lists/listinfo/cx-oracle-users

--===============6396188798811550622==--