Thread: [cx-oracle-users] Trying to copy the value of a SDO_GEOMETRY column from one table to another

Brought to you by: atuining

cx-oracle-users

[cx-oracle-users] Trying to copy the value of a SDO_GEOMETRY column from one table to another

From: Michael H. <mhu...@gm...> - 2016-02-07 14:13:33

Hi,

I'm querying from a table with a SDO_GEOMETRY column and want to store the
content of this column to another table. If I do so, I'm receiving the
following exception:

cx_Oracle.NotSupportedError: Variable_TypeByValue(): unhandled data type
cx_Oracle.OBJECT

Please find a basic example below:

Python 3.5.1 (default, Jan 20 2016, 15:13:56)
[GCC 4.8.5 20150623 (Red Hat 4.8.5-4)] on linux
Type "help", "copyright", "credits" or "license" for more information.
>>> import cx_Oracle
>>> con = cx_Oracle.connect('user1/pass1@hostname:1521/TESTDB')
>>> cur = con.cursor()
>>>
>>> cur.execute("SELECT * FROM USER1.SOURCE_TABLE WHERE id = 1")
<cx_Oracle.Cursor on <cx_Oracle.Connection to user1@hostname:1521/TESTDB>>
>>> source_data = cur.fetchall()
>>>
>>> print(source_data)
[(1, 'testvalue', <cx_Oracle.OBJECT object at 0x7f39e2561c70>)]
>>>
>>> cur.execute("INSERT INTO USER1.TARGET_TABLE VALUES (:1, :2, :3 )",
[source_data[0][0], source_data[0][1], source_data[0][2] ])
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
cx_Oracle.NotSupportedError: Variable_TypeByValue(): unhandled data type
cx_Oracle.OBJECT
>>>

Is it not supported to write the content of a SDO_GEOMETRY column back to
an Oracle database? Thanks in advance!

Best,
Micheal

Re: [cx-oracle-users] Trying to copy the value of a SDO_GEOMETRY column from one table to another

From: Jani T. <re...@gm...> - 2016-02-08 09:33:44

Hi,

Unfortunately cx_Oracle doesn't support writing user types to database, 
only reading.

There are few options to overcome your problem:

Make output as varchar/clob like wkt. This is relatively slow.

Another option is to use nice approach described here: 
http://www.tolon.co.uk/2012/09/geometry-objects-across-dblink/


On 07.02.2016 16:13, Michael Huber wrote:
> Hi,
>
> I'm querying from a table with a SDO_GEOMETRY column and want to store 
> the content of this column to another table. If I do so, I'm receiving 
> the following exception:
>
> cx_Oracle.NotSupportedError: Variable_TypeByValue(): unhandled data 
> type cx_Oracle.OBJECT
>
> Please find a basic example below:
>
> Python 3.5.1 (default, Jan 20 2016, 15:13:56)
> [GCC 4.8.5 20150623 (Red Hat 4.8.5-4)] on linux
> Type "help", "copyright", "credits" or "license" for more information.
> >>> import cx_Oracle
> >>> con = cx_Oracle.connect('user1/pass1@hostname:1521/TESTDB')
> >>> cur = con.cursor()
> >>>
> >>> cur.execute("SELECT * FROM USER1.SOURCE_TABLE WHERE id = 1")
> <cx_Oracle.Cursor on <cx_Oracle.Connection to user1@hostname:1521/TESTDB>>
> >>> source_data = cur.fetchall()
> >>>
> >>> print(source_data)
> [(1, 'testvalue', <cx_Oracle.OBJECT object at 0x7f39e2561c70>)]
> >>>
> >>> cur.execute("INSERT INTO USER1.TARGET_TABLE VALUES (:1, :2, :3 )", 
> [source_data[0][0], source_data[0][1], source_data[0][2] ])
> Traceback (most recent call last):
>   File "<stdin>", line 1, in <module>
> cx_Oracle.NotSupportedError: Variable_TypeByValue(): unhandled data 
> type cx_Oracle.OBJECT
> >>>
>
> Is it not supported to write the content of a SDO_GEOMETRY column back 
> to an Oracle database? Thanks in advance!
>
> Best,
> Micheal
>
>
> ------------------------------------------------------------------------------
> Site24x7 APM Insight: Get Deep Visibility into Application Performance
> APM + Mobile APM + RUM: Monitor 3 App instances at just $35/Month
> Monitor end-to-end web transactions and take corrective actions now
> Troubleshoot faster and improve end-user experience. Signup Now!
> http://pubads.g.doubleclick.net/gampad/clk?id=272487151&iu=/4140
>
>
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users

