sql loader --Need Help
Date: Sat, 21 Jul 2001 21:46:10 GMT
Message-ID: <893.990795302194_at_iw0.mailusenet.com>
Hi,
I need to import a file every day with 6-7 million records using
sql loader. two columns in the file contain numeric data in the
form of 999,99 and if no data is available contains a NA.
Please note that NA is different from a 0 here.
I need to import the data in such a way that the formatted 999,99.9999 is imported as 99999.99 and zero's are imported as it is but I need to insert a null if NA is present in the column. I am using the to_number to convert 999,99.99 to a number but the problem is the moment it encounters a NA it gives a invalid number error.
Please find below the control file , a sample data file and the error I am getting. Please suggest a workaround as this needs to be a scheduled process and any manual intervention is not possible to replace NA with a null. I tried all possible altrernatives using translate, decode, replace etc but without any success.
Please Help.
Thanks
Anurag
aminocha_at_herold.com
Control File
load data
infile 'test.txt'
badfile 'test.bad'
discardfile 'test.dcs'
truncate
into table test
(company position(01:10) char,
time position(11:20) char,
region position(21:30) char,
lineitem position(31:45) char,
actual position(46:65) char NULLIF (actual
= "NA") "TO_NUMBER(:actual,'999,999.999999')",
actualus position(66:85) char NULLIF (actual
= "NA") "TO_NUMBER(:actualus,'999,999.9999999')")
Sample Data
MBOC YR_1991 REGROLL BSPSIEB 1.000000 1.000000 ALLEP YR_1991 REGROLL COFYE 24.000000 24.000000 ALLEP YR_1991 REGROLL COLOP 48.000000 48.000000 ALLEP YR_1991 REGROLL COEPAM 8.000000 8.000000ALLEP YR_1991 REGROLL COCAI 5.000000 5.000000 ALLEP YR_1991 REGROLL COTWNE 9,274.000000 9,274.000000
ALLEP YR_1991 REGROLL ISHDR 1.000000 NA Error while importing the last record in the log file
Column ACTUAL is NULL if ACTUAL = 0X4e41
(character 'NA')
Column ACTUAL had SQL string
"TO_NUMBER(:actual,'999,999.999999')"
applied to it.
Column ACTUALUS is NULL if ACTUAL = 0X4e41
(character 'NA')
Column ACTUALUS had SQL string
"TO_NUMBER(nvl(:actualus,0),'999,999.9999999')" applied to it.
Record 8: Rejected - Error on table TEST, column
ACTUALUS.
ORA-01722: invalid number
Posted via http://www.etin.com - the FREE public USENET portal on the Web Complete SEARCHING, BROWSING, and POSTING of text and BINARY messages! Received on Sat Jul 21 2001 - 23:46:10 CEST