CODEWITHSUNDEEP
randomplsql
junior
junior

Reputation: 37

Generating Random Values in pl/sql

I try to generate random value in pl/sql. But i need these values must be fix 12 character.

For example 654696544846, 234864687644, 438792168431

Thanks.

Upvotes: 0

Views: 3757

Answers (2)

Carlo Sirna
Carlo Sirna

Reputation: 1251

If you need a strictly numeric string made of decimal characters, you can use the dbms_random.value function which, when given 2 values X and Y, returns a random number between X (included) and Y (excluded)

  select trunc(dbms_random.value(100000000000,1000000000000)) from dual

if you can accept also alphanumeric chars, you can use the dbms_random.string function:

  select dbms_random.string('X',12) from dual

the second parameter is the required string length, the first dictates the subset of characters that are allowed in the string

 'u', 'U' - uppercase alpha characters
 'l', 'L' - lowercase alpha characters
 'a', 'A' - mixed case alpha characters
 'x', 'X' - uppercase alpha-numeric characters
 'p', 'P' - any printable characters.

source: https://docs.oracle.com/database/121/ARPLS/d_random.htm

Upvotes: 4

Trijit
Trijit

Reputation: 531

dbms_random.value returns a value between 0 and 1 with a precision of 39 digits, which you then multiply by 1000000000000 to get 12 digit number. It has a decimal part was well which can be removed with a suitable call to to_char which will also format it with a constant length.

select to_char(dbms_random.value * 1000000000000,'fm000000000000') from dual;

Upvotes: 1

Related Questions