How to use the pl/sql MERGE statement against a versioned view

TheodoreRakel — Tue, 19 Apr 2016 20:36:51 GMT

I have to update a versioned table using data from an unversioned table. These two tables represent customer data and have over 1 million rows in each. The geodatabase is Oracle 11g and the ArcGIS version is 10.2.1. The Oracle MERGE statement seems perfect for this situation. The source is the unversioned table and the target is the version view of the versioned table (ending in _EVW). However, when I try to use the Oracle MERGE statement I get this error:

SQL Error: ORA-01446: cannot select ROWID from, or sample, a view with DISTINCT, GROUP BY, etc.

01446. 00000 - "cannot select ROWID from, or sample, a view with DISTINCT, GROUP BY, etc."

I get this error when the target of the MERGE statement is a versioned view. I don't get it when the target is a unversioned table. Does anyone have experience using the Oracle MERGE statement where the target is a versioned view?

The statement that fails in sql plus is :

MERGE INTO SERVICEDELIVERYPOINT_EVW S

USING (SELECT SITE, ISSEQ, PRIORITY FROM CISCATALOG) C

ON (C.SITE = S.SITEID AND C.ISSEQ = S.SEQUENCE)

WHEN MATCHED THEN

UPDATE SET S.PRIORITY = C.PRIORITY

WHEN NOT MATCHED THEN INSERT (S.SITEID, S.SEQUENCE) VALUES (C.SITE, C.ISSEQ)

topic How to use the pl/sql MERGE statement against a versioned view in Geodatabase Questions

How to use the pl/sql MERGE statement against a versioned view