Oracle PL/SQL for DBAs

Generating Strings

Take, for instance, the value of the column ACC_TYPE, which calls for equal probability of S, C, M, D, and X, or 20% probability each. If we generate a whole number between 1 and 5 (inclusive), the probability of each generated number will be 20%. Then I can use a SQL DECODE function to get the ACC_TYPE value based on the number generated.

      1  SELECT DECODE (FLOOR (DBMS_RANDOM.VALUE (1, 6)),
      2                 1, 'S',
      3                 2, 'C',
      4                 3, 'M',
      5                 4, 'D',
      6                 'X'
      7                )
      8*   FROM DUAL;

Let’s see what’s going on here. First, I am generating a number between 1 and 5 (line 1). Because the number generated is less than the highest value passed as a parameter, I have specified 6. Because I want a whole number, I use the FLOOR function in line 1. It truncates all decimal values from the generated number. Depending on the number obtained, I use DECODE to get one of the values—S, C, M, D, or X. Because the numbers 1, 2, 3, 4, and 5 all have an equal probability of getting generated, so will the letters—at 20% each.

This technique is very useful in generating random but predetermined values of the type shown in the example. The same approach can be used to generate almost all types of predetermined random values.