Solved

SQLLDR : ORA-01722

Posted on 2002-04-08
12
2,763 Views
Last Modified: 2012-02-23
Hello,

my table a with 3 column:
nr1 number,
datum date,
nr number


LOAD DATA
INFILE *
INTO TABLE a
append
FIELDS TERMINATED BY ';'
(NR1,
DATUM "to_date(:datum,'HH24:MI:SS')",
NR
)
BEGINDATA
3000;15:37:51;5
4000.000000;15:37:52;5
50000.0001;15:37:53;5
-------



Record 2: Rejected - Error on table A, column NR1.
ORA-01722: invalid number

Record 3: Rejected - Error on table A, column NR1.
ORA-01722: invalid number

What can i do?

0
Comment
Question by:belem
  • 5
  • 3
  • 2
  • +2
12 Comments
 
LVL 5

Expert Comment

by:Bajwa
ID: 6925519
I just tried it on 8.1.7 database with exactly above and it works for me...  

0
 
LVL 1

Author Comment

by:belem
ID: 6925546
Hei Bajwa,
dátabase is 8.1.7 and it don't works
0
 
LVL 1

Author Comment

by:belem
ID: 6925603

BEGINDATA
3000;15:37:51;5
4000.000000;15:37:52;5
50000.0000;15:37:53;5
60000,0005;15:37:53;5  <-- This works fine

SQL> select * from a;

DATUM                       NR        NR1
------------------- ---------- ----------
01.04.2002 15:37:53          5 60000.0005  <----
01.04.2002 15:37:51          5       3000
0
 
LVL 22

Expert Comment

by:DrSQL
ID: 6925631
belem,
   The problem is NR1 and the decimal point.  SQL*Loader doesn't handle formatted number conversions automatically.  You need FLOAT EXTERNAL.  Here's a link:

http://otn.oracle.com/docs/products/oracle8i/doc_library/817_doc/server.817/a76955/ch05.htm#27701

And here's a possible fix:

LOAD DATA
INFILE *
INTO TABLE a
append
FIELDS TERMINATED BY ';'
(NR1 external float,
DATUM "to_date(:datum,'HH24:MI:SS')",
NR
)
BEGINDATA
3000;15:37:51;5
4000.000000;15:37:52;5
50000.0001;15:37:53;5

I think that's the right place.

Good luck!
0
 
LVL 5

Expert Comment

by:Bajwa
ID: 6925639
Are you saying that using , instead of . and it works fine?

if I replace . with , it does not work for me.

what I did was created a table a and created a control file with copy and paste of your query.
------ begin -------
LOAD DATA
INFILE *
INTO TABLE a
append
FIELDS TERMINATED BY ';'
(NR1,
DATUM "to_date(:datum,'HH24:MI:SS')",
NR
)
BEGINDATA
3000;15:37:51;5
4000.000000;15:37:52;5
50000.0001;15:37:53;5
60000.0005;15:37:53;5
----- end ----------------------
and ran the sqlldr as

sqlldr username/password@connect-string control=filename

and it inserted four records but if I change . to , it only inserts three records.


SQL> select * from a;

       NR1 DATUM             NR
---------- --------- ----------
      3000 01-APR-02          5
      4000 01-APR-02          5
50000.0001 01-APR-02          5
60000.0005 01-APR-02          5
0
 
LVL 7

Expert Comment

by:Bigfam5
ID: 6925651
Since, it accepts 60000,0005 and not 4000.000000 for the NUMBER field;
you may have a problem in the way NLS_NUMERIC_CHARACTERS (sets the decimal character) initilization parameter.


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 5

Expert Comment

by:Bajwa
ID: 6925674
Yep.. that is it.. the default nls_numric_character is . but if you have set it to , in your session (or pfile i guess) then that is the problem, just use , as a numeric decimal character.

column value format a10
select value,name from v$parameter where upper(name) like '%NLS_NUMERIC_CHAR%'
/

will show you your value for this parameter.
0
 
LVL 1

Author Comment

by:belem
ID: 6925686
Hi DrSQL,

great, it works if I type "float external",

please post it as answer.
Thank You
0
 
LVL 1

Author Comment

by:belem
ID: 6925695
Hi DrSQL,

