Versioned data - view on base table or EVW views?

[robert_at_work]

Hi there,

we have our data in Oracle (11.2.)and the feature classes are registered as versioned.

I need to create a view to get data from multiple tables in order to export the aggregated data conveniently to Excel. Export needs to be done in ArcView (10.5.1). We also use third party app.

When I create the view based on the versioned tables then newly added data is missing and deleted data is included in my view. Lets call that "view1".
When I create the view based on the EVW views of the tables I get the data exactly how I want them including newly added data but without deleted data. Lets call that "view2".

Now, here comes the problem. The third party app comes with a nice query tool for tables. But when I perform a query on view2 I get an ESRI error message -

esriDataSourceGDB.SdeWorkspace.1: column not found [ORA-00904: "V_30674"."VERTRAG_REF": invalid identifier][VMG.vmg_v_dbv_vertrag_eig][STATE_ID = 16579]

When I run the same query on view1 there is no error message (but my results might be wrong as new data is missing, deleted data is shown). Also - when I look which query has been executed by Oracle I do not see any difference between the executed query for view1 and view2.

Anyone having had a similar issue?
Any advice?

Thanks, Rob

[Asrujit_SenGupta]

Is Oracle Extproc configured for this Geodatabase? Try configuring that and then check with View2.

Configure the Oracle extproc to access the geodatabase with SQL—Help | ArcGIS Desktop 

[robert_at_work]

No, Extproc is not configured. It would involve quite some work and money to get it done as Oracle is managed by kind of external service provider. Is that just a general suggestion by yours or do you have specific knowledge that configuring extproc  solves the problem?

[robert_at_work]

forgot to mention - no spatial data involved here....

[George_Thompson]

When you look at the versioned view it is looking at the data for that version. For example, you are connecting to EVW and looking at that data. It will be looking at the DEFAULT version of the data. Any edits that have been added in another version, i.e. EDIT, will not show up till a reconcile/post is performed.

Can you query all the data in the EVW with the tool and no extra joins? If that does not work, then you will need to configure the EXTPROC as the version views relay on the complete configuration of the geodatabase (including the EXTPROC).

[robert_at_work]

@George_Thompson-esristaff - I did a quick test and created a view for an existing EVW view:

create view vmg_v_dbv_vertrag_test1 as select * from vmg_dbv_vertrag_evw;‍

The third party query tool returns the same error message as before. So, basically I cannot query any EVW based data source.

Now, just to give that little bit of extra information on how versioning works in our setup: whenever we edit data the third party app we use creates a new version automaticlly and after editing is finished it closes the version and perfoms an reconcile/post all in the background. We do not have any "open" versions for more than a few seconds - completely hidden to the user. The only thing we - as users/admins - need to do is do a compress on a regular basis. So when I query EVW views I see everything I need.

back to the problem - why do I need to have EXPROC configured? The docs meantion only that EXPROC is required if you need to run (external) SQL on the spatial data. The tables I'm working on right now do not have spatial data at all. I dont mind setting up EXPROC - its just that I cant do it myself and it will cost us having it done. I want to be sure that missing EXPROC configuration is just more then a good guess as cause for the problem.

[robert_at_work]

I did a bit of further testing....I dont think it has anything to do with EXPROC. When I create a simple view like this:

create or replace test1 as select * from simple_table_evw;

and then query "test1" with the third party query tool it gets executed without any error message.

But my view is more complex and contains joins. I also guess that the third party query tool actually executes two queries - the one I can see successfully executed in Oracle (by looking for it in  v$sql view) and a  second, subsequent one that fails due to a complex view defintion and maybe a bug the query tool and for which I get the error message. I now have one option left - rewriting the join and instead of using ANSI SQL apply the old Oracle Join syntax...but thats something for next week ...good weekend all readers, rob

[JoshuaBixby]

If you open SQLPLUS and query against vmg_v_dbv_vertrag_test1 does it generate the same error?  I am wondering if it is something the third-party app is doing and not anything with the views themselves.

[robert_at_work · Accepted solution]

it seems the message is related to the thrid party tool. the query tool needs to be configured and you need to link the query to the data by means of an SQL statement(see below).

That SQL statement itself is a join between my view and some other tables. If I refer to the other tables in my join I get the error message but if I refer to the EVWs view of these tables I dont get an error message and everything appears to work like a charm.

This will work:

<sql>
				select distinct vmg_v_dbv_vertrag_eig.objectid							
				from vmg_v_dbv_vertrag_eig  left join vmgr_vertrag_eigentuemer_evw  on vmg_v_dbv_vertrag_eig.globalid = vmgr_vertrag_eigentuemer_evw.vertrag_ref
				left join vmg_eigentuemer_evw on vmgr_vertrag_eigentuemer_evw.eigentuemer_ref = vmg_eigentuemer_evw.globalid						
				where 1=1
				{and upper(vmg_v_dbv_vertrag_eig.dokument_nr) like upper([cmbdoknr])}
				...
				
			</sql>‍‍‍‍‍‍‍‍‍

But this one will throw the error message:

<sql>
				select distinct vmg_v_dbv_vertrag_eig.objectid							
				from vmg_v_dbv_vertrag_eig  left join vmgr_vertrag_eigentuemer  on vmg_v_dbv_vertrag_eig.globalid = vmgr_vertrag_eigentuemer.vertrag_ref
				left join vmg_eigentuemer on vmgr_vertrag_eigentuemer.eigentuemer_ref = vmg_eigentuemer.globalid						
				where 1=1
				{and upper(vmg_v_dbv_vertrag_eig.dokument_nr) like upper([cmbdoknr])}
				...
			</sql>‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

thanks to all of you for your suggestions.