3

Finding an Identity Column Sequence in Oracle

 2 years ago
source link: https://mikesmithers.wordpress.com/2021/12/29/finding-an-identity-column-sequence-in-oracle/
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
neoserver,ios ssh client

Finding an Identity Column Sequence in Oracle

Posted on December 29, 2021

It’s that time again. It’s the week between Christmas and New Year and I’ve been grappling with the advanced mathematics required to calculate when the next refuse collection is due.
I’ve got some code that does the calculation and inserts the collection date into a table, which I’m running on Oracle 18cXE.
Automating the test for this code is a bit tricky because the table uses an identity column as it’s key.
Therefore, I need to figure out the name of the sequence used to generate the identity value and find it’s current value in the session to identify and test the record we’ve just inserted.

What I’m going to cover here is :

  • my search for the sequence associated with an identity column
  • predicting the name of the identity column sequence without having to look it up
  • just using a named sequence instead of an identity column but without having to write a trigger

Fortified by a traditional Betwixmas breakfast of cold sprouts and trifle, I’m ready to get cracking…

Normally, the bin collections happen on a Wednesday. If there are any public holidays in the seven days prior to the collection, it moves back one day per holiday. There are no collections at the weekend so the collection day would be moved to the following Monday if it were otherwise to fall on a Saturday or Sunday.

To start with then, we have a table which holds the Bank Holiday dates :

create table bank_holidays(
    holiday_date date primary key,
    holiday_name varchar2(25))
/

insert into bank_holidays( holiday_date, holiday_name)
values( date '2021-01-01', 'New Year');

insert into bank_holidays( holiday_date, holiday_name)
values( date '2021-04-02', 'Good Friday');

insert into bank_holidays( holiday_date, holiday_name)
values( date '2021-04-05', 'Easter Monday');

insert into bank_holidays( holiday_date, holiday_name)
values( date '2021-05-03', 'Early May Bank Holiday');

insert into bank_holidays( holiday_date, holiday_name)
values( date '2021-05-31', 'Spring Bank Holiday');

insert into bank_holidays( holiday_date, holiday_name)
values( date '2021-08-30', 'Summer Bank Holiday');
    
insert into bank_holidays( holiday_date, holiday_name)
values( date '2021-12-27', 'Christmas Bank Holiday');

insert into bank_holidays( holiday_date, holiday_name)
values( date '2021-12-28', 'Boxing Day Holiday');

insert into bank_holidays( holiday_date, holiday_name)
values( date '2022-01-03', 'New Year Bank Holiday');

commit;

We then have a table into which the collection days are inserted. This table has an identity column :

create table bin_days(
id number generated by default on null as identity primary key,
weekday varchar2(25) not null,
collection_date date not null)
/

The procedure to populate this table looks like this :

create or replace procedure calculate_bin_day( i_date in date)
is
v_offset pls_integer;
v_new_date date;
begin
select count(*)
into v_offset
from bank_holidays
where holiday_date between i_date - 7 and i_date;
v_new_date := i_date + v_offset;
if to_char(v_new_date, 'DY') in ('SAT', 'SUN') then
v_new_date := next_day( v_new_date, 'MONDAY');
end if;
insert into bin_days( weekday, collection_date)
values(to_char(v_new_date, 'DY'), v_new_date);
end calculate_bin_day;
/

What’s that ? This example is intentionally convoluted so that I can play with Identity columns ? Wherever did you get that idea ?

I want to write an anonymous block to test the procedure.
Once we’ve executed the procedure in the test, we’ll have access to the sequence’s CURRVAL value in the session.
What we need to know is what the sequence is called. Bear in mind that the test may need to run in multiple pre-production environments ( e.g. DEV, SIT, UAT) and there’s every chance that the sequence will have a different name when it’s created in each environment. Therefore, we need a way of working out the sequence name at runtime…

Finding the Sequence in the Data Dictionary

Unfortunately, Oracle does not give you the option of naming the sequence for an identity column. However, you can find a reference to it in USER_TAB_COLUMNS…

select data_default
from user_tab_columns
where table_name = 'BIN_DAYS'
and column_name = 'ID'
and identity_column = 'YES';

which, in my case, returns :

