Pretty often we need a module that generates an id (incremented number), which must be unique: for example auto-numbering modules, etc.
To ensure uniqueness of these ids, we must synchronize the calls in order to exclude possible duplicate values for concurent calls. But if we have a distributed environment, the module is executed in parallel, unsynchronized environments. How to solve this problem?
We won't have synchronization issues if we implement the incrementer at the Database level (which is only one even for multiple Content Server environment).
The most convenient way is to use sequences (for Oracle, SQL Server, etc., other DBMS should also have something similar).
Below you'll find a smart way to implement the incremented number generator. Only DQL queries are used (executing also some SQL commands):
1. Create the DB sequence
execute exec_sql with query = 'create sequence my_sequence start with 1';
2. Create a function get_next_code that will use the sequence and return the incremented value (as string)
execute exec_sql with query = 'create or replace function get_next_code return varchar2 as code number; begin select my_sequence.nextval into code from dual; return to_char(code); end;';
3. Create a view which will pass the incremented value
execute exec_sql with query = 'create or replace view my_counter (my_code) as (select get_next_code() from dual)';
4. Register table (view)
register table dm_dbo.my_counter (
my_code char(32)
);
update dm_registered object
set owner_table_permit = 1,
set group_table_permit = 1,
set world_table_permit = 1
where object_name = 'my_counter';
The id generator is ready. Now in order to get the next id, you just need to run the following DQL:
select my_code from dm_dbo.my_counter
Couldn't be easier, isn't it?
To ensure uniqueness of these ids, we must synchronize the calls in order to exclude possible duplicate values for concurent calls. But if we have a distributed environment, the module is executed in parallel, unsynchronized environments. How to solve this problem?
We won't have synchronization issues if we implement the incrementer at the Database level (which is only one even for multiple Content Server environment).
The most convenient way is to use sequences (for Oracle, SQL Server, etc., other DBMS should also have something similar).
Below you'll find a smart way to implement the incremented number generator. Only DQL queries are used (executing also some SQL commands):
1. Create the DB sequence
execute exec_sql with query = 'create sequence my_sequence start with 1';
2. Create a function get_next_code that will use the sequence and return the incremented value (as string)
execute exec_sql with query = 'create or replace function get_next_code return varchar2 as code number; begin select my_sequence.nextval into code from dual; return to_char(code); end;';
3. Create a view which will pass the incremented value
execute exec_sql with query = 'create or replace view my_counter (my_code) as (select get_next_code() from dual)';
4. Register table (view)
register table dm_dbo.my_counter (
my_code char(32)
);
update dm_registered object
set owner_table_permit = 1,
set group_table_permit = 1,
set world_table_permit = 1
where object_name = 'my_counter';
The id generator is ready. Now in order to get the next id, you just need to run the following DQL:
select my_code from dm_dbo.my_counter
Couldn't be easier, isn't it?