Solved

sqlldr error when doing bulk insert into oracle 10g.

Posted on 2011-02-23
6
1,057 Views
Last Modified: 2012-05-11
I have included the log file generated from sqlldr command (dow_dividends.log).  I have also included a csv file and the sql create file.  I think my error has something to do with inserting numbers and the precision, buy I'm not sure.
This is the sqlldr command:
sqlldr user/password@db_name control=dow_dividends.ctl

 dow.sql dow-dividends.log dow-dividends.csv
0
Comment
Question by:dmalovich
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 3

Accepted Solution

by:
LFLFM earned 500 total points
ID: 34965142
Is your machine configured in english, number format is in english (eg. the decimal separator is . and not ,)?
Perhaps you need to set NLS_NUMERIC_CHARACTERS=".,"
0
 

Author Comment

by:dmalovich
ID: 34965802
my csv file is a comma seperated value file.  It has decimal point numbers in it.  example:

MMM,MMM,2.68,5.76,6.36,2.10,2.04,2.00,1.92,1.84,1.68,1.44,1.32,1.24,1.20,1.16,1.12,1.1,1.06,2.16,0.94,0.88,0.83,0.8,0.78,0.73,0.65,0.53,0.465,0.45,0.43752,0.425,0.41248,0.4,0.375,0.35,0.3,0.25,0.21248,0.181,0.16876,0.15652,0.13126,0.12036,0.11576,0.08201,
Alcoa,AA,-28.67,-14.87,-0.89,0.12,0.26,0.68,0.68,0.65,0.60,0.60,0.60,0.60,0.60,0.5,0.4025,0.28125,0.24375,0.252,0.225,0.2,0.2,0.20563,0.22813,0.41875,0.34,0.1625,0.13375,0.15,0.15,0.15,0.15,0.20625,0.19688,0.2,0.1625,0.11876,0.10626,0.08562,0.08376,0.08376,0.07938,0.075,0.075,0.075,
0
 
LVL 15

Expert Comment

by:Aaron Shilo
ID: 34968072
hi

could you use the BADFILE parameter to save the rejected rows and upload it .
so we can see the specific rejected rows.
0
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 

Author Comment

by:dmalovich
ID: 34969516
this is my .ctl file:

load data
infile 'C:\xampp\webpages\longrundata\dividends\dow_dividends.csv'
insert into table dow
fields terminated by ","
TRAILING NULLCOLS
 
(stock_name,stock_symbol,five,ten,twenty_five,year2010,year2009,      
 year2008,year2007,year2006,year2005,year2004,year2003,      
 year2002,year2001,year2000,year1999,year1998,year1997,
 year1996,year1995,year1994,year1993,year1992,year1991,
 year1990,year1989,year1988,year1987,year1986,year1985,      
 year1984,year1983,year1982,year1981,year1980,year1979,      
 year1978,year1977,year1976,year1975,year1974,year1973,
 year1972,year1971,year1970
)

these are the errors I get in the .log file:

Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
STOCK_NAME                          FIRST     *   ,       CHARACTER            
STOCK_SYMBOL                         NEXT     *   ,       CHARACTER            
FIVE                                 NEXT     *   ,       CHARACTER            
TEN                                  NEXT     *   ,       CHARACTER            
TWENTY_FIVE                          NEXT     *   ,       CHARACTER            
YEAR2010                             NEXT     *   ,       CHARACTER            
YEAR2009                             NEXT     *   ,       CHARACTER            
YEAR2008                             NEXT     *   ,       CHARACTER            
YEAR2007                             NEXT     *   ,       CHARACTER            
YEAR2006                             NEXT     *   ,       CHARACTER            
YEAR2005                             NEXT     *   ,       CHARACTER            
YEAR2004                             NEXT     *   ,       CHARACTER            
YEAR2003                             NEXT     *   ,       CHARACTER            
YEAR2002                             NEXT     *   ,       CHARACTER            
YEAR2001                             NEXT     *   ,       CHARACTER            
YEAR2000                             NEXT     *   ,       CHARACTER            
YEAR1999                             NEXT     *   ,       CHARACTER            
YEAR1998                             NEXT     *   ,       CHARACTER            
YEAR1997                             NEXT     *   ,       CHARACTER            
YEAR1996                             NEXT     *   ,       CHARACTER            
YEAR1995                             NEXT     *   ,       CHARACTER            
YEAR1994                             NEXT     *   ,       CHARACTER            
YEAR1993                             NEXT     *   ,       CHARACTER            
YEAR1992                             NEXT     *   ,       CHARACTER            
YEAR1991                             NEXT     *   ,       CHARACTER            
YEAR1990                             NEXT     *   ,       CHARACTER            
YEAR1989                             NEXT     *   ,       CHARACTER            
YEAR1988                             NEXT     *   ,       CHARACTER            
YEAR1987                             NEXT     *   ,       CHARACTER            
YEAR1986                             NEXT     *   ,       CHARACTER            
YEAR1985                             NEXT     *   ,       CHARACTER            
YEAR1984                             NEXT     *   ,       CHARACTER            
YEAR1983                             NEXT     *   ,       CHARACTER            
YEAR1982                             NEXT     *   ,       CHARACTER            
YEAR1981                             NEXT     *   ,       CHARACTER            
YEAR1980                             NEXT     *   ,       CHARACTER            
YEAR1979                             NEXT     *   ,       CHARACTER            
YEAR1978                             NEXT     *   ,       CHARACTER            
YEAR1977                             NEXT     *   ,       CHARACTER            
YEAR1976                             NEXT     *   ,       CHARACTER            
YEAR1975                             NEXT     *   ,       CHARACTER            
YEAR1974                             NEXT     *   ,       CHARACTER            
YEAR1973                             NEXT     *   ,       CHARACTER            
YEAR1972                             NEXT     *   ,       CHARACTER            
YEAR1971                             NEXT     *   ,       CHARACTER            
YEAR1970                             NEXT     *   ,       CHARACTER            

value used for ROWS parameter changed from 64 to 21
Record 1: Rejected - Error on table DOW, column FIVE.
ORA-01722: invalid number

Record 2: Rejected - Error on table DOW, column FIVE.
ORA-01722: invalid number................

