Toad World® Forums

Toad for Oracle Beta Release 12.5.0.58

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

where is your error?
Please take a look in post: http://www.toadworld.com/products/toad-for-oracle/toad_for_oracle_beta_program/f/86/t/20789.aspx to see how to explain problem

Think that subject may be a little more descriptive that one that it is here …

:slight_smile:

Hey Carol,

You’re not doing anything wrong. I also see what you’re seeing. It looks like this is a small bug in the CTL export logic of Data Generation. For a quick work-around, adjust the header section of the CTL file to the following:

load data
characterset utf8
infile *
truncate
into table L1CLKDBA.FEDCOUNTERFEITDETAIL
fields terminated by “\t” optionally enclosed by ‘"’
(
. . .,
COUNTERFEITIDOLD NULLIF (COUNTERFEITIDOLD=“NULL”),
. . .
BPSREELKEY NULLIF (BPSREELKEY=“NULL”),
. . .
)
begindata
. . .

That should fix your issue, and I’ll get this fixed going forward for the next release. Thanks for catching this!

-John

Hi – here is my log file showing the error, you have the matching data file.

Copyright © 1982, 2011, Oracle and/or its affiliates. All rights reserved.

Commit point reached - logical record count 64

p3dcpor12g$ cat L1CLKDBA_FEDCOUNTERFEITDETAIL.log

SQL*Loader: Release 11.2.0.3.0 - Production on Fri Apr 4 11:20:00 2014

Copyright © 1982, 2011, Oracle and/or its affiliates. All rights reserved.

Control File: L1CLKDBA_FEDCOUNTERFEITDETAIL.ctl

Character Set utf8 specified for all input.

Data File: L1CLKDBA_FEDCOUNTERFEITDETAIL.ctl

Bad File: L1CLKDBA_FEDCOUNTERFEITDETAIL.bad

Discard File: none specified

(Allow all discards)

Number to load: ALL

Number to skip: 0

Errors allowed: 50

Bind array: 64 rows, maximum of 256000 bytes

Continuation: none specified

Path used: Conventional

Table L1CLKDBA.FEDCOUNTERFEITDETAIL, loaded from every logical record.

Insert option in effect for this table: TRUNCATE

Column Name Position Len Term Encl Datatype

Hi - I have an new problem.

I just upgraded to 12.5.058 , and generate data only provides the PK column, no others, seems something has changed for Data generation in this version.

Hey Carol,

Yes, this was a bug that was inadvertently introduced in Beta 58 when fixing something else. Sorry about that. It’s already been fixed in the code and will appear in the next beta, which should be today.

Sorry about that, Carol! Let me know if the next beta doesn’t solve the problem, or if you run into anything else.

Thanks!

-John

P.S. Just an FYI – the fix for your original issue should also appear in the next beta.