How can I INSERT INTO using results of a query?

Question

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

PaulFrank · Answer

Yes I am still seeing the error when I try to insert a buffer manually, so that would seem to be the issue, although I tried to add a fairly large polygon that I shortened for viewing purposes:

INSERT INTO planningcadastre.demolition_notifications_gis
(OBJECTID, NOTIFICATION_ID, SHAPE)
VALUES (4, 5, sde.st_geometry('polygon ((3122386.18571073 10066866.95637797, 3122364.77499239 10067520.98755021, 3122300.63437265 10068172.21820313, 3122194.03845723 10068817.85962838, 3122045.44393815 10069455.14673963, 3121855.48696898 10070081.35119589, 3121624.98119640 10070693.79058780, ...3122300.63437265 10065561.69455279, 3122364.77499239 10066212.92520571, 3122386.18571073 10066866.95637797)',2277))

Christian_Wells · Answer

Glad to hear we made some progress! On the insufficient permissions it might be the tables from the LOCATION or PLANNINGCADASTRE schema. Can you verify that you can do a SELECT one each table without the ST call? Can you verify you can do an INSERT on DEMOLITION_NOTIFICATIONS_GIS without the ST call?

PaulFrank · Answer

Thanks everyone.  I modified the query to this and good news - I dont receive a syntax error, just ORA-01031: insufficient privileges!  I suspect I dont have those for the geometry_st functions?  I am asking my IT about that.  BTW, I also tried it without the where clause as suggested, and I get the same error.

INSERT INTO planningcadastre.demolition_notifications_gis
(OBJECTID, NOTIFICATION_ID, SHAPE)
select 1,2,sde.st_buffer(l.shape, 500) from location.address_point l where l.place_id = 802689;