SQLLoader problem III

Toad for Oracle - Beta
1

Hi ,

Toad 12.5.0.4 beta
Oracle 12.2.0.3 x64 EE Windows
Windows 7 Pro X64 SP1

I have a table:
CREATE TABLE RMS.ITEM_LOC_HIST_MTH
(
ITEM VARCHAR2(25 BYTE) NOT NULL,
CREATE_DATETIME DATE NOT NULL,
LAST_UPDATE_DATETIME DATE NOT NULL,
LAST_UPDATE_ID VARCHAR2(30 BYTE) NOT NULL,
LOC NUMBER(10) NOT NULL,
LOC_TYPE VARCHAR2(1 BYTE) NOT NULL,
EOM_DATE DATE NOT NULL,
MONTH_454 NUMBER(2) NOT NULL,
YEAR_454 NUMBER(4) NOT NULL,
SALES_TYPE VARCHAR2(1 BYTE) NOT NULL,
SALES_ISSUES NUMBER(12,4),
VALUE NUMBER(20,4),
GP NUMBER(20,4),
STOCK NUMBER(12,4),
RETAIL NUMBER(20,4),
AV_COST NUMBER(20,4)
)
NOCOMPRESS
TABLESPACE RETEK_DATA
RESULT_CACHE (MODE DEFAULT)
PCTUSED 0
PCTFREE 10
INITRANS 6
MAXTRANS 255
STORAGE (
BUFFER_POOL DEFAULT
FLASH_CACHE DEFAULT
CELL_FLASH_CACHE DEFAULT
)
PARTITION BY RANGE (EOM_DATE)
SUBPARTITION BY HASH (LOC)
SUBPARTITION TEMPLATE
(SUBPARTITION S01 TABLESPACE RETEK_DATA,
SUBPARTITION S02 TABLESPACE RETEK_DATA,
SUBPARTITION S03 TABLESPACE RETEK_DATA,
SUBPARTITION S04 TABLESPACE RETEK_DATA,
SUBPARTITION S05 TABLESPACE RETEK_DATA,
SUBPARTITION S06 TABLESPACE RETEK_DATA,
SUBPARTITION S07 TABLESPACE RETEK_DATA,
SUBPARTITION S08 TABLESPACE RETEK_DATA,
SUBPARTITION S09 TABLESPACE RETEK_DATA,
SUBPARTITION S10 TABLESPACE RETEK_DATA,
SUBPARTITION S11 TABLESPACE RETEK_DATA,
SUBPARTITION S12 TABLESPACE RETEK_DATA,
SUBPARTITION S13 TABLESPACE RETEK_DATA,
SUBPARTITION S14 TABLESPACE RETEK_DATA,
SUBPARTITION S15 TABLESPACE RETEK_DATA,
SUBPARTITION S16 TABLESPACE RETEK_DATA,
SUBPARTITION S17 TABLESPACE RETEK_DATA,
SUBPARTITION S18 TABLESPACE RETEK_DATA,
SUBPARTITION S19 TABLESPACE RETEK_DATA,
SUBPARTITION S20 TABLESPACE RETEK_DATA,
SUBPARTITION S21 TABLESPACE RETEK_DATA,
SUBPARTITION S22 TABLESPACE RETEK_DATA,
SUBPARTITION S23 TABLESPACE RETEK_DATA,
SUBPARTITION S24 TABLESPACE RETEK_DATA,
SUBPARTITION S25 TABLESPACE RETEK_DATA,
SUBPARTITION S26 TABLESPACE RETEK_DATA,
SUBPARTITION S27 TABLESPACE RETEK_DATA,
SUBPARTITION S28 TABLESPACE RETEK_DATA,
SUBPARTITION S29 TABLESPACE RETEK_DATA,
SUBPARTITION S30 TABLESPACE RETEK_DATA,
SUBPARTITION S31 TABLESPACE RETEK_DATA,
SUBPARTITION S32 TABLESPACE RETEK_DATA,
SUBPARTITION S33 TABLESPACE RETEK_DATA,
SUBPARTITION S34 TABLESPACE RETEK_DATA,
SUBPARTITION S35 TABLESPACE RETEK_DATA,
SUBPARTITION S36 TABLESPACE RETEK_DATA,
SUBPARTITION S37 TABLESPACE RETEK_DATA,
SUBPARTITION S38 TABLESPACE RETEK_DATA,
SUBPARTITION S39 TABLESPACE RETEK_DATA,
SUBPARTITION S40 TABLESPACE RETEK_DATA,
SUBPARTITION S41 TABLESPACE RETEK_DATA,
SUBPARTITION S42 TABLESPACE RETEK_DATA,
SUBPARTITION S43 TABLESPACE RETEK_DATA,
SUBPARTITION S44 TABLESPACE RETEK_DATA,
SUBPARTITION S45 TABLESPACE RETEK_DATA,
SUBPARTITION S46 TABLESPACE RETEK_DATA,
SUBPARTITION S47 TABLESPACE RETEK_DATA,
SUBPARTITION S48 TABLESPACE RETEK_DATA,
SUBPARTITION S49 TABLESPACE RETEK_DATA,
SUBPARTITION S50 TABLESPACE RETEK_DATA,
SUBPARTITION S51 TABLESPACE RETEK_DATA,
SUBPARTITION S52 TABLESPACE RETEK_DATA,
SUBPARTITION S53 TABLESPACE RETEK_DATA,
SUBPARTITION S54 TABLESPACE RETEK_DATA,
SUBPARTITION S55 TABLESPACE RETEK_DATA,
SUBPARTITION S56 TABLESPACE RETEK_DATA,
SUBPARTITION S57 TABLESPACE RETEK_DATA,
SUBPARTITION S58 TABLESPACE RETEK_DATA,
SUBPARTITION S59 TABLESPACE RETEK_DATA,
SUBPARTITION S60 TABLESPACE RETEK_DATA,
SUBPARTITION S61 TABLESPACE RETEK_DATA,
SUBPARTITION S62 TABLESPACE RETEK_DATA,
SUBPARTITION S63 TABLESPACE RETEK_DATA,
SUBPARTITION S64 TABLESPACE RETEK_DATA
)
( PARTITION ITEM_LOC_HIST_MTH_MAX VALUES LESS THAN (MAXVALUE)
NOLOGGING
NOCOMPRESS
TABLESPACE RETEK_DATA
PCTFREE 10
INITRANS 6
MAXTRANS 255
STORAGE (
MAXSIZE UNLIMITED
BUFFER_POOL DEFAULT
FLASH_CACHE DEFAULT
CELL_FLASH_CACHE DEFAULT
)
( SUBPARTITION ITEM_LOC_HIST_MTH_MAX_S01 TABLESPACE RETEK_DATA,
SUBPARTITION ITEM_LOC_HIST_MTH_MAX_S02 TABLESPACE RETEK_DATA,
SUBPARTITION ITEM_LOC_HIST_MTH_MAX_S03 TABLESPACE RETEK_DATA,
SUBPARTITION ITEM_LOC_HIST_MTH_MAX_S04 TABLESPACE RETEK_DATA,
SUBPARTITION ITEM_LOC_HIST_MTH_MAX_S05 TABLESPACE RETEK_DATA,
SUBPARTITION ITEM_LOC_HIST_MTH_MAX_S06 TABLESPACE RETEK_DATA,
SUBPARTITION ITEM_LOC_HIST_MTH_MAX_S07 TABLESPACE RETEK_DATA,
SUBPARTITION ITEM_LOC_HIST_MTH_MAX_S08 TABLESPACE RETEK_DATA,
SUBPARTITION ITEM_LOC_HIST_MTH_MAX_S09 TABLESPACE RETEK_DATA,
SUBPARTITION ITEM_LOC_HIST_MTH_MAX_S10 TABLESPACE RETEK_DATA,
SUBPARTITION ITEM_LOC_HIST_MTH_MAX_S11 TABLESPACE RETEK_DATA,
SUBPARTITION ITEM_LOC_HIST_MTH_MAX_S12 TABLESPACE RETEK_DATA,
SUBPARTITION ITEM_LOC_HIST_MTH_MAX_S13 TABLESPACE RETEK_DATA,
SUBPARTITION ITEM_LOC_HIST_MTH_MAX_S14 TABLESPACE RETEK_DATA,
SUBPARTITION ITEM_LOC_HIST_MTH_MAX_S15 TABLESPACE RETEK_DATA,
SUBPARTITION ITEM_LOC_HIST_MTH_MAX_S16 TABLESPACE RETEK_DATA,
SUBPARTITION ITEM_LOC_HIST_MTH_MAX_S17 TABLESPACE RETEK_DATA,
SUBPARTITION ITEM_LOC_HIST_MTH_MAX_S18 TABLESPACE RETEK_DATA,
SUBPARTITION ITEM_LOC_HIST_MTH_MAX_S19 TABLESPACE RETEK_DATA,
SUBPARTITION ITEM_LOC_HIST_MTH_MAX_S20 TABLESPACE RETEK_DATA,
SUBPARTITION ITEM_LOC_HIST_MTH_MAX_S21 TABLESPACE RETEK_DATA,
SUBPARTITION ITEM_LOC_HIST_MTH_MAX_S22 TABLESPACE RETEK_DATA,
SUBPARTITION ITEM_LOC_HIST_MTH_MAX_S23 TABLESPACE RETEK_DATA,
SUBPARTITION ITEM_LOC_HIST_MTH_MAX_S24 TABLESPACE RETEK_DATA,
SUBPARTITION ITEM_LOC_HIST_MTH_MAX_S25 TABLESPACE RETEK_DATA,
SUBPARTITION ITEM_LOC_HIST_MTH_MAX_S26 TABLESPACE RETEK_DATA,
SUBPARTITION ITEM_LOC_HIST_MTH_MAX_S27 TABLESPACE RETEK_DATA,
SUBPARTITION ITEM_LOC_HIST_MTH_MAX_S28 TABLESPACE RETEK_DATA,
SUBPARTITION ITEM_LOC_HIST_MTH_MAX_S29 TABLESPACE RETEK_DATA,
SUBPARTITION ITEM_LOC_HIST_MTH_MAX_S30 TABLESPACE RETEK_DATA,
SUBPARTITION ITEM_LOC_HIST_MTH_MAX_S31 TABLESPACE RETEK_DATA,
SUBPARTITION ITEM_LOC_HIST_MTH_MAX_S32 TABLESPACE RETEK_DATA,
SUBPARTITION ITEM_LOC_HIST_MTH_MAX_S33 TABLESPACE RETEK_DATA,
SUBPARTITION ITEM_LOC_HIST_MTH_MAX_S34 TABLESPACE RETEK_DATA,
SUBPARTITION ITEM_LOC_HIST_MTH_MAX_S35 TABLESPACE RETEK_DATA,
SUBPARTITION ITEM_LOC_HIST_MTH_MAX_S36 TABLESPACE RETEK_DATA,
SUBPARTITION ITEM_LOC_HIST_MTH_MAX_S37 TABLESPACE RETEK_DATA,
SUBPARTITION ITEM_LOC_HIST_MTH_MAX_S38 TABLESPACE RETEK_DATA,
SUBPARTITION ITEM_LOC_HIST_MTH_MAX_S39 TABLESPACE RETEK_DATA,
SUBPARTITION ITEM_LOC_HIST_MTH_MAX_S40 TABLESPACE RETEK_DATA,
SUBPARTITION ITEM_LOC_HIST_MTH_MAX_S41 TABLESPACE RETEK_DATA,
SUBPARTITION ITEM_LOC_HIST_MTH_MAX_S42 TABLESPACE RETEK_DATA,
SUBPARTITION ITEM_LOC_HIST_MTH_MAX_S43 TABLESPACE RETEK_DATA,
SUBPARTITION ITEM_LOC_HIST_MTH_MAX_S44 TABLESPACE RETEK_DATA,
SUBPARTITION ITEM_LOC_HIST_MTH_MAX_S45 TABLESPACE RETEK_DATA,
SUBPARTITION ITEM_LOC_HIST_MTH_MAX_S46 TABLESPACE RETEK_DATA,
SUBPARTITION ITEM_LOC_HIST_MTH_MAX_S47 TABLESPACE RETEK_DATA,
SUBPARTITION ITEM_LOC_HIST_MTH_MAX_S48 TABLESPACE RETEK_DATA,
SUBPARTITION ITEM_LOC_HIST_MTH_MAX_S49 TABLESPACE RETEK_DATA,
SUBPARTITION ITEM_LOC_HIST_MTH_MAX_S50 TABLESPACE RETEK_DATA,
SUBPARTITION ITEM_LOC_HIST_MTH_MAX_S51 TABLESPACE RETEK_DATA,
SUBPARTITION ITEM_LOC_HIST_MTH_MAX_S52 TABLESPACE RETEK_DATA,
SUBPARTITION ITEM_LOC_HIST_MTH_MAX_S53 TABLESPACE RETEK_DATA,
SUBPARTITION ITEM_LOC_HIST_MTH_MAX_S54 TABLESPACE RETEK_DATA,
SUBPARTITION ITEM_LOC_HIST_MTH_MAX_S55 TABLESPACE RETEK_DATA,
SUBPARTITION ITEM_LOC_HIST_MTH_MAX_S56 TABLESPACE RETEK_DATA,
SUBPARTITION ITEM_LOC_HIST_MTH_MAX_S57 TABLESPACE RETEK_DATA,
SUBPARTITION ITEM_LOC_HIST_MTH_MAX_S58 TABLESPACE RETEK_DATA,
SUBPARTITION ITEM_LOC_HIST_MTH_MAX_S59 TABLESPACE RETEK_DATA,
SUBPARTITION ITEM_LOC_HIST_MTH_MAX_S60 TABLESPACE RETEK_DATA,
SUBPARTITION ITEM_LOC_HIST_MTH_MAX_S61 TABLESPACE RETEK_DATA,
SUBPARTITION ITEM_LOC_HIST_MTH_MAX_S62 TABLESPACE RETEK_DATA,
SUBPARTITION ITEM_LOC_HIST_MTH_MAX_S63 TABLESPACE RETEK_DATA,
SUBPARTITION ITEM_LOC_HIST_MTH_MAX_S64 TABLESPACE RETEK_DATA )
)
NOCACHE
NOPARALLEL
MONITORING
;

