A random string of digits

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.

Jeffrey Kemp 15 December 2015

PL/SQL / tips-&-tricks /

About Jeffrey Kemp

Application Designer & Developer at Oracle specialising in Oracle APEX (Application Express), Oracle SQL and PL/SQL. Oracle ACE Alumni ♠️. Piano player, father, husband, Christian. The views expressed on this blog are my own and do not necessarily reflect the views of Oracle.