Generate random numbers based on column length (oracle)

Question

I have list of numbers start from

100000000000000000

to

100000000000999999

always 18 digit.

Now, I want to generate random numbers between 100000000000000000 and 100000000000999999. (need to have 18 digit)

declare
 cursor c1 is select * from testNumbers k for update;
 minNumbers INTEGER;
 maxNumbers INTEGER;
 
begin

 select LENGTH(min(someColumn)) into minNumbers from testNumbers; 
 select min(someColumn) into maxNumbers from testNumbers;

  open c1;
    loop fetch c1 into i; 
      exit when c1%notfound;
      
      DBMS_OUTPUT.PUT_LINE(TRUNC(dbms_random.value (minNumbers, maxNumbers)));

    end loop;
  close c1;
  
end;

But I got 16 or 17 digit numbers like:

58637356300054590
1502386622593360
34871420042208564
2696370666671141
62734000905097141

When I change to:

select max(someColumn) into maxNumbers from testNumbers

did not help.

Answer 1

I want to generate random numbers between 100000000000000000 and 100000000000999999

Use the DBMS_RANDOM.VALUE method from the minimum value to the maximum value + 1 and then FLOOR the random value:

SELECT FLOOR(
         DBMS_RANDOM.VALUE(
           100000000000000000,
           100000000001000000
         )
       ) AS random_value
FROM   DUAL;

Which may, randomly, output:

RANDOM_VALUE
100000000000102434

If you want to use your minimum and maximum values then use MIN and MAX (and not LENGTH):

SELECT FLOOR(
         DBMS_RANDOM.VALUE(
           MIN(someColumn),
           MAX(someColumn) + 1
         )
       ) AS random_value
FROM   testNumbers;

or, if you want to output a random value for every row then use analytic functions (rather than aggregation):

SELECT someColumn,
       FLOOR(
         DBMS_RANDOM.VALUE(
           MIN(someColumn) OVER (),
           MAX(someColumn) OVER () + 1
         )
       ) AS random_value
FROM   testNumbers;

fiddle