[Qgis-user] Can't access Oracle sequences

[PIERRE Sylvain]

Hi Jürgen,

There's a trigger on the Oracle table, that's why I didn't understand... 
Saving edits failed for several tables. But these tables have a particular trigger (because of a third party application)
With standard trigger, editing works well!
Particular trigger is written like that:
create trigger BAMBOUE_GEOM.T_SECTEUR_EPANDAGE_GEOM_B_I before insert
on BAMBOUE_GEOM.SECTEUR_EPANDAGE_GEOM for each row
begin
  if :new.ID_SECTEUR_EPANDAGE_GEOM is null then
    select S_SECTEUR_EPANDAGE_GEOM.NEXTVAL
    into :new.ID_SECTEUR_EPANDAGE_GEOM
    from dual;
  end if;
end;

I think this is the reason why editing failed...

Sylvain

-----Message d'origine-----
De : qgis-user-bounces at lists.osgeo.org [mailto:qgis-user-bounces at lists.osgeo.org] De la part de Jürgen E. Fischer
Envoyé : lundi 16 mars 2015 22:51
À : qgis-user at lists.osgeo.org
Objet : Re: [Qgis-user] Can't access Oracle sequences

Hi Sylvain,

On Mon, 16. Mar 2015 at 18:01:35 +0100, PIERRE Sylvain wrote:
> With Postgis/QGIS configuration I can manage sequences on primary key 
> when editing  new object (editing forms show a 
> nextval(‘xxxxxxxxxx’::regclass) formula in pk field) With my 
> Oracle/QGIS conf I can’t manage sequences, and editing failed when I want to save.
> Where is the problem?
> Is it an Oracle provider issue ?

Not really.

In PostgreSQL the sequence value is retrieved by a default expression.  In Oracle sequences used to be retrieved and assigned to the new record using a before insert trigger.  But in Oracle 12c you can also use default values to retrieve and assign the next sequence value.  I suppose that works in QGIS too, but I don't currently have setup a 12c to verify.

So unless you have 12c with a default value, you have to have a trigger to deal with assigning the key value.



Jürgen

-- 
Jürgen E. Fischer           norBIT GmbH             Tel. +49-4931-918175-31
Dipl.-Inf. (FH)             Rheinstraße 13          Fax. +49-4931-918175-50
Software Engineer           D-26506 Norden             http://www.norbit.de
QGIS release manager (PSC)  Germany                    IRC: jef on FreeNode