Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

sqlldr error when doing bulk insert into oracle 10g.

Posted on 2011-02-23
6
Medium Priority
?
1,118 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
  • 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
Industry Leaders: 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!

 

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

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

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 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…
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.
Via a live example, show how to take different types of Oracle backups using RMAN.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

782 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