Resetting system generated sequence numbers for identity columns

Exported and imported a table containing an identity column and the last number conflicts with the data?

No problem, just execute the following:

alter table table_name modify column_name generated as identity start with limit value;

Oracle will scan the table for the maximum value and set the last number to that number + increment.

Thanks to the following article of Jonathan Lewis for explaining how to do it, I believe that the documentation of Oracle 19 is missing this info whilst Oracle 23 docs are mentioning this option.

ORA-00942: table or view does not exist on inserting rows with a user other than the table owner

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.

As of Oracle 12.1 it became possible to create tables with IDENTITY columns, that is a numeric column where the value is automatically generated using a system created sequence.

In Oracle 12.2, and perhaps also in 12.1, one could also define a numeric column with a default value based on some sequence.nextval.

The result is similar but there is a subtle difference:
the behavior of an INSERT statement executed by a user that is not the owner of the table.

In the case of an identity column, if the table owner grants insert on the table to a user or role X,  X would be able to insert a row into the table and the same holds if one creates a before insert trigger in the old fashion.

The surprise comes when using the sequence as a DEFAULT value.

SQL Error: ORA-00942: table or view does not exist

The error message is misleading, it refers to the fact that X hasn't got the GRANT SELECT on the sequence, not on the table.

 

CREATE TABLE TEST_GRANT_ID(
  ID    INTEGER GENERATED ALWAYS AS IDENTITY,
  TEXT  VARCHAR2(10)
);

CREATE SEQUENCE TEST_GRANT_SEQ_DFT;

CREATE TABLE TEST_GRANT_DFT(
  ID    INTEGER DEFAULT TEST_GRANT_SEQ_DFT.NEXTVAL,
  TEXT  VARCHAR2(10)
);

CREATE SEQUENCE TEST_GRANT_SEQ_TRG;

CREATE TABLE TEST_GRANT_TRG(
  ID    INTEGER,
  TEXT  VARCHAR2(10)
);

CREATE OR REPLACE TRIGGER BI_TEST_GRANT_TRG  
   before insert on TEST_GRANT_TRG 
   for each row 
begin  
   if inserting then 
      if :NEW."ID" is null then 
         select TEST_GRANT_SEQ_TRG.nextval into :NEW."ID" from dual; 
      end if; 
   end if; 
end;
/

GRANT INSERT ON TEST_GRANT_ID TO &&USER_X.;
GRANT INSERT ON TEST_GRANT_DFT TO &USER_X.;
GRANT INSERT ON TEST_GRANT_TRG TO &USER_X.;

Now you can connect as USER_X and see the different behavior when inserting the rows.

See message translations for ORA-00942 and search additional resources.

ORA-02287: sequence number not allowed here

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.

This error can be easily reproduced by executing a non-sense query like:

create sequence test_seq;

select 1 as a
 from dual
order by test_seq.nextval;

ORA-02287: sequence number not allowed here

But we can also see that a query like the following is raising the same problem:

select 1 as A, test_seq.nextval as B
 from dual
union all
select 2 as A, test_seq.nextval as B
 from dual;

ORA-02287: sequence number not allowed here

Oracle doesn't like the fact that we are using the sequence in conjunction with a set operator like UNION ALL (or any other set operator of your liking...).

Note however that i can rewrite the query above in a slightly more convoluted way:

select a, test_seq.nextval as b
from (
 select 1 as a
   from dual
 union all
 select 2 as a
  from dual
 order by a);

A B
- -
1 1
2 2

I added the ORDER BY clause inside the query because you might want to spawn sequence numbers according to the ordering of some other column (either ascending or descending), so here is the correct way of achieving that result.
Please note that Oracle will raise ORA-02287 again if you put the ORDER BY clause in the outer query:

select a, test_seq.nextval as b
from (
 select 1 as a
   from dual
 union all
 select 2 as a
  from dual)
order by a;

ORA-02287: sequence number not allowed here

See message translations for ORA-02287 and search additional resources.

ORA-08002

Believe it or not, for many years i have been wondering about what's for pseudo-column currval, that is useful for retrieving an oracle sequence's current value.

I mean, i understand how it works, but i always failed to find a scenario where its usage was necessary.

Until the day i created a trigger before dropping the content of three tables in a cascade fashion and save the deleted rows in a custom recycle bin.

The hierarchical structure of the tables is as follows:
a parent table with two children tables with a foreign key pointing to the parent's primary key, with cascade delete option enabled on both constraints.

This allows me to delete everything concerning a certain entry by deleting the parent row.
Since i want to backup the deleted records in another structure in case of a user's mistake, i created a different primary key that i populate using an oracle sequence, which allows me to handle even multiple deletions of the same entry over time.

The primary key value then is retrieved inside a BEFORE DELETE trigger on the parent table.
But how to retrieve that value inside the triggers associated to the children tables?
Here is finally where currval pseudo column comes into play.

As each call to pseudo column nextval would increase the counter, i cannot use it inside the children tables trigger. I must use currval instead.

Now you may wonder what has ORA-08002 to do with this?

Well, the fact initially i completely forgot to handle the case of a user deleting a simple record from one of the children tables, which is perfectly acceptable, resulting in this exception:

ORA-8002 sequence string.CURRVAL is
not yet defined in this session

So, all i had to do, was to trap ORA-08002 inside each of the children tables triggers and do nothing, as follows:

declare
currval_not_def     exception;
pragma exception_init(currval_not_def, -8002);
begin
...(trigger body)...
exception
when currval_not_def then null;
...
end;

See message translations for ORA-08002 and search additional resources.

---

ORA-08002: la sequenza .CURRVAL non è ancora definita in questa sessione
ORA-08002: la secuencia .CURRVAL todavía no se ha definido en esta sesión
ORA-08002: encara no s'ha definit el valor .CURRVAL pel seqüenciador per a aquesta sessió
ORA-08002: séquence .CURRVAL pas encore définie dans cette session
ORA-08002: Sequenz .CURRVAL ist in dieser Session noch nicht definiert
ORA-08002: ακολουθία .CURRVAL δεν έχει ορισθεί ακόμα σε αυτή την περίοδο λειτουργίας
ORA-08002: sekvensen .CURRVAL er endnu ikke defineret i denne session
ORA-08002: sekvensen .CURRVAL ännu inte definierad i denna session
ORA-08002: sekvensen .CURRVAL er ikke definert ennå i denne sesjonen
ORA-08002: sarjaa .CURRVAL ei ole vielä määritetty tässä istunnossa
ORA-08002: .CURRVAL szekvencia még nem definiált ebben a munkamenetben
ORA-08002: secvenţa .CURRVAL nu este încă definită în această sesiune
ORA-08002: Reeks .CURRVAL is nog niet gedefinieerd in deze sessie.
ORA-08002: a seqüência .CURRVAL ainda não foi definido nesta sessão
ORA-08002: sequência .CURRVAL ainda não está definida nesta sessão
ORA-08002: последов. .CURRVAL еще не определен в этом сеансе
ORA-08002: sekvence .CURRVAL není v této relaci ještě definována
ORA-08002: .CURRVAL nie je ešte definovaný v tejto relácii
ORA-08002: sekwencja .CURRVAL nie jest jeszcze zdefiniowana w tej sesji
ORA-08002: sıra .CURRVAL henüz bu oturumda tanımlanmadı