-- 
Jani Tiainen

Re: [cx-oracle-users] Trying to copy the value of a SDO_GEOMETRY column from one table to another

From: Anthony T. <ant...@gm...> - 2016-02-08 13:54:44

Hi Michael,

Jani is right in that cx_Oracle currently does not support binding
objects....but I am working on changing that and hope to have that
capability implemented soon. :-) I'll reply back again when that is done
but if you need it immediately you'll have to follow the workaround Jani
suggested.

Anthony

On Mon, Feb 8, 2016 at 2:33 AM, Jani Tiainen <re...@gm...> wrote:

> Hi,
>
> Unfortunately cx_Oracle doesn't support writing user types to database,
> only reading.
>
> There are few options to overcome your problem:
>
> Make output as varchar/clob like wkt. This is relatively slow.
>
> Another option is to use nice approach described here:
> http://www.tolon.co.uk/2012/09/geometry-objects-across-dblink/
>
>
>
> On 07.02.2016 16:13, Michael Huber wrote:
>
> Hi,
>
> I'm querying from a table with a SDO_GEOMETRY column and want to store the
> content of this column to another table. If I do so, I'm receiving the
> following exception:
>
> cx_Oracle.NotSupportedError: Variable_TypeByValue(): unhandled data type
> cx_Oracle.OBJECT
>
> Please find a basic example below:
>
> Python 3.5.1 (default, Jan 20 2016, 15:13:56)
> [GCC 4.8.5 20150623 (Red Hat 4.8.5-4)] on linux
> Type "help", "copyright", "credits" or "license" for more information.
> >>> import cx_Oracle
> >>> con = cx_Oracle.connect('user1/pass1@hostname:1521/TESTDB')
> >>> cur = con.cursor()
> >>>
> >>> cur.execute("SELECT * FROM USER1.SOURCE_TABLE WHERE id = 1")
> <cx_Oracle.Cursor on <cx_Oracle.Connection to user1@hostname:1521/TESTDB>>
> >>> source_data = cur.fetchall()
> >>>
> >>> print(source_data)
> [(1, 'testvalue', <cx_Oracle.OBJECT object at 0x7f39e2561c70>)]
> >>>
> >>> cur.execute("INSERT INTO USER1.TARGET_TABLE VALUES (:1, :2, :3 )",
> [source_data[0][0], source_data[0][1], source_data[0][2] ])
> Traceback (most recent call last):
>   File "<stdin>", line 1, in <module>
> cx_Oracle.NotSupportedError: Variable_TypeByValue(): unhandled data type
> cx_Oracle.OBJECT
> >>>
>
> Is it not supported to write the content of a SDO_GEOMETRY column back to
> an Oracle database? Thanks in advance!
>
> Best,
> Micheal
>
>
> ------------------------------------------------------------------------------
> Site24x7 APM Insight: Get Deep Visibility into Application Performance
> APM + Mobile APM + RUM: Monitor 3 App instances at just $35/Month
> Monitor end-to-end web transactions and take corrective actions now
> Troubleshoot faster and improve end-user experience. Signup Now!http://pubads.g.doubleclick.net/gampad/clk?id=272487151&iu=/4140
>
>
>
> _______________________________________________
> cx-oracle-users mailing lis...@li...https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>
>
> --
> Jani Tiainen
>
>
> ------------------------------------------------------------------------------
> Site24x7 APM Insight: Get Deep Visibility into Application Performance
> APM + Mobile APM + RUM: Monitor 3 App instances at just $35/Month
> Monitor end-to-end web transactions and take corrective actions now
> Troubleshoot faster and improve end-user experience. Signup Now!
> http://pubads.g.doubleclick.net/gampad/clk?id=272487151&iu=/4140
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>
>

