OraFAQ Forum: Server Utilities » Date with Null Problem in SQL Loader

Home » RDBMS Server » Server Utilities » Date with Null Problem in SQL Loader (SQL Loader , UNIX)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Date with Null Problem in SQL Loader [message #376945]

Fri, 19 December 2008 04:16

spmano1983
Messages: 269
Registered: September 2007

Senior Member

Hi,

I have data file and one column is date_to having date with format 'mm/dd/yyyy', this column will be null also in the flat file.

This filed is last filed in my data file.

In my control file i have tried

1. date_to date 'mm/dd/yyyy'
2. date_to date 'mm/dd/yyyy' nullif(date_to="null")
3. "decode(:date_to,null,null,to_date('mm/dd/yyyy'))"
4. date_to date 'mm/dd/yyyy' nullif(date_to=blanks)
5. "rtrim(to_date(:date_to,'mm/dd/yyyy'))"

But, i am getting error ORA-01843: not a valid month

I would appreciate if any suggestion..!

Report message to a moderator

Re: Date with Null Problem in SQL Loader [message #376947 is a reply to message #376945]

Fri, 19 December 2008 04:19

Frank Naude
Messages: 4579
Registered: April 1998

Senior Member

Valid months are 1 to 12. Anything else will justifiably result in an ORA-01843: not a valid month. So, what is your point?

Report message to a moderator

Re: Date with Null Problem in SQL Loader [message #376952 is a reply to message #376945]

Fri, 19 December 2008 04:24

spmano1983
Messages: 269
Registered: September 2007

Senior Member

After execute the sqlldr, records are rejected and in log file
i am seeing this error.

Report message to a moderator

Re: Date with Null Problem in SQL Loader [message #376956 is a reply to message #376952]

Fri, 19 December 2008 04:29

Littlefoot
Messages: 21808
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

If you had posted the control file as well as sample (especially invalid) records, we'd probably be able to answer the question.

Report message to a moderator

Re: Date with Null Problem in SQL Loader [message #376957 is a reply to message #376952]

Fri, 19 December 2008 04:30

Frank Naude
Messages: 4579
Registered: April 1998

Senior Member

Shouldn't you rather be looking at the data?

PS: It will also help if you can post a small test case.

Report message to a moderator

Re: Date with Null Problem in SQL Loader [message #376958 is a reply to message #376945]

Fri, 19 December 2008 04:32

spmano1983
Messages: 269
Registered: September 2007

Senior Member

Hi,

This is my sample data file. As i ananlysed all rejected records are having date_to is null in flat file.

LOAD DATA
INFILE '/usr/tmp/per_addresses.dat'
INTO TABLE "BOLINF"."XOS1_PER_ADDRESSES1"
FIELDS TERMINATED BY '|'
TRAILING NULLCOLS
(
   COUNTRY,
   ADDRESS_TYPE,
   ADDRESS_LINE3,
   ADDRESS_LINE2,
   ADDRESS_LINE1,
   STYLE,
   PRIMARY_FLAG,
   DATE_FROM DATE "MM/DD/YYYY",
   EMPLOYEE_NUMBER,
   PERSON_ORG_NAME,
   FULL_NAME,
   NATIONAL_IDENTIFIER,
   BG_NAME,
   ADDRESS_ID,
   COMMENTS,
   GEOMETRY,
   DATE_TO DATE "MM/DD/YYYY"
)

Report message to a moderator

Re: Date with Null Problem in SQL Loader [message #376960 is a reply to message #376958]

Fri, 19 December 2008 04:36

Frank Naude
Messages: 4579
Registered: April 1998

Senior Member

Quote:

This is my sample data file.

You've posted your control file. Can you please post a line or to from the data file as well? Choose a line with a null date.

Report message to a moderator

Re: Date with Null Problem in SQL Loader [message #376964 is a reply to message #376945]

Fri, 19 December 2008 04:43

spmano1983
Messages: 269
Registered: September 2007

Senior Member

Hi,

Sample rejected record:

Data for first line:

COUNTRY|ADDRESS_TYPE|ADDRESS_LINE3|ADDRESS_LINE2|ADDRESS_LINE1|
STYLE|PRIMARY_FLAG|DATE_FROM|EMPLOYEE_NUMBER|PERSON_ORG_NAME|
FULL_NAME|NATIONAL_IDENTIFIER|NAME|ADDRESS_ID|COMMENTS|GEOMETRY|
DERIVED_LOCALE|PARTY_NAME|ADD_INFORMATION20|ADD_INFORMATION19|
ADD_INFORMATION18|ADD_INFORMATION17|ADD_INFORMATION16|
ADD_INFORMATION15|ADD_INFORMATION14|ADD_INFORMATION13|
ADDR_ATTRIBUTE20|ADDR_ATTRIBUTE19|ADDR_ATTRIBUTE18|
ADDR_ATTRIBUTE17|ADDR_ATTRIBUTE16|ADDR_ATTRIBUTE15|
ADDR_ATTRIBUTE14|ADDR_ATTRIBUTE13|ADDR_ATTRIBUTE12|
ADDR_ATTRIBUTE11|ADDR_ATTRIBUTE10|ADDR_ATTRIBUTE9|
ADDR_ATTRIBUTE8|ADDR_ATTRIBUTE7|ADDR_ATTRIBUTE6|
ADDR_ATTRIBUTE5|ADDR_ATTRIBUTE4|ADDR_ATTRIBUTE3|
ADDR_ATTRIBUTE2|ADDR_ATTRIBUTE1|ADDR_ATTRIBUTE_CATEGORY|
PROGRAM_UPDATE_DATE|PROGRAM_ID|PROGRAM_APPLICATION_ID|
REQUEST_ID|TOWN_OR_CITY|TELEPHONE_NUMBER_3|TELEPHONE_NUMBER_2|
TELEPHONE_NUMBER_1|REGION_3|REGION_2|REGION_1|POSTAL_CODE|DATE_TO

Data for 2nd line:

US|TRV_H||APT #53|250 SANDERS FERRY ROAD|US|Y|01/29/2004|
060399|US.ORBITZ WORLD WIDE.CHEAPTICKETS.CC CUST SRV - HOTEL|
HAILEY, AMY L|XXX-XX-XXXX|TRV_US|412799|||Hendersonville, TN, US|
AMY HAILEY||||||||||||||||||||||||||||||||||Hendersonville|
||||TN|Sumner|37075|

Report message to a moderator

Re: Date with Null Problem in SQL Loader [message #376968 is a reply to message #376964]

Fri, 19 December 2008 04:56

Frank Naude
Messages: 4579
Registered: April 1998

Senior Member

Column 17 should be the DATE_TO filed, right? However, it's corresponding value in the data file is "Hendersonville, TN, US". So yes, Oracle is right, that is an invalid date!

Report message to a moderator

Re: Date with Null Problem in SQL Loader [message #376970 is a reply to message #376945]

Fri, 19 December 2008 05:01

spmano1983
Messages: 269
Registered: September 2007

Senior Member

Hi,

How column17 should be DATE_TO??

It is delimited by | symbol.. If you open in excel you will get the column name with the corresponding value.

Report message to a moderator

Re: Date with Null Problem in SQL Loader [message #376973 is a reply to message #376970]

Fri, 19 December 2008 05:20

Frank Naude
Messages: 4579
Registered: April 1998

Senior Member

The point is that your control file doesn't map to the data.

Column 17 in your control file is a DATE, but in the data file its a string.

Report message to a moderator

Re: Date with Null Problem in SQL Loader [message #376981 is a reply to message #376945]

Fri, 19 December 2008 05:35

spmano1983
Messages: 269
Registered: September 2007

Senior Member

Hi,

really sorry frank, I did not posted complete CTL file... Just i cuttend lines betteween

This is the CTL file

LOAD DATA
INFILE '/usr/tmp/per_addresses.dat'
INTO TABLE "BOLINF"."XOS1_PER_ADDRESSES1"
FIELDS TERMINATED BY '|'
TRAILING NULLCOLS
(
   COUNTRY,
   ADDRESS_TYPE,
   ADDRESS_LINE3,
   ADDRESS_LINE2,
   ADDRESS_LINE1,
   STYLE,
   PRIMARY_FLAG,
   DATE_FROM DATE "MM/DD/YYYY",
   EMPLOYEE_NUMBER,
   PERSON_ORG_NAME,
   FULL_NAME,
   NATIONAL_IDENTIFIER,
   BG_NAME,
   ADDRESS_ID,
   COMMENTS,
   GEOMETRY,
   DERIVED_LOCALE,
   PARTY_NAME,
   ADD_INFORMATION20,
   ADD_INFORMATION19,
   ADD_INFORMATION18,
   ADD_INFORMATION17,
   ADD_INFORMATION16,
   ADD_INFORMATION15,
   ADD_INFORMATION14,
   ADD_INFORMATION13,
   ADDR_ATTRIBUTE20,
   ADDR_ATTRIBUTE19,
   ADDR_ATTRIBUTE18,
   ADDR_ATTRIBUTE17,
   ADDR_ATTRIBUTE16,
   ADDR_ATTRIBUTE15,
   ADDR_ATTRIBUTE14,
   ADDR_ATTRIBUTE13,
   ADDR_ATTRIBUTE12,
   ADDR_ATTRIBUTE11,
   ADDR_ATTRIBUTE10,
   ADDR_ATTRIBUTE10,
   ADDR_ATTRIBUTE9,
   ADDR_ATTRIBUTE8,
   ADDR_ATTRIBUTE7,
   ADDR_ATTRIBUTE6,
   ADDR_ATTRIBUTE5,
   ADDR_ATTRIBUTE4,
   ADDR_ATTRIBUTE3,
   ADDR_ATTRIBUTE2,
   ADDR_ATTRIBUTE1,
   ADDR_ATTRIBUTE_CATEGORY,
   PROGRAM_UPDATE_DATE,
   PROGRAM_ID,
   PROGRAM_APPLICATION_ID,
   REQUEST_ID,
   TOWN_OR_CITY,
   TELEPHONE_NUMBER_3,
   TELEPHONE_NUMBER_2,
   TELEPHONE_NUMBER_1,
   REGION_3,
   REGION_2,
   REGION_1,
   POSTAL_CODE,
   DATE_TO
)

Report message to a moderator

Re: Date with Null Problem in SQL Loader [message #377059 is a reply to message #376981]

Fri, 19 December 2008 12:42

Barbara Boehmer
Messages: 9092
Registered: November 2002
Location: California, USA

Senior Member

In your latest posted control file you are missing the date format for the date_to field.

Report message to a moderator

Re: Date with Null Problem in SQL Loader [message #377290 is a reply to message #376945]

Mon, 22 December 2008 04:29

spmano1983
Messages: 269
Registered: September 2007

Senior Member

Hi,

I am using MM/DD/YYYY format only. please anyone 1 tell me that what is the solution for this case.

My date field may be null in my flat file and i am using format mask in control file, and i am getting error when i am executing this control file

Report message to a moderator

Re: Date with Null Problem in SQL Loader [message #377293 is a reply to message #377290]

Mon, 22 December 2008 04:34

Frank Naude
Messages: 4579
Registered: April 1998

Senior Member

We cannot keep on guessing what's in you control and data files. Please prepare a SMALL, but COMPLETE test case and post it here. See test case for some suggestions.

Report message to a moderator

Re: Date with Null Problem in SQL Loader [message #377390 is a reply to message #377059]

Mon, 22 December 2008 12:37

joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA

Senior Member

Barbara Boehmer wrote on Fri, 19 December 2008 13:42

In your latest posted control file you are missing the date format for the date_to field.

Report message to a moderator

Previous Topic:	Error
Next Topic:	SQL Loader (Urgent Need immediate reply)

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri May 03 16:09:13 CDT 2024