Home » RDBMS Server » Server Utilities » SQL Loader, data load with Date and blank date column error
icon9.gif  SQL Loader, data load with Date and blank date column error [message #111858] Mon, 21 March 2005 06:43 Go to next message
bsfartiyal
Messages: 14
Registered: March 2005
Location: INDIA
Junior Member
hi

I am loading data through sql loader and getting problem when i load the data flat file date column giveing error message date column data shuld not be value 0,

but my date column some row has date and some are blank. So please give the solution how i load the data date column wite data and without date.

regards
bs fartiyal

Report message to a moderator

Re: SQL Loader, data load with Date and blank date column error [message #111862 is a reply to message #111858] Mon, 21 March 2005 07:02 Go to previous messageGo to next message
Maaher
Messages: 7065
Registered: December 2001
Senior Member
No duplicate posts please.

MHE

Report message to a moderator

Re: SQL Loader, data load with Date and blank date column error [message #111936 is a reply to message #111858] Mon, 21 March 2005 23:43 Go to previous messageGo to next message
sridcpp
Messages: 23
Registered: February 2005
Location: India
Junior Member
try using this option

LOAD DATA
INFILE 'mail_orders.txt'
BADFILE 'bad_orders.txt'
APPEND
INTO TABLE mailing_list
FIELDS TERMINATED BY ","
( addr,
city,
state,
zipcode,
tdate "decode(:tdate, null, sysdate, :tdate)",
mailing_city "decode(:mailing_city, null, :city, :mailing_city)",
mailing_state
)

Report message to a moderator

Re: SQL Loader, data load with Date and blank date column error [message #111937 is a reply to message #111858] Mon, 21 March 2005 23:44 Go to previous messageGo to next message
sridcpp
Messages: 23
Registered: February 2005
Location: India
Junior Member
try using this option

LOAD DATA
INFILE 'mail_orders.txt'
BADFILE 'bad_orders.txt'
APPEND
INTO TABLE mailing_list
FIELDS TERMINATED BY ","
( addr,
city,
state,
zipcode,
tdate "decode(:tdate, null, null, :tdate)",
mailing_city "decode(:mailing_city, null, :city, :mailing_city)",
mailing_state
)

Report message to a moderator

Re: SQL Loader, data load with Date and blank date column error [message #111977 is a reply to message #111936] Tue, 22 March 2005 03:36 Go to previous messageGo to next message
bsfartiyal
Messages: 14
Registered: March 2005
Location: INDIA
Junior Member
Actually my table is as

order_no varchar2(10),
order_dt date,
ref_no varchar2(10),
ref_dt date


text data file is as

"10101",01/01/2005,"46464",12/12/2004
"10102",01/01/2005,"65664",12/12/2004
"10103",01/01/2005," " / /
"10104",01/01/2005,"9664",12/12/2004
"10104",01/01/2005,"4669",/ /

problem comes when ref date loading, checking refdate and convering in to number but this not null and 0, so that loader raise error message

Please send solution such type of problem.

also i would like to know, how to take incremental export backup.

regards
bsfartiyal

Report message to a moderator

Re: SQL Loader, data load with Date and blank date column error [message #115747 is a reply to message #111977] Thu, 14 April 2005 02:56 Go to previous message
Barbara Boehmer
Messages: 9102
Registered: November 2002
Location: California, USA
Senior Member
load data
infile 'your_text_file.dat'
into table your_table
fields terminated by ','
trailing nullcols
(order_no,
order_dt "to_date (replace (replace (:order_dt, '/', ''), ' ', ''), 'mmddyyyy')",
ref_no,
ref_dt "to_date (replace (replace (:ref_dt, '/', ''), ' ', ''), 'mmddyyyy')")

Report message to a moderator

Previous Topic: update ASCII FIle
Next Topic: Importing Problem
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Sun Jan 12 16:08:49 CST 2025
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.