Home » RDBMS Server » Server Utilities » Invalid Number Error in sql loader
Invalid Number Error in sql loader [message #231059] Fri, 13 April 2007 18:34 Go to next message
jdizon
Messages: 1
Registered: April 2007
Junior Member
I am a newbie trying to load a csv flat file into an Oracle table. Im getting the erro below on my load.
--
Error on table PS_GRADE_TBL, column GRADE_POINTS.
ORA-01722: invalid number
--
MY control file looks like this below. The column Grade_Points is define in the table as type Number(9,3) Not Null

OPTIONS (SKIP=10)
load data
infile 'c:\temp\new\hsu_gradescheme_all22.csv'
append into table PS_GRADE_TBL
fields terminated by "," optionally enclosed by '"' trailing nullcols
(
cl FILLER,
setid,
grading_scheme,
effdt,
c5 FILLER,
c6 FILLER,
c7 FILLER,
grading_basis,
c9 FILLER,
c10 FILLER,
c11 FILLER,
c12 FILLER,
c13 FILLER,
c14 FILLER,
c15 FILLER,
c16 FILLER,
c17 FILLER,
crse_grade_input "nvl(:crse_grade_input,' ')",
grade_convert "nvl(:grade_convert,' ')",
descr,
descrshort position(1:10)char,
exclude_prgrss_unt,
grade_points INTEGER EXTERNAL,
grade_category,
ssr_grade_flag "nvl(:ssr_grade_flag,' ')",
in_progress_grd,
include_in_gpa,
earn_credit,
valid_attempt,
c30 FILLER,
c31 FILLER,
c32 FILLER,
c33 FILLER
)

---
my data looks like this below: ( to big to paste here) grade_points values are; 3.7, 4 and 0 going into a Number(9,3) column.
xxx,jkk,.....,3.7,y,n,y
bbb,kjh,.....,4,n,n,n
xdc,bnm,.....,0,y,y,y

---

I've tried all I can find on this forum to no success:
"nvl(:grade_points,0)"
integer external

Any help will be greatly appreciated.
thx
Re: Invalid Number Error in sql loader [message #231062 is a reply to message #231059] Fri, 13 April 2007 18:50 Go to previous message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
On the surface, I would tend to believe Oracle & that you are trying to load a non-numeric value into that column.
How many BAD rows exist in the input file?
As a test, you could change the column to VARCHAR2(10) & load.
Then TRANSLATE all numerals & decimal point to a space character.
Then select where GPA not like ' '

[Updated on: Fri, 13 April 2007 18:51] by Moderator

Report message to a moderator

Previous Topic: tkprof - not generating output
Next Topic: ORACLE error 19206
Goto Forum:
  


Current Time: Sun Dec 04 00:20:55 CST 2016

Total time taken to generate the page: 0.09665 seconds