Solved

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

Posted on 2010-09-24
14
1,129 Views
Last Modified: 2012-06-21
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
0
Comment
Question by:KathysFriend
  • 5
  • 5
  • 2
  • +1
14 Comments
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 33756461
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
 
LVL 15

Expert Comment

by:Walter Ritzel
ID: 33761487
Yeap, seems the ctl files are not the same... easy fix.
0
 

Author Comment

by:KathysFriend
ID: 33768581
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
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 33770059
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
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 33775110
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
 

Author Comment

by:KathysFriend
ID: 33809953
All of that is already included in the first question
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 33816028
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
 

Author Comment

by:KathysFriend
ID: 33821582
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
 
LVL 28

Accepted Solution

by:
Naveen Kumar earned 500 total points
ID: 33823613
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
 

Author Comment

by:KathysFriend
ID: 33833912
That worked.
0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 33836634
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
 

Author Comment

by:KathysFriend
ID: 33839177
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
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 34455977
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

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now