Toad World® Forums

Data generation in Toad, wrong content of ctl file


#1

Hi all,
Toad 13.1.0.78 x64 on Oracle 11.2.0.3 x64

I want to use Toad data generator with “foreign column” value (values should be randomly taken from other table).
I use this for two columns First_NAME and LAST_NAME.
When I defined it all and want to see generated CTL file, it shows initial scramble values. Exampl of start of generated ctl file

load data
characterset utf8
infile *
append
into table GDPR_EDO_UNKNOWN_M
fields terminated by “\t” optionally enclosed by ‘"’
(
KUMS_ID,
BAN,
FIRST_NAME EXPRESSION “(SELECT MALE_NAME FROM (SELECT MALE_NAME FROM GDPR_MALE_NAMES ORDER BY DBMS_RANDOM.VALUE) WHERE rownum = 1)”,
LAST_NAME EXPRESSION “(SELECT SURNAME FROM (SELECT SURNAME FROM GDPR_SURNAMES ORDER BY DBMS_RANDOM.VALUE) WHERE rownum = 1)”,
GENDER,
BIRTHDATE DATE “MM/DD/YYYY”
)
begindata
“sdeoGxFx” 1825437816 “i” 03/23/1956
“IHhAcqfOHHbbG” 1609036889 “T” 06/29/2004
“lIlMEVU” 1340439505 “Um” 02/27/1988
“IYrdRyQK” 1255994940 “x” 11/10/1958
“LYXfPItagn” 161787312 “pD” 07/27/1984
“lamexPWjIF” 330221710 “Iw” 09/23/1954
“bVLxlPVEOrhM” 729886779 “rP” 09/28/1991
“IvhGklrhthNmKK” 357204127 “k” 04/05/1959
“WZobNblqP” 780176878 “F” 11/04/1953
“dIRW” 102529986 “Al” 04/07/1998
“uBRwfRWV” 210250373 “kr” 10/18/1958
“lLzMmX” 1278371581 “Hj” 03/20/1966
“UeyojmJj” 790968968 “Sg” 01/28/1950
“kz” 985210018 “Su” 11/16/1964
“ZsalL” 774316024 “t” 10/04/1978
“W” 765405203 “yh” 05/05/1965

How to generate loader with proper (dbms_random values as they should be?
Brg
Damir
P.S.
Script file option work, but it is not usable for bigger number of records generation, so ctl should be perfect regardless, longer generation part-import will be fast enough


#2


This is how I define that field…


#3

Hi Damir,

I’m a bit confused to your question. Are you looking for the DBMS_RANDOM() functions to be used in the data portion of your SQL*Loader control file, or are you wondering why the data is starting with random characters?

If the former, the SQL*Loader export for Data Generation is only available if using the “Internal” data generation engine. I can look at adding SQL*Loader export capabilities using the DBMS_RANDOM() data generation engine. If the latter, how do you have your table defined? I suspect it may be that your KUMS_ID column is defined as character data and your BAN column is defined as an integer. When I try it locally here, using the following for my table DDL:

CREATE TABLE TESTTABLE
(
KUMS_ID INTEGER,
BAN VARCHAR2(20 BYTE),
FIRST_NAME VARCHAR2(40 BYTE),
LAST_NAME VARCHAR2(40 BYTE),
GENDER VARCHAR2(20 BYTE),
BIRTHDATE DATE
)

The control file appears to be created correctly and imports correctly. Can you perhaps provide a bit more information on the issue you’re seeing? We can try to troubleshoot it a bit further from there.

Thanks!

-John


#4

Start Data generation for one table
Use internal Random Data Engine
Uncheck all columns but 3 (in mine case)
for two varchar2 columns (first_name and last_name) I choose Foreign Column (like on the picture) generation
for third one, date column I leave as is
other columns are uncheck for generation
click on the icon “create SQL Loader file”
generated content is like on the initial post, but here is once again:

load data
characterset utf8
infile *
append
into table Q383211.GDPR_EDO_UNKNOWN_M
fields terminated by “\t” optionally enclosed by ‘"’
(
KUMS_ID,
BAN,
FIRST_NAME EXPRESSION “(SELECT MALE_NAME FROM (SELECT MALE_NAME FROM Q383211.GDPR_MALE_NAMES ORDER BY DBMS_RANDOM.VALUE) WHERE rownum = 1)”,
LAST_NAME EXPRESSION “(SELECT SURNAME FROM (SELECT SURNAME FROM Q383211.GDPR_SURNAMES ORDER BY DBMS_RANDOM.VALUE) WHERE rownum = 1)”,
GENDER,
BIRTHDATE DATE “MM/DD/YYYY”
)
begindata
“OeYZuhtdRhqMttjac” 2051995481 “ZU” 12/01/1981
“DVxIyYFQfz” 475766296 “JL” 02/06/1993
“VanjB” 1315457327 “E” 11/13/1953
“nTUvF” 786787052 “vI” 03/10/1977
“vx” 490804294 “dw” 05/25/1960
“fA” 1672809920 “ba” 07/19/1953
“Gy” 76652890 “b” 07/19/1999
“aZt” 956446996 “sd” 10/25/1996
“qg” 1254579950 “qi” 09/01/1961
“R” 1436506669 “lY” 04/22/1959
“dBgWDGdJsUc” 1558574430 “Bn” 04/12/1965
“wQjOUooDXrqztbpMOXS” 456133205 “x” 03/10/1963
“OwnzjvmlWnzv” 1281104049 “U” 05/27/1977
“QYuxYYKCvBEWze” 683543431 “BF” 09/02/1988
“jufL” 219412496 “sn” 10/11/2005
“MlsJlOME” 1151503798 “rU” 07/04/2003

And I see that Toad did generated properly ctl file…it has placed initial “Random String Style” vallues, which were there when I open Generate data dialog.
is now all clear?

if not please reproduce steps like I described


#5

I.E. why columns which are unchecked are generated (they should remain the same!)?
Why foreign key values are not generated but scrambled initias random strings are?
I do not know what else to show?


#6

Hey Damir,

Ahh… This was the piece I was missing.

That looks to be a bug. It’s been fixed for Monday’s beta. Feel free to try that and see if that’ll solve your issue.

The foreign key values are part of the column declaration section in the control file. You shouldn’t see them in the data section at all.

Lemme know if this solves the issue when you give it a shot after Monday’s beta. And thanks for bringing this to our attention!!

-John


#7

Hi John,

I do not know if anyone of you tried to generate 3 fields in 10.000 rows table (using dbms_random and generated script)?
It takes cca 1 sec per one row!
What I would like is that generation of SQL Loader file is directly having ability to get those three generated fields (from foreign column) and leave other fields intact. Thois will be faster in generation and import will be the fastest possible.
Would that be as well in this beta you are proposing?
If not, please tell me what is the fastest way to generate data based on foreign column?


#8

Hi,
In 13.2.0.9 Beta (which I have download 12th November 2018 at 08:00 CET) still has that bug.
Brg
Damir


#9

Hi Damir,

You won’t see this change until the new beta is released later today. 13.2.0.9 is the beta that was released last Thursday.

-John