OraFAQ Forum: Server Utilities » Error in SQL* Loader pgm

Home » RDBMS Server » Server Utilities » Error in SQL* Loader pgm

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

Error in SQL* Loader pgm [message #399250]

Tue, 21 April 2009 05:16

Nshan
Messages: 62
Registered: February 2009

Member

Hi,

I have got an error as

--Record 9: Rejected - Error on table "DDD"."DAC_TRANS_ACTIVITIES_DUMP", column ACTIVITY_ID.
ORA-01722: invalid number

I have gone through the forum but i couldnt find solution for my pblm as per the input data...

My ctrl file is

OPTIONS (skip=1)
LOAD DATA
APPEND
into table DAC_TRANS_ACTIVITIES_DUMP
FIELDS TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
ACTIVITY_ID INTEGER EXTERNAL ,
ACTIVITY_SOURCE CHAR ,
TRANSACTION_ID INTEGER EXTERNAL ,
ACTIVITY_DATE CHAR ,
LOGGED_BY CHAR
)

My 9th record data are from data file --

"1825062"|"UserGenerated"|"1265304"|"01/02/09"|"pl33318"|

In table the data type of ACTIVITY_ID is NUMBER...

Any one can clarify me??
Thanks in advance....

Report message to a moderator

Re: Error in SQL* Loader pgm [message #399251 is a reply to message #399250]

Tue, 21 April 2009 05:23

bonker
Messages: 402
Registered: July 2005

Senior Member

Quote:

My 9th record data are from data file --

"1825062"|"UserGenerated"|"1265304"|"01/02/09"|"pl33318"|

Instead of checking in data file can you check record put in bad file by the SQL*Loader program?

Report message to a moderator

Re: Error in SQL* Loader pgm [message #399301 is a reply to message #399251]

Tue, 21 April 2009 08:45

Nshan
Messages: 62
Registered: February 2009

Member

I have checked in bad file

"1825062"|"User Generated"|"1265304"|"01/02/09"|"pl33318"|

This is the data present in my bad file...

Report message to a moderator

Re: Error in SQL* Loader pgm [message #399337 is a reply to message #399301]

Tue, 21 April 2009 12:47

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

Senior Member

That is not the same record. One has a space in the second element and the other doesn't.

Report message to a moderator

Re: Error in SQL* Loader pgm [message #399387 is a reply to message #399337]

Tue, 21 April 2009 23:23

Nshan
Messages: 62
Registered: February 2009

Member

This is the data present in my data file

"1825062"|"User Generated"|"1265304"|"01/02/09"|"pl33318"|

This is the data present in my bad file

"1825062"|"User Generated"|"1265304"|"01/02/09"|"pl33318"|

Error is saying pblm with 1st column ie.ACTIVITY_ID...

Record 11: Rejected - Error on table "DAC"."DAC_TRANS_ACTIVITIES_DUMP", column ACTIVITY_ID.
ORA-01722: invalid number

Not only one record...

Following records also giving error...

"1825080"|"User Generated"|"1261949"|"01/02/09"|"pl33318"|
"1825082"|"User Generated"|"1261949"|"01/02/09"|"pl33318"|
"1825083"|"User Generated"|"1258442"|"01/02/09"|"pl33318"|
"1825083"|"User Generated"|"1259867"|"01/02/09"|"pl33318"|

Kindly help me on this.......

Report message to a moderator

Re: Error in SQL* Loader pgm [message #399391 is a reply to message #399387]

Tue, 21 April 2009 23:58

bonker
Messages: 402
Registered: July 2005

Senior Member

Were all the records in data file rejected?

In case some were loaded then can you check those loaded records with rejected records to see if you are able to spot out the issue (I am just taking a wild guess with this suggestion).

[Updated on: Wed, 22 April 2009 00:00]

Report message to a moderator

Re: Error in SQL* Loader pgm [message #399519 is a reply to message #399387]

Wed, 22 April 2009 13:24

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

Senior Member

I am unable to reproduce your error. What you have provided loads fine. It might help to see your log file, a describe of your table, and the first 12 lines of your data file. Please copy and paste as the problem might be due to control characters and/or spaces that are eliminated when you type instead of copying and pasting. Also, I would specify a date format, rather than relying on matching formats for implicit conversion.

Report message to a moderator

Re: Error in SQL* Loader pgm [message #406503 is a reply to message #399519]

Thu, 04 June 2009 05:14

Nshan
Messages: 62
Registered: February 2009

Member

Thanks for all your responses...
Its done...

Report message to a moderator

Previous Topic:	expdp (merged 4)
Next Topic:	SQL Loader problem in Oracle 10g

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Apr 24 04:37:31 CDT 2024