Home » RDBMS Server » Server Utilities » Error in SQL* Loader pgm
Error in SQL* Loader pgm [message #399250] Tue, 21 April 2009 05:16 Go to next message
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....
Re: Error in SQL* Loader pgm [message #399251 is a reply to message #399250] Tue, 21 April 2009 05:23 Go to previous messageGo to next message
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?
Re: Error in SQL* Loader pgm [message #399301 is a reply to message #399251] Tue, 21 April 2009 08:45 Go to previous messageGo to next message
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...
Re: Error in SQL* Loader pgm [message #399337 is a reply to message #399301] Tue, 21 April 2009 12:47 Go to previous messageGo to next message
joy_division
Messages: 4641
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.
Re: Error in SQL* Loader pgm [message #399387 is a reply to message #399337] Tue, 21 April 2009 23:23 Go to previous messageGo to next message
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.......
Re: Error in SQL* Loader pgm [message #399391 is a reply to message #399387] Tue, 21 April 2009 23:58 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8635
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.


Re: Error in SQL* Loader pgm [message #406503 is a reply to message #399519] Thu, 04 June 2009 05:14 Go to previous message
Nshan
Messages: 62
Registered: February 2009
Member
Thanks for all your responses...
Its done...
Previous Topic: expdp (merged 4)
Next Topic: SQL Loader problem in Oracle 10g
Goto Forum:
  


Current Time: Wed Dec 07 10:56:00 CST 2016

Total time taken to generate the page: 0.15072 seconds