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
KathysFriendDBAAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Naveen KumarConnect With a Mentor Production Manager / Application Support ManagerCommented:
can you please do this..so that i am sure that it is due to format issues.

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 and run the sql loader script. This will let us know whether there are any issues which the data is taken over to unix from windows. If this works fine for you, then we can concentrate to resolve the format issues/errors.
0
 
slightwv (䄆 Netminder) Commented:
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.
0
 
Walter RitzelSenior Software EngineerCommented:
Yeap, seems the ctl files are not the same... easy fix.
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
KathysFriendDBAAuthor Commented:
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
0
 
slightwv (䄆 Netminder) Commented:
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.
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
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.
0
 
KathysFriendDBAAuthor Commented:
All of that is already included in the first question
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
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
0
 
KathysFriendDBAAuthor Commented:
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?
0
 
KathysFriendDBAAuthor Commented:
That worked.
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
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,
0
 
KathysFriendDBAAuthor Commented:
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.
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.