Random number generation in Oracle

Question

In C# I can generate random number from a string like this:

public string CreateRandomNumber(string normal)
{
    string abnormal = (new Random(normal.GetHashCode())).Next().ToString();
    return abnormal;
}

But I want to do the same (kind of) operation inside the oracle database. So, how can I generate a random number from a string ?

Answer 1

Take a look here:

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:831827028200

where he uses a combination of sys_guid and to_number.

Answer 2

There is a package in Oracle called DBMS_RANDOM, but it only takes numbers as seed. To seed it with a string, you need some way to generate a numeric hash code for the string. Something along the lines of

FUNCTION simpleHash(st varchar2) RETURN NUMBER is
  n NUMBER;
  s NUMBER;
BEGIN
  n:=1;
  s:=0;
  FOR i IN 1..LENGTH(st) LOOP
     s:=s+n*ASCII(SUBSTR(st,i,1));
     n:=mod(n*75, 65537);
  END LOOP;
END;
/

should do the trick.

Answer 3

The DBMS_RANDOM package provides a built-in random number generator.

Using the SEED Procedures, you can pass a VARCHAR2 as the seed, so you can skip the hash generation step.

SEED Procedures

This procedure resets the seed.

Syntax

DBMS_RANDOM.SEED (
   seed  IN  BINARY_INTEGER);

DBMS_RANDOM.SEED (
   seed  IN  VARCHAR2);