I have 2 questions related to the package dbms_random in Oracle:

1. I wonder if there is a difference between initialize and seed in the dbms_random package. I have the need to randomize a fixed number of records from a larger set but I would like to have the records observed remain stable until I change the seed.

2. After reviewing the record, is there a way to generate a different set of records but still keep the seed, I wonder if there is a function to reset or reinitialize the package with the same seed?

Below is my sample code for illustration purpose. Can I generate a different set of records but still keep the original seed?

begin
--dbms_random.initialize(100);  
dbms_random.seed(100);  
SELECT *
FROM   (
    SELECT *
    FROM   table
    ORDER BY DBMS_RANDOM.value)
WHERE  rownum < 21;

end;

CodePudding user response：

"I wonder if there is a difference between initialize and seed in the dbms_random package"

Yes, there is a difference. initialize() is deprecated and is only retained in DBMS_RANDOM for backwards compatibility. All new code should use seed(). This is covered in the Oracle documentation.

is there a way to generate a different set of records but still keep the seed

Not with the code sample you posted. That will set the seed each time, which will give you the same set of records each time. To get multiple sets of different records spawned from the same seed once you need to separate the seed setting from the record generation.

Here is a demonstration on db<>fiddle