<div dir="ltr">
<div style="font-size:12.8px;text-decoration-style:initial;text-decoration-color:initial">Hi,</div><div style="font-size:12.8px;text-decoration-style:initial;text-decoration-color:initial">(crossposted from qgis-users, perhaps this is the better place)</div><div style="font-size:12.8px;text-decoration-style:initial;text-decoration-color:initial"><br></div><div style="font-size:12.8px;text-decoration-style:initial;text-decoration-color:initial">i have a problem here with editing an Oracle View:</div><div style="font-size:12.8px;text-decoration-style:initial;text-decoration-color:initial">QGIS 3.2.0-Bonn</div><div style="font-size:12.8px;text-decoration-style:initial;text-decoration-color:initial"><br></div><div style="font-size:12.8px;text-decoration-style:initial;text-decoration-color:initial">The view is defined between a table with geodata and an alphanumeric:</div><div style="font-size:12.8px;text-decoration-style:initial;text-decoration-color:initial"><br></div><div style="font-size:12.8px;text-decoration-style:initial;text-decoration-color:initial">CREATE OR REPLACE FORCE EDITIONABLE VIEW "C ## GIS". "VW_GEO_TEST" ("GID", "PNAME", "GEOM",</div><div style="font-size:12.8px;text-decoration-style:initial;text-decoration-color:initial">CONSTRAINT "VW_TEST_PK" PRIMARY KEY ("GID") DISABLE) AS</div><div style="font-size:12.8px;text-decoration-style:initial;text-decoration-color:initial"> (select b.gid,<span> </span><a href="http://a.name/" target="_blank" style="color:rgb(17,85,204)">a.name</a><span> </span>pname, a.geom</div><div style="font-size:12.8px;text-decoration-style:initial;text-decoration-color:initial">from GEO_QGIS_TEST a, geo_alpha_test b</div><div style="font-size:12.8px;text-decoration-style:initial;text-decoration-color:initial">where to_char (id) = b.gid);</div><div style="font-size:12.8px;text-decoration-style:initial;text-decoration-color:initial"><br></div><div style="font-size:12.8px;text-decoration-style:initial;text-decoration-color:initial"><br></div><div style="font-size:12.8px;text-decoration-style:initial;text-decoration-color:initial">To edit the view I defined an instead of triggers, for insert:</div><div style="font-size:12.8px;text-decoration-style:initial;text-decoration-color:initial"><br></div><div style="font-size:12.8px;text-decoration-style:initial;text-decoration-color:initial">CREATE OR REPLACE EDITIONABLE TRIGGER "C ## GIS". "IOFT_INSERT_VW_GEO_TEST"</div><div style="font-size:12.8px;text-decoration-style:initial;text-decoration-color:initial">INSTEAD OF INSERT ON VW_GEO_TEST</div><div style="font-size:12.8px;text-decoration-style:initial;text-decoration-color:initial">FOR EACH ROW</div><div style="font-size:12.8px;text-decoration-style:initial;text-decoration-color:initial">DECLARE</div><div style="font-size:12.8px;text-decoration-style:initial;text-decoration-color:initial">BEGIN</div><div style="font-size:12.8px;text-decoration-style:initial;text-decoration-color:initial">insert into GEO_QGIS_TEST (id, name, geom) values (to_number (: NEW.gid) ,: NEW.pname,: NEW.geom);</div><div style="font-size:12.8px;text-decoration-style:initial;text-decoration-color:initial">insert into geo_alpha_test (gid, at1) values (: NEW.gid,: NEW.pname);</div><div style="font-size:12.8px;text-decoration-style:initial;text-decoration-color:initial">END ioft_insert_vw_all_abs;</div><div style="font-size:12.8px;text-decoration-style:initial;text-decoration-color:initial">/</div><div style="font-size:12.8px;text-decoration-style:initial;text-decoration-color:initial">ALTER TRIGGER "C ## GIS". "IOFT_INSERT_VW_GEO_TEST" ENABLE;</div><div style="font-size:12.8px;text-decoration-style:initial;text-decoration-color:initial"><br></div><div style="font-size:12.8px;text-decoration-style:initial;text-decoration-color:initial"><br></div><div style="font-size:12.8px;text-decoration-style:initial;text-decoration-color:initial">In the view I can easily insert objects via SQL, they appear correctly in both tables.</div><div style="font-size:12.8px;text-decoration-style:initial;text-decoration-color:initial"><br></div><div style="font-size:12.8px;text-decoration-style:initial;text-decoration-color:initial">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:</div><div style="font-size:12.8px;text-decoration-style:initial;text-decoration-color:initial"><br></div><div style="font-size:12.8px;text-decoration-style:initial;text-decoration-color:initial">==============================<wbr>====</div><div style="font-size:12.8px;text-decoration-style:initial;text-decoration-color:initial">Konnte Änderungen am Layer VW_GEO_TEST nicht festschreiben</div><div style="font-size:12.8px;text-decoration-style:initial;text-decoration-color:initial"><br></div><div style="font-size:12.8px;text-decoration-style:initial;text-decoration-color:initial">Fehler: FEHLER: Ein Objekt nicht hinzugefügt.</div><div style="font-size:12.8px;text-decoration-style:initial;text-decoration-color:initial"> </div><div style="font-size:12.8px;text-decoration-style:initial;text-decoration-color:initial">Datenanbieterfehler:</div><div style="font-size:12.8px;text-decoration-style:initial;text-decoration-color:initial"> Oracle-Fehler beim Attributhinzufügen: Oracle-Fehler: Konnte Objektkennung -4 nicht laden</div><div style="font-size:12.8px;text-decoration-style:initial;text-decoration-color:initial"> SQL: ORA-01445: Kann keine ROWID aus einer Join-View ohne Basistabelle (der View) auswählen oder erfassen</div><div style="font-size:12.8px;text-decoration-style:initial;text-decoration-color:initial"> Konnte Anweisung nicht ausführen</div><div style="font-size:12.8px;text-decoration-style:initial;text-decoration-color:initial"> Fehler: SELECT "GID" FROM "C##GIS"."VW_GEO_TEST" WHERE ROWID=:a</div><div style="font-size:12.8px;text-decoration-style:initial;text-decoration-color:initial"><br></div><div style="font-size:12.8px;text-decoration-style:initial;text-decoration-color:initial">==============================<wbr>====<span style="white-space:pre-wrap"> </span></div><div style="font-size:12.8px;text-decoration-style:initial;text-decoration-color:initial"><br></div><div style="font-size:12.8px;text-decoration-style:initial;text-decoration-color:initial"><br></div><div style="font-size:12.8px;text-decoration-style:initial;text-decoration-color:initial">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.</div><div style="font-size:12.8px;text-decoration-style:initial;text-decoration-color:initial">Unfortunately, this view has no ROWID:</div><div style="font-size:12.8px;text-decoration-style:initial;text-decoration-color:initial"><br></div><div style="font-size:12.8px;text-decoration-style:initial;text-decoration-color:initial">> select a.rowid, a. * from VW_GEO_TEST a;</div><div style="font-size:12.8px;text-decoration-style:initial;text-decoration-color:initial">results</div><div style="font-size:12.8px;text-decoration-style:initial;text-decoration-color:initial">ORA-01445: Can not select or capture a ROWID from a join view without a base table (the view)</div><div style="font-size:12.8px;text-decoration-style:initial;text-decoration-color:initial">01445. 00000 - "can not select ROWID from, or sample, a join view without a key-preserved table"</div><div style="font-size:12.8px;text-decoration-style:initial;text-decoration-color:initial">* Cause:</div><div style="font-size:12.8px;text-decoration-style:initial;text-decoration-color:initial">* Action:</div><div style="font-size:12.8px;text-decoration-style:initial;text-decoration-color:initial">Error in line: 59 Column: 25</div><div style="font-size:12.8px;text-decoration-style:initial;text-decoration-color:initial"><br></div><div style="font-size:12.8px;text-decoration-style:initial;text-decoration-color:initial">Is this so intentional or a mistake of QGIS? Mistake of me?</div><div style="font-size:12.8px;text-decoration-style:initial;text-decoration-color:initial"><br></div><div style="font-size:12.8px;text-decoration-style:initial;text-decoration-color:initial">Looking at the sourcecode at src/providers/oracle/<wbr>qgsoracleprovider.cpp it seems to that this is done intentionally but perhaps changeable. </div><div style="font-size:12.8px;text-decoration-style:initial;text-decoration-color:initial">My poor C++-Skills are not good enough to estimate the effort</div><div style="font-size:12.8px;text-decoration-style:initial;text-decoration-color:initial"><br></div><div style="font-size:12.8px;text-decoration-style:initial;text-decoration-color:initial">I read a similar question on<span> </span><a href="https://gis.stackexchange.com/questions/188339/edits-in-oracle-spatial-layers" target="_blank" style="color:rgb(17,85,204)">https://gis.stackexchange.com/<wbr>questions/188339/edits-in-<wbr>oracle-spatial-layers</a><span> </span>but not a solution for me.</div><div style="font-size:12.8px;text-decoration-style:initial;text-decoration-color:initial"><br></div><div style="font-size:12.8px;text-decoration-style:initial;text-decoration-color:initial">Regards</div><div style="font-size:12.8px;text-decoration-style:initial;text-decoration-color:initial">Thomas</div><div class="gmail-yj6qo gmail-ajU" style="outline:none;padding:10px 0px;width:22px;margin:2px 0px 0px;font-size:12.8px;text-decoration-style:initial;text-decoration-color:initial"><div id="gmail-:wy" class="gmail-ajR" tabindex="0" style="background-color:rgb(241,241,241);border:1px solid rgb(221,221,221);clear:both;line-height:6px;outline:none;width:20px"><img class="gmail-ajT" src="https://ssl.gstatic.com/ui/v1/icons/mail/images/cleardot.gif" style="background: url("//ssl.gstatic.com/ui/v1/icons/mail/ellipsis.png") no-repeat; height: 8px; opacity: 0.3; width: 20px;"></div></div>
<br clear="all"><div><br></div>-- <br><div class="gmail_signature" data-smartmail="gmail_signature"><div dir="ltr"><div><pre><div>
Thomas Baschetti - Systemanalyse Geographische Informationssysteme
Hakenstraße 8D
49074 Osnabrück
Tel: 0541 25 91 90 | mobil 01577 189 25 91
E-Mail: <a href="mailto:info@thomas-baschetti.de" target="_blank">info@thomas-baschetti.de</a>
<a href="http://www.thomas-baschetti.de/" target="_blank">www.thomas-baschetti.de</a>
Ust-IdNr.: DE264355072
</div></pre></div></div></div>
</div>