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 Go to next message](/forum/theme/orafaq/images/down.png) |
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 Go to previous message](/forum/theme/orafaq/images/up.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/136107.jpg) |
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
|
|
|
Goto Forum:
Current Time: Sat Sep 07 11:37:53 CDT 2024
|