Oracle sql random number by seed

Question

Is there way to generate random number with some seed?

SELECT  DBMS_RANDOM.value FROM dual;

Here code in java which fit my purposes

Random r1 = new Random(47);
        Random r2 = new Random(47);
        System.out.println(r1.nextInt());
        System.out.println(r2.nextInt());
        System.out.println(r1.nextInt());
        System.out.println(r2.nextInt());

here result

-1172028779
-1172028779
1717241110
1717241110

If i will run this code again and again i'll get the same result. Is there any way to do like this in Oracle sql?

Answer 1

The documentation for the DBMS_RANDOM package describes the SEED procedure, which resets the seed for the current session.

DBMS_RANDOM can be explicitly initialized, but does not need to be initialized before calling the random number generator. It will automatically initialize with the date, user ID, and process ID if no explicit initialization is performed.

If this package is seeded twice with the same seed, then accessed in the same way, it will produce the same results in both cases.

In some cases, such as when testing, you may want the sequence of random numbers to be the same on every run. In that case, you seed the generator with a constant value by calling one of the overloads of DBMS_RANDOM.SEED. To produce different output for every run, simply to omit the call to "Seed" and the system will choose a suitable seed for you.

To demonstrate:

exec dbms_random.seed(47);

anonymous block completed

select dbms_random.value from dual;

     VALUE
----------
  .5976804 

select dbms_random.value from dual;

     VALUE
----------
.329618924 

-- reset
exec dbms_random.seed(47);

anonymous block completed

select dbms_random.value from dual;

     VALUE
----------
  .5976804 

select dbms_random.value from dual;

     VALUE
----------
.329618924 

You can't call a procedure from SQL, you have to use a PL/SQL block (which is all execute is). You could, if you really need to, create your own wrapper function:

create function my_random_value(p_seed in number default null)
return number is
begin
  if p_seed is not null then
    dbms_random.seed(p_seed);
  end if;
  return dbms_random.value;
end;
/

Then call that with or without a seed:

select my_random_value(47) from dual;

MY_RANDOM_VALUE(47)
-------------------
           .5976804 

select my_random_value from dual;

MY_RANDOM_VALUE
---------------
     .329618924