DATA_DEFAULT                                                                    
-----------------------------
"MIKE"."ISEQ$$_75278".nextval

The DEFAULT_VALUE column is a LONG, which may make it a bit fiddly to work with.
A cleaner source for this information is the “purpose-built” USER_TAB_IDENTITY_COLS…

select sequence_name
from user_tab_identity_cols
where table_name = 'BIN_DAYS'
and column_name = 'ID';
SEQUENCE_NAME                                                                                                                   
-------------
ISEQ$$_75278

We can obtain the sequence name using this query and drop it into a bit of dynamic SQL. As with any selected query string being concatenated into a dynamic SQL statement, we would need to be mindful of the possibility of a blind injection and take appropriate steps.
However, there is another option which does not require us to select a VARCHAR2 value…

Predicting the identity sequence name

Thanks to this article by Julian Dyke, we know that Oracle uses this pattern to name an identity sequence :

ISEQ$$_<object_id of the table to which the identity column belongs>

USER_OBJECTS gives us the object_id of the BIN_DAYS table :

select object_id
from user_objects
where object_type = 'TABLE'
and object_name = 'BIN_DAYS';
 OBJECT_ID
----------
     75278

…which is indeed the numeric component of the sequence name in question…

select sequence_name
from user_tab_identity_cols
where table_name = 'BIN_DAYS'
and column_name = 'ID';
SEQUENCE_NAME                                                                                                                   
--------------
ISEQ$$_75278

As OBJECT_ID is a number, not a string we don’t have to worry about any nefarious SQL injection shenanigans if we concatenate it into a dynmaic SQL select string. Therefore, we can write our test like this :

set serverout on size unlimited
clear screen
declare
v_in_date date;
v_expected_date date;
v_actual_date date;
v_id bin_days.id%type;
v_seq_name user_sequences.sequence_name%type;
v_result varchar2(4000);
begin
-- setup
v_in_date  := date '2021-04-07';
v_expected_date := date '2021-04-09';
dbms_output.put_line('Input date : '||to_char(v_in_date, 'DD-MON-YYYY'));
-- execute
calculate_bin_day(v_in_date);
-- validate
-- find the sequence name and obtain the id value
select 'ISEQ$$_'||object_id
into v_seq_name
from user_objects
where object_type = 'TABLE'
and object_name = 'BIN_DAYS';
execute immediate 'select '||v_seq_name||'.currval from dual' into v_id;
select collection_date
into v_actual_date
from bin_days
where id = v_id;
if v_actual_date = v_expected_date then
v_result := 'PASS';
else
v_result := 'FAIL : expected '||to_char(v_expected_date, 'DD-MON-YYYY')
||' got '||to_char(v_actual_date, 'DD-MON-YYYY');
end if;
dbms_output.put_line(v_result);
end;
/

Using a named sequence

Rather than trying to figure out the sequence name at runtime, we could just use an explicitly named sequence and dispense with the identity column altogether :

create sequence bin_days_id_seq;
drop table bin_days;
create table bin_days(
id number default bin_days_id_seq.nextval primary key,
weekday varchar2(25) not null,
collection_date date not null)
/

This makes our test much simpler…

set serverout on size unlimited
clear screen
declare
v_in_date date;
v_expected_date date;
v_actual_date date;
v_id bin_days.id%type;
v_result varchar2(4000);
begin
-- setup
v_in_date  := date '2021-12-27';
v_expected_date := date '2021-12-31';
dbms_output.put_line('Input date : '||to_char(v_in_date, 'DD-MON-YYYY'));
-- execute
calculate_bin_day(v_in_date);
-- validate
v_id := bin_days_id_seq.currval;
select collection_date
into v_actual_date
from bin_days
where id = v_id;
if v_actual_date = v_expected_date then
v_result := 'PASS';
else
v_result := 'FAIL : expected '||to_char(v_expected_date, 'DD-MON-YYYY')||' got '||to_char(v_actual_date, 'DD-MON-YYYY');
end if;
dbms_output.put_line(v_result);
end;
/

…although we do lose some of the built-in functionality that comes with using an identity column.

As always, the best option will depend entirely on the circumstances you find yourself in, but it’s nice to know you have a choice.

Advertisements
Report this ad
Loading...

report

Recommend

About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK