Hi- I get some errors using the .CTL Files that are created for Toad Data Generator.
When I click on Allow Nulls for a colum, the sqlldr complains saying that the NULL text is not a valid value for the field.
What am I doing wrong? I invoke the loader just using the
sqlldr user/pw@db control = filename.ctl
Here is the first few lines of the CTL file I am getting this error with:
load data
characterset utf8
infile *
truncate
into table L1CLKDBA.FEDCOUNTERFEITDETAIL
fields terminated by “\t” optionally enclosed by ‘"’
(
COUNTERFEITKEY EXPRESSION “L1CLKDBA.FEDCOUNTERFEITDETAILSEQ.NextVal”,
COUNTERFEITIDOLD,
FEDOFFICEKEY EXPRESSION “(SELECT FEDOFFICEKEY FROM (SELECT FEDOFFICEKEY FROM L1CLKDBA.FEDOFFICE ORDER BY DBMS_RANDOM.VALUE) WHERE rownum = 1)”,
FEDTEAMKEY EXPRESSION “(SELECT FEDTEAMKEY FROM (SELECT FEDTEAMKEY FROM L1CLKDBA.FEDTEAM ORDER BY DBMS_RANDOM.VALUE) WHERE rownum = 1)”,
DENOMINATIONKEY EXPRESSION “(SELECT DENOMINATIONKEY FROM (SELECT DENOMINATIONKEY FROM L1CLKDBA.DENOMINATION ORDER BY DBMS_RANDOM.VALUE) WHERE rownum = 1)”,
BPSREELKEY,
DIFFERENCEKEY EXPRESSION “(SELECT DIFFERENCEKEY FROM (SELECT DIFFERENCEKEY FROM L1CLKDBA.FEDDIFFERENCE ORDER BY DBMS_RANDOM.VALUE) WHERE rownum = 1)”,
EXTERNALTRANSFERKEY EXPRESSION “(SELECT EXTERNALTRANSFERKEY FROM (SELECT EXTERNALTRANSFERKEY FROM L1CLKDBA.EXTERNALTRANSFER ORDER BY DBMS_RANDOM.VALUE) WHERE rownum = 1)”,
INVENTORYDETAILKEY EXPRESSION “(SELECT INVENTORYDETAILKEY FROM (SELECT INVENTORYDETAILKEY FROM L1CLKDBA.INVENTORYDETAIL ORDER BY DBMS_RANDOM.VALUE) WHERE rownum = 1)”,
DETECTEDBUSINESSDATE DATE “MM/DD/YYYY”,
SERIESYEARCODE EXPRESSION “(SELECT CURRENCYYEAR FROM (SELECT CURRENCYYEAR FROM L1CLKDBA.NEWCURRENCYSERIAL ORDER BY DBMS_RANDOM.VALUE) WHERE rownum = 1)”,
DISTRICTLETTER EXPRESSION “(SELECT DISTRICT FROM (SELECT DISTRICT FROM L1CLKDBA.NEWCURRENCYSERIAL ORDER BY DBMS_RANDOM.VALUE) WHERE rownum = 1)”,
SERIALNUMBER EXPRESSION “(SELECT BEGINNINGSERIALNUMBER FROM (SELECT BEGINNINGSERIALNUMBER FROM L1CLKDBA.NEWCURRENCYSERIAL ORDER BY DBMS_RANDOM.VALUE) WHERE rownum = 1)”,
RUNNUMBER EXPRESSION “(SELECT CARRIERRUNKEY FROM (SELECT CARRIERRUNKEY FROM L1CLKDBA.CARRIERRUN ORDER BY DBMS_RANDOM.VALUE) WHERE rownum = 1)”,
CIRCULARNUMBER,
SERIESYEAR EXPRESSION “(SELECT CURRENCYYEAR FROM (SELECT CURRENCYYEAR FROM L1CLKDBA.NEWCURRENCYSERIAL ORDER BY DBMS_RANDOM.VALUE) WHERE rownum = 1)”,
RAISEDDENOMINATIONKEY EXPRESSION “(SELECT DENOMINATIONKEY FROM (SELECT DENOMINATIONKEY FROM L1CLKDBA.DENOMINATION ORDER BY DBMS_RANDOM.VALUE) WHERE rownum = 1)”,
FACEPLATENUMBER,
BACKPLATENUMBER,
CHECKLETTER,
THREADEDNOTEINDICATOR,
COUNTRYCODE CONSTANT ‘USA’,
BUSINESSDATETIME DATE “MM/DD/YYYY”,
DEPOSITKEY EXPRESSION “(SELECT DEPOSITKEY FROM (SELECT DEPOSITKEY FROM L1CLKDBA.DEPOSIT ORDER BY DBMS_RANDOM.VALUE) WHERE rownum = 1)”,
CREATEDDATE DATE “MM/DD/YYYY”,
LASTMODIFIEDDATE DATE “MM/DD/YYYY”,
SOFTDELETEINDICATOR,
SOURCEOFDATA CONSTANT ‘Toad’,
VERSION CONSTANT 1
)
begindata
NULL NULL 08/02/1922 “T” “LOZUNDCPYY” “EMEXQGGFHO” “P” 1 04/10/2010 07/25/2081 05/23/2094 0
NULL NULL 08/19/1999 “k” “JPQFIEXOOO” “MUYRLGFERU” “N” 0 12/11/2050 08/25/2033 01/30/2026 0
NULL NULL 09/09/1934 “jNIFjS” “SXAKQASIGG” “MLQYWHFGNQ” “Z” 1 06/05/2098 10/03/1956 11/21/1954 1
NULL NULL 07/02/2065 “gCLguA” “XTNVFCAPSE” “NQPJTHXNGA” “B” 0 02/28/2075 07/02/1941 03/19/1997 0
NULL NULL 06/29/2019 “lOp” “WBKWYILWRY” “JTXLTGCJYR” “T” 0 06/13/2032 11/15/2035 07/07/1919 0
NULL NULL 01/03/1900 “q” “OOGOOPETMV” “MGIFDRKNQK” “R” 0 06/29/1916 06/17/1983 02/08/1982 0
NULL NULL 03/07/2064 “Sl” “AGHCWGBYUU” “TVZZPVPTKD” “G” 1 09/06/1942 10/16/2095 12/18/1920 1
NULL NULL 09/20/2087 “uI” “ATPKHZXYCB” “LMKVJVNWUN” “B” 0 10/01/1997 05/27/2083 05/15/1990 0
