Small demo about sequences and identity columns

A recent twitter discussion lead me to (re)test the syntax a little more around auto-incrementing ID columns that work without triggers. I was especially curious about the LIMIT VALUE option, that I didn’t know before.

identity options

This option is documented (as opposed to RESTART which still seems not to made it into the docs)

Demo 1 – Attach a sequence to a table via the DEFAULT option

The comments explain what I’m trying to do/show.

-- Create some sequences to be used later
create sequence mySeq1;
Sequence MYSEQ1 created.

create sequence mySeq2;
Sequence MYSEQ2 created.

-- Table with PK not tied to a sequence
create table t1 (id  number not null primary key
               , txt varchar2(30)); 
Table T1 created.

-- fill PK using a Sequence
insert into t1 values (mySeq1.nextval, 'Test1');
1 row inserted.

-- set the sequence to a new start value 55
alter sequence mySeq1 restart start with 55;
Sequence MYSEQ1 altered.

-- insert more data
insert into t1 values (mySeq1.nextval, 'Test2');
1 row inserted.

-- check data
select * from t1;

ID TXT
1 Test1
55 Test2

Unfortunately we can not easily change our PK into an identity column.

alter table t1 modify id generated as identity;

ORA-30673: column to be modified is not an identity column

alter table t1 modify id generated by default on null as identity;

ORA-30673: column to be modified is not an identity column

Other syntax variants including using LIMIT VALUE also do not work.

However it is easily possible to use a default value for the column and populate it by an existing (user managed) sequence.

-- Use a default value column and a different sequence
alter table t1 modify id default on null mySeq2.nextval;
Table T1 altered.

-- Trying to insert a values give a UK error, 
-- because we used a fresh sequence which gave us value 1
-- which exists already.
insert into t1 (txt) values ('Test3');
ORA-00001: unique constraint (MYUSER.SYS_C00145531) violated

-- set the sequence to a different value using RESTART
alter sequence mySeq2 restart start with 70;
Sequence MYSEQ2 altered.

insert into t1 (txt) values ('Test3');
1 row inserted.

insert into t1 values (null, 'Test4');
1 row inserted.

insert into t1 values (default, 'Test5');
1 row inserted.

-- Check data
select * from t1;

ID TXT
1 Test1
55 Test2
70 Test3
71 Test4
72 Test5

So Demo 1 shows some commands around normal ID columns that are populated via a default value setting.

Demo 2 – Use identity column from the start

create table t2 (id  number generated always as identity primary key
               , txt varchar2(30)); 
Table T2 created.

-- Try to insert some rows
insert into t2 values (null, 'Test1');
ORA-32795: cannot insert into a generated always identity column

insert into t2 values (default, 'Test2');
1 row inserted.

insert into t2 (txt) values ('Test3');
1 row inserted.

-- Try to insert a record via plsql
declare 
  r t2%rowtype;
begin 
  r.txt := 'Test4';
  insert into t2 values r;
end;
/

ORA-32795: cannot insert into a generated always identity column

-- hide the ID column then try row insert by record again
alter table t2 modify id invisible;
Table T2 altered.

declare 
  r t2%rowtype;
begin 
  r.txt := 'Test5';
  insert into t2 values r;
end;
/
PL/SQL procedure successfully completed.

-- Check data
select * from t2;

TXT
Test2
Test3
Test5

The ID is missing? Well it is there.

select id, txt from t2;

ID TXT
1 Test2
2 Test3
3 Test5

Hiding the ID column is one possible way to make certain types of inserts work again. Not my preferred way thou.

-- make column visible again
alter table t2 modify id visible;
Table T2 altered.

-- logical column order now is changed. This can impact DML that depend on column order!
select * from t2;

TXT ID
Test2 1
Test3 2
Test5 3

-- restore original column order by toggling other columns invisible
alter table t2 modify txt invisible;
Table T2 altered.

alter table t2 modify txt visible;
Table T2 altered.

-- modify to allow other data values (instead of generated ALWAYS)
alter table t2 modify id generated by default on null as identity ;
Table T2 altered.

-- Try inserts again that didn't work previously
insert into t2 values (null, 'Test1');
1 row inserted.

declare 
  r t2%rowtype;
begin 
  r.txt := 'Test4';
  insert into t2 values r;
end;
/
PL/SQL procedure successfully completed.

-- Check data
select * from t2;

ID TXT
1 Test2
2 Test3
3 Test5
4 Test1
5 Test4

-- add custom IDs
insert into t2 values (-1, 'Test6');
1 row inserted.

insert into t2 values (100, 'Test7');
1 row inserted.

-- Check data
select * from t2;

ID TXT
1 Test2
2 Test3
3 Test5
4 Test1
5 Test4
-1 Test6
100 Test7

So now we can insert other ID values into that column, even thou, we have an identity column. Eventually this will lead to a UK error when the value created by the Oracle managed sequence reaches 100.

This can be solved with the LIMIT VALUE clause

-- Check current high water mark (last value) of sequence identity column
select identity_column, data_default from user_tab_columns where table_name = 'T2' and column_name = 'ID';

IDENTITY_COLUMN	DATA_DEFAULT
-------
YES	"MYUSER"."ISEQ$$_258212".nextval

-- using the sequence name from the previous select
select last_number, cache_size 
from user_sequences where sequence_name = 'ISEQ$$_258212';

LAST_NUMBER	CACHE_SIZE
-------
24	20

-- reset Identity column to the highest value that is currently in the table 
alter table t2 modify ID  generated by default on null as identity start with limit value;
Table T2 altered. 

-- Check high water mark again 
select last_number, cache_size 
from user_sequences where sequence_name = 'ISEQ$$_258212';

