OraFAQ Forum: Server Utilities » Invalid Number Error in sql loader

Home » RDBMS Server » Server Utilities » Invalid Number Error in sql loader

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

Invalid Number Error in sql loader [message #231059]

Fri, 13 April 2007 18:34

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

Report message to a moderator

Re: Invalid Number Error in sql loader [message #231062 is a reply to message #231059]

Fri, 13 April 2007 18:50

BlackSwan
Messages: 26766
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:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Dec 25 22:07:32 CST 2024