ORA 01858 error in SQL Loader 1

[kevina555]

Hi All,

I'm a new subscriber to this forum, but have been a great admirer and regular visitor. Very Informative. I've now run into a problem while using SQL Loader. I've used this in the past, but never had suchan irritating issue.

I'm trying to load a delimited file into Oracle using SQLLDR. I have a couple of dates in the INFILE. When I try to declare it as

STMTDATE Date "MM/DD/YYYY hh24:mi:ss"

in the CTL file, I get an error

Record 1: Rejected - Error on table FCSTATEMENTS, column STMTDATE.
ORA-01858: a non-numeric character was found where a numeric was expected

I also tried STMTDATE Date "MM/DD/YYYY hh24:mi:ss" "to_date("stmtdate, 'MM/DD/YYYY hh24:mi:ss'"

I get the same error. I've even tried to set the nls_date_format through SQL+, but didn't work(here's another question: Why am I not getting the same result in TOAD as I got in SQL+ after I altered the nls-date-format?).

The format for the target column is date, in 9i.
The source fields are like: The last but one being the field of interest.
(847)576-5000~~Y~12/31/1992 00:00:00~0

I don't know what I'm doing wrong. I'm running out of options. Any expedited response would be of a great help.

 

[LKBrwnDBA]

Try:

STMTDATE Timestamp "MM/DD/YYYY hh24:mi:ss"

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[LKBrwnDBA]

Post your control file.




----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[kevina555]

Hi LkBrwnDBA,

Thank you for the suggestion. I really appreciate the response. I have tried using the TimeStamp definition previously and even now, after your suggestion. I get the error

Record 1: Rejected - Error on table FCSTATEMENTS, column STMTDATE.
ORA-01843: not a valid month

even though all the dates appear to have a valid month to the naked eye.

Anyways, here is my CTL file:


LOAD DATA
INFILE 'c:\ARKfeed0225.txt'
BADFILE 'c:\ARKfeedStmt0224.bad'
DISCARDFILE 'c:\ARKfeedStmt0224.dsc'
APPEND
INTO TABLE fcSTATEMENTS
FIELDS TERMINATED BY "~"
TRAILING NULLCOLS
( XLONGNAME FILLER CHAR,
CUSTOMERID SEQUENCE (19000,1),
XREMCODE FILLER CHAR,
XCOMPANYEXPDATE FILLER CHAR,
XSTMTTYPE FILLER CHAR,
XYEARFOUND FILLER CHAR,
XCOUNTRY FILLER CHAR,
XCuurency FILLER CHAR,
XFinUnits FILLER CHAR,
XAssetsinUS$ FILLER CHAR,
XSource FILLER CHAR,
XINDCODE FILLER CHAR,
XOwnership FILLER CHAR,
XGovetSens FILLER CHAR,
XMarketShare FILLER CHAR,
XDUNS FILLER CHAR,
XUltimateDUNS FILLER CHAR,
XCUSIP# FILLER CHAR,
XADDRESS1 FILLER CHAR,
XCITY FILLER CHAR,
XREGIONORSTATE FILLER CHAR,
XPOSTALCODE FILLER CHAR,
XPhone FILLER CHAR,
XFax FILLER CHAR,
XStmtPeriod FILLER CHAR,
STMTDATE TimeStamp "MM/DD/YYYY HH24:MI:SS",
XAuditopinionCode FILLER CHAR,
XNetSales FILLER CHAR,
XDepAmort FILLER CHAR,
XInterestExpenses FILLER CHAR,
XCurrentAssets FILLER CHAR,
XAccountsPayable FILLER CHAR,
XShortTermDebt FILLER CHAR,
XCurrPortionLongTermdebt FILLER CHAR,
XCurrentLiabilities FILLER CHAR,
XLongTermDebt FILLER CHAR,
XCapitalLeases FILLER CHAR,
XACCOUNTID FILLER CHAR,
XPeriodStartDate FILLER CHAR,
XBALANCEVALUE FILLER CHAR,
XAccountDesc FILLER CHAR,

--StmtDate timestamp "(select sysdate from dual)",
ContextID INTEGER "0",
StatementID INTEGER "000",
Periods integer "12",
Duplicates integer "0",
CashflwreconcileID integer "-1",
GrowthReconcileID integer "-1",
Flags integer "49160",
NeedsRecalc integer "-6")





Just out of curiosity, what should the nls_lang parameter be?

Thanks......

 

[LKBrwnDBA]

Post your control file.




----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[kevina555]

Hi LKBrwnDBA,

I have already posted my control file.

Anyways, here it is again.....


LOAD DATA
INFILE 'c:\ARKfeed0225.txt'
BADFILE 'c:\ARKfeedStmt0224.bad'
DISCARDFILE 'c:\ARKfeedStmt0224.dsc'
APPEND
INTO TABLE fcSTATEMENTS
FIELDS TERMINATED BY "~"
TRAILING NULLCOLS
( XLONGNAME FILLER CHAR,
CUSTOMERID SEQUENCE (19000,1),
XREMCODE FILLER CHAR,
XCOMPANYEXPDATE FILLER CHAR,
XSTMTTYPE FILLER CHAR,
XYEARFOUND FILLER CHAR,
XCOUNTRY FILLER CHAR,
XCuurency FILLER CHAR,
XFinUnits FILLER CHAR,
XAssetsinUS$ FILLER CHAR,
XSource FILLER CHAR,
XINDCODE FILLER CHAR,
XOwnership FILLER CHAR,
XGovetSens FILLER CHAR,
XMarketShare FILLER CHAR,
XDUNS FILLER CHAR,
XUltimateDUNS FILLER CHAR,
XCUSIP# FILLER CHAR,
XADDRESS1 FILLER CHAR,
XCITY FILLER CHAR,
XREGIONORSTATE FILLER CHAR,
XPOSTALCODE FILLER CHAR,
XPhone FILLER CHAR,
XFax FILLER CHAR,
XStmtPeriod FILLER CHAR,
STMTDATE TimeStamp "MM/DD/YYYY HH24:MI:SS",
XAuditopinionCode FILLER CHAR,
XNetSales FILLER CHAR,
XDepAmort FILLER CHAR,
XInterestExpenses FILLER CHAR,
XCurrentAssets FILLER CHAR,
XAccountsPayable FILLER CHAR,
XShortTermDebt FILLER CHAR,
XCurrPortionLongTermdebt FILLER CHAR,
XCurrentLiabilities FILLER CHAR,
XLongTermDebt FILLER CHAR,
XCapitalLeases FILLER CHAR,
XACCOUNTID FILLER CHAR,
XPeriodStartDate FILLER CHAR,
XBALANCEVALUE FILLER CHAR,
XAccountDesc FILLER CHAR,

--StmtDate timestamp "(select sysdate from dual)",
ContextID INTEGER "0",
StatementID INTEGER "000",
Periods integer "12",
Duplicates integer "0",
CashflwreconcileID integer "-1",
GrowthReconcileID integer "-1",
Flags integer "49160",
NeedsRecalc integer "-6")

 

[LKBrwnDBA]

Ooops, posted twice...

NLS_LANG does not affect your load.

NLS_LANG environment variable supercedes the NLS_LANGUAGE database parameter.

I will check your CTL file and get back to you.


----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[kevina555]

Thank you for the info.....I did not have any value under the nls_lang parameter box in the registry for hkey\localmachine\software\oracle

so, I added AMERICAN_AMERICA.WEISO8859P1 as the value. Also, the nls_lang value for hkey\localmachine\software\oracle\homeo

had a different value. I modified it to the same AMERICAN_AMERICA.WEISO8859P1


This, I did by reading on one of the forums, after trying out all the available options. Hope this does not affect any suggestion you have for me......

Please let me know if I have to change these.....

Thank you

 

[sem]

Are you sure that your STMTDATE is in "MM/DD/YYYY HH24:MI:SS" and not "DD/MM/YYYY HH24:MI:SS" (also quite popular in many countries) format?

Regards, Dima

http://seminihin.narod.ru

 

[LKBrwnDBA]

OK the problem is the following:

Your input data file has these 5 fields:
Phone or fax847)576-5000~
empty?:~
some flag: Y~
stmtdate: 12/31/1992 00:00:00~
and?: 0

Then you have some fields that apperar to be CONSTRANTS like:

ContextID INTEGER "0",
StatementID INTEGER "000",
Periods integer "12",
Duplicates integer "0",
CashflwreconcileID integer "-1",
GrowthReconcileID integer "-1",
Flags integer "49160",
NeedsRecalc integer "-6")

Therefore, get rid off all the extra FILLER because these serve only as place-holders when the field EXISTS in the input file and you want to skip it.

Now, your CTL file should look something like this:

LOAD DATA
      INFILE 'c:\ARKfeed0225.txt'
      BADFILE 'c:\ARKfeedStmt0224.bad'
      DISCARDFILE 'c:\ARKfeedStmt0224.dsc'
    APPEND
       INTO TABLE fcSTATEMENTS
       FIELDS TERMINATED BY "~"
       TRAILING NULLCOLS
      ( CUSTOMERID                 SEQUENCE (19000,1),
        XPhone_or_Fax?,
        Xemptyfield                FILLER CHAR,
        XAuditopinionCode,
        STMTDATE                   DATE "MM/DD/YYYY HH24:MI:SS",
        ContextID                  CONSTANT "0",
        StatementID                CONSTANT "000",
        Periods                    CONSTANT "12",
        Duplicates                 CONSTANT "0",
        CashflwreconcileID         CONSTANT "-1",
        GrowthReconcileID          CONSTANT "-1",
        Flags                      CONSTANT "49160",
        NeedsRecalc                CONSTANT "-6")

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[kevina555]

When I look at the DB through TOAD, I get "MM/DD/YYYY hh24:mi:ss"

When I query the same Db through SQLPlus, I get "DD-MON-YY" format results. Now, I understand that the DB has a default format of DD-MON-YY for any column declared as a DATE variable, but I seem to be too confused with this difference, and which to trust.

 

[kevina555]

I'm extremely sorry for not mentioning this in the same message, but the INFILE I sent you appears only 5 fields because I sent you only the ones surrounding STMTDATE.

Here's my ugly scenario:

In fact, there are a total of 41 fields in the INFILE that need to be transferred to 9 different tables, with 9 different control files (I didn't use multiple load in a single CTL file because all the tables have a primary key that needs to be a sequence.....I might be taking the long way, I donno).That is the reason for all the other fillers. They are used in other table loads. The original record with all the 41 records is:


Motorola Inc~38815.00~~~Y~1928.00~USA~USD~Millions~0.00~GE CMF Fleet Services~3663~Private~L~9.00~00-132-5463~~62007610~1303 E. Algonquin Road~Schaumburg~IL~60196~(847)576-5000~~Y~12/31/1992 00:00:00~0~13303.00~1000.00~196.00~5218.00~1127.00~437.00~1771.00~3335.00~1258.00~0.00~11717.00~01/01/1992 00:00:00~2558.00~Accounts receivable

 

[LKBrwnDBA]

It is more cumbersome to create 9 control files, but easier to debug.

Create your CTL files folowing these rules:

1) First code ALL (41) fields/fillers in the order they appear in the INPUT file (including any trailing fields you may not use on that particular table).

2) For DATE fields code the correct format as it comes in the INPUT file.

2) Next code the SEQUENCES and CONSTANTS.

Good luck.

PS: Use DATE instead of TIMESTAMP on STMTDATE.













----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[kevina555]

Hi LkBrwnDBA,

Thank you for your suggestions. I have followed all in writing the control files. But still.....I think I'm out of luck on this.....Thanks anyways. I really appreciate all your effort.

Thank you

 

[LKBrwnDBA]

Check the number of fields!, i tested this ctl file with your data and it worked:

LOAD DATA
      INFILE * --'c:\ARKfeed0225.txt'
      --BADFILE 'c:\ARKfeedStmt0224.bad'
      --DISCARDFILE 'c:\ARKfeedStmt0224.dsc'
    APPEND
       INTO TABLE fcSTATEMENTS
       FIELDS TERMINATED BY "~" TRAILING NULLCOLS
      ( XLONGNAME               FILLER CHAR,
        XREMCODE                FILLER CHAR,
        XCOMPANYEXPDATE         FILLER CHAR,
        XSTMTTYPE               FILLER CHAR,
        XYEARFOUND              FILLER CHAR,
        XCOUNTRY                FILLER CHAR,
        XCurency                FILLER CHAR,
        XFinUnits               FILLER CHAR,
        XAssetsinUS$            FILLER CHAR,
        XSource                 FILLER CHAR,
        XINDCODE                FILLER CHAR,
        XOwnership              FILLER CHAR,
        XGovetSens              FILLER CHAR,
        XMarketShare            FILLER CHAR,
        XDUNS                   FILLER CHAR,
        XUltimateDUNS           FILLER CHAR,
        XCUSIP#                 FILLER CHAR,
        XADDRESS1               FILLER CHAR,
        XCITY                   FILLER CHAR,
        XREGIONORSTATE          FILLER CHAR,
        XPOSTALCODE             FILLER CHAR,
        XPhone                  FILLER CHAR,
        XFax,
        XStmtPeriod             FILLER CHAR,
        XAuditopinionCode,
        STMTDATE                DATE "MM/DD/YYYY HH24:MI:SS",
        XNetSales               FILLER CHAR,
        XDepAmort               FILLER CHAR,
        XInterestExpenses       FILLER CHAR,
        XCurrentAssets          FILLER CHAR,
        XAccountsPayable        FILLER CHAR,
        XShortTermDebt          FILLER CHAR,
        XCurrPortionLongTermdebt FILLER CHAR,
        XCurrentLiabilities     FILLER CHAR,
        XLongTermDebt           FILLER CHAR,
        XCapitalLeases          FILLER CHAR,
        XACCOUNTID              FILLER CHAR,        
        XPeriodStartDate        FILLER CHAR,
        XBALANCEVALUE           FILLER CHAR,
        XAccountDesc            FILLER CHAR,
        CUSTOMERID              SEQUENCE (19000,1),
        ContextID               CONSTANT "0",
        StatementID             CONSTANT "000",
        Periods                 CONSTANT "12",
        Duplicates              CONSTANT "0",
        CashflwreconcileID      CONSTANT "-1",
        GrowthReconcileID       CONSTANT "-1",
        Flags                   CONSTANT "49160",
        NeedsRecalc             CONSTANT "-6")            
