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