Re: insert a well known binary object (WKB)
Anthony Tuininga <[email protected]> Thu, 9 Jul 2020 13:34:07 -0600
| Newsgroups | gmane.comp.python.db.cx-oracle |
|---|---|
| Message-ID | <CAE1XR-6yhxhqXg0PEPsO9C4G+LOaRq2KKgem-n8yOuMWQZqKZg@mail.gmail.com> |
--===============1770271340321091759==
Content-Type: multipart/alternative; boundary="00000000000080a50905aa074fa1"
--00000000000080a50905aa074fa1
Content-Type: text/plain; charset="UTF-8"
Yes, that makes sense. You are informing cx_Oracle that you are binding a
(temporary) BLOB -- which eliminates the error you were getting. Glad you
got it worked out!
On Mon, Jul 6, 2020 at 11:40 AM John Cartwright - NOAA Federal via
cx-oracle-users <cx-oracle-users-5NWGOfrQmneRv+LV9MX5uipxlwaOVQ5f@public.gmane.org> wrote:
> 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
>>
> _______________________________________________
> cx-oracle-users mailing list
> cx-oracle-users-5NWGOfrQmneRv+LV9MX5uipxlwaOVQ5f@public.gmane.org
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>
--00000000000080a50905aa074fa1
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
<div dir=3D"ltr">Yes, that makes sense. You are informing cx_Oracle that yo=
u are binding a (temporary) BLOB -- which eliminates the error you were get=
ting. Glad you got it worked out!</div><br><div class=3D"gmail_quote"><div =
dir=3D"ltr" class=3D"gmail_attr">On Mon, Jul 6, 2020 at 11:40 AM John Cartw=
right - NOAA Federal via cx-oracle-users <<a href=3D"mailto:cx-oracle-us=
[email protected]">cx-oracle-users-5NWGOfrQmneRv+LV9MX5uipxlwaOVQ5f@public.gmane.org</a>> wr=
ote:<br></div><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px=
0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir=3D=
"ltr"><div class=3D"gmail_default" style=3D"font-size:small">Thanks for you=
r reply Anthony.=C2=A0 I seem to have found a workaround by using "cur=
sor.setinputsizes()" but I don't completely understand how that wo=
rks.=C2=A0 See code snippet below and note that I changed to using=C2=A0the=
WKB in the SDO_Geometry constructor rather than the SDO_UTIL.FROM_WKBGEOME=
TRY utility.=C2=A0 Does what I'm doing seem reasonable to you?</div><di=
v class=3D"gmail_default" style=3D"font-size:small"><br></div><div class=3D=
"gmail_default" style=3D"font-size:small">feature =3D data['features=
9;][0]<br>geom =3D arcgis.geometry.Geometry(feature['geometry'])<br=
>wkb =3D geom.WKB<br>objectid =3D get_next_objectid()<br>cursor.setinputsiz=
es(wkb=3Dcx_Oracle.BLOB)<br>cursor.execute("""insert into SU=
RVEY(OBJECTID,SURVEY,SHAPE) values (:objectid, :survey, MDSYS.SDO_GEOMETRY(=
:wkb, 8265))""",=C2=A0</div><div class=3D"gmail_default" sty=
le=3D"font-size:small">=C2=A0 objectid=3Dobjectid, survey=3D'H13195'=
;, wkb=3Dwkb)</div></div><br><div class=3D"gmail_quote"><div dir=3D"ltr" cl=
ass=3D"gmail_attr">On Fri, Jul 3, 2020 at 9:20 AM Anthony Tuininga <<a h=
ref=3D"mailto:[email protected]" target=3D"_blank">anthony.tuining=
[email protected]</a>> wrote:<br></div><blockquote class=3D"gmail_quote" style=
=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding=
-left:1ex"><div dir=3D"ltr">At a guess: you're trying to build an objec=
t which makes use of PL/SQL. PL/SQL doesn't allow strings longer than 3=
2,767 bytes to be used. So you have two possibilities:<div><br></div><div>(=
1) convert :wkb into a temporary CLOB and bind that instead of the string</=
div><div>(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><b=
r></div><div>If neither of those suggestions=C2=A0work for you, let me sugg=
est that 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_Oracle/issues</a> with a standalone test case that demonstrates the iss=
ue. I can then look into it in more detail.</div><div><br></div><div>Anthon=
y</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 c=
x-oracle-users <<a href=3D"mailto:cx-oracle-users-5NWGOfrQmneRv+LV9MX5uipxlwaOVQ5f@public.gmane.org"=
target=3D"_blank">cx-oracle-users-5NWGOfrQmneRv+LV9MX5uipxlwaOVQ5f@public.gmane.org</a>> wrote:<br>=
</div><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;b=
order-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir=3D"ltr"><d=
iv class=3D"gmail_default" style=3D"font-size:small">Hello All,</div><div c=
lass=3D"gmail_default" style=3D"font-size:small"><br></div><div class=3D"gm=
ail_default" style=3D"font-size:small">I have a WKB that I'm trying to =
insert into a SDO_GEOMETRY column.=C2=A0 Something like this:</div><div cla=
ss=3D"gmail_default" style=3D"font-size:small"><br></div><div class=3D"gmai=
l_default" style=3D"font-size:small">cursor.execute("""<br>=
=C2=A0 =C2=A0 insert into SURVEY(OBJECTID,SURVEY,SHAPE)<br>=C2=A0 =C2=A0 va=
lues (:objectid, :survey, SDO_UTIL.FROM_WKBGEOMETRY(:wkb))"""=
;,<br>=C2=A0 =C2=A0 objectid=3Dobjectid, survey=3D'H13195', wkb=3Dw=
kb</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">However, I keep getting the er=
ror:</div><div class=3D"gmail_default" style=3D"font-size:small"><br></div>=
<div class=3D"gmail_default" style=3D"font-size:small">cx_Oracle.DatabaseEr=
ror: ORA-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><di=
v 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 th=
e right direction?</div><div class=3D"gmail_default" style=3D"font-size:sma=
ll"><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><di=
v class=3D"gmail_default" style=3D"font-size:small">--john</div><div class=
=3D"gmail_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>
_______________________________________________<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>
--00000000000080a50905aa074fa1--
--===============1770271340321091759==
Content-Type: text/plain; charset="us-ascii"
MIME-Version: 1.0
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
--===============1770271340321091759==
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
--===============1770271340321091759==--