Skip to main content
Solved

How to insert geometry column into Oracle from SQL Executor?

  • February 20, 2020
  • 8 replies
  • 140 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?

Best answer by gauripearl

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

View original
Did this help you find an answer to your question?

8 replies

david_r
Evangelist
  • david_rEvangelist
  • February 20, 2020

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
  • gauripearl
  • Author
  • February 20, 2020
david_r wrote:

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
  • gauripearl
  • Author
  • February 20, 2020

Is there any way to insert geometries from sql executor.

 

david_r
Evangelist
  • david_rEvangelist
  • February 20, 2020
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
  • gauripearl
  • Author
  • February 20, 2020
david_r wrote:
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
Evangelist
  • david_rEvangelist
  • February 20, 2020

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
  • gauripearl
  • Author
  • February 20, 2020
david_r wrote:

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
  • gauripearl
  • Author
  • Best Answer
  • February 21, 2020

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


Helpful Members This Week

Community Stats

30,957
Posts
117,323
Replies
38,743
Members

Latest FME

Terms & ConditionsCookie settings

Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie 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.