Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

sqlldr error when doing bulk insert into oracle 10g.

Posted on 2011-02-23
6
Medium Priority
?
1,090 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 2000 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

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

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

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

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
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.
Via a live example, show how to take different types of Oracle backups using RMAN.

670 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