Create database view using IWorkspace.ExecuteSQL?

[bojko108]

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

[BrentHoskisson]

I recommend using C# directly.

First, download and install ODP from this site http://www.oracle.com/technetwork/topics/dotnet/index-085163.html

After the installation, add a reference to the assembly Oracle.DataAccess.dll.

You should be able to do everything in c# now without ESRI in the middle.

Brent Hoskisson

[bojko108]

Thanks, I was hoping to do everything with ArcObjects, without installing other libraries. 

[GKmieliauskas · Accepted solution]

Hi,

Have you tried to split sql statements to few ExecuteSQL parts and do not use ';' at the end of part?

[bojko108]

Thank you :), I've already tried splitting the SQL into separate statements, but didn't remove the semicolon at the end. After I removed it the logic works as expected, without throwing exceptions. Thanks 🙂