great, it works if I type "float external",

please post it as answer.
Thank You
0
 
LVL 1

Author Comment

by:belem
ID: 6925711
Hi DrSQL,

great, it works if I type "float external",

please post it as answer.
Thank You
0
 
LVL 22

Accepted Solution

by:
DrSQL earned 200 total points
ID: 6925771
belem,
   You can also use the "Accept Comment" button on my comment to accept it as the answer.  But, here it is in "Answer" form as well.

belem,
  The problem is NR1 and the decimal point.  SQL*Loader doesn't handle formatted number conversions
automatically.  You need FLOAT EXTERNAL.  Here's a link:

http://otn.oracle.com/docs/products/oracle8i/doc_library/817_doc/server.817/a76955/ch05.htm#27701

And here's a possible fix:

LOAD DATA
INFILE *
INTO TABLE a
append
FIELDS TERMINATED BY ';'
(NR1 external float,
DATUM "to_date(:datum,'HH24:MI:SS')",
NR
)
BEGINDATA
3000;15:37:51;5
4000.000000;15:37:52;5
50000.0001;15:37:53;5

I think that's the right place.

Good luck!
0
 

Expert Comment

by:Pragnya12
ID: 37635471
Hi All,

I have a control file mentioned below :
 
LOAD DATA
APPEND INTO TABLE xxhr_salary_history_tab
FIELDS TERMINATED BY ","  
TRAILING  NULLCOLS
(
   employee_number              CHAR  "TRIM(:employee_number)"
  ,effective_date                      DATE  'DD-MON-YYYY'
  ,proposed_salary                   DECIMAL EXTERNAL    
)

and the data which I am trying to upload is a csv file with the sample data as:

9083,01-Sep-2011,10770.00
1177,01-Sep-2011,37740.00
8691,01-Sep-2011,122400.00

I m getting the below error when I run the request :


Control File:   /dpzcui/applmgr/CUSTOM/xbol/12.0.0/bin/XXHR_SAL_HIST_LOAD.ctl
Data File:      /interface/infpzcui/DPZCUI/incoming/TESTBeauty_Salary_Oct2011.csv
  Bad File:     /dpzcui/applcsf/out/DPZCUI_auohspzcu02/o530682.out
  Discard File: /dpzcui/applcsf/out/DPZCUI_auohspzcu02/o530682.out
 (Allow all discards)

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

Table "BOLINF"."XXHR_SALARY_HISTORY_TAB", loaded from every logical record.
Insert option in effect for this table: APPEND
TRAILING NULLCOLS option in effect

   Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
EMPLOYEE_NUMBER                     FIRST     *   ,       CHARACTER            
    SQL string for column : "TRIM(:employee_number)"
EFFECTIVE_DATE                       NEXT     *   ,       DATE DD-MON-YYYY    
PROPOSED_SALARY                      NEXT     *   ,       CHARACTER            

Record 2: Rejected - Error on table "BOLINF"."XXHR_SALARY_HISTORY_TAB", column PROPOSED_SALARY.
ORA-01722: invalid number

Record 3: Rejected - Error on table "BOLINF"."XXHR_SALARY_HISTORY_TAB", column PROPOSED_SALARY.
ORA-01722: invalid number

Record 4: Rejected - Error on table "BOLINF"."XXHR_SALARY_HISTORY_TAB", column PROPOSED_SALARY.
ORA-01722: invalid number

Record 5: Rejected - Error on table "BOLINF"."XXHR_SALARY_HISTORY_TAB", column PROPOSED_SALARY.
ORA-01722: invalid number

Record 6: Rejected - Error on table "BOLINF"."XXHR_SALARY_HISTORY_TAB", column PROPOSED_SALARY.
ORA-01722: invalid number

Record 7: Rejected - Error on table "BOLINF"."XXHR_SALARY_HISTORY_TAB", column PROPOSED_SALARY.
ORA-01722: invalid number

Record 8: Rejected - Error on table "BOLINF"."XXHR_SALARY_HISTORY_TAB", column PROPOSED_SALARY.
ORA-01722: invalid number

Record 9: Rejected - Error on table "BOLINF"."XXHR_SALARY_HISTORY_TAB", column PROPOSED_SALARY.
ORA-01722: invalid number