Re: [cx-oracle-users] Trying to copy the value of a SDO_GEOMETRY column from one table to another

From: Anthony T. <ant...@gm...> - 2016-02-12 02:00:23

All,

I have implemented the ability to bind objects which you can see in the
checked-in code today. If you wish to try it out and let me know if your
case still does not work, that would be appreciated. I will be working on
an example for SDO_GEOMETRY so any one who has a good suggestion on what
ought to be in said example, that would also be appreciated.

You can take a look at the ObjectVar test case and the documentation which
shows the new capabilities but here is a brief example.

typeObj = connection.gettype(name)
obj = typeObj.newobject()
obj.ATTR1 = 5
obj.ATTR2 = "Some string"
cursor.callproc("someproc", (obj, 5))

Anthony

On Mon, Feb 8, 2016 at 6:54 AM, Anthony Tuininga <ant...@gm...
> wrote:

> Hi Michael,
>
> Jani is right in that cx_Oracle currently does not support binding
> objects....but I am working on changing that and hope to have that
> capability implemented soon. :-) I'll reply back again when that is done
> but if you need it immediately you'll have to follow the workaround Jani
> suggested.
>
> Anthony
>
> On Mon, Feb 8, 2016 at 2:33 AM, Jani Tiainen <re...@gm...> wrote:
>
>> Hi,
>>
>> Unfortunately cx_Oracle doesn't support writing user types to database,
>> only reading.
>>
>> There are few options to overcome your problem:
>>
>> Make output as varchar/clob like wkt. This is relatively slow.
>>
>> Another option is to use nice approach described here:
>> http://www.tolon.co.uk/2012/09/geometry-objects-across-dblink/
>>
>>
>>
>> On 07.02.2016 16:13, Michael Huber wrote:
>>
>> Hi,
>>
>> I'm querying from a table with a SDO_GEOMETRY column and want to store
>> the content of this column to another table. If I do so, I'm receiving the
>> following exception:
>>
>> cx_Oracle.NotSupportedError: Variable_TypeByValue(): unhandled data type
>> cx_Oracle.OBJECT
>>
>> Please find a basic example below:
>>
>> Python 3.5.1 (default, Jan 20 2016, 15:13:56)
>> [GCC 4.8.5 20150623 (Red Hat 4.8.5-4)] on linux
>> Type "help", "copyright", "credits" or "license" for more information.
>> >>> import cx_Oracle
>> >>> con = cx_Oracle.connect('user1/pass1@hostname:1521/TESTDB')
>> >>> cur = con.cursor()
>> >>>
>> >>> cur.execute("SELECT * FROM USER1.SOURCE_TABLE WHERE id = 1")
>> <cx_Oracle.Cursor on <cx_Oracle.Connection to user1@hostname
>> :1521/TESTDB>>
>> >>> source_data = cur.fetchall()
>> >>>
>> >>> print(source_data)
>> [(1, 'testvalue', <cx_Oracle.OBJECT object at 0x7f39e2561c70>)]
>> >>>
>> >>> cur.execute("INSERT INTO USER1.TARGET_TABLE VALUES (:1, :2, :3 )",
>> [source_data[0][0], source_data[0][1], source_data[0][2] ])
>> Traceback (most recent call last):
>>   File "<stdin>", line 1, in <module>
>> cx_Oracle.NotSupportedError: Variable_TypeByValue(): unhandled data type
>> cx_Oracle.OBJECT
>> >>>
>>
>> Is it not supported to write the content of a SDO_GEOMETRY column back to
>> an Oracle database? Thanks in advance!
>>
>> Best,
>> Micheal
>>
>>
>> ------------------------------------------------------------------------------
>> Site24x7 APM Insight: Get Deep Visibility into Application Performance
>> APM + Mobile APM + RUM: Monitor 3 App instances at just $35/Month
>> Monitor end-to-end web transactions and take corrective actions now
>> Troubleshoot faster and improve end-user experience. Signup Now!http://pubads.g.doubleclick.net/gampad/clk?id=272487151&iu=/4140
>>
>>
>>
>> _______________________________________________
>> cx-oracle-users mailing lis...@li...https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>>
>>
>> --
>> Jani Tiainen
>>
>>
>> ------------------------------------------------------------------------------
>> Site24x7 APM Insight: Get Deep Visibility into Application Performance
>> APM + Mobile APM + RUM: Monitor 3 App instances at just $35/Month
>> Monitor end-to-end web transactions and take corrective actions now
>> Troubleshoot faster and improve end-user experience. Signup Now!
>> http://pubads.g.doubleclick.net/gampad/clk?id=272487151&iu=/4140
>> _______________________________________________
>> cx-oracle-users mailing list
>> cx-...@li...
>> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>>
>>
>

