OraFAQ Forum: Server Utilities » sql loader error invalid number

Home » RDBMS Server » Server Utilities » sql loader error invalid number

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

sql loader error invalid number [message #319409]

Sat, 10 May 2008 14:36

derekgee
Messages: 5
Registered: May 2008
Location: scotland

Junior Member

hi, i'm new to oracle and having problems bulk loading a flat file.

load data
infile 'c:\bulk_track_file.txt'
into table temp_track_file
fields terminated by "#"
trailing nullcols
(track_id integer external,
dist_id integer external,
file_type_id integer external,
local_file integer external,
preview integer external,
created char(50),
inserted char(50),
modified char(50))

Record 1: Rejected - Error on table TEMP_TRACK_FILE, column TRACK_ID.
ORA-01722: invalid number

the row looks like this
1647168#40#62#0#0#NULL#2007-10-26 07:14:39.950#2007-10-26 07:14:39.950#2007-10-26 07:14:39.950

there are no comma or full stops in the track_id col.

i get this error for all records.

any ideas?

Report message to a moderator

Re: sql loader error invalid number [message #319410 is a reply to message #319409]

Sat, 10 May 2008 14:38

Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

What is the size of the column in your table?
Also search if there is a non-printable character in the number.

Regards
Michel

Report message to a moderator

Re: sql loader error invalid number [message #319412 is a reply to message #319410]

Sat, 10 May 2008 14:46

derekgee
Messages: 5
Registered: May 2008
Location: scotland

Junior Member

track_id is number(10) and
what do you mean by non printable?

Report message to a moderator

Re: sql loader error invalid number [message #319414 is a reply to message #319412]

Sat, 10 May 2008 14:55

Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Something that is present but you can't see it for instance a control character.

Regards
Michel

Report message to a moderator

Re: sql loader error invalid number [message #319415 is a reply to message #319412]

Sat, 10 May 2008 14:56

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

derekgee wrote on Sat, 10 May 2008 12:46

what do you mean by non printable?

A non-printable character is one whose decimal value is less than 32.

You should specify bad=bad-recs.lis to see which rows get rejected

sqlldr help=yes
userid ORACLE username/password
control Control file name
log Log file name
bad Bad file name
data Data file name
discard Discard file name
discardmax Number of discards to allow
skip Number of logical records to skip
load Number of logical records to load
errors Number of errors to allow
rows Number of rows in conventional path bind array or between direct path data saves
bindsize Size of conventional path bind array in bytes
silent Suppress messages during run (header,feedback,errors,discards,partitions)
direct use direct path
_synchro internal testing
parfile parameter file: name of file that contains parameter specifications
parallel do parallel load
file File to allocate extents from
skip_unusable_indexes disallow/allow unusable indexes or index partitions
skip_index_maintenance do not maintain indexes, mark affected indexes as unusable
commit_discontinued commit loaded rows when load is discontinued
_display_exitcode Display exit code for SQL*Loader execution
readsize Size of Read buffer
external_table use external table for load; NOT_USED, GENERATE_ONLY, EXECUTE
columnarrayrows Number of rows for direct path column array
streamsize Size of direct path stream buffer in bytes
multithreading use multithreading in direct path
resumable enable or disable resumable for current session
resumable_name text string to help identify resumable statement
resumable_timeout wait time (in seconds) for RESUMABLE
date_cache size (in entries) of date conversion cache

Report message to a moderator

Re: sql loader error invalid number [message #319416 is a reply to message #319414]

Sat, 10 May 2008 15:06

derekgee
Messages: 5
Registered: May 2008
Location: scotland

Junior Member

there are no control characters in the track_id. The field is generated from sql server numeric(10,0) to a txt file, if that helps.
all rows are failing.

Report message to a moderator

Re: sql loader error invalid number [message #319417 is a reply to message #319409]

Sat, 10 May 2008 15:16

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

You'd have better results if you read & followed the Fine Manual
http://download.oracle.com/docs/cd/B19306_01/server.102/b14215/ldr_field_list.htm#sthref1089

Report message to a moderator

Re: sql loader error invalid number [message #319420 is a reply to message #319417]

Sat, 10 May 2008 15:43

derekgee
Messages: 5
Registered: May 2008
Location: scotland

Junior Member

as far as i can see from this link the config and data is correct.

Report message to a moderator

Re: sql loader error invalid number [message #319422 is a reply to message #319409]

Sat, 10 May 2008 16:24

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>as far as i can see from this link the config and data is correct.
How do you reconcile the statement above with the FACT errors are reported by SQLLDR?

If your statement above is correct, then SQLLDR must have a bug & is reporting errors where no problem exists. Right?

Which reality is more likely; your assessment or SQLLDR's correctness?

When Oracle reports an error, you really, really, really should believe it.

Alternatively, you could try a simple exercise & a variation on your ultimate goal by making all the data types to be VARCHAR2;
in order to gain some perspective on how Oracle is dealing with the data & control file contents.

[Updated on: Sat, 10 May 2008 17:55] by Moderator

Report message to a moderator

Re: sql loader error invalid number [message #319430 is a reply to message #319416]

Sun, 11 May 2008 00:20

Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

there are no control characters in the track_id.

How did you verify this?

Regards
Michel

Report message to a moderator

Re: sql loader error invalid number [message #319439 is a reply to message #319430]

Sun, 11 May 2008 05:50

derekgee
Messages: 5
Registered: May 2008
Location: scotland

Junior Member

i verified this in vedit. i do however have nul values between each character in the track_id field, this may be the issue. any ideas on removing this?

Report message to a moderator

Re: sql loader error invalid number [message #319445 is a reply to message #319439]

Sun, 11 May 2008 06:23

Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

i do however have nul values between each character in the track_id field, this may be the issue.

Yes, it is.

Regards
Michel

Report message to a moderator

Re: sql loader error invalid number [message #319478 is a reply to message #319409]

Sun, 11 May 2008 17:18

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>any ideas on removing this?
I contend the best course of action is to avoid including the null values in the original file;
rather than trying to remove them after the fact.

Report message to a moderator

Previous Topic:	I Need Answer Urgent
Next Topic:	SQL Loader: problem loading csv file with extra commas

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Dec 24 23:52:08 CST 2024