Solved

sqlldr error when doing bulk insert into oracle 10g.

Posted on 2011-02-23
6
1,048 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 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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 Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
oracle RMAN - trying to duplicate a database 5 39
best datatype for oracle table email creation 8 58
Oracle encryption 12 48
Creation date for a PDB 5 41
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
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…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

821 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