This forum is currently locked. You can't register or post questions at this time. (read more)

SQL Loader Date Format

All posts relating to Oracle database administration.

Moderator: Tim...

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

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

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

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

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

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

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

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


Return to “Oracle Database Administration”

Who is online

Users browsing this forum: No registered users and 2 guests