Hello!
I'm trying to find a way to run SQL statements using ArcObjects from a standalone application. My idea is to create a materialized view in an oracle database, read data from that view, make some calculations and then to export the results to another table. Currently I'm creating the materialized view manually using SQL Developer IDE, before executing the standalone application which will do the rest of the work. I was wondering if it's possible to create the materialized view from the standalone application - using ArcObjects or something else?
My SQL looks like this:
DROP MATERIALIZED VIEW MV_GIS_SUBAREAS;
CREATE MATERIALIZED VIEW MV_GIS_SUBAREAS
REFRESH ON DEMAND AS
SELECT * FROM GDIM.GIS_SUBAREAS;
I've tried to use IWorkspace.ExecuteSQL(), but seems I'm not using it correctly, or the SQL statement, that I'm trying to run, is not supported:
string sql = "[ABOVE SQL]";
IWorkspace workspace = Helpers.GetSourceWorkspace();
workspace.ExecuteSQL(sql);
I receive the following exception when I run the above code: Underlying DBMS error [ORA-00933: SQL command not properly ended]. I've tried splitting the SQL statements into a separate calls: first dropping the view then creating it, but again in the first call to the ExecuteSQL() it throws the same exception.
I'm using ArcGIS 10.8.1 and Oracle 19c.
Thanks, Bogdan