I needed to generate a random string with an exact length consisting of numeric digits, that I could send in an SMS to a user as a temporary account “pin”.
DBMS_RANDOM.string is unsuitable for this purpose as its supported modes all include alphabetic characters. So I used
DBMS_RANDOM.value instead. I call
TRUNC afterwards to lop off the decimal portion.
FUNCTION random_pin (digits IN NUMBER) RETURN NUMBER IS BEGIN IF digits IS NULL OR digits < 1 OR digits > 39 THEN RAISE_APPLICATION_ERROR(-20000,'digits must be 1..39'); END IF; IF digits = 1 THEN RETURN TRUNC( DBMS_RANDOM.value(0,10) ); ELSE RETURN TRUNC( DBMS_RANDOM.value( POWER(10, digits-1) ,POWER(10, digits) )); END IF; END random_pin; random_pin(digits => 6); 482372
EDIT 8/1/2016: added special case for 1 digit
Because the requirements of my “pin” function was to return a value that would remain unchanged when represented as an integer, it cannot return a string of digits starting with any zeros, which is why the lowerbound for the random function is
POWER(10,digits-1). This, unfortunately, makes it somewhat less than perfectly random because zeroes are less frequent – if you call this function 1000 times for a given length of digits, then counted the frequency of each digit from 0..9, you will notice that 0 has a small but significantly lower frequency than the digits 1 to 9.
To fix this, the following function returns a random string of digits, with equal chance of returning a string starting with one or more zeroes:
FUNCTION random_digits (digits IN NUMBER) RETURN VARCHAR2 IS BEGIN IF digits IS NULL OR digits < 1 OR digits > 39 THEN RAISE_APPLICATION_ERROR(-20000,'digits must be 1..39'); END IF; RETURN LPAD( TRUNC( DBMS_RANDOM.value(0, POWER(10, digits)) ), digits, '0'); END random_digits;
The above functions may be tested and downloaded from Oracle Live SQL.