My target is to provide sample ETL data flow in PL/SQL, to do that, I need to generate sample data. Basically, I will operate on two data sources: 1) orders data and 2) contact form data:

1. Oracle Database Sample Schema CO: Customer Orders link
2. PL/SQL code to generate contact form data, manual below =>

I will make table: contact_form, that will be connected to orders table, by a algorithm:

• join by email address
• deduplication on order_id (we want to present max one order/sale for best contact_form)
• best contact_form will have:
  • creation date maximum 30 days before order date (it represent new client - first contact within 30 days before order)
  • source - only www

Business requirements, are that we want to make a order report based on contact_form, more in next post

Below script, creates table:

create table contact_form (
    contact_form_id     NUMBER GENERATED BY DEFAULT AS IDENTITY,
    contact_date        DATE NOT NULL,
    full_name           VARCHAR2(100),
    email_address       VARCHAR2(100),
    source              VARCHAR2(100),
    CONSTRAINT contact_form_pk PRIMARY KEY (contact_form_id)
)

and populate automatically data, by creating a function (there are more simplier ways).

Below function requirements:

• provide argument randomInt with random number
• provide argument randomString with string containing random values/words, comma separeted
• output will be randomly choosed value/word from provided string
• I want standalone function to generate sample data

CREATE OR REPLACE FUNCTION genRandomName(randomInt IN number, randomString IN VARCHAR2)
RETURN VARCHAR2 AS
    --randomString VARCHAR2(100) := 'PRZEMEK, SARA,ASIA, BOB';
    --randomInt NUMBER(4) := round(dbms_random.value(1, 10));

    --table with random values
    noOfChars NUMBER(4) := 0; 
    randomNrPresent NUMBER := 1;
    randomNumberMin NUMBER(4) := 1;
    randomNumberMax NUMBER(4) := round(dbms_random.value(1, 100));
    TYPE randomWordsType IS TABLE OF VARCHAR2(100) index by pls_integer;
    randomWordsTable randomWordsType;
    singleWord NUMBER(4) := 1;
    strChars VARCHAR2(100); 

    -- define a record type
    TYPE randomNrRecordType
    IS
    RECORD
    (id                 NUMBER,
    randomValue         VARCHAR2(100),
    randomNumberMin    NUMBER,
    randomNumberMax    NUMBER);
    -- declare a record
    randomNrRecord randomNrRecordType;

    TYPE randomNrType is table of randomNrRecordType index by pls_integer;
    randomNrTable randomNrType;

    randomOut VARCHAR2(100);  
BEGIN
    --transforming randomString into separate rows
    --itereatung over each character in randomString
    FOR i IN 1..Length(randomString)
    LOOP  
        if Substr(randomString, i, 1) = ',' then
            -- Count no. of characters  ',' in input string
            noOfChars := noOfChars + 1;  

            -- insert whole words
            randomWordsTable(singleWord) := strChars; 
            --dbms_output.put_line(randomWordsTable(singleWord));
            strChars := '';
            singleWord := singleWord + 1;
        else
             -- write whole word into a row
            strChars := strChars || trim(Substr(randomString, i, 1));
            --last word
            if i = Length(randomString) then
                randomWordsTable(singleWord) := strChars; 
                --dbms_output.put_line(strChars);
            end if;
        end if;
    END LOOP; 

    --assosiated array withrandom variables + numbers
    for i in 1..(noOfChars + 1)
    loop
        select randomNrPresent as id,
            --'randomValue' || randomNrPresent as randomValue,
            randomWordsTable(i) as randomValue,
            randomNumberMin,
            randomNumberMax
        into randomNrRecord
        from dual;

        randomNrTable(i) := randomNrRecord;
        randomNrPresent := randomNrPresent + 1; --id, incremental number: 1,2,3,4,..

        ----randomNrMax for last row (i = noOfChars), must be equal 100, because it can not be lower then randomInt

        if i = (noOfChars) then
            randomNumberMin := randomNumberMax + 1;
            randomNumberMax := 100;
        else
            randomNumberMin := randomNumberMax + 1;
            randomNumberMax := round(dbms_random.value(randomNumberMin, 100));
        end if;

        --dbms_output.put_line(randomNrTable(i).id || ' ' || randomNrTable(i).randomValue || ' ' || randomNrTable(i).randomNumberMin || ' ' || randomNrTable(i).randomNumberMax);
    end loop;

--generating randomOut output, based on parameter randomInt
    randomOut := randomNrTable(1).randomValue; --default value, if randomInt < min(randomNrValue)
    for i in 1 .. randomNrTable.count
    loop
        if randomInt >= randomNrTable(i).randomNumberMin and randomInt <= randomNrTable(i).randomNumberMax then
            randomOut := randomNrTable(i).randomValue;
        end if;
    end loop;

   --dbms_output.put_line('randomInt: '|| randomInt || ' randomOut: ' ||  randomOut);

    RETURN randomOut;

END;

• Usage =>

select A.NAME, count(*)
from (
        select genRandomName(dbms_random.value(1, 100), 'PRZEMEK, SARA,ASIA, BOB') as NAME
        from dual connect by level <= 1000
) A
group by cube(A.NAME);

• Inserting new data => 100 rows

BEGIN
    EXECUTE IMMEDIATE 'truncate table contact_form';

    FOR i IN 1..100
    LOOP
        insert into contact_form (contact_date, full_name, email_address, source)   
        SELECT A.contact_date, A.full_name, (A.full_name || '@GOOGLE.PL') as email_address, A.source
        FROM (
            SELECT
            (to_date('2021-02-01', 'yyyy-mm-dd') + dbms_random.value(1, 365))  AS contact_date,
            (select genRandomName(dbms_random.value(1, 100), 'PRZEMEK, SARA, ASIA, BOB')) AS full_name,
            (select genRandomName(dbms_random.value(1, 100), 'WWW, CALL-CENTER, BRANCH')) AS source
            FROM DUAL
        ) A;
    END LOOP;

    COMMIT;

END;

Display table:

select * from contact_form;