SQL*Plus variables – Oracle Next ID Number without a Database Sequence

Traditionally the mechanism to retrieve near sequential numbers for the purposes of a database table PK is to use a database sequence. Occasionally there might be holes in the Oracle sequence value due to selecting the next value from the sequence and the value not saved or the transaction rolled back, sequence cache size and RAC, the database instance restarting, and probably a few other things that slip my mind just now. Regardless the proven and efficient approach is to use sequenceName.NEXTVAL as shown in the code snippet below.

SQL>
SQL>
SQL> create sequence mikes start with 100;

Sequence created.

SQL> select mikeseq.nextval from dual;

   NEXTVAL
----------
       100

SQL> select mikeseq.nextval from dual;

   NEXTVAL
----------
       101

SQL>

Since Oracle 12c (12.1 specifically), finally Oracle caught up with almost all other RDBMS vendors and provided IDENTITY columns for the same purpose although some superficial investigation shows that the ID value returned is also sourced from an underlying sequence too, it’s just that the ID column value can be populated automagically.

Many/most corporate databases, those implemented prior to 12.1, will still source ID values/numbers, often used for primary keys in lookup tables and the like, from a database sequence of sorts. When a new/updated version of Oracle is released, no-one will go through the existing implementation and upgrade proven production code just to use a new feature given that there is none or little business benefit. Furthermore you would have to be brave to use any new Oracle feature as, as a general rule of thumb, new features in Oracle never work properly and it often feels like Oracle don’t test their products thoroughly and wait for the user base to complain and report bugs. Put another way, new Oracle RDBMS features are very often littered with bugs (it took Oracle till version 9.2 to get the ANSI join syntax working reliably after release in 9.1; even now the implementation is not reliable).

One of my clients releases new product features weekly with lookup ID column values retrieved from a database sequence. If the table name is salesregion or producttype then the sequence name, one for each table, might be named salesregion_seq or producttype_seq. Although a single database sequence would have sufficed for these lookup tables, the convention is one per table and  for reasons of performance in an OLTP system, arguably the approach of having a sequence per table is defensible.

Occasionally there is a scenario where, for some reason unbeknown to anyone, there are lookup tables devoid of a corresponding sequence and a new value/row needs to be added to the underlying table. Although arguments of technical debt are very valid, it is important not to make a meal out of things …. for these types of trivial BAU ticket items, just adding the new row to the lookup table, closing out the ticket, and sending for QA/UAT is the effort expected from the developer. Of course things can be refactored such as adding a new database sequence, or perhaps table structure redesign to use the (now not so new) IDENTITY column, or bar the potential for collisions using the inbuilt sys_guid() function, but these are all bigger tech-only task for another day and also likely to involve a bigger impact analysis, and the nightmare that is having QA and regulatory departments sticking their oar in. Furthermore changes of this type should address all database tables and sequences in one tranche and not one table or database sequence at a time spread out over the next decade. The two approaches to obtaining a new ID column for a database table, leaving it numeric and an integer, are a) adding the new lookup ID with a magic number in the deployment scripts, eg. 102, 103, 104 and so on, or b) using an ID value based on the existing table ID values,  eg. adding 1 to the existing maximum ID number. Both these approaches have obvious concerns, the former being that the ID numbers found in UAT and other staging environments may not be consistent meaning that a deployment script executed in a UAT environment may execute without event however fail during PROD script rollout. The second approach has concerns focusing on potential full table or index scans, or scripts that retrieve only a partial set of data, and therefore a max ID value that does not reflect the full table due to row-level-security (RLS) (rebranded due to significant functional enhancements as virtual-private-database [VPD]).

For the purposes of this blog however, and laying out my stall and the potential concerns of the reader, let’s assume that this is a simple lookup table devoid of a corresponding sequence and IDENTITY column and the executing process has access to the full resultset, the question is how to get the next maximum ID value from the underlying table.

Retrieving the maximum value from some database table column is trivial, as is adding 1 to a numeric value. What is typically seen in the wild in these scenarios is where this maximum value (+1) is stored and used in some PL/SQL variable (think SELECT sequenceName.nextVal INTO someVariable FROM DUAL), or as a variable in a loop. Rarely seen in the wild are retaining the variable value, the state, within an SQL*Plus variable. This is, as the title of this blog article would indicate, the crux of this blog article. I am writing this blog article too so that the next time someone at the same client asks me the same question, I can send them a link to this blog article. This will save me time, save me from having to write deviations of the same script repeatedly, on what is a most boring subject.

SQL>
SQL>
SQL> create table mikeTest as select rownum id from dual connect by rownum<=5;

Table created.

SQL> select * from mikeTest;

        ID
----------
         1
         2
         3
         4
         5


SQL>
SQL>
SQL> column mikeId new_value mikeId
SQL> select max(id)+1 mikeId from mikeTest;

    MIKEID
----------
         6

SQL> insert into mikeTest(id) values(&mikeId);
old:   1: insert into mikeTest(id) values (&mikeId);
new:   1: insert into mikeTest(id) values(       6);

1 row created.

SQL>
SQL> insert into mikeTest(id) values(&mikeId);
old: 1: insert into mikeTest(id) values (&mikeId);
new: 1: insert into mikeTest(id) values(         7);

1 row created.

SQL> select * from mikeTest order by id asc;

        ID
----------
         1
         2
         3
         4
         5
         6
         7

7 rows selected.

SQL>
SQL>

There you go, short and simple, but the point is and remains that the ID value is stored in an SQL*Plus variable, can be used more than once if necessary, and without the necessity to use PL/SQL or database sequences. Of course none of this would be required if a GUID were used as the ID value or the sequence existed from the outset, but this has been previously covered vide supra. Of course its usage also relies in this case that SQL*Plus is used for deployment.

— Published by Mike, Sonntag 20:31:41 20 Nov 2022 (CEST)