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
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:
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.
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?
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?
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?
