Toad World® Forums

SQLLoader problem

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 MR_APP.ITEM_LOC_HIST
(
ITEM VARCHAR2(25 BYTE) NOT NULL,
LOC NUMBER(10) NOT NULL,
LOC_TYPE VARCHAR2(1 BYTE) NOT NULL,
EOW_DATE DATE NOT NULL,
WEEK_454 NUMBER(2),
MONTH_454 NUMBER(2),
YEAR_454 NUMBER(4),
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),
CREATE_DATETIME DATE NOT NULL,
LAST_UPDATE_DATETIME DATE NOT NULL,
LAST_UPDATE_ID VARCHAR2(30 BYTE) NOT NULL
)
NOCOMPRESS
TABLESPACE MR_APP
PCTUSED 0
PCTFREE 10
INITRANS 6
MAXTRANS 255
STORAGE (
BUFFER_POOL DEFAULT
)
PARTITION BY RANGE (EOW_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
)

I export table through SQLLoader interfacce:

Content of that file is here:

  • 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.BAD'
    DISCARDFILE './ITEM_LOC_HIST.DSC'
    DISCARDMAX 1
    APPEND INTO TABLE ITEM_LOC_HIST
    Fields terminated by ";" Optionally enclosed by '"'
    (
    ITEM,
    LOC NULLIF (LOC="NULL"),
    LOC_TYPE,
    EOW_DATE DATE "MM/DD/YYYY HH24:MI:SS" NULLIF (EOW_DATE="NULL"),
    WEEK_454 NULLIF (WEEK_454="NULL"),
    MONTH_454 NULLIF (MONTH_454="NULL"),
    YEAR_454 NULLIF (YEAR_454="NULL"),
    SALES_TYPE,
    SALES_ISSUES NULLIF (SALES_ISSUES="NULL"),
    VALUE,
    GP,
    STOCK NULLIF (STOCK="NULL"),
    RETAIL,
    AV_COST,
    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
    )
    BEGINDATA
    "01415022";5;"S";"06/23/2013 00:00:00";3;6;2013;"R";1;15,99;7,392;NULL;;;"06/17/2013 23:13:14";"06/17/2013 23:13:14";"RMS"
    "04030846";5;"S";"06/23/2013 00:00:00";3;6;2013;"P";16;107,04;16,512;NULL;;;"06/17/2013 23:13:14";"06/22/2013 23:08:45";"RMS"
    "05081679";5;"S";"06/23/2013 00:00:00";3;6;2013;"P";7;173,53;15,344;NULL;;;"06/17/2013 23:13:14";"06/22/2013 23:08:45";"RMS"
    "04145961";5;"S";"06/23/2013 00:00:00";3;6;2013;"R";1;13,39;2,562;NULL;;;"06/17/2013 23:13:15";"06/17/2013 23:13:15";"RMS"
    "02321008";5;"S";"06/23/2013 00:00:00";3;6;2013;"P";29;89,61;18,038;NULL;;;"06/17/2013 23:13:15";"06/21/2013 23:09:24";"RMS"
    "04194736";5;"S";"06/23/2013 00:00:00";3;6;2013;"R";6;29,34;6,072;NULL;;;"06/17/2013 23:13:15";"06/24/2013 07:07:12";"RMS"
    "01403009";5;"S";"06/23/2013 00:00:00";3;6;2013;"R";24;130,76;37,408;NULL;;;"06/17/2013 23:13:15";"06/24/2013 07:07:12";"RMS"
    "04190350";5;"S";"06/23/2013 00:00:00";3;6;2013;"R";5;42,95;10,46;NULL;;;"06/17/2013 23:13:15";"06/20/2013 23:09:35";"RMS"
    "04051251";5;"S";"06/23/2013 00:00:00";3;6;2013;"P";2;22,58;5,304;NULL;;;"06/17/2013 23:13:15";"06/19/2013 15:19:01";"RMS"
    "03180154";5;"S";"06/23/2013 00:00:00";3;6;2013;"R";26;134,94;33,072;NULL;;;"06/17/2013 23:13:15";"06/24/2013 07:07:12";"RMS"

Then I truncate table and tried to import with the same user, on the same host and same Toad:

I got error:

Then I removed "--" lines at the begining, save to a file named "ITEM_LOC_HIST_EDITED.ctl" and tried again

Then I got this error:

I was not studding too deep the cause but rather spent time to make this post more clear ..

:slight_smile:

Brg,

Damir Vadas

Hi,

I just find out that the same file run through command line (sqlldr) works OK.

Brg,

Damir Vadas

Your screen shots show data pump import, not sql loader.

Sometimes it is hard to believe that one glass of vine can last so long …

Sorry for this post.

8(

No problem, we’ve all been there!

One glass? Happy Friday!

From: damir.vadas_531 [mailto:bounce-damirvadas_531@toadworld.com]

Sent: Friday, November 15, 2013 12:17 PM

To: toadoraclebeta@toadworld.com

Subject: RE: [Toad for Oracle - Beta Discussion Forum] SQLLoader problem

RE: SQLLoader problem

Reply by damir.vadas_531

Sometimes it is hard to believe that one glass of vine can last so long …

Sorry for this post.

8(

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or
Unsubscribe from Toad for Oracle - Beta
notifications altogether.

Toad for Oracle - Beta Discussion Forum

Flag
this post as spam/abuse.

Ok ok ok … I admit … it was more then one … bottle!

:slight_smile: