asked on

SQL loader works in windows fails in Unix with ORA-01722: invalid number

Here is all of the pieces to recreate it...

sqlldr control=hasp_load.ctl, log=hasp_load.log, bad=hasp_load.bad, data=data_sample.txt, userid=piercecl@gns79

options (skip=1)
load data
replace
into table mis.hasp_load
fields terminated by X'09'
trailing nullcols
(fiscal_year,
month,
dha "case when :dha in (1000,3000) then 10 else to_number(substr(:dha,3,1)) end",
primary_code,
secondary_code,
sector_id "case when :sector_id is not null then lpad(:sector_id, 2, '0') else null end",
logical_facility_code filler,
fin_balance "to_number(:fin_balance, '999,999,999.99')",
fin_budget "to_number(:fin_budget, '999,999,999.99')",
stat_balance "to_number(:stat_balance, '999,999,999.99')",
stat_budget "to_number(:stat_budget, '999,999,999.99')",
first_month "case when :month in (1,2,3) then 1 when :month in (4,5,6) then 4 when :month in (7,8,9) then 7 when :month in (10,11,12) then 10 else 13 end"
)

CREATE TABLE MIS.HASP_LOAD
(
FISCAL_YEAR VARCHAR2(4 BYTE),
MONTH VARCHAR2(25 BYTE),
DHA VARCHAR2(4 BYTE),
PRIMARY_CODE VARCHAR2(9 BYTE),
SECONDARY_CODE VARCHAR2(7 BYTE),
SECTOR_ID VARCHAR2(2 BYTE),
LOGICAL_FACILITY_CODE NUMBER(3),
FIN_BALANCE NUMBER(38,2),
FIN_BUDGET NUMBER(38,2),
STAT_BALANCE NUMBER(38,2),
STAT_BUDGET NUMBER(38,2),
FIRST_MONTH NUMBER
)

Results from Windows:

Table MIS.HASP_LOAD, loaded from every logical record.
Insert option in effect for this table: REPLACE
TRAILING NULLCOLS option in effect

Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
FISCAL_YEAR FIRST * WHT CHARACTER
MONTH NEXT * WHT CHARACTER
DHA NEXT * WHT CHARACTER
SQL string for column : "case when :dha in (1000,3000) then 10 else to_number(substr(:dha,3,1)) end"
PRIMARY_CODE NEXT * WHT CHARACTER
SECONDARY_CODE NEXT * WHT CHARACTER
SECTOR_ID NEXT * WHT CHARACTER
SQL string for column : "case when :sector_id is not null then lpad(:sector_id, 2, '0') else null end"
LOGICAL_FACILITY_CODE NEXT * WHT CHARACTER
(FILLER FIELD)
FIN_BALANCE NEXT * WHT CHARACTER
SQL string for column : "to_number(:fin_balance, '999,999,999.99')"
FIN_BUDGET NEXT * WHT CHARACTER
SQL string for column : "to_number(:fin_budget, '999,999,999.99')"
STAT_BALANCE NEXT * WHT CHARACTER
SQL string for column : "to_number(:stat_balance, '999,999,999.99')"
STAT_BUDGET NEXT * WHT CHARACTER
SQL string for column : "to_number(:stat_budget, '999,999,999.99')"
FIRST_MONTH NEXT * WHT CHARACTER
SQL string for column : "case when :month in (1,2,3) then 1 when :month in (4,5,6) then 4 when :month in (7,8,9) then 7 when :month in (10,11,12) then 10 else 13 end"

Table MIS.HASP_LOAD:
6 Rows successfully loaded.
0 Rows not loaded due to data errors.
0 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.

Results from Unix:
Table MIS.HASP_LOAD, loaded from every logical record.
Insert option in effect for this table: REPLACE
TRAILING NULLCOLS option in effect

Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
FISCAL_YEAR FIRST * WHT CHARACTER
MONTH NEXT * WHT CHARACTER
DHA NEXT * WHT CHARACTER
SQL string for column : "case when :dha in (1000,3000) then 10 else to_number(substr(:dha,3,1)) end"
PRIMARY_CODE NEXT * WHT CHARACTER
SECONDARY_CODE NEXT * WHT CHARACTER
SECTOR_ID NEXT * WHT CHARACTER
SQL string for column : "case when :sector_id is not null then lpad(:sector_id, 2, '0') else null end"
LOGICAL_FACILITY_CODE NEXT * WHT CHARACTER
SQL string for column : "mis.get_facility(:logical_facility_code)"
FIN_BALANCE NEXT * WHT CHARACTER
FIN_BUDGET NEXT * WHT CHARACTER
STAT_BALANCE NEXT * WHT CHARACTER
STAT_BUDGET NEXT * WHT CHARACTER
FIRST_MONTH NEXT * WHT CHARACTER
SQL string for column : "case when :month in (1,2,3) then 1 when :month in (4,5,6) then 4 when :month in (7,8,9) then 7 when :month in (10,11,12) then 10 else 13 end"

Record 1: Rejected - Error on table MIS.HASP_LOAD, column STAT_BUDGET.
ORA-01722: invalid number

Record 2: Rejected - Error on table MIS.HASP_LOAD, column STAT_BUDGET.
ORA-01722: invalid number

data-sample.txt

slightwv (䄆 Netminder)

I have to ask: Are you sure the control file is the same?

I don't see:
SQL string for column : "to_number(:stat_budget, '999,999,999.99')"

from the unix run for STAT_BUDGET.

Walter Ritzel

Yeap, seems the ctl files are not the same... easy fix.

KathysFriend

ASKER

It's not that easy. The control files were the same - starting out that is - I wanted to eliminate the formatted numbers to see if that was the problem - so I changed the dat file and the ctl file to match. So I will start from scratch with the formatted numbers - works on Windows not on Unix. Here is the output:

SQL*Loader: Release 10.2.0.4.0 - Production on Mon Sep 27 07:59:32 2010

Copyright (c) 1982, 2007, Oracle. All rights reserved.

Control File: sqlloader/hasp_load.ctl
Data File: ../data_sample.dat
Bad File: log/hasp_load.bad
Discard File: none specified

(Allow all discards)

Number to load: ALL
Number to skip: 1
Errors allowed: 50
Bind array: 64 rows, maximum of 256000 bytes
Continuation: none specified
Path used: Conventional

Table MIS.HASP_LOAD, loaded from every logical record.
Insert option in effect for this table: REPLACE
TRAILING NULLCOLS option in effect

Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
FISCAL_YEAR FIRST * WHT CHARACTER
MONTH NEXT * WHT CHARACTER
DHA NEXT * WHT CHARACTER
SQL string for column : "case when :dha in (1000,3000) then 10 else to_number(substr(:dha,3,1)) end"
PRIMARY_CODE NEXT * WHT CHARACTER
SECONDARY_CODE NEXT * WHT CHARACTER
SECTOR_ID NEXT * WHT CHARACTER
SQL string for column : "case when :sector_id is not null then lpad(:sector_id, 2, '0') else null end"
LOGICAL_FACILITY_CODE NEXT * WHT CHARACTER
(FILLER FIELD)
FIN_BALANCE NEXT * WHT CHARACTER
SQL string for column : "to_number(:fin_balance, '999,999,999.99')"
FIN_BUDGET NEXT * WHT CHARACTER
SQL string for column : "to_number(:fin_budget, '999,999,999.99')"
STAT_BALANCE NEXT * WHT CHARACTER
SQL string for column : "to_number(:stat_balance, '999,999,999.99')"
STAT_BUDGET NEXT * WHT CHARACTER
SQL string for column : "to_number(:stat_budget, '999,999,999.99')"
FIRST_MONTH NEXT * WHT CHARACTER
SQL string for column : "case when :month in (1,2,3) then 1 when :month in (4,5,6) then 4 when :month in (7,8,9) then 7 when :month in (10,11,12) then 10 else 13 end"

Record 1: Rejected - Error on table MIS.HASP_LOAD, column FIN_BALANCE.
ORA-01722: invalid number

Record 2: Rejected - Error on table MIS.HASP_LOAD, column FIN_BALANCE.
ORA-01722: invalid number

Record 3: Rejected - Error on table MIS.HASP_LOAD, column STAT_BUDGET.
ORA-01722: invalid number

Record 4: Rejected - Error on table MIS.HASP_LOAD, column STAT_BUDGET.
ORA-01722: invalid number

slightwv (䄆 Netminder)

Not sure why one works and one doesn't. The only other think I can think to check is make sure the UNIX version of the data file is really tab characters separating the fields.

I suggest you open an SR with Oracle. This wouldn't be the first OS specific bug I've seen.

Naveen Kumar

Hi KathysFriend,

<<It's not that easy. The control files were the same - starting out that is - I wanted to eliminate the formatted numbers to see if that was the problem - so I changed the dat file and the ctl file to match. So I will start from scratch with the formatted numbers - works on Windows not on Unix. Here is the output:>>>

For this, can you give me the ctl file , data file and log file added as attachments to this post. I have SUN Solaris Unix OS and can get this tested if you need.

KathysFriend

ASKER

All of that is already included in the first question

Naveen Kumar

Hi,

It works perfectly fine for me. please see the attached questions ?

ok.. can you tell me the below ;

1) how did you get sample_data.txt in the unix directory ? did you create or copy or ftp or what ?

2) can you try to create the file "sample_data.txt" in the vi editor and then copy the data from the file which is opened in your windows os notepad and then paste it there for first few records. This will let us know whether there are any issues which the data is taken over to unix from windows. I believe thsi is the case.

Thanks
ee.doc

KathysFriend

ASKER

1) I received the file as an excel 2007 spreadsheet - I opened it in excel 2003 - cut and pasted into textpad and saved it - at this point it is a tab-delimited dat file. I used winscp to copy it to Unix. It is now the same format as all of the files that I use.
2) I think that you are right - I just need to know exactly where it fails in the process so that I can automate the whole process. I was able to load this file into vi and I could see ctrl-M at the end of each line. So I realized that it was in the wrong format. Since then, I received a new file and I went through the same process - in vi it looks ok - no ctrl-M at the end of the lines - but I still get the same error.

Any suggestions?

ASKER CERTIFIED SOLUTION

Naveen Kumar

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

KathysFriend

ASKER

That worked.

Naveen Kumar

so in simple, it is a format issue. I belive you need to change the option of how the file gets transmitted, any other options related to transfer of file in the winscp. as far as i can remember, there is some radio button or something like that in winscp to say that you want ascii mode file transfer or binary file transfer. may be you can change this to see if can get that working.

Thanks,

KathysFriend

ASKER

I will try that. This is so weird - I do this all the time and this file doesn't look any different from my other files.

Naveen Kumar

33833912 update says that whatever has been suggested as part of testing the issue has worked. So i believe that should be the answer here.