Home » RDBMS Server » Server Utilities » sql loader error invalid number
sql loader error invalid number [message #319409] Sat, 10 May 2008 14:36 Go to next message
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?

Re: sql loader error invalid number [message #319410 is a reply to message #319409] Sat, 10 May 2008 14:38 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: sql loader error invalid number [message #319412 is a reply to message #319410] Sat, 10 May 2008 14:46 Go to previous messageGo to next message
derekgee
Messages: 5
Registered: May 2008
Location: scotland
Junior Member
track_id is number(10) and
what do you mean by non printable?
Re: sql loader error invalid number [message #319414 is a reply to message #319412] Sat, 10 May 2008 14:55 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Something that is present but you can't see it for instance a control character.

Regards
Michel
Re: sql loader error invalid number [message #319415 is a reply to message #319412] Sat, 10 May 2008 14:56 Go to previous messageGo to next message
BlackSwan
Messages: 25041
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
Re: sql loader error invalid number [message #319416 is a reply to message #319414] Sat, 10 May 2008 15:06 Go to previous messageGo to next message
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.
Re: sql loader error invalid number [message #319417 is a reply to message #319409] Sat, 10 May 2008 15:16 Go to previous messageGo to next message
BlackSwan
Messages: 25041
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
Re: sql loader error invalid number [message #319420 is a reply to message #319417] Sat, 10 May 2008 15:43 Go to previous messageGo to next message
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.
Re: sql loader error invalid number [message #319422 is a reply to message #319409] Sat, 10 May 2008 16:24 Go to previous messageGo to next message
BlackSwan
Messages: 25041
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 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
there are no control characters in the track_id.

How did you verify this?

Regards
Michel
Re: sql loader error invalid number [message #319439 is a reply to message #319430] Sun, 11 May 2008 05:50 Go to previous messageGo to next message
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?

Re: sql loader error invalid number [message #319445 is a reply to message #319439] Sun, 11 May 2008 06:23 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: sql loader error invalid number [message #319478 is a reply to message #319409] Sun, 11 May 2008 17:18 Go to previous message
BlackSwan
Messages: 25041
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.
Previous Topic: I Need Answer Urgent
Next Topic: SQL Loader: problem loading csv file with extra commas
Goto Forum:
  


Current Time: Tue Dec 06 10:46:42 CST 2016

Total time taken to generate the page: 0.13329 seconds