Record 10: Rejected - Error on table "BOLINF"."XXHR_SALARY_HISTORY_TAB", column PROPOSED_SALARY.
ORA-01722: invalid number

Record 11: Rejected - Error on table "BOLINF"."XXHR_SALARY_HISTORY_TAB", column PROPOSED_SALARY.
ORA-01722: invalid number

Record 12: Rejected - Error on table "BOLINF"."XXHR_SALARY_HISTORY_TAB", column PROPOSED_SALARY.
ORA-01722: invalid number

Record 13: Rejected - Error on table "BOLINF"."XXHR_SALARY_HISTORY_TAB", column PROPOSED_SALARY.
ORA-01722: invalid number

Record 14: Rejected - Error on table "BOLINF"."XXHR_SALARY_HISTORY_TAB", column PROPOSED_SALARY.
ORA-01722: invalid number

Record 15: Rejected - Error on table "BOLINF"."XXHR_SALARY_HISTORY_TAB", column PROPOSED_SALARY.
ORA-01722: invalid number

Record 16: Rejected - Error on table "BOLINF"."XXHR_SALARY_HISTORY_TAB", column PROPOSED_SALARY.
ORA-01722: invalid number

Record 17: Rejected - Error on table "BOLINF"."XXHR_SALARY_HISTORY_TAB", column PROPOSED_SALARY.
ORA-01722: invalid number

Record 18: Rejected - Error on table "BOLINF"."XXHR_SALARY_HISTORY_TAB", column PROPOSED_SALARY.
ORA-01722: invalid number

Record 19: Rejected - Error on table "BOLINF"."XXHR_SALARY_HISTORY_TAB", column PROPOSED_SALARY.
ORA-01722: invalid number

Record 20: Rejected - Error on table "BOLINF"."XXHR_SALARY_HISTORY_TAB", column PROPOSED_SALARY.
ORA-01722: invalid number

Record 21: Rejected - Error on table "BOLINF"."XXHR_SALARY_HISTORY_TAB", column PROPOSED_SALARY.
ORA-01722: invalid number

Record 22: Rejected - Error on table "BOLINF"."XXHR_SALARY_HISTORY_TAB", column PROPOSED_SALARY.
ORA-01722: invalid number

Record 23: Rejected - Error on table "BOLINF"."XXHR_SALARY_HISTORY_TAB", column PROPOSED_SALARY.
ORA-01722: invalid number

Record 24: Rejected - Error on table "BOLINF"."XXHR_SALARY_HISTORY_TAB", column PROPOSED_SALARY.
ORA-01722: invalid number

Record 25: Rejected - Error on table "BOLINF"."XXHR_SALARY_HISTORY_TAB", column PROPOSED_SALARY.
ORA-01722: invalid number

Record 26: Rejected - Error on table "BOLINF"."XXHR_SALARY_HISTORY_TAB", column PROPOSED_SALARY.
ORA-01722: invalid number

Record 27: Rejected - Error on table "BOLINF"."XXHR_SALARY_HISTORY_TAB", column PROPOSED_SALARY.
ORA-01722: invalid number

Record 28: Rejected - Error on table "BOLINF"."XXHR_SALARY_HISTORY_TAB", column PROPOSED_SALARY.
ORA-01722: invalid number

Record 29: Rejected - Error on table "BOLINF"."XXHR_SALARY_HISTORY_TAB", column PROPOSED_SALARY.
ORA-01722: invalid number

Record 30: Rejected - Error on table "BOLINF"."XXHR_SALARY_HISTORY_TAB", column PROPOSED_SALARY.
ORA-01722: invalid number

Record 31: Rejected - Error on table "BOLINF"."XXHR_SALARY_HISTORY_TAB", column PROPOSED_SALARY.
ORA-01722: invalid number

Record 32: Rejected - Error on table "BOLINF"."XXHR_SALARY_HISTORY_TAB", column PROPOSED_SALARY.
ORA-01722: invalid number

Record 33: Rejected - Error on table "BOLINF"."XXHR_SALARY_HISTORY_TAB", column PROPOSED_SALARY.
ORA-01722: invalid number