I export data through SQLLoader as:

sqlloader_problem3.png
sqlloader_problem3.png445×710 43.4 KB

Generated SQLLoader file:
-- SQL Loader Control and Data File created by TOAD
-- Variable length, terminated enclosed data formatting

-- The format for executing this file with SQL Loader is:
-- SQLLDR control= Be sure to substitute your
-- version of SQL LOADER and the filename for this file.

-- Note: Nested table datatypes are not supported here and
-- will be exported as nulls.
LOAD DATA
INFILE *
BADFILE './ITEM_LOC_HIST_MTH_org.BAD'
DISCARDFILE './ITEM_LOC_HIST_MTH_org.DSC'
TRUNCATE INTO TABLE ITEM_LOC_HIST_MTH
Fields terminated by ";" Optionally enclosed by '"'
(
ITEM,
CREATE_DATETIME DATE "MM/DD/YYYY HH24:MI:SS" NULLIF (CREATE_DATETIME="NULL"),
LAST_UPDATE_DATETIME DATE "MM/DD/YYYY HH24:MI:SS" NULLIF (LAST_UPDATE_DATETIME="NULL"),
LAST_UPDATE_ID,
LOC NULLIF (LOC="NULL"),
LOC_TYPE,
EOM_DATE DATE "MM/DD/YYYY HH24:MI:SS" NULLIF (EOM_DATE="NULL"),
MONTH_454 NULLIF (MONTH_454="NULL"),
YEAR_454 NULLIF (YEAR_454="NULL"),
SALES_TYPE,
SALES_ISSUES NULLIF (SALES_ISSUES="NULL"),
VALUE,
GP,

2

Hi Damir,

Thanks for the details. I am in the middle of something else at the moment and can’t look at this immediately but will soon (probably later today or tomorrow). The"export dataset" dialog is not meant to provide every SQLLoader export option, but the created file should at least be functional! If “trailing nullcols” is all it takes to fix this, then I will add it to the export dataset dialog. If you want more options in your SQLLoader file, you should use the SQL*Loader export/import windows to create the control file.

-John

3

Hi Damir,

I created your table and then used Toad’s “Generate Data” feature to get some rows into it. Then I used the “Export Dataset” window to export the rows into a CTL file. The first thing I noticed was that the header was a little different:

(

ITEM,

CREATE_DATETIME DATE “MM/DD/YYYY HH24:MI:SS” NULLIF (CREATE_DATETIME=“NULL”),

LAST_UPDATE_DATETIME DATE “MM/DD/YYYY HH24:MI:SS” NULLIF (LAST_UPDATE_DATETIME=“NULL”),

LAST_UPDATE_ID,

LOC NULLIF (LOC=“NULL”),

LOC_TYPE,

EOM_DATE DATE “MM/DD/YYYY HH24:MI:SS” NULLIF (EOM_DATE=“NULL”),

MONTH_454 NULLIF (MONTH_454=“NULL”),

YEAR_454 NULLIF (YEAR_454=“NULL”),

SALES_TYPE,

SALES_ISSUES NULLIF (SALES_ISSUES=“NULL”),

VALUE NULLIF (VALUE=“NULL”),

GP NULLIF (GP=“NULL”),

STOCK NULLIF (STOCK=“NULL”),

RETAIL NULLIF (RETAIL=“NULL”),

AV_COST NULLIF (AV_COST=“NULL”)

)

Specifially, there were a lot more NULLIFs in mine. When I ran SQL*Loader with this header and your data, I didn’t get any errors (I did have to change decimal separator in your data from a comma to a period). Toad seems to be leaving them off when you have “display numbers in scientific notation” unchecked. In this case, Toad treats that data as a different datatype internally. So the fix is to add the NULLIF for that internal datatype. This will be fixed next beta. I do not see a need to add TRAILING NULLCOLs to the data export dialog with the fix in place.

-John

4

John,

“display numbers in scientific notation” is unchecked in mine case.

To be proper … I’ll send to your mail the whole ctl file so you can reproduce on data as I had … no a small chunk which obviously might be misleading to Toad export logic somehow.

Brg

Damir Vadas

5

Right, the problem is caused when the option is unchecked. It’s fixed.

6

John,

Ok… so no data is needed in this case. Good. Another issue fixed …

[H]

But please pay attention on mine post.
OPTIONS (DIRECT=TRUE)
UNRECOVERABLE

which is defined in SQL Export dialog and missing in created cpl file. I have opened separate thread (www.toadworld.com/.../20784.aspx) to accent this issue as well.

For the end, have a question according your statement:
you should use the SQL*Loader export/import windows to create the control file
What do you mean by this ? Which separate SQL*Loader export window you meant? Please help me.

Brg,
Damir Vadas

7

I see what you mean about direct and unrecoverable. I agree, something is wrong here. I will look at that next.

Oops, I don’t know WHAT I was thinking when I said "SQLLoader Export". I didn’t even have any wine! But if you want to have all of the available SQLLoader options, in the SQLLoader Wizard (the one you already found), you can set it to “Build Control File” and then specify whatever you want for SQLLoader options. Then in “export dataset” just use the “delimited text” option to create a data file without any SQL*Loader details. Sorry for the confusion!

8

wine fumes from mime celebration!?

:slight_smile: