Solved

How to insert geometry column into Oracle from SQL Executor?

  • 20 February 2020
  • 8 replies
  • 25 views
G

I am using SQL executor to perform insert command to insert table data into Oracle. I have a column with geometry and data type as SDO_Geometry in already existing oracle table. The current geometry I want to insert into oracle is in UTM format. I have converted it using geometry replacer and reprojector in my workspace.

SQL Executor is throwing an error with too long string literals and can not be inserted.

What is the way that I can insert geometry column with UTM coordinates into Oracle SDO_Geometry column?

icon

Best answer by gauripearl 21 February 2020, 18:08

View original

8 replies

david_r
Rank
Userlevel 4

It's known limitation in Oracle that you cannot pass string literals longer than 4000 characters in SQL statements, and of course complex geometries can easily go above that limit.

Have you considered using the DatabaseUpdater rather than the SQLExecutor?

G

It's known limitation in Oracle that you cannot pass string literals longer than 4000 characters in SQL statements, and of course complex geometries can easily go above that limit.

Have you considered using the DatabaseUpdater rather than the SQLExecutor?

I want to insert data into oracle table. I can't see Database Upater have option for that.

G

Is there any way to insert geometries from sql executor.

 

david_r
Rank
Userlevel 4
Sorry, not sure how I got the idea that you were updating rather than inserting.

For simply inserting records, you should look at using the FeatureWriter, probably set to using the Oracle Spatial Object writer. Or have you perhaps already tried it?

 

G
Sorry, not sure how I got the idea that you were updating rather than inserting.

For simply inserting records, you should look at using the FeatureWriter, probably set to using the Oracle Spatial Object writer. Or have you perhaps already tried it?

 

I have tried Oracle spatial object writer and having issues with inserting another column for sequence so I am using sl executor.

david_r
Rank
Userlevel 4

Try using the SQLExecutor to get the next sequence value, then use that in the FeatureWriter. That should work (I've done it several times myself), and you won't need to jump through a lot of hoops to avoid the 4000 character limit in Oracle (I've done that too and it wasn't pretty).

G

Try using the SQLExecutor to get the next sequence value, then use that in the FeatureWriter. That should work (I've done it several times myself), and you won't need to jump through a lot of hoops to avoid the 4000 character limit in Oracle (I've done that too and it wasn't pretty).

Yes it worked using SQL Executor.

G

Geometry is safely added to Oracle from Oracle spatial object writer. I used Oracle spatial object writer to add geometry column into the database.

Reply


Community Stats

28,707
Posts
109,476
Replies
36,551
Members
Terms & ConditionsCookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.