CREATE OR REPLACE
FUNCTION random_pin (digits IN NUMBER)
RETURN NUMBER IS
/* http://jeffkemponoracle.com/2015/12/15/a-random-string-of-digits/
Generate a random string with an exact length consisting of numeric digits
that will be preserved if represented as an integer.
Warning: due to its requirements there is a small non-random weight that
causes zeros to appear slightly less frequently than the other 9. If this
is a problem for you, consider using random_digits instead (see below).
*/
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; Statement processed.
CREATE OR REPLACE
FUNCTION random_digits (digits IN NUMBER)
RETURN VARCHAR2 IS
/* http://jeffkemponoracle.com/2015/12/15/a-random-string-of-digits/
Generate a random string with an exact length consisting of numeric digits.
Warning: may return one or more leading zeros, which means that its value
may change if represented as an integer.
*/
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; Statement processed.
SELECT ROWNUM AS len, random_pin(ROWNUM) AS pin FROM DUAL CONNECT BY LEVEL <= 39| LEN | PIN | 1 | 2 | 2 | 66 | 3 | 605 | 4 | 2486 | 5 | 12059 | 6 | 491726 | 7 | 9634575 | 8 | 96383556 | 9 | 634277073 | 10 | 6429799872 | 11 | 35920119839 | 12 | 969251646574 | 13 | 4527632171711 | 14 | 20702305308955 | 15 | 819906612287171 | 16 | 3224154458224453 | 17 | 68435408993956489 | 18 | 221211935587448555 | 19 | 7424115283845923427 | 20 | 90381750987912120472 | 21 | 623597497920749166162 | 22 | 8520748610443454076856 | 23 | 43086084074444448791074 | 24 | 183594209606554467326441 | 25 | 6066700723794589604948417 | 26 | 12584932696158878956010611 | 27 | 516667807610774515346015052 | 28 | 4024013402032149160784416423 | 29 | 73302176618672384614851200065 | 30 | 457215875895863546459411696683 | 31 | 7479275009827794956852362517903 | 32 | 32503981766873616644309217208233 | 33 | 603996766257646694402537737282944 | 34 | 7823418635207913991998288224065784 | 35 | 14421883132916252045405303616448346 | 36 | 977584481554594213672195270933707627 | 37 | 9428014093791165530536655465996070037 | 38 | 30142878746845332115999005029548343503 | 39 | 548079318215370233482680771149009357578 |
|---|
SELECT ROWNUM AS len, random_digits(ROWNUM) AS randigits FROM DUAL CONNECT BY LEVEL <= 39| LEN | RANDIGITS | 1 | 4 | 2 | 74 | 3 | 784 | 4 | 2516 | 5 | 16455 | 6 | 096583 | 7 | 6957121 | 8 | 55677808 | 9 | 734725571 | 10 | 6340162055 | 11 | 14653757765 | 12 | 518968125595 | 13 | 0007027352356 | 14 | 26301305553711 | 15 | 882504528657085 | 16 | 8903354495118180 | 17 | 76724676290928548 | 18 | 653785414018895750 | 19 | 0251558812834495909 | 20 | 50120795963117827184 | 21 | 726239049271565925662 | 22 | 8321582620902326701066 | 23 | 67931668532717117799614 | 24 | 209861538532610891443847 | 25 | 1536375996243017110453782 | 26 | 36146872309853107276208366 | 27 | 337133366363129417552213179 | 28 | 9409290312548519742896831460 | 29 | 32840514061141482780429387260 | 30 | 342724267286674754514081078861 | 31 | 2977621450028239698010371008629 | 32 | 69536958545258443355222497572632 | 33 | 397316642985546578093892556432147 | 34 | 9192561454291474596396460248419978 | 35 | 96541550783130868698360995970109689 | 36 | 057682689349244862079182541118839769 | 37 | 6783581905076027512944034866767809373 | 38 | 53135093225220098371235122781259282688 | 39 | 924401243721594425655693548754797226280 |
|---|