Data Generator generate wrong reference table columns for child table

Toad for Oracle
1

Hi
Oracle 13.3.0.181
Master table:
CREATE TABLE Q383211.CUSTOM (
EVENT_ID VARCHAR2(255 CHAR) NOT NULL,
CONCEPT VARCHAR2(255 CHAR),
EVENT VARCHAR2(255 CHAR),
EVENT_SOURCE VARCHAR2(255 CHAR),
EVENT_TIME NUMBER(19) NOT NULL,
LINKED_SESSION_ID VARCHAR2(255 CHAR),
EVENT_TIMESTAMP TIMESTAMP(3)
)
PARTITION BY RANGE (EVENT_TIMESTAMP)
INTERVAL(NUMTODSINTERVAL(1,'DAY'))
(
partition custom_p0 values less than (to_date('01-01-2020', 'DD-MM-YYYY'))
)

Detail table:
CREATE TABLE Q383211.LOG_ENTRY (
LOG_ENTRY_ID VARCHAR2(255 CHAR) NOT NULL,
EVENT_ID VARCHAR2(255 CHAR) NOT NULL,
EVENT_TIMESTAMP TIMESTAMP(3) NOT NULL, --THIS IS MUST!
CLIENT_IP VARCHAR2(255 CHAR),
CONSTRAINT CUSTOM_FK FOREIGN KEY (EVENT_ID,EVENT_TIMESTAMP) REFERENCES Q383211.CUSTOM
)
PARTITION BY REFERENCE.

When wants to generate data for child table:

image
image875×529 21.6 KB

image
image743×536 22.8 KB

Generated file that loads data has twice same EVENT_ID column:

DECLARE
BEGIN
-- Generate Random Data
INSERT INTO Q383211.LOG_ENTRY (
LOG_ENTRY_ID, EVENT_ID, EVENT_TIMESTAMP, CLIENT_IP
) VALUES (
'NcuHuzLlLJ',
( SELECT EVENT_ID FROM (SELECT EVENT_ID FROM "Q383211"."CUSTOM" ORDER BY DBMS_RANDOM.VALUE) WHERE rownum = 1 ),
( SELECT EVENT_ID FROM (SELECT EVENT_ID FROM "Q383211"."CUSTOM" ORDER BY DBMS_RANDOM.VALUE) WHERE rownum = 1 ),
'yViKSZqmIM'
);

two columns referential insert must be somehow differently generated.
Cheers
Damir

2

Simple solve it with mine script, which logic should be somehow implemented in Toad generation code:

BEGIN
for i in 1..10 LOOP
for j in (SELECT EVENT_ID,EVENT_TIMESTAMP FROM (SELECT EVENT_ID,EVENT_TIMESTAMP FROM "Q383211"."CUSTOM" ORDER BY DBMS_RANDOM.VALUE) WHERE rownum = 1) LOOP
INSERT INTO Q383211.LOG_ENTRY (
LOG_ENTRY_ID, EVENT_ID, EVENT_TIMESTAMP, CLIENT_IP
) VALUES (
DBMS_RANDOM.string ('a',32),
j.EVENT_ID,
j.EVENT_TIMESTAMP,
DBMS_RANDOM.string ('a',32)
);
END LOOP;
END LOOP;
COMMIT;
END;
/

3

Hi Damir,

It looks like that's a bug. I've opened a ticket to see if we can get that issue fixed. Thanks for pointing that issue out to us. I'll let you know when I'm able to get that addressed.

Thanks again!

-John

4

Hi Damir,

This issue should be fixed in today's beta. Feel free to give it a shot and let me know if that'll work for what you need and if you run into anything else!

Thanks for catching this one!

-John