topic Re: Create database view using IWorkspace.ExecuteSQL? in ArcObjects SDK Questions

Create database view using IWorkspace.ExecuteSQL?

bojko108 — Wed, 21 Sep 2022 17:19:11 GMT

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

Re: Create database view using IWorkspace.ExecuteSQL?

BrentHoskisson — Wed, 21 Sep 2022 19:32:12 GMT

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

Re: Create database view using IWorkspace.ExecuteSQL?

GKmieliauskas — Thu, 22 Sep 2022 05:18:14 GMT

Hi,

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

Re: Create database view using IWorkspace.ExecuteSQL?

bojko108 — Thu, 22 Sep 2022 12:45:48 GMT

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

Re: Create database view using IWorkspace.ExecuteSQL?

bojko108 — Thu, 22 Sep 2022 12:48:41 GMT

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 🙂