Home » RDBMS Server » Server Utilities » SQLLDR, dates and null columns
SQLLDR, dates and null columns [message #216654] Mon, 29 January 2007 17:30 Go to next message
stevelutz
Messages: 2
Registered: January 2007
Junior Member
Hello, I have the following table that I am trying to import data into using SQLLDR. I am having problems loading my tab seperated data because it appears that TRAILING NULLCOLS is not working. Many of the date fields (expire_date, indexlater, lastmoddate) are null in the input file.

CREATE TABLE DOCUMENT_IMPORT
( docid varchar2(15) NOT NULL,
install_date date,
delete_date date,
publish_date date NOT NULL,
author varchar2(500) ,
title varchar2(4000),
doc_size int,
publication_id int,
batch_name varchar(50),
expire_date date,
indexlater date,
LastModDate date )

The sqlldr definition file I have is:

load data
infile 'input.txt'
into table document_import
fields terminated by X'09'
TRAILING NULLCOLS
(
docid nullif (docid=BLANKS),
install_date date "YYYY-MM-DD" nullif (install_date=BLANKS),
delete_date date "YYYY-MM-DD" nullif (delete_date=BLANKS),
publish_date date "YYYY-MM-DD" nullif (publish_date=BLANKS),
author char(4000) nullif (Author=BLANKS),
title char(4000) nullif (Title=BLANKS),
doc_size INTEGER nullif (doc_size=BLANKS),
publication_id INTEGER nullif (publication_id=BLANKS),
batch_name char(50) nullif (batch_name=BLANKS),
expire_date date "YYYY-MM-DD" NULLIF (expire_date=BLANKS),
indexlater date "YYYY-MM-DD" nullif (indexlater=BLANKS),
LastModDate date "YYYY-MM-DD" nullif (LastModDate = BLANKS)


The input file (partial) is:
(For this exercise, I've replaced the tabs with "|"'s so that it is visible)

1G1:9172104|2004-12-20||1991-01-01|West, Raymond O.|A doctor? The hospital? Or 911? How to choose your health-care provider.(includes related information on HMOs and PPOs)|10331|516|ALTA1991.zip|1999-10-10||
1G1:9172440|2004-12-20||1991-01-01|Craig, Winston J.|Golden oil of the Mediterranean; it may be worth even more than gold to your body. (olive oil)|9617|516|ALTA1991.zip|||
1G1:9172480|2004-12-20||1991-01-01|Evans, Mark|Health in a jocular vein. (children's ideas of anatomy)|10226|516|ALTA1991.zip|||
1G1:9183284|2004-12-20||1991-01-01|Hamilton, Ted|Take charge of your life! It's time to take personal responsibility for your own health.|11844|516|ALTA1991.zip|||


The log file shows the following errors:

Record 1: Rejected - Error on table DOCUMENT_IMPORT, column EXPIRE_DATE.
ORA-01841: (full) year must be between -4713 and +9999, and not be 0

(lots more of them).

Any help would be appreciated. I've tried combinations of NVL, and TO_DATE, and even DECODE, but nothing seemed to help me. What am I missing?

Thanks

Steve
Re: SQLLDR, dates and null columns [message #216659 is a reply to message #216654] Mon, 29 January 2007 17:57 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Please post the partial input datafile in tab format as attachment.
Re: SQLLDR, dates and null columns [message #216660 is a reply to message #216659] Mon, 29 January 2007 18:03 Go to previous messageGo to next message
stevelutz
Messages: 2
Registered: January 2007
Junior Member
Attached is my input file.
Re: SQLLDR, dates and null columns [message #216880 is a reply to message #216660] Tue, 30 January 2007 14:41 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
I tried a few options i am aware off and nothing works.
There are few bugs with sqlldr dealing with nulls & dates.
Either it is the case or we are completely missing something very basic here Smile.
Please raise a TAR.
Re: SQLLDR, dates and null columns [message #216923 is a reply to message #216880] Tue, 30 January 2007 21:52 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
-- test.ctl:
load data
infile 'input.txt'
into table document_import
fields terminated by X'09'
TRAILING NULLCOLS
(
docid                  nullif (docid=BLANKS),
install_date           "date_or_null (:install_date)",
delete_date            "date_or_null (:delete_date)",
publish_date           "date_or_null (:publish_date)",
author char(4000)      nullif (Author=BLANKS),
title char(4000)       nullif (Title=BLANKS),
doc_size INTEGER       nullif (doc_size=BLANKS),
publication_id INTEGER nullif (publication_id=BLANKS),
batch_name char(50)    nullif (batch_name=BLANKS),
expire_date            "date_or_null (:expire_date)",
indexlater             "date_or_null (:indexlater)",
LastModDate            "date_or_null (:lastmoddate)"
)


SCOTT@10gXE> CREATE TABLE DOCUMENT_IMPORT
  2    (docid		varchar2(15) NOT NULL,
  3  	install_date	date,
  4  	delete_date	date,
  5  	publish_date	date NOT NULL,
  6  	author		varchar2(500) ,
  7  	title		varchar2(4000),
  8  	doc_size	int,
  9  	publication_id	int,
 10  	batch_name	varchar(50),
 11  	expire_date	date,
 12  	indexlater	date,
 13  	LastModDate	date)
 14  /

Table created.

SCOTT@10gXE> CREATE OR REPLACE FUNCTION date_or_null
  2    (p_date IN VARCHAR2)
  3    RETURN DATE
  4  AS
  5  BEGIN
  6    RETURN TO_DATE (p_date, 'YYYY-MM-DD');
  7  EXCEPTION
  8    WHEN OTHERS THEN RETURN NULL;
  9  END date_or_null;
 10  /

Function created.

SCOTT@10gXE> SHOW ERRORS
No errors.
SCOTT@10gXE> host sqlldr scott/tiger control=test.ctl log=test.log

SCOTT@10gXE> select count(*) from document_import
  2  /

  COUNT(*)
----------
       985

SCOTT@10gXE>

[Updated on: Tue, 30 January 2007 21:55]

Report message to a moderator

Re: SQLLDR, dates and null columns [message #216981 is a reply to message #216923] Wed, 31 January 2007 02:52 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Cool Smile
Re: SQLLDR, dates and null columns [message #218116 is a reply to message #216660] Tue, 06 February 2007 21:13 Go to previous message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
I realize if you didn't use Barbara's suggestion above, then you've probably obtained a workaround for your problem by now, however the following control file should accommodate your NULL DATE fields. In tests that I've run, the data seemed to load fine.

load data
infile 'input.txt'
into table document_import
TRAILING NULLCOLS
(
docid 				terminated by X'09' nullif (docid=BLANKS),
install_date	date		"RRRR-MM-DD" terminated by X'09' nullif (install_date=BLANKS),
delete_date	date		"RRRR-MM-DD" terminated by X'09' nullif (delete_date=BLANKS),
publish_date	date		"RRRR-MM-DD" terminated by X'09' nullif (publish_date=BLANKS),
author		char(4000)	terminated by X'09' nullif (Author=BLANKS),
title		char(4000)	terminated by X'09' nullif (Title=BLANKS),
doc_size	INTEGER EXTERNAL terminated by X'09' nullif (doc_size=BLANKS),
publication_id	INTEGER EXTERNAL terminated by X'09' nullif (publication_id=BLANKS),
batch_name	char(50)	terminated by X'09' nullif (batch_name=BLANKS),
expire_date	date		"RRRR-MM-DD" terminated by X'09' nullif (expire_date=BLANKS),
indexlater	date		"RRRR-MM-DD" terminated by X'09' nullif (indexlater=BLANKS),
LastModDate	date		"RRRR-MM-DD" nullif (LastModDate = BLANKS))
Previous Topic: SQLLDR issue whit leading white spaces.
Next Topic: Can we export/import at runtime
Goto Forum:
  


Current Time: Wed Dec 07 08:32:45 CST 2016

Total time taken to generate the page: 0.15380 seconds