SQL Loader Date Format

All posts relating to Oracle database administration.

Moderator: Tim...

SQL Loader Date Format

Postby dnunknown » Thu Oct 11, 2012 9:01 pm

Hi Tim,

11.2

I am loading some data from a file and I am having issue w/ the date format

In the DB the column is a date type
In the text file the data is like this for the date column;
1999-07-30 18:41:58
So I have a line in my control file for the sql load that is
Code: Select all
MODIFYDATE  DATE "YYYY-MM-DD HH24:MI:SS" ,
Error -
Record 1: Rejected - Error on table <owner>.<table>, column MODIFYDATE.
ORA-01841: (full) year must be between -4713 and +9999, and not be 0


So then I change it to
Code: Select all
MODIFYDATE  EXPRESSION "TO_DATE(MODIFYDATE, 'YYYY-MM-DD HH24:MI:SS')" ,
Error -
SQL*Loader-350: Syntax error at line 34.
Expecting keyword INTO, found "1999".
1999-07-30 18:41:58


Have also looked on your site and used example code from ;
http://www.oracle-base.com/articles/10g/load-lob-data-using-sql-loader.php
To make it this;
Code: Select all
MODIFYDATE DATE "YYYY-MM-DD HH24:MI:SS" ":MODIFYDATE",
Error -
Record 1: Rejected - Error on table AGILEPROD.ITEM_2, column MODIFYDATE.
ORA-01841: (full) year must be between -4713 and +9999, and not be 0


The complete control file (.dat) looks like this w/ the column for MODIFYDATE being altered for the above senerios;
Code: Select all
load data
INFILE '<file>' "str '\n'"
INTO TABLE <owner>.<table>
APPEND
FIELDS TERMINATED BY '|^'  optionally enclosed by '"' trailing nullcols
(col_1 INTEGER,
MODIFYDATE DATE "YYYY-MM-DD HH24:MI:SS" ":MODIFYDATE",
col_3 INTEGER
)


How can I resolve this format error?

Thanks,
Steve
dnunknown
Advisor
 
Posts: 282
Joined: Wed Aug 01, 2007 5:35 pm
Location: Boulder, CO USA

Re: SQL Loader Date Format

Postby Tim... » Sat Oct 13, 2012 11:57 am

Hi.

The date formatting looks fine to me. I would suggest that it is the data you are trying to load that is the problem.

Try it against a single row with correct data in each field and see if that works before trying to load a whole file.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17935
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: SQL Loader Date Format

Postby dnunknown » Mon Oct 15, 2012 6:07 pm

I created my own file and it loaded w/o issue - thanks
dnunknown
Advisor
 
Posts: 282
Joined: Wed Aug 01, 2007 5:35 pm
Location: Boulder, CO USA

Re: SQL Loader Date Format

Postby Tim... » Mon Oct 15, 2012 6:14 pm

:)
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17935
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK


Return to Oracle Database Administration

Who is online

Users browsing this forum: No registered users and 10 guests

cron