topic How I can insert SDO_GEOMETRY using tOracleOutput in Talend Studio

How I can insert SDO_GEOMETRY using tOracleOutput

tornilleiro — Wed, 22 Mar 2017 09:11:51 GMT

Hi,
I'm trying to insert a SDO_GEOMETRY object/element in one column using tOracleOutput.
My first option was to insert the rows using the tOracleRow component using the following query string: INSERT INTO SAMPLE_POINTS values(1,SDO_GEOMETRY(?)). This option worked properly but the performance is really bad.
My second option was to use the component tOracleOutput to perform a batchupdate but it wasn't work:
I have tried to use different kind of objects,byte[], string for passing the data to SDO_GEOMETRY column but always get an error.
Reading Oracle Documentation Oracle JGeometry Api I see that is necessary create an STRUCT object, but I don't know how I can include this code in Talend.

 /// writing a geometry back to database
     PreparedStatement ps = connection.prepareStatement(
         "UPDATE states set geometry=? where name='Florida'");
     //convert JGeometry instance to DB STRUCT using the SDO pickler
     STRUCT obj = JGeometry.store(connection, j_geom);
     ps.setObject(1, obj);
     ps.execute();

Have someone any clue how to work with Oracle spatial types and Talend (tOracleOutput)?
Have someone any clue how to use custom code in the PreparedStatement for the tOracleOutput?

Thank you in advance.
David

Re: How I can insert SDO_GEOMETRY using tOracleOutput

Anonymous — Thu, 23 Mar 2017 07:48:20 GMT

Hi,
The "Use PreparedStatement" option has been added to all db row components (Advanced settings section).
For your performance issue, have you already checked "Commit every" option to ensure transaction quality and above all better performance on executions?
Best regards
Sabrina

Re: How I can insert SDO_GEOMETRY using tOracleOutput

vrobine — Fri, 07 Aug 2020 08:34:14 GMT

Hello,

Do you find any solution to fill the Oracle SDO_GEOMETRY type with the tOracleOutput.

I still trying to get the SDO_GEOMETRY type on my list ou data type.

I have add the object type SDO_GEOMETRY to the mapping_oracle.xml file, on the project stting mapping directory and the configuration mapping directory, but no result, nothing appears.

I'm assuming the SDI component works with Oracle, but I can only find old post and no example on how to implement with Oracle.

Is it possible to get the SDO_GEOMTRY type on the list of DB TYPE?

has anyone implemented it successfully?

Thanks a lot for your help,

Vincent

Re: How I can insert SDO_GEOMETRY using tOracleOutput

tornilleiro — Wed, 08 Sep 2021 09:14:28 GMT

Hi Vincent,

I've just seen today your message... It is possible to add the SDO_GEOMETRY type to work with ORACLE using the mapping.xml file of Talend. May be you already have discovered, long time ago that you post the message...