BEGINDATA
Motorola Inc~38815.00~~~Y~1928.00~USA~USD~Millions~0.00~GE CMF Fleet Services~3663~Private~L~9.00~00-132-5463~~62007610~1303 E. Algonquin Road~Schaumburg~IL~60196~(847)576-5000~~Y~12/31/1992 00:00:00~0~13303.00~1000.00~196.00~5218.00~1127.00~437.00~1771.00~3335.00~1258.00~0.00~11717.00~01/01/1992 00:00:00~2558.00~Accounts receivable

and this is the log:

SQL*Loader: Release 8.1.7.4.0 - Production on Mon Feb 28 14:13:43 2005

(c) Copyright 2000 Oracle Corporation.  All rights reserved.

Control File:   c0.ctl
Data File:      c0.ctl
  Bad File:     c0.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 65536 bytes
Continuation:    none specified
Path used:      Conventional

Table FCSTATEMENTS, loaded from every logical record.
Insert option in effect for this table: APPEND
TRAILING NULLCOLS option in effect

   Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
XLONGNAME                           FIRST     *   ~       CHARACTER            
  (FILLER FIELD)
XREMCODE                             NEXT     *   ~       CHARACTER            
  (FILLER FIELD)
XCOMPANYEXPDATE                      NEXT     *   ~       CHARACTER            
  (FILLER FIELD)
XSTMTTYPE                            NEXT     *   ~       CHARACTER            
  (FILLER FIELD)
XYEARFOUND                           NEXT     *   ~       CHARACTER            
  (FILLER FIELD)
XCOUNTRY                             NEXT     *   ~       CHARACTER            
  (FILLER FIELD)
XCURENCY                             NEXT     *   ~       CHARACTER            
  (FILLER FIELD)
XFINUNITS                            NEXT     *   ~       CHARACTER            
  (FILLER FIELD)
XASSETSINUS$                         NEXT     *   ~       CHARACTER            
  (FILLER FIELD)
XSOURCE                              NEXT     *   ~       CHARACTER            
  (FILLER FIELD)
XINDCODE                             NEXT     *   ~       CHARACTER            
  (FILLER FIELD)
XOWNERSHIP                           NEXT     *   ~       CHARACTER            
  (FILLER FIELD)
XGOVETSENS                           NEXT     *   ~       CHARACTER            
  (FILLER FIELD)
XMARKETSHARE                         NEXT     *   ~       CHARACTER            
  (FILLER FIELD)
XDUNS                                NEXT     *   ~       CHARACTER            
  (FILLER FIELD)
XULTIMATEDUNS                        NEXT     *   ~       CHARACTER            
  (FILLER FIELD)
XCUSIP#                              NEXT     *   ~       CHARACTER            
  (FILLER FIELD)
XADDRESS1                            NEXT     *   ~       CHARACTER            
  (FILLER FIELD)
XCITY                                NEXT     *   ~       CHARACTER            
  (FILLER FIELD)
XREGIONORSTATE                       NEXT     *   ~       CHARACTER            
  (FILLER FIELD)
XPOSTALCODE                          NEXT     *   ~       CHARACTER            
  (FILLER FIELD)
XPHONE                               NEXT     *   ~       CHARACTER            
  (FILLER FIELD)
XFAX                                 NEXT     *   ~       CHARACTER            
XSTMTPERIOD                          NEXT     *   ~       CHARACTER            
  (FILLER FIELD)
XAUDITOPINIONCODE                    NEXT     *   ~       CHARACTER            
STMTDATE                             NEXT     *   ~       DATE MM/DD/YYYY HH24:M
I:SS
XNETSALES                            NEXT     *   ~       CHARACTER            
  (FILLER FIELD)
XDEPAMORT                            NEXT     *   ~       CHARACTER            
  (FILLER FIELD)
XINTERESTEXPENSES                    NEXT     *   ~       CHARACTER            
  (FILLER FIELD)
XCURRENTASSETS                       NEXT     *   ~       CHARACTER            
  (FILLER FIELD)
XACCOUNTSPAYABLE                     NEXT     *   ~       CHARACTER            
  (FILLER FIELD)
XSHORTTERMDEBT                       NEXT     *   ~       CHARACTER            
  (FILLER FIELD)
XCURRPORTIONLONGTERMDEBT             NEXT     *   ~       CHARACTER            
  (FILLER FIELD)
XCURRENTLIABILITIES                  NEXT     *   ~       CHARACTER            
  (FILLER FIELD)
XLONGTERMDEBT                        NEXT     *   ~       CHARACTER            
  (FILLER FIELD)
XCAPITALLEASES                       NEXT     *   ~       CHARACTER            
  (FILLER FIELD)
XACCOUNTID                           NEXT     *   ~       CHARACTER            
  (FILLER FIELD)
XPERIODSTARTDATE                     NEXT     *   ~       CHARACTER            
  (FILLER FIELD)
XBALANCEVALUE                        NEXT     *   ~       CHARACTER            
  (FILLER FIELD)
XACCOUNTDESC                         NEXT     *   ~       CHARACTER            
  (FILLER FIELD)
CUSTOMERID                                                SEQUENCE (19000, 1)
CONTEXTID                                                 CONSTANT
    Value is '0'
STATEMENTID                                               CONSTANT
    Value is '000'
PERIODS                                                   CONSTANT
    Value is '12'
DUPLICATES                                                CONSTANT
    Value is '0'
CASHFLWRECONCILEID                                        CONSTANT
    Value is '-1'
GROWTHRECONCILEID                                         CONSTANT
    Value is '-1'
FLAGS                                                     CONSTANT
    Value is '49160'
NEEDSRECALC                                               CONSTANT
    Value is '-6'


Table FCSTATEMENTS:
  1 Row successfully loaded.
  0 Rows not loaded due to data errors.
  0 Rows not loaded because all WHEN clauses were failed.
  0 Rows not loaded because all fields were null.


Space allocated for bind array:                  52672 bytes(64 rows)
Space allocated for memory besides bind array:        0 bytes

Total logical records skipped:          0
Total logical records read:             1
Total logical records rejected:         0
Total logical records discarded:        0

Run began on Mon Feb 28 14:13:43 2005
Run ended on Mon Feb 28 14:13:43 2005

Elapsed time was:     00:00:00.09
CPU time was:         00:00:00.02

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[kevina555]

Hi LKBrwnDBA,

KUDOS TO YOU!!!!!


Although your solution did not work for me, it did trigger a thought (declaring the sequence at the end of the regular INFILE fields and giving a filler in its original place) and it worked splendid.

Although I said that I followed all your suggestions, I misinterpreted the second 2) in your list.

The flaw in your solution is that you have moved a field, XAUDITOPINIONCODE , over to before the STMTDATE (which is not desired). But, it showed me some light and the emtire credit goes to you. I would not have thought of this if it weren't for you.

Thanks a bunch for all this...........

 

[LKBrwnDBA]

You are wellcome.

Good luck!



----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[SantaMufasa]

Kevina,

A standard method by which we recognise and appreciate contributions such as LK's is by clicking the "Thank LKBrwnDBA for this valuable post" button. It appears to me that LK has invested hours of time in helping you rresolve this dicey issue and would probably appreciate the Purple Star.

Cheers,

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

@ 19:47 (28Feb05) UTC (aka "GMT" and "Zulu"),
@ 12:47 (28Feb05) Mountain Time

Do you use Oracle and live or work in Utah, USA? Then click here to join Utah Oracle Users Group on Tek-Tips.

 

[kevina555]

Hi Mufasa,

I apologize, this is my first time here and don't know the customs. Thank for the suggestion. I followed it.

LKBrwnDBA,

Thank you for the great help.....Many tahnks with all my heart(I donno about the stars and all, but you very well deserve a real one). You are a lifesaver.

Thank you.....