2. Home
  3. Articles
  4. Database
  5. Oracle PL/SQL
  6. Data Randomization Function in Oracle PL/SQL

Data Randomization Function in Oracle PL/SQL

Oracle PL/SQL
Hits: 8731
What?
This article describes a function that will return random data based on a given value. The function intends to determine the data type and return data that is absolutely irrelevant to the original data but the same length and the same type of data.

Why?
The joy of working with the plethora of applications out there result in me using MySQL, Oracle PL/SQL and Transact SQL in my day job. The equivalent functions have to be written up all in the name of "seamless integration"...

How?
Previously I had tried to write a smart function which could determine the datatype automatically but as you may know, Oracle has a funny way of working with dates which made this need to be changed. The following function now requires a second parameter where you specify the data type:
copyraw 
CREATE OR REPLACE FUNCTION UFN_DATASCRAMBLE
        (
                p_original_value        IN VARCHAR2,
                p_original_datatype     IN VARCHAR2,
                p_original_range_1      IN VARCHAR2 DEFAULT NULL,       -- no effect on strings
                p_original_range_2      IN VARCHAR2 DEFAULT NULL        -- no effect on strings
        )

RETURN VARCHAR2
IS
        v_char_at       NUMBER          := 0;   -- v_char_at      : internal variable for string indexing
        v_rand_param1   VARCHAR2(50)    := '';  -- v_rand_param1  : parameter 1 for dbms_random function
        v_rand_param2   VARCHAR2(50)    := '';  -- v_rand_param2  : parameter 2 for dbms_random function
        v_returnedval   VARCHAR2(255)   := '';  -- v_returnedval  : value to be returned
BEGIN
        IF p_original_value IS NULL THEN
                v_returnedval   := NULL;

        ELSIF p_original_datatype='INT' THEN
                -- Check Parameters and assign proper default values
                IF p_original_range_1 IS NULL THEN
                        v_rand_param1   := POWER(10, (LENGTH(p_original_value)-1));  -- should yield eg. 100000
                ELSE
                        v_rand_param1   := p_original_range_1;
                END IF;
                IF p_original_range_2 IS NULL THEN
                        v_rand_param2   := POWER(10, LENGTH(p_original_value))-1;    -- should yield eg. 999999
                ELSE
                        v_rand_param2   := p_original_range_2;
                END IF;

                -- Determined submitted value as 'INTEGER';
                v_returnedval   := TRUNC(DBMS_RANDOM.VALUE(v_rand_param1, v_rand_param2));
                v_returnedval   := SUBSTR(v_returnedval,1,LENGTH(p_original_value));

        ELSIF p_original_datatype='FLOAT' THEN
                -- Determined submitted value as 'FLOAT';
                v_char_at       := (INSTR(p_original_value, '.')-1);
                v_rand_param1   := POWER(10, (LENGTH(p_original_value) - v_char_at));        -- should yield eg. 10
                v_rand_param2   := POWER(10, (LENGTH(p_original_value) - v_char_at+1)-1);    -- should yield eg. 99
                v_returnedval   := TRUNC(DBMS_RANDOM.VALUE(v_rand_param1, v_rand_param2));
                v_rand_param1   := POWER(10, (v_char_at - 1));                          -- should yield eg. 100000
                v_rand_param2   := POWER(10, v_char_at) - 1;                            -- should yield eg. 999999
                v_returnedval   := TRUNC(DBMS_RANDOM.VALUE(v_rand_param1, v_rand_param2))||'.'||v_returnedval;
                v_returnedval   := SUBSTR(v_returnedval,1,LENGTH(p_original_value));

        ELSIF p_original_datatype='VARCHAR' THEN

                IF LENGTH(TRIM(TRANSLATE(p_original_value, 'abcdefghijklmnopqrstuvwxyz ', ' '))) IS NULL THEN
                        -- Determined submitted value as 'ALPHALOWER';
                        v_rand_param1   := 'L';
                        v_rand_param2   := LENGTH(p_original_value);
                        v_returnedval   := DBMS_RANDOM.STRING(v_rand_param1, v_rand_param2);

                ELSIF LENGTH(TRIM(TRANSLATE(p_original_value, 'ABCDEFGHIJKLMNOPQRSTUVWXYZ ', ' '))) IS NULL THEN
                        -- Determined submitted value as 'ALPHAUPPER';
                        v_rand_param1   := 'U';
                        v_rand_param2   := LENGTH(p_original_value);
                        v_returnedval   := DBMS_RANDOM.STRING(v_rand_param1, v_rand_param2);

                ELSIF LENGTH(TRIM(TRANSLATE(p_original_value, 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ ', ' '))) IS NULL THEN
                        -- Determined submitted value as 'ALPHABOTH';
                        v_rand_param1   := 'A';
                        v_rand_param2   := LENGTH(p_original_value);
                        v_returnedval   := DBMS_RANDOM.STRING(v_rand_param1, v_rand_param2);

                ELSIF LENGTH(TRIM(TRANSLATE(LOWER(p_original_value), 'abcdefghijklmnopqrstuvwxyz0123456789 ', ' '))) IS NULL THEN
                        -- Determined submitted value as 'ALPHANUMERIC';
                        v_rand_param1   := 'X';
                        v_rand_param2   := LENGTH(p_original_value);
                        v_returnedval   := DBMS_RANDOM.STRING(v_rand_param1, v_rand_param2);

                ELSE
                        -- Determined submitted value as 'STRING';
                        v_rand_param1   := 'P';
                        v_returnedval   := DBMS_RANDOM.STRING(v_rand_param1, LENGTH(p_original_value));
                END IF;

                v_returnedval   := SUBSTR(v_returnedval,1,LENGTH(p_original_value));

        ELSIF p_original_datatype='DATE' THEN
                -- Check Parameters and assign proper default values
                IF p_original_range_1 IS NULL THEN
                        v_rand_param1   := '01/01/1901 00:00:00';
                ELSE
                        v_rand_param1   := p_original_range_1;
                END IF;
                IF p_original_range_2 IS NULL THEN
                        v_rand_param2   := '01/01/2001 23:59:59';
                ELSE
                        v_rand_param2   := p_original_range_2;
                END IF;

                -- Determined submitted value as 'DATE';
                v_rand_param1   := TO_CHAR(TO_DATE(v_rand_param1,'dd/mm/yyyy hh24:mi:ss'),'J'); -- eg. from 01/01/1901 (Julian Days)
                v_rand_param2   := TO_CHAR(TO_DATE(v_rand_param2,'dd/mm/yyyy hh24:mi:ss'),'J'); -- eg. to 01/01/2001 (Julian Days)
                v_returnedval   := TO_DATE(TRUNC(DBMS_RANDOM.VALUE(v_rand_param1,v_rand_param2)),'J');

        END IF;

        RETURN v_returnedval;


  EXCEPTION
        WHEN no_data_found THEN
                RETURN ('Error in submitted value');

        WHEN others THEN
                RETURN ('Unable to generate random value');

END UFN_DATASCRAMBLE;
  1.  CREATE OR REPLACE FUNCTION UFN_DATASCRAMBLE 
  2.          ( 
  3.                  p_original_value        IN VARCHAR2, 
  4.                  p_original_datatype     IN VARCHAR2, 
  5.                  p_original_range_1      IN VARCHAR2 DEFAULT NULL,       -- no effect on strings 
  6.                  p_original_range_2      IN VARCHAR2 DEFAULT NULL        -- no effect on strings 
  7.          ) 
  8.   
  9.  RETURN VARCHAR2 
  10.  IS 
  11.          v_char_at       NUMBER          := 0;   -- v_char_at      : internal variable for string indexing 
  12.          v_rand_param1   VARCHAR2(50)    := '';  -- v_rand_param1  : parameter 1 for dbms_random function 
  13.          v_rand_param2   VARCHAR2(50)    := '';  -- v_rand_param2  : parameter 2 for dbms_random function 
  14.          v_returnedval   VARCHAR2(255)   := '';  -- v_returnedval  : value to be returned 
  15.  BEGIN 
  16.          IF p_original_value IS NULL THEN 
  17.                  v_returnedval   := null; 
  18.   
  19.          ELSIF p_original_datatype='INT' THEN 
  20.                  -- Check Parameters and assign proper default values 
  21.                  IF p_original_range_1 IS NULL THEN 
  22.                          v_rand_param1   := POWER(10, (LENGTH(p_original_value)-1));  -- should yield eg. 100000 
  23.                  ELSE 
  24.                          v_rand_param1   := p_original_range_1; 
  25.                  END IF; 
  26.                  IF p_original_range_2 IS NULL THEN 
  27.                          v_rand_param2   := POWER(10, LENGTH(p_original_value))-1;    -- should yield eg. 999999 
  28.                  ELSE 
  29.                          v_rand_param2   := p_original_range_2; 
  30.                  END IF; 
  31.   
  32.                  -- Determined submitted value as 'INTEGER'; 
  33.                  v_returnedval   := TRUNC(DBMS_RANDOM.VALUE(v_rand_param1, v_rand_param2)); 
  34.                  v_returnedval   := SUBSTR(v_returnedval,1,LENGTH(p_original_value)); 
  35.   
  36.          ELSIF p_original_datatype='FLOAT' THEN 
  37.                  -- Determined submitted value as 'FLOAT'; 
  38.                  v_char_at       := (INSTR(p_original_value, '.')-1); 
  39.                  v_rand_param1   := POWER(10, (LENGTH(p_original_value) - v_char_at));        -- should yield eg. 10 
  40.                  v_rand_param2   := POWER(10, (LENGTH(p_original_value) - v_char_at+1)-1);    -- should yield eg. 99 
  41.                  v_returnedval   := TRUNC(DBMS_RANDOM.VALUE(v_rand_param1, v_rand_param2)); 
  42.                  v_rand_param1   := POWER(10, (v_char_at - 1));                          -- should yield eg. 100000 
  43.                  v_rand_param2   := POWER(10, v_char_at) - 1;                            -- should yield eg. 999999 
  44.                  v_returnedval   := TRUNC(DBMS_RANDOM.VALUE(v_rand_param1, v_rand_param2))||'.'||v_returnedval; 
  45.                  v_returnedval   := SUBSTR(v_returnedval,1,LENGTH(p_original_value)); 
  46.   
  47.          ELSIF p_original_datatype='VARCHAR' THEN 
  48.   
  49.                  IF LENGTH(TRIM(TRANSLATE(p_original_value, 'abcdefghijklmnopqrstuvwxyz ', ' '))) IS NULL THEN 
  50.                          -- Determined submitted value as 'ALPHALOWER'; 
  51.                          v_rand_param1   := 'L'; 
  52.                          v_rand_param2   := LENGTH(p_original_value); 
  53.                          v_returnedval   := DBMS_RANDOM.STRING(v_rand_param1, v_rand_param2); 
  54.   
  55.                  ELSIF LENGTH(TRIM(TRANSLATE(p_original_value, 'ABCDEFGHIJKLMNOPQRSTUVWXYZ ', ' '))) IS NULL THEN 
  56.                          -- Determined submitted value as 'ALPHAUPPER'; 
  57.                          v_rand_param1   := 'U'; 
  58.                          v_rand_param2   := LENGTH(p_original_value); 
  59.                          v_returnedval   := DBMS_RANDOM.STRING(v_rand_param1, v_rand_param2); 
  60.   
  61.                  ELSIF LENGTH(TRIM(TRANSLATE(p_original_value, 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ ', ' '))) IS NULL THEN 
  62.                          -- Determined submitted value as 'ALPHABOTH'; 
  63.                          v_rand_param1   := 'A'; 
  64.                          v_rand_param2   := LENGTH(p_original_value); 
  65.                          v_returnedval   := DBMS_RANDOM.STRING(v_rand_param1, v_rand_param2); 
  66.   
  67.                  ELSIF LENGTH(TRIM(TRANSLATE(LOWER(p_original_value), 'abcdefghijklmnopqrstuvwxyz0123456789 ', ' '))) IS NULL THEN 
  68.                          -- Determined submitted value as 'ALPHANUMERIC'; 
  69.                          v_rand_param1   := 'X'; 
  70.                          v_rand_param2   := LENGTH(p_original_value); 
  71.                          v_returnedval   := DBMS_RANDOM.STRING(v_rand_param1, v_rand_param2); 
  72.   
  73.                  ELSE 
  74.                          -- Determined submitted value as 'STRING'; 
  75.                          v_rand_param1   := 'P'; 
  76.                          v_returnedval   := DBMS_RANDOM.STRING(v_rand_param1, LENGTH(p_original_value)); 
  77.                  END IF; 
  78.   
  79.                  v_returnedval   := SUBSTR(v_returnedval,1,LENGTH(p_original_value)); 
  80.   
  81.          ELSIF p_original_datatype='DATE' THEN 
  82.                  -- Check Parameters and assign proper default values 
  83.                  IF p_original_range_1 IS NULL THEN 
  84.                          v_rand_param1   := '01/01/1901 00:00:00'; 
  85.                  ELSE 
  86.                          v_rand_param1   := p_original_range_1; 
  87.                  END IF; 
  88.                  IF p_original_range_2 IS NULL THEN 
  89.                          v_rand_param2   := '01/01/2001 23:59:59'; 
  90.                  ELSE 
  91.                          v_rand_param2   := p_original_range_2; 
  92.                  END IF; 
  93.   
  94.                  -- Determined submitted value as 'DATE'; 
  95.                  v_rand_param1   := TO_CHAR(TO_DATE(v_rand_param1,'dd/mm/yyyy hh24:mi:ss'),'J'); -- eg. from 01/01/1901 (Julian Days) 
  96.                  v_rand_param2   := TO_CHAR(TO_DATE(v_rand_param2,'dd/mm/yyyy hh24:mi:ss'),'J'); -- eg. to 01/01/2001 (Julian Days) 
  97.                  v_returnedval   := TO_DATE(TRUNC(DBMS_RANDOM.VALUE(v_rand_param1,v_rand_param2)),'J'); 
  98.   
  99.          END IF; 
  100.   
  101.          RETURN v_returnedval; 
  102.   
  103.   
  104.    EXCEPTION 
  105.          WHEN no_data_found THEN 
  106.                  RETURN ('Error in submitted value'); 
  107.   
  108.          WHEN others THEN 
  109.                  RETURN ('Unable to generate random value'); 
  110.   
  111.  END UFN_DATASCRAMBLE; 

Usage
copyraw 
SELECT
  -- UFN_DATASCRAMBLE(DATE_CREATED, 'DATE') scramble_column,            -- testing inputted column
  UFN_DATASCRAMBLE(0123456789012345, 'INT') random_integer,             -- testing 16 digit number
  UFN_DATASCRAMBLE(0123456789012345, 'INT',1,9) random_integer_range,   -- testing integer with range
  UFN_DATASCRAMBLE(1234.567, 'FLOAT') random_float,                     -- testing decimal
  UFN_DATASCRAMBLE('ABCDEFGH', 'VARCHAR') random_upper,                 -- testing uppercase
  UFN_DATASCRAMBLE('abcdefgh', 'VARCHAR') random_lower,                 -- testing lowercase
  UFN_DATASCRAMBLE('ABCDefgh', 'VARCHAR') random_both,                  -- testing upper and lowercase
  UFN_DATASCRAMBLE('ABCD1234', 'VARCHAR') random_alphanum,              -- testing alphanumeric
  UFN_DATASCRAMBLE('ABcd12.!', 'VARCHAR') random_other,                 -- testing string (any char)
  UFN_DATASCRAMBLE(TO_DATE('01:00:00','HH:MI:SS'), 'DATE') random_time, -- testing time
  UFN_DATASCRAMBLE(TO_DATE('12/31/2012','mm/dd/yyyy'), 'DATE') random_date,  -- testing date
  UFN_DATASCRAMBLE(TO_DATE('2012-01-01 05:31:18','YYYY-MM-DD HH24:MI:SS'), 'DATE') random_timestamp,  -- testing timestamp
  UFN_DATASCRAMBLE(TO_DATE('2012-01-01','yyyy-mm-dd'), 'DATE','01/01/1000','31/12/1002') random_daterange  -- testing date range
FROM
  UFN_STUDENT_ACCOUNTS
WHERE
  ROWNUM
  1.  SELECT 
  2.    -- UFN_DATASCRAMBLE(DATE_CREATED, 'DATE') scramble_column,            -- testing inputted column 
  3.    UFN_DATASCRAMBLE(0123456789012345, 'INT') random_integer,             -- testing 16 digit number 
  4.    UFN_DATASCRAMBLE(0123456789012345, 'INT',1,9) random_integer_range,   -- testing integer with range 
  5.    UFN_DATASCRAMBLE(1234.567, 'FLOAT') random_float,                     -- testing decimal 
  6.    UFN_DATASCRAMBLE('ABCDEFGH', 'VARCHAR') random_upper,                 -- testing uppercase 
  7.    UFN_DATASCRAMBLE('abcdefgh', 'VARCHAR') random_lower,                 -- testing lowercase 
  8.    UFN_DATASCRAMBLE('ABCDefgh', 'VARCHAR') random_both,                  -- testing upper and lowercase 
  9.    UFN_DATASCRAMBLE('ABCD1234', 'VARCHAR') random_alphanum,              -- testing alphanumeric 
  10.    UFN_DATASCRAMBLE('ABcd12.!', 'VARCHAR') random_other,                 -- testing string (any char) 
  11.    UFN_DATASCRAMBLE(TO_DATE('01:00:00','HH:MI:SS'), 'DATE') random_time, -- testing time 
  12.    UFN_DATASCRAMBLE(TO_DATE('12/31/2012','mm/dd/yyyy'), 'DATE') random_date,  -- testing date 
  13.    UFN_DATASCRAMBLE(TO_DATE('2012-01-01 05:31:18','YYYY-MM-DD HH24:MI:SS'), 'DATE') random_timestamp,  -- testing timestamp 
  14.    UFN_DATASCRAMBLE(TO_DATE('2012-01-01','yyyy-mm-dd'), 'DATE','01/01/1000','31/12/1002') random_daterange  -- testing date range 
  15.  FROM 
  16.    UFN_STUDENT_ACCOUNTS 
  17.  WHERE 
  18.    ROWNUM 

Additional
Note the return values for the dates and times, in Oracle you would need to specify how you want the return date specified. In the case where the above is used to update a set of data in a table (hopefully a copy of the original one :)), the database will set the format automatically:
copyraw 
UPDATE my_table SET my_column=UFN_DATASCRAMBLE(my_column, my_datatype);
  1.  UPDATE my_table SET my_column=UFN_DATASCRAMBLE(my_column, my_datatype); 
Also note the date format being a mix of european or US american format. This is more dependent on how your database is setup but as shown in the usage examples above, you can specify the format as well.
Category: Oracle PL/SQL :: Article: 432

Credit where Credit is Due:


Feel free to copy, redistribute and share this information. All that we ask is that you attribute credit and possibly even a link back to this website as it really helps in our search engine rankings.

Disclaimer: Please note that the information provided on this website is intended for informational purposes only and does not represent a warranty. The opinions expressed are those of the author only. We recommend testing any solutions in a development environment before implementing them in production. The articles are based on our good faith efforts and were current at the time of writing, reflecting our practical experience in a commercial setting.

Thank you for visiting and, as always, we hope this website was of some use to you!

Kind Regards,

Joel Lipman
www.joellipman.com

Related Articles

Joes Revolver Map

Joes Word Cloud

Accreditation

Badge - Certified Zoho Creator Associate
Badge - Certified Zoho Creator Associate

Donate & Support

If you like my content, and would like to support this sharing site, feel free to donate using a method below:

Paypal:
Donate to Joel Lipman via PayPal

Bitcoin:
Donate to Joel Lipman with Bitcoin bc1qf6elrdxc968h0k673l2djc9wrpazhqtxw8qqp4

Ethereum:
Donate to Joel Lipman with Ethereum 0xb038962F3809b425D661EF5D22294Cf45E02FebF
© 2024 Joel Lipman .com. All Rights Reserved.