LAST_NUMBER	CACHE_SIZE
-------
101	20

-- insert 
insert into t2 values (default, 'Test8');
1 row inserted.

-- Check data
select * from t2;

ID TXT
1 Test2
2 Test3
3 Test5
4 Test1
5 Test4
-1 Test6
100 Test7
101 Test8

Success!

So Demo 2 showed commands how to work with an identity column. Including the possibility to switch it from generated always as identity to generated by default on null. Which opens up some ways to manipulate the data inside this managed ID column.

Remember we can not influence (alter) the Oracle managed sequence that drives the identity column directly. But we can change its properties via ALTER TABLE MODIFY .

cleanup code

-- cleanup
drop sequence mySeq1;
drop sequence mySeq2;
drop table t1 purge;
drop table t2 purge;

How to avoid misleading plsql warning PLW-07206: analysis suggests that the assignment to ‘V_DUMMY’ may be unnecessary

PLW-messages are warnings issued by the PL/SQL compiler.

The PLW-07206 warning tells us that we have some code (an assignment statement) that is not needed. Usually because the target of the assignment is not used later anywhere.

PLW-07206: analysis suggests that the assignment to <something> may be unnecessary

Problem demonstration

Example 1)

Here is a very reduced demonstration of the issue.

First we enable the PL/SQL warnings. I assume this setting is still in place for all following examples.

-- set plsql warning on
alter session set PLSQL_WARNINGS = 'ENABLE:ALL';

In SQL Developer we can set the warning level in the preferences, but this setting is not applied to code running in a SQL worksheet as a script.

Then a function is created.

create or replace function dummy_existsCheck return boolean 
authid definer
is
      v_dummy number(1);
begin
      select 1
      into v_dummy
      from all_objects
      where 1=2;
      return true;  
      -- Do something here if a record was found
exception  
     when no_data_found then
      -- Do something else here if NO record was found
      return false;
end dummy_existsCheck;
/  

Compiling the function shows the warning.

Function DUMMY_EXISTSCHECK compiled
LINE/COL ERROR

6/7 PLW-07206: analysis suggests that the assignment to 'V_DUMMY' may be unnecessary

Example 2)

Here is an example where the warning is correctly added.

create or replace function dummy_existsCheck return boolean 
authid definer
is
   v_dummy varchar2(1);
begin
   v_dummy := 'B';
     
   -- Do something here without using v_dummy
   return null; 
end dummy_existsCheck;
/ 

Function DUMMY_EXISTSCHECK compiled
LINE/COL ERROR

6/5 PLW-07206: analysis suggests that the assignment to 'V_DUMMY' may be unnecessary

The difference between the two examples is that a SELECT statement in PL/SQL needs an INTO clause. And because of that this dummy variable is needed. So the warning in the first example is wrong.

The compiler is aware of this situation already. For some strange reason the warning goes away when we change the datatype of the variable from number to varchar2. Which is also the suggested workaround.

Workaround for example 1)

create or replace function dummy_existsCheck return boolean 
authid definer
is
      v_dummy varchar(1);
begin
      select 'x'
      into v_dummy
      from all_objects
      where 1=2;
      return true;  
      -- Do something here if a record was found
exception  
     when no_data_found then
      -- Do something else here if NO record was found
      return false;
end dummy_existsCheck;
/  

Function DUMMY_EXISTSCHECK compiled

No warning issued.

Obviously this is a bug. Although a very minor one.

Example 3)

Here is another slightly more complex example. The same message appears when we assign the result of a function. Because a function always has a return value, we need to assign it to some variable, even if the variable later in the code is not used anymore.

create or replace function dummy_getID(p_table in varchar2) return number
authid definer
is
      v_result number(1);
begin
      select 1
      into v_result
      from user_tables
      where table_name = p_table;
      
      return v_result;
end dummy_getID;
/  

create or replace function dummy_existsCheck return boolean
authid definer
is
  v_tab varchar2(30) := 'DUMMY';
  v_dummy number(1);
begin
  v_dummy := dummy_getID(v_tab);
  return true;
exception
  when no_data_found then
    dbms_output.put_line('Table '||v_tab||' does not exist.');
    return false;
end dummy_existsCheck;
/ 

Function DUMMY_GETID compiled
Function DUMMY_EXISTSCHECK compiled
LINE/COL ERROR

7/3 PLW-07206: analysis suggests that the assignment to 'V_DUMMY' may be unnecessary

Workaround for example 3)

Again, changing the variable to a varchar2 data type, avoids the warning.

create or replace function dummy_existsCheck return boolean
authid definer
is
  v_tab varchar2(30) := 'DUMMY';
  v_dummy varchar2(1);
begin
  v_dummy := dummy_getID(v_tab);
  return true;
exception
  when no_data_found then
    dbms_output.put_line('Table '||v_tab||' does not exist.');
    return false;
end dummy_existsCheck;
/ 

Function DUMMY_EXISTSCHECK compiled

Datatype check

Surprisingly other numeric data types also avoid the warning. It seems that it happens mostly for number(x).

The following data types did not produce the warning:

• varchar2(1)
• varchar(1)
• char(1)
• pls_integer
• binary_integer
• number
• float

The following data types did produce the warning:

• number(1)
• number(38)
• decimal
• integer
• integer(1)
• simple_integer

Conclusion

Compiler warnings are a good thing. Wrong or misleading warnings are not.

PLW-07206 can (sometimes) be avoided by changing the data type of the assignment target from number(x) to some other data type.

Cleanup demo code

drop function dummy_existsCheck;
drop function dummy_getID;