Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

How to generate random number with each digit in it is distinct?

871594Sep 5 2012 — edited Sep 6 2012
Hello,

i am writing a function to mask Account numbers and SSN to give the production data to testers.

for this iam generating random number as below and passing to translate function to mask. But it is generating random number contains duplicate digits.
i want to generate random number with each digit is distinct. Any special function for this purpose ?

SQL> select ABS(dbms_random.value(100000000,999999999)) into rndm_num from dual;

ABS(DBMS_RANDOM.VALUE(100000000,999999999))
-------------------------------------------------------------------------
729131083

select translate(Incol,'0123456789',rndm_num ) INTO mask_col FROM DUAL;

return mask_col;

Thanks,
Venkat Vadlamudi
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Oct 4 2012
Added on Sep 5 2012
11 comments
8,547 views