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ı