Record 34: Rejected - Error on table "BOLINF"."XXHR_SALARY_HISTORY_TAB", column PROPOSED_SALARY.
ORA-01722: invalid number

Record 35: Rejected - Error on table "BOLINF"."XXHR_SALARY_HISTORY_TAB", column PROPOSED_SALARY.
ORA-01722: invalid number

Record 36: Rejected - Error on table "BOLINF"."XXHR_SALARY_HISTORY_TAB", column PROPOSED_SALARY.
ORA-01722: invalid number

Record 37: Rejected - Error on table "BOLINF"."XXHR_SALARY_HISTORY_TAB", column PROPOSED_SALARY.
ORA-01722: invalid number

Record 38: Rejected - Error on table "BOLINF"."XXHR_SALARY_HISTORY_TAB", column PROPOSED_SALARY.
ORA-01722: invalid number

Record 39: Rejected - Error on table "BOLINF"."XXHR_SALARY_HISTORY_TAB", column PROPOSED_SALARY.
ORA-01722: invalid number

Record 40: Rejected - Error on table "BOLINF"."XXHR_SALARY_HISTORY_TAB", column PROPOSED_SALARY.
ORA-01722: invalid number

Record 41: Rejected - Error on table "BOLINF"."XXHR_SALARY_HISTORY_TAB", column PROPOSED_SALARY.
ORA-01722: invalid number

Record 42: Rejected - Error on table "BOLINF"."XXHR_SALARY_HISTORY_TAB", column PROPOSED_SALARY.
ORA-01722: invalid number

Record 43: Rejected - Error on table "BOLINF"."XXHR_SALARY_HISTORY_TAB", column PROPOSED_SALARY.
ORA-01722: invalid number

Record 44: Rejected - Error on table "BOLINF"."XXHR_SALARY_HISTORY_TAB", column PROPOSED_SALARY.
ORA-01722: invalid number

Record 45: Rejected - Error on table "BOLINF"."XXHR_SALARY_HISTORY_TAB", column PROPOSED_SALARY.
ORA-01722: invalid number

Record 46: Rejected - Error on table "BOLINF"."XXHR_SALARY_HISTORY_TAB", column PROPOSED_SALARY.
ORA-01722: invalid number

Record 47: Rejected - Error on table "BOLINF"."XXHR_SALARY_HISTORY_TAB", column PROPOSED_SALARY.
ORA-01722: invalid number

Record 48: Rejected - Error on table "BOLINF"."XXHR_SALARY_HISTORY_TAB", column PROPOSED_SALARY.
ORA-01722: invalid number

Record 49: Rejected - Error on table "BOLINF"."XXHR_SALARY_HISTORY_TAB", column PROPOSED_SALARY.
ORA-01722: invalid number

Record 50: Rejected - Error on table "BOLINF"."XXHR_SALARY_HISTORY_TAB", column PROPOSED_SALARY.
ORA-01722: invalid number

Record 51: Rejected - Error on table "BOLINF"."XXHR_SALARY_HISTORY_TAB", column PROPOSED_SALARY.
ORA-01722: invalid number

Record 52: Rejected - Error on table "BOLINF"."XXHR_SALARY_HISTORY_TAB", column PROPOSED_SALARY.
ORA-01722: invalid number


MAXIMUM ERROR COUNT EXCEEDED - Above statistics reflect partial run.

Table "BOLINF"."XXHR_SALARY_HISTORY_TAB":
  1 Row successfully loaded.
  51 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.


Space allocated for bind array:                  49536 bytes(64 rows)
Read   buffer bytes: 1048576

Total logical records skipped:          0
Total logical records read:            64
Total logical records rejected:        51
Total logical records discarded:        0

Run began on Thu Feb 23 08:48:55 2012
Run ended on Thu Feb 23 08:48:57 2012

Elapsed time was:     00:00:01.58
CPU time was:         00:00:00.01



+---------------------------------------------------------------------------+
Executing request completion options...


Finished executing request completion options.


Thanks for the help
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

Suggested Solutions

Title # Comments Views Activity
Oracle regular expression 6 30
compre toata in where clue oracle 4 44
Oracle Subquery bad Join 11 46
sql query 5 52
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.

762 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

19 Experts available now in Live!

Get 1:1 Help Now