Solved

sqlldr error when doing bulk insert into oracle 10g.

Posted on 2011-02-23
6
1,073 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
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 

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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Suggested Courses

626 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