AnsweredAssumed Answered

How can I INSERT INTO using results of a query?

Question asked by luapknarf on Nov 26, 2019
Latest reply on Dec 2, 2019 by luapknarf

Hello,

 

Is it possible to use an st_geometry function that is part of a SELECT statement to INSERT records into an Oracle SDE geodatabase (12g)?  I have constructed the following statement from which I receive Error ORA-00917; missing comma.

 

INSERT INTO gis_table
(objectid, gis_table_id, shape)
VALUES (1, 2, (sde.st_geometry ('polygon((SELECT sde.st_astext(sde.st_buffer(l.shape, 500)) as shapebuffer FROM location.address_point l where l.place_id = 802689))', 2277))

 The part of the query

SELECT sde.st_astext(sde.st_buffer(l.shape, 500)) as shapebuffer FROM location.address_point l where l.place_id = 802689

returns and Oracle type CLOB, which turns out to actually be POLYGON (( 3112886.18568189 10066866.95637797, 3112885.11514598 10066899.65808421, etc etc)), So I see I may be duplicating the keyword POLYGON, but I have changed that and I get other errors.  Or I freely admit I may be using the wrong functions or process to do this.

 

Thanks in advance of the great answers I am sure to get...

Paul Frank

Outcomes