Pre DBMS_RANDOM < Eddie Awad’s Blog

Pre DBMS_RANDOM

Back in the old days when DBMS_RANDOM did not exist ( pre Oracle 8 ), How did Oracle developers generate random numbers? Now I know.

While trouble shooting a performance issue, I found the following code in an old version of an Oracle Applications package (pre 11.5). It was a function that returned a random number between 0 and 1 based on a seed and a date (used as randomizing factor):

scott@eddev> create or replace function rand(
  2      seed in number,
  3      now in date)
  4  return number is
  5      ret_val number;
  6      d       number;
  7      h       number;
  8      m       number;
  9      s       number;
 10  begin
 11      d := to_number(to_char(now,'DD')) + 1;
 12      h := to_number(to_char(now,'HH')) + 1;
 13      m := to_number(to_char(now,'MI')) + 1;
 14      s := to_number(to_char(now,'SS')) + 1;
 15      ret_val := LN(d * h * m * s * (abs(seed) + 1));
 16      ret_val := ret_val - floor(ret_val);
 17      return (ret_val);
 18  end;
 19  /

Function created.

scott@eddev> begin
  2      for seed in 1..10 loop
  3          dbms_output.put_line(rand(seed, sysdate));
  4      end loop;
  5  end;
  6  /
.55393007636625964625217881606342782874
.95939518447442402823019193152777696532
.24707725692620495566941093752160439681
.47022080824041471143570602783143890019
.65254236503436933764742405298595353339
.8066930448616276419402994380484309903
.94022443748615026508664305897978096488
.05800747314253371962543716845030266995
.16336798880036002085293814928961546826
.25867816860468488089689027257038056049

PL/SQL procedure successfully completed.

scott@eddev> /
.94022443748615026508664305897978096488
.34568954559431464706465617444413010146
.63337161804609557450387518043795753296
.85651516936030533027017027074779203634
.03883672615425995648188829590230666954
.19298740598151826077476368096478412646
.32651879860604088392110730189613410104
.4443018342624243384599014113666558061
.54966234992025063968740239220596860441
.64497252972457549973135451548673369663

PL/SQL procedure successfully completed.

Quite intersting. Tom Kyte has another similar function.

Of course, nowadays (Oracle 8 and up), you use Oracle’s built-in function DBMS_RANDOM.VALUE to accomplish the same thing.

Possibly related: