How to generate a dynamic sequence in Oracle

sequence increment in oracle
how to describe sequence in oracle
oracle create sequence starts with
trigger to generate sequence number in oracle
oracle create sequence 11g
oracle default sequence nextval
grant create sequence privilege oracle
how to create sequence in oracle for varchar

I have a table A which represents a valid sequence of numbers, which looks something like this:

| id | start |   end | step |
|----|-------|-------|------|
|  1 |  4000 |  4999 |    4 |
|  2 |     3 | 20000 |    1 | 

A[1] thus represents the sequence [4000, 4004, 4008, ...4996]

and another B of "occupied" numbers that looks like this:

|  id | number | ... |
|-----|--------|-----|
|   1 |   4000 | ... |
|   2 |   4003 | ... |
| ... |    ... | ... |

I want to construct a query which using A and B, finds the first unoccupied number for a particular sequence.

I have been trying – and failing – to do, is to generate a list of valid numbers from a row in A and then left outer join table B on B.number = valid_number where B.id is null from which result I could then select min(...).

How to generate a dynamic sequence in Oracle, How about this? I simplified your test case ( END value isn't that high) in order to save space (otherwise, I'd have to use smaller font :)). To create a sequence in your own schema, you must have the CREATE SEQUENCE system privilege. To create a sequence in another user's schema, you must have the CREATE ANY SEQUENCE system privilege. Syntax. create_sequence::= Description of the illustration create_sequence.gif. Semantics. schema. Specify the schema to contain the sequence. If you omit schema, then Oracle Database creates the sequence in your own schema. sequence

You can use LEAD to compute the difference between ordered rows in table B. Any row having a difference (to the next row) that exceeds the step value for that sequence is a gap.

Here's that concept, implemented (below). I threw in a sequence ID "3" that has no values in table B, to illustrate that it generates the proper first value.

with
   a (id, cstart, cend, step) as
     (select 1, 4000,  4032, 4 from dual union all
      select 2,    3, 20000, 1 from dual union all
      select 3,  100,   200, 3 from dual
     ),
   b (id, cnumber) as
     (select 1, 4000 from dual union all
      select 1, 4004 from dual union all
      select 1, 4012 from dual union all
      select 2, 4003 from dual
     ),
work1 as (
select a.id, 
       b.cnumber cnumber, 
       lead(b.cnumber,1) over ( partition by b.id order by b.cnumber ) - b.cnumber diff,
       a.step,
       a.cstart,
       a.cend
from   a left join b on b.id = a.id )
select w1.id, 
       CASE WHEN min(w1.cnumber) is null THEN w1.cstart 
            WHEN min(w1.cnumber)+w1.step < w1.cend THEN min(w1.cnumber)+w1.step 
            ELSE null END next_cnumber
from work1 w1
where ( diff is null or diff > w1.step )
group by w1.id, w1.step, w1.cstart, w1.cend
order by w1.id
+----+--------------+
| ID | NEXT_CNUMBER |
+----+--------------+
|  1 |         4008 |
|  2 |         4004 |
|  3 |          100 |
+----+--------------+

You can further improve the results by excluding rows in table B that are impossible for the sequence. E.g., exclude a row for ID #1 having a value of, say, 4007.

Oracle CREATE SEQUENCE Explained By Practical Examples, In this tutorial, you will learn how to use the Oracle CREATE SEQUENCE statement to create a new sequence in Oracle. Teams. Q&A for Work. Stack Overflow for Teams is a private, secure spot for you and your coworkers to find and share information

I'll ask the obvious and suggest why not use an actual sequence?

SQL> set timing on
SQL> CREATE SEQUENCE SEQ_TEST_A
START WITH 4000
INCREMENT BY 4
MINVALUE 4000
MAXVALUE 4999
NOCACHE 
NOCYCLE 
ORDER
Sequence created.
Elapsed: 00:00:01.09
SQL> CREATE SEQUENCE SEQ_TEST_B
START WITH 3
INCREMENT BY 1
MINVALUE 3
MAXVALUE 20000
NOCACHE 
NOCYCLE 
ORDER
Sequence created.
Elapsed: 00:00:00.07

SQL> -- get nexvals from A
SQL> select seq_test_a.nextval from dual

   NEXTVAL
----------
      4000
1 row selected.
Elapsed: 00:00:00.09
SQL> select seq_test_a.nextval from dual

   NEXTVAL
----------
      4004
1 row selected.
Elapsed: 00:00:00.08
SQL> select seq_test_a.nextval from dual

   NEXTVAL
----------
      4008
1 row selected.
Elapsed: 00:00:00.08
SQL> -- get nextvals from B
SQL> select seq_test_b.nextval from dual

   NEXTVAL
----------
         3
1 row selected.
Elapsed: 00:00:00.08
SQL> select seq_test_b.nextval from dual

   NEXTVAL
----------
         4
1 row selected.
Elapsed: 00:00:00.08
SQL> select seq_test_b.nextval from dual

   NEXTVAL
----------
         5
1 row selected.
Elapsed: 00:00:00.08

OraFAQ Forum: SQL & PL/SQL » Dynamic Sequence , I cannot seem to dynamically create a sequence with a result from another table. In my ideal world, this hypothetical statement would look like: The syntax to create a sequence in Oracle is: CREATE SEQUENCE sequence_name MINVALUE value MAXVALUE value START WITH value INCREMENT BY value CACHE value; sequence_name The name of the sequence that you wish to create. Example. Let's look at an example of how to create a sequence in Oracle. For example:

How to get next value of Sequence dynamically in Oracle , My_Sequence_Name.NextVal - check here and here. Psoug is a great site for Oracle. I used to a SQL guy and trying my hands on Oracle front. I am getting dynamic values of sequence in a variable. I like to fetch next value for that sequence object in some output variable. How to handle this in Oracle. I wrote something same for SQL like this:

Oracle / PLSQL: Sequences (Autonumber), This Oracle tutorial explains how to create and drop sequences in Oracle with syntax and examples. In Oracle, you can create an autonumber field by using  Use the CREATE SEQUENCE statement to create a sequence, which is a database object from which multiple users may generate unique integers. You can use sequences to automatically generate primary key values. When a sequence number is generated, the sequence is incremented, independent of the transaction committing or rolling back.

SQL, Following is the sequence query creating sequence in ascending order. Example 1: CREATE SEQUENCE sequence_1 start with 1 increment by 1 minvalue 0  Specify the schema to contain the sequence. If you omit schema, then Oracle Database creates the sequence in your own schema. sequence. Specify the name of the sequence to be created. If you specify none of the following clauses, then you create an ascending sequence that starts with 1 and increases by 1 with no upper limit.