How to generate random unique id of length 5 to 10 digit in oracle?

Question

How to Generate random Unique ID for table of length 5 to 10 digit without using any other table.

I know SYS_GUID and DBMS_RANDOM.value(100000,999999).

In SYS_GUID is 16 character and DBMS_RANDOM.value(100000,999999) does not guarantee that next is unique and it uses another table to store previous generated number and this is not good idea.

Answer 1

Expanding on an earlier comment, if you really need to do this and don't want a separate table to hold the used or available values (which is reasonable as that has its own issues), you could achieve this if you can create a CRUD procedure to handle the insert for you, something like:

create table t42 (id number, some_col number, constraint pk_t42 primary key (id));

create or replace procedure insert_t42(p_some_col t42.some_col%type) as
begin
  loop
    begin
      -- attempt to insert with a random number
      insert into t42 (id, some_col)
      values (ceil(dbms_random.value(9,99)), p_some_col);
      -- no error, so we are done - exit the loop
      exit;
    exception
      when dup_val_on_index then
        -- random ID already existed; ignore error, will loop again
        null;
        -- debug message just for demo
        dbms_output.put_line('Duplicate ignored');
    end;
  end loop;
end;
/

The loop will continue until it happens upon a random value in the required range which does not throw a unique constraint violation (dup_val_on_index exception). I've used a small range for the demo to make clashes likely, for 5-10 digits you'd use ceil(dbms_random.value(9999,9999999999)).

Even so, you really need further handling to prevent it looping forever once all available values in that range have been used - possibly with a counter incremented in the loop that throws its own exception when some large-ish number has been reached. I'll leave that as an exercise...

Anyway, to demonstrate that procedure in action, this block inserts 50 records with random IDs by looping 50 times and calling the procedure once per iteration:

set serveroutput on
begin
  for i in 1..50 loop
    insert_t42(i);
  end loop;
end;
/

Duplicate ignored
Duplicate ignored
Duplicate ignored
Duplicate ignored
Duplicate ignored
Duplicate ignored
Duplicate ignored
Duplicate ignored
Duplicate ignored
Duplicate ignored
Duplicate ignored
Duplicate ignored
Duplicate ignored
Duplicate ignored
Duplicate ignored
Duplicate ignored
Duplicate ignored
Duplicate ignored
Duplicate ignored
Duplicate ignored
Duplicate ignored

PL/SQL procedure successfully completed.

That run hit the dup_val_on_index exception 21 times, but because the procedure just went around the loop again when that happened, it still inserted 50 unique values:

select * from t42 order by id;

        ID   SOME_COL
---------- ----------
        11         16
        12         46
        14         22
        18         20
        19         44
        22         26
        23         34
        26         36
        27         14
        29         18
        31         39
        32         35
        33         48
        35         19
        36         42
        37         10
        38          5
        39         24
        42         38
        43         50
        49         32
        50         41
        51         12
        52         33
        53         47
        56         21
        57         40
        59         43
        61          8
        63         37
        64         29
        65          1
        67         25
        68         27
        70         49
        73          2
        75         31
        77         13
        78          6
        80          4
        83         30
        87         11
        90          7
        92         23
        93         15
        94          9
        95         17
        96         45
        98          3
        99         28

50 rows selected. 

---

You could achieve a similar effect without a procedure if you're doing the insert from an application, either by executing an anonymous block that has the same loop logic, or using a native Java (or C, or whatever) loop that repeats an insert statement as a JDBC (or OCI) call and checks for an exception being returned. The principle is the same - insert inside a loop until you don't get an error.