Create a Goldengate MySQL Application Oracle Database Tips by Donald BurlesonJuly 3, 2015

This is an excerpt from the book book Oracle GoldenGate 12c: A Hands-on Guide to Data Replication & Integration using Oracle & SQL Server.

Create the following MySQL procedure for SDB1, SDB2, SDB3 and SDB4.          The procedure has (2) input parameters, X and Y. Parameter X is the starting CUST_NO. Parameter Y is the terminating CUST_NO. Hence, the true condition   of X < Y is required for the program to execute.

USE SDB1

DELIMITER ##

-- =================================

-- x is the starting CUST_NO

-- y is the ending CUST_NO

-- The procedure terminates when x=y

-- =================================

DROP PROCEDURE IF EXISTS InsCust##

CREATE PROCEDURE InsCust(x INT, y INT)

BEGIN

IF x < y THEN

    DECLARE fn VARCHAR(30);

    DECLARE ln VARCHAR(30);

    DECLARE mn VARCHAR(30);

    DECLARE dn VARCHAR(128);

    DECLARE vCUST_NO      INT;

    DECLARE vCUST_NAME    VARCHAR(128);

    DECLARE vCUST_EMAIL   VARCHAR(128);

    DECLARE vCUST_MOBILE  VARCHAR(32);

    DECLARE vCUST_ADDRESS VARCHAR(32);

    DECLARE vPOL_VALUE    FLOAT(8, 2);

    DECLARE pt VARCHAR(8);

    DECLARE dt VARCHAR(8);

    EOL: LOOP

       IF x = y THEN

           LEAVE  EOL;

       END IF;

       SET x = x + 1;

       SET vCUST_NO = x;

       SELECT UPPER(rf_name) into @fn

       FROM RANDOM_FIRST ORDER BY rand() LIMIT 1;

       SELECT UPPER(rm_name) into @mn

       FROM RANDOM_MIDDLE ORDER BY rand() LIMIT 1;

       SELECT UPPER(rl_name) into @ln

       FROM RANDOM_LAST ORDER BY rand() LIMIT 1;

       SELECT UPPER(rd_name) into @dn

       FROM RANDOM_DOMAIN ORDER BY rand() LIMIT 1;

       SET vCUST_MOBILE =  CONCAT('704-', FLOOR(RAND() * 999), '-',

                                  FLOOR(RAND() * 9999));

       SET vCUST_NAME = CONCAT(@fn, ' ', @mn, ' ', @ln);

       SET vCUST_EMAIL =  CONCAT(@fn, '.', @ln, '@', @dn, '.COM');

       SET vCUST_ADDRESS =  CONCAT('PO BOX ', FLOOR(RAND() * 99999), ',

                                    NC 27306, USA');

       INSERT INTO CUSTOMERS VALUES(vCUST_NO, vCUST_NAME, vCUST_EMAIL,

                                    vCUST_MOBILE, vCUST_ADDRESS);

       COMMIT;

       SELECT pt_code INTO @pt FROM POLICY_TYPES ORDER BY rand() LIMIT 1;

       SELECT dt_code INTO @dt FROM DISCOUNT_TYPES ORDER BY rand() LIMIT 1;

       SET vPOL_VALUE = ROUND(RAND() * 9999.99, 2);

       INSERT INTO POLICIES VALUES(1000+vCUST_NO, CURDATE(),

                                   DATE_ADD(CURDATE(), INTERVAL 1 YEAR),

                                   vPOL_VALUE, vPOL_VALUE, @pt, @dt,

                                   vPOL_VALUE, vCUST_NO);

       COMMIT;

    END LOOP;

END IF;

END;

##

DELIMITER ;

Execute the Applications

Run the applications for SDB1, SDB2, SDB3 and SDB4. Query the maximum value to avoid a primary key constraint violation.

mysql> USE SDB1

Database changed

mysql> SELECT MAX(cust_no) FROM CUSTOMERS;

+--------------+

| MAX(cust_no) |

+--------------+

|     10550084 |

+--------------+

1 row in set (0.21 sec)

mysql> CALL InsCust(10550085, 10550100);

Query OK, 0 rows affected (2.02 sec)

mysql>

mysql> USE SDB2

Database changed

mysql> SELECT max(cust_no) FROM CUSTOMERS;

+--------------+

| max(cust_no) |

+--------------+

|     10548858 |

+--------------+

1 row in set (0.16 sec)

mysql> CALL InsCust(10548859, 10548900);

Query OK, 0 rows affected (3.56 sec)

mysql>

mysql> USE SDB3

Database changed

mysql> SELECT max(cust_no) FROM CUSTOMERS;

+--------------+

| max(cust_no) |

+--------------+

|     10548765 |

+--------------+

1 row in set (0.16 sec)

mysql> CALL InsCust(10548766, 10548800);

Query OK, 0 rows affected (3.56 sec)

mysql>

mysql> USE SDB4

Database changed

mysql> SELECT max(cust_no) FROM CUSTOMERS;

+--------------+

| max(cust_no) |

+--------------+

|     10548234 |

+--------------+

1 row in set (0.16 sec)

mysql> CALL InsCust(10548235, 10548300);

Query OK, 0 rows affected (3.56 sec)