Re: [cx-oracle-users] Trying to copy the value of a SDO_GEOMETRY column from one table to another

From: Anthony T. <ant...@gm...> - 2016-02-23 23:25:12

Hi Michael,

The current code in the source repository will allow you to do this now.
There is also an example that demonstrates how to create an SDO_GEOMETRY
object from scratch. This will (at some point) become 5.3 but you can use
it today if you're up for building it yourself -- since you're on Linux
that isn't too difficult.

Anthony

On Sun, Feb 7, 2016 at 7:13 AM, Michael Huber <mhu...@gm...> wrote:

> Hi,
>
> I'm querying from a table with a SDO_GEOMETRY column and want to store the
> content of this column to another table. If I do so, I'm receiving the
> following exception:
>
> cx_Oracle.NotSupportedError: Variable_TypeByValue(): unhandled data type
> cx_Oracle.OBJECT
>
> Please find a basic example below:
>
> Python 3.5.1 (default, Jan 20 2016, 15:13:56)
> [GCC 4.8.5 20150623 (Red Hat 4.8.5-4)] on linux
> Type "help", "copyright", "credits" or "license" for more information.
> >>> import cx_Oracle
> >>> con = cx_Oracle.connect('user1/pass1@hostname:1521/TESTDB')
> >>> cur = con.cursor()
> >>>
> >>> cur.execute("SELECT * FROM USER1.SOURCE_TABLE WHERE id = 1")
> <cx_Oracle.Cursor on <cx_Oracle.Connection to user1@hostname:1521/TESTDB>>
> >>> source_data = cur.fetchall()
> >>>
> >>> print(source_data)
> [(1, 'testvalue', <cx_Oracle.OBJECT object at 0x7f39e2561c70>)]
> >>>
> >>> cur.execute("INSERT INTO USER1.TARGET_TABLE VALUES (:1, :2, :3 )",
> [source_data[0][0], source_data[0][1], source_data[0][2] ])
> Traceback (most recent call last):
>   File "<stdin>", line 1, in <module>
> cx_Oracle.NotSupportedError: Variable_TypeByValue(): unhandled data type
> cx_Oracle.OBJECT
> >>>
>
> Is it not supported to write the content of a SDO_GEOMETRY column back to
> an Oracle database? Thanks in advance!
>
> Best,
> Micheal
>
>
> ------------------------------------------------------------------------------
> Site24x7 APM Insight: Get Deep Visibility into Application Performance
> APM + Mobile APM + RUM: Monitor 3 App instances at just $35/Month
> Monitor end-to-end web transactions and take corrective actions now
> Troubleshoot faster and improve end-user experience. Signup Now!
> http://pubads.g.doubleclick.net/gampad/clk?id=272487151&iu=/4140
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>
>