this is the .bad file:
MMM,MMM,2.68,5.76,6.36,2.10,2.04,2.00,1.92,1.84,1.68,1.44,1.32,1.24,1.20,1.16,1.12,1.1,1.06,2.16,0.94,0.88,0.83,0.8,0.78,0.73,0.65,0.53,0.465,0.45,0.43752,0.425,0.41248,0.4,0.375,0.35,0.3,0.25,0.21248,0.181,0.16876,0.15652,0.13126,0.12036,0.11576,0.08201,
Alcoa,AA,-28.67,-14.87,-0.89,0.12,0.26,0.68,0.68,0.65,0.60,0.60,0.60,0.60,0.60,0.5,0.4025,0.28125,0.24375,0.252,0.225,0.2,0.2,0.20563,0.22813,0.41875,0.34,0.1625,0.13375,0.15,0.15,0.15,0.15,0.20625,0.19688,0.2,0.1625,0.11876,0.10626,0.08562,0.08376,0.08376,0.07938,0.075,0.075,0.075,
American Express,AXP,5.92,8.45,4.67,0.72,0.72,0.72,0.60,0.54,7.76,0.32,0.38,0.40,0.32,0.315,0.30033,0.225,0.3,0.375,0.225,1.59999,0.3332,0.24999,0.32,0.30668,0.28667,0.32332,0.36665,0.23001,0.22,0.21332,0.25999,0.13749,0.12501,0.16668,0.15,0.12916,0.10834,0,0,0,0,0,0,0,
AT&T,T,4.75,5.11,4.76,1.68,1.64,1.6,1.42,1.332,1.292,1.252,1.369,1.066,1.021,1.006,0.961,0.92,0.887,0.851,0.8155,0.781,0.74825,0.725,0.705,0.68,0.6425,0.61,0.42333,0.52499,0.49167,0.11667,0,0,0,0,0,0,0,0,0,0,0,0,0,0,
Bank of America,BAC,-54.80,-28.47,-5.09,0.04,0.04,2.24,2.4,2.12,1.9,1.7,1.44,1.22,1.14,1.03,0.925,0.795,0.685,0.6,0.52,0.47,0.41,0.3775,0.37,0.355,0.275,0.235,0.215,0.1475,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,
Boeing Co.,BA,6.96,9.47,7.64,1.68,1.68,1.60,1.40,1.20,1.00,0.77,0.68,0.68,0.68,0.56,0.56,0.56,0.56,0.545,0.50,0.50,0.50,0.50,0.50,0.475,0.38889,0.25556,0.31112,0.26668,0.21185,0.2074,0.2074,0.2074,0.2074,0.20739,0.07407,0.0395,0.05596,0.04115,0.03291,0.0247,0.01316,0.01316,0.01316,0.00987,
Caterpillar Inc.,CAT,9.35,9.56,14.18,1.72,1.68,1.56,1.32,1.10,0.91,0.78,0.71,0.70,0.69,0.665,0.625,0.55,0.45,0.375,0.30,0.1125,0.075,0.075,0.15,0.15,0.15,0.09424,0.06248,0.06248,0.06248,0.15626,0.18752,0.30002,0,0,0,0,0,0,0,0,0,0,0,0,
Chevron Corp.,CVX,7.16,7.92,6.42,2.84,2.66,2.53,2.26,2.01,1.75,1.53,1.43,1.40,1.325,1.30,1.24,1.22,1.14,1.04,0.962,0.9245,0.875,0.825,0.8125,0.7375,0.70,0.6375,0.60,0.60,0.60,0.45,0,0,0,0,0,0,0,0.26875,0.25,0.25,0.1672,0.18124,0.175,0.175,
Cisco Systems Inc.,CSCO,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,
Coca-Cola Company,KO,7.26,12.54,10.98,1.76,1.64,1.52,1.36,1.24,1.12,1.00,0.88,0.80,0.54,0.68,0.64,0.60,0.56,0.50,0.44,0.39,0.34,0.28,0.24,0.20,0.17,0.15,0.14,0.13,0.12332,0.115,0.11168,0.10332,0.09668,0.09,0.08168,0.07248,0.06416,0.05516,0.04792,0.04336,0.0375,0.03416,0.03292,0.0225,
DuPont & Co.,DD,2.07,1.59,4.80,1.64,1.64,1.64,1.52,1.48,1.46,1.40,1.40,1.40,1.40,1.40,1.40,1.365,1.23,1.115,1.015,0.91,0.88,0.87,0.84,0.81,0.725,0.61669,0.55002,0.50834,0.50,0.48334,0.41667,0.40,0.45834,0.45835,0.45835,0.4028,0.31946,0.29168,0.23613,0.30558,0.31946,0.3028,0.2778,0.2778,
Exxon Mobil Corp.,XOM,6.33,6.70,5.56,1.74,1.66,1.55,1.37,1.28,1.14,1.06,0.98,0.92,0.91,0.88,0.835,0.82,0.8125,0.78,0.75,0.7275,0.72,0.7075,0.67,0.6175,0.575,0.5375,0.475,0.45,0.43125,0.41875,0.3875,0.46875,0.375,0.3375,0.24375,0.20624,0.18752,0.17031,0.1953,0.15626,0.1328,0.11872,0.11875,0.08907,
General Electric,GE,-14.89,-3.55,6.35,0.46,0.61,1.24,1.15,1.03,0.91,0.82,0.77,0.73,0.66,0.57067,0.48668,0.41667,0.36001,0.31668,0.28166,0.24833,0.2175,0.19333,0.17333,0.16001,0.14168,0.11668,0.11042,0.09876,0.09293,0.08541,0.07812,0.0698,0.06563,0.06144,0.05728,0.05208,0.04375,0.0354,0.03332,0.03332,0.03124,0.02916,0.02864,0.02708,
Hewlett-Packard,HPQ,0.00,0.00,10.31,0.32,0.32,0.32,0.32,0.32,0.32,0.32,0.32,0.32,0.32,0.32,0.32,0.31,0.27,0.23,0.19125,0.14375,0.11875,0.10,0.06248,0.05248,0.0475,0.03502,0.03002,0.02752,0.02752,0.025,0.0206,0.01593,0.0125,0.01252,0.01016,0.01015,0.00626,0.00468,0.0039,0.00312,0.00312,0.11493,0.00312,0.00469,
Home Depot Inc.,HD,6.94,18.70,0.00,0.94,0.90,0.90,0.90,0.675,0.40,0.325,0.26,0.21,0.17,0.16,0.11334,0.07667,0.06334,0.0511,0.04222,0.03334,0.02501,0.01833,0.01221,0.00814,0.00543,0.00363,0.00066,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,
Intel Corp.,INTC,9.58,22.96,0.00,0.63,0.56,0.548,0.452,0.4,0.32,0.16,0.08,0.08,0.08,0.07,0.06,0.0325,0.0275,0.0225,0.0175,0.01376,0.01252,0.00313,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,
IBM Corp.,IBM,17.84,16.35,3.34,2.50,2.15,1.90,1.50,1.10,0.78,0.70,0.63,0.59,0.55,0.51,0.47,0.43,0.2875,0.325,0.25,0.25,0.395,1.21,1.21,1.21,1.1825,1.10,1.10,1.10,1.10,1.025,0.9275,0.86,0.86,0.86,0.86,0.72,0.625,0.50,0.40626,0.3475,0.28,0.27,0.26,0.18,
Johnson & Johnson,JNJ,7.72,11.67,13.66,2.11,1.93,1.795,1.62,1.455,1.275,1.095,0.925,0.795,0.70,0.62,0.545,0.485,0.425,0.3675,0.32,0.2825,0.2525,0.2225,0.1925,0.16375,0.14,0.12,0.10062,0.08592,0.07968,0.07344,0.06719,0.06061,0.05323,0.04636,0.04168,0.03544,0.02916,0.02188,0.01772,0.01511,0.01096,0.00934,0.00897,0.0071,
JPMorgan Chase ,JPM,-31.84,-17.32,-5.70,0.20,0.53,1.52,1.44,1.36,1.36,1.36,1.36,1.36,1.34,1.23333,1.05999,0.92667,0.80668,0.54001,0.48001,0.54668,0.45667,0.40,0.34965,0.76334,0.90668,0.90668,0.90668,0.86668,0.82668,0.78668,0,0,0,0,0,0,0,0,0,0,0,0,0,0,
Kraft Foods Inc.,KFT,3.86,24.47,0.00,1.16,1.16,1.12,1.04,0.96,0.87,0.77,0.66,0.56,0.13,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,
McDonald's Corp.,MCD,17.71,25.95,16.55,2.26,2.05,1.625,1.50,1.00,0.67,0.55,0.40,0.235,0.225,0.215,0.196,0.176,0.1605,0.1465,0.132,0.11675,0.10525,0.09875,0.09025,0.083,0.07537,0.07,0.06057,0.04917,0.04889,0.04213,0.03593,0.02914,0.02345,0.01828,0.0126,0.00789,0.00431,0.00186,0,0,0,0,0,0,
Merck & Co. Inc.,MRK,0.00,0.97,11.03,1.52,1.52,1.52,1.52,1.52,1.52,1.50,1.46,1.42,1.38,1.26,1.12,0.99,0.87,0.74,0.64,0.58,0.53,0.48,0.39668,0.33668,0.28668,0.21751,0.14998,0.1111,0.09166,0.08471,0.07918,0.0778,0.07363,0.06597,0.05557,0.0486,0.04306,0.03958,0.03888,0.02916,0.03429,0.02347,0.03056,0.02152,
Microsoft Corp.,MSFT,8.25,0.00,0.00,0.55,0.52,0.46,0.41,0.37,0.32,3.16,0.24,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,
Pfizer Inc.,PFE,-5.59,5.05,9.88,0.72,0.80,1.28,1.16,0.96,0.76,0.68,0.60,0.52,0.44,0.36,0.30666,0.25332,0.22668,0.20,0.17332,0.15668,0.14,0.12332,0.11,0.10,0.09168,0.08332,0.075,0.06832,0.06168,0.055,0.04832,0.03832,0,0,0,0,0,0,0,0,0,0,0,0,
Procter & Gamble,PG,9.28,9.96,10.18,1.89,1.72,1.55,1.36,1.21,1.09,0.9775,0.865,1.135,0.73,0.67,0.605,0.537,0.47725,0.425,0.375,0.33,0.2925,0.26875,0.25,0.23125,0.20625,0.175,0.16876,0.1672,0.16252,0.15626,0.13125,0.13124,0.12185,0.11248,0.10312,0.09063,0.08124,0.06719,0.06248,0.05624,0.0525,0.04782,0.04532,0.06485,
Travelers Companies,TRV,6.90,2.33,0.00,1.41,1.23,1.19,1.13,1.01,0.69,1.16,1.16,1.16,1.12,1.08,1.04,1.00,0.94,0.88,0.80,0.75,0.70,0.68,0.65,0.60,0.55,0.50,0.154,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,
United Technologies,UTX,10.87,14.22,9.52,1.70,1.54,1.345,1.17,1.015,0.88,0.7,0.5675,0.49,0.45,0.4125,0.37,0.3475,0.31,0.275,0.25625,0.2375,0.225,0.225,0.225,0.225,0.2,0.19375,0.175,0.175,0.13125,0.17188,0.15939,0.15,0.15,0.13752,0.13752,0.125,0.10314,0.07422,0.06248,0.06092,0.05624,0.05624,0.05624,0.05624,
Verizon Comm.,VZ,3.38,2.19,3.06,1.91,1.855,1.92,1.645,1.62,1.60,1.54,1.54,1.54,1.54,1.587,1.541,1.54,1.115,1.43,1.395,1.37,0.995,1.29,1.24,1.16,0.825,1.02,0.96,0.90,0.65875,0.80,0,0,0,0,0,0,0,0,0,0,0,0,0,0,
Wal-Mart Stores Inc.,WMT,12.52,15.78,20.91,1.21,1.09,0.952,0.88,0.672,0.60,0.52,0.36,0.30,0.28,0.24,0.20,0.156,0.136,0.104,0.10,0.084,0.064,0.052,0.042,0.035,0.02752,0.02,0.015,0.01052,0.00876,0.00648,0.0044,0.0028,0.00497,0.00195,0.0012,0.00084,0.00061,0.00034,0.00026,0.0002,0,0,0,0,
Walt Disney Co.,DIS,5.23,6.66,11.44,0.40,0.35,0.35,0.35,0.31,0.27,0.24,0.21,0.21,0.21,0.21,0.263,0.20067,0.16867,0.11001,0.145,0.09567,0.07934,0.06674,0.05582,0.04624,0.03833,0.03166,0.02001,0.02668,0.025,0.025,0.025,0.025,0,0,0,0,0,0,0,0,0,0,0,0,

0
 
LVL 3

Expert Comment

by:LFLFM
ID: 34969576
my question was about your workstation configuration; maybe your Oracle is expecting the decimal separator to be the comma... google NLS_NUMERIC_CHARACTERS and you'll know what I'm talking about.

also I noticed in your log that you do have a badfile; please send it to us... its called "dow_dividends.bad"

althought it does seem to be every single row for the first numeric field (column called "five"). So I still think you should check the numeric characters
0
 

Author Closing Comment

by:dmalovich
ID: 34969752
Your solution worked. Thanks......
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This video shows how to recover a database from a user managed backup

710 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question