CODEWITHSUNDEEP
sql-loader
JohnD
JohnD

Reputation: 353

SQL Loader- data not uploaded to the table. All went to .bad

I tried to upload some records into my table ABC. None of the records went through and they all showed up in the .bad log.

I am pretty new to sqlldr. Not quite sure where did I messed up. Let me show you the steps I took.

First, I created an empty table called ABC.

 create table abc
    (
      location_id varchar2(10),
      sold_month date,
      item_name varchar2(30),
      company_id varchar2(10),
      qty_sold number(10),
      total_revenue number(14,3),
      promotional_code varchar2(10)
    );

Here is my flat file abcflat.dat. The columns correspond to the columns in the table above.

"1000","02/01/1957","Washing Machine","200011","10","10000","ABCDE"
"1000","05/02/2013","Computer","200012","5","5000","ABCDE"
"1000","05/01/2013","Bolt","200010","100","500","ABCDE"
"1000","05/03/2013","Coca Cola","200011","1000","1000","ABCDE"

Here is my control file abc.ctl

LOAD DATA
INFILE 'C:\Users\Public\abcflat.dat'
INTO TABLE ABC
FIELDS TERMINATED BY "," 
enclosed by '"'
(
Location_ID
, Sold_month
, item_name
, Company_id
, QTY_Sold
, Total_revenue
, Promotional_Code
)

And my last step

sqlldr hr/open@xe control=c:\users\public\abc.ctl

It says 

Commit point reached - logical record count 3
Commit point reached - logical record count 4

but none of the record showed up on my ABC table.

Thank You

Upvotes: 0

Views: 1186

Answers (1)

user330315
user330315

Reputation:

It's most probably the date format, try this:

LOAD DATA
INFILE 'C:\Users\Public\abcflat.dat'
INTO TABLE ABC
FIELDS TERMINATED BY "," 
enclosed by '"'
(
Location_ID
, Sold_month DATE "DD/MM/YYYY"
, item_name
, Company_id
, QTY_Sold
, Total_revenue
, Promotional_Code
)

Upvotes: 1

Related Questions