Home » RDBMS Server » Server Utilities » SQLLDR, dates and null columns
| SQLLDR, dates and null columns [message #216654] |
Mon, 29 January 2007 17:30  |
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 #216923 is a reply to message #216880] |
Tue, 30 January 2007 21:52  |
 |
Barbara Boehmer
Messages: 9097
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 #218116 is a reply to message #216660] |
Tue, 06 February 2007 21:13 |
 |
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)) |
|
|
|
Goto Forum:
Current Time: Tue Jul 02 16:59:45 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
.