[QGIS-Developer] Error while editing Oracle View without ROWID

[Thomas Baschetti]

 Hi,
(crossposted from qgis-users, perhaps this is the better place)

i have a problem here with editing an Oracle View:
QGIS 3.2.0-Bonn

The view is defined between a table with geodata and an alphanumeric:

CREATE OR REPLACE FORCE EDITIONABLE VIEW "C ## GIS". "VW_GEO_TEST" ("GID",
"PNAME", "GEOM",
CONSTRAINT "VW_TEST_PK" PRIMARY KEY ("GID") DISABLE) AS
  (select b.gid, a.name pname, a.geom
from GEO_QGIS_TEST a, geo_alpha_test b
where to_char (id) = b.gid);


To edit the view I defined an instead of triggers, for insert:

CREATE OR REPLACE EDITIONABLE TRIGGER "C ## GIS". "IOFT_INSERT_VW_GEO_TEST"
INSTEAD OF INSERT ON VW_GEO_TEST
FOR EACH ROW
DECLARE
BEGIN
insert into GEO_QGIS_TEST (id, name, geom) values ​​(to_number (: NEW.gid)
,: NEW.pname,: NEW.geom);
insert into geo_alpha_test (gid, at1) values ​​(: NEW.gid,: NEW.pname);
END ioft_insert_vw_all_abs;
/
ALTER TRIGGER "C ## GIS". "IOFT_INSERT_VW_GEO_TEST" ENABLE;


In the view I can easily insert objects via SQL, they appear correctly in
both tables.

If I digitize a new object via QGIS and then save the changes, the data is
also saved in both tables, but then QGIS issues an error message:

==================================
Konnte Änderungen am Layer VW_GEO_TEST nicht festschreiben

Fehler: FEHLER: Ein Objekt nicht hinzugefügt.

Datenanbieterfehler:
      Oracle-Fehler beim Attributhinzufügen: Oracle-Fehler: Konnte
Objektkennung -4 nicht laden
    SQL: ORA-01445: Kann keine ROWID aus einer Join-View ohne Basistabelle
(der View) auswählen oder erfassen
    Konnte Anweisung nicht ausführen
    Fehler: SELECT "GID" FROM "C##GIS"."VW_GEO_TEST" WHERE ROWID=:a

==================================


The error message is somewhat misleading here, the objects were actually
saved in the database, but then QGIS probably tries to read the object
again via a ROWID and fails.
Unfortunately, this view has no ROWID:

> select a.rowid, a. * from VW_GEO_TEST a;
results
ORA-01445: Can not select or capture a ROWID from a join view without a
base table (the view)
01445. 00000 - "can not select ROWID from, or sample, a join view without a
key-preserved table"
* Cause:
* Action:
Error in line: 59 Column: 25

Is this so intentional or a mistake of QGIS? Mistake of me?

Looking at the sourcecode at src/providers/oracle/qgsoracleprovider.cpp it
seems to that this is done intentionally but perhaps changeable.
My poor C++-Skills are not good enough to estimate the effort

I read a similar question on https://gis.stackexchange.com/
questions/188339/edits-in-oracle-spatial-layers but not a solution for me.

Regards
Thomas


-- 


Thomas Baschetti - Systemanalyse Geographische Informationssysteme
Hakenstraße 8D
49074 Osnabrück

Tel: 0541 25 91 90 | mobil 01577 189 25 91
E-Mail: info at thomas-baschetti.dewww.thomas-baschetti.de
Ust-IdNr.: DE264355072
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/qgis-developer/attachments/20180716/1d3a5021/attachment-0001.html>