715175039885776956103287888080

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*

ADDENDUM

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.