No question at this time
DBA Top 10
1 M. Cadot 17900
2 A. Kavsek 15900
3 M. Hidayathullah ... 9200
4 B. Vroman 8600
5 T. Boles 4900
6 P. Wisse 4700
7 J. Schnackenberg 3800
8 K. Van Nieuwenhov... 2100
9 G. Van Cauter 1400
10 A. Hudspith 800
10 T. P 800
About
DBA-Village
Download PLATO
The free tool for auditing and tuning your database
Version 55 now available
Sep 02, 2016
The DBA-Village forum
Forum as RSS
as RSS feed
Site Statistics
Ever registered users48079
Total active users1704
Act. users last 24h9
Act. users last hour0
Registered user hits last week295
Registered user hits last month1831
Go up

sqlldr ORA-01722: invalid number
Next thread: Matrix Statistics Report
Prev thread: Query Output Format

Message Score Author Date
Hi masters, I am trying to upload data from the...... javed akhtar Nov 14, 2017, 10:03
Hello, the <I>string of characters</I> 'NULL' i...... Score: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 Pts Bruno Vroman Nov 14, 2017, 10:41
Hello Bruno, but when i am inserting data from ...... javed akhtar Nov 14, 2017, 10:44
Hi, <pre> load data infile '/home/oracle/mcar/...... Score: 700 PtsScore: 700 PtsScore: 700 PtsScore: 700 PtsScore: 700 Pts Ales Kavsek Nov 14, 2017, 10:47
Hello Javed, try the following:<pre>SELECT TO_N...... Bruno Vroman Nov 14, 2017, 10:56
BTW, if you look at the "NEWSATE" column: I assum...... Bruno Vroman Nov 14, 2017, 11:49

Follow up by mail Click here


Subject: sqlldr ORA-01722: invalid number
Author: javed akhtar, India
Date: Nov 14, 2017, 10:03, 8 days ago
Os info: linux
Oracle info: 11g
Message: Hi masters,

I am trying to upload data from the flat file but I am getting mentioned error.

CONTROL FILE.
load data infile '/home/oracle/mcar/CAMPAIGN_EVENT.csv' insert into table event fields terminated by ","
TRAILING NULLCOLS
(EVENTID,
ID,
INTERFACE char,
EVENTTYPE char,
CMID,
NEWSTATE,
NEWPRIORITY,
EVENTTIME DATE "YYYY-MM-DD HH24:MI:SS",
USERNAME char,
STATUS char)


FILE.

1,1706,SMS,SUSPEND_CM,1706,NULL,NULL,2016-10-06 00:00:00,clm,done


TABLE.
SQL> desc event
Name Null? Type
----------------------------------------- -------- ----------------------------
EVENTID NOT NULL NUMBER(20)
ID NOT NULL NUMBER(11)
INTERFACE VARCHAR2(20)
EVENTTYPE NOT NULL VARCHAR2(20)
CMID NUMBER(11)
NEWSTATE VARCHAR2(20)
NEWPRIORITY NUMBER(11)
EVENTTIME DATE
USERNAME NOT NULL VARCHAR2(100)
STATUS NOT NULL VARCHAR2(100)

EORROR

Record 1: Rejected - Error on table EVENT, column NEWPRIORITY.
ORA-01722: invalid number

Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: sqlldr ORA-01722: invalid number
Author: Bruno Vroman, Belgium
Date: Nov 14, 2017, 10:41, 8 days ago
Score:   Score: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 Pts
Message: Hello,

the string of characters 'NULL' is not a valid number...

The "NULL" is misleading... It is not NULL like "unknown value", it is the string 'NULL' like it could be the string 'BULL'...

Best regards,

Bruno Vroman.
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: sqlldr ORA-01722: invalid number
Author: javed akhtar, India
Date: Nov 14, 2017, 10:44, 8 days ago
Message: Hello Bruno,

but when i am inserting data from sqlplus it is working fine.

SQL> insert into event values (1,1706,'SMS','SUSPEND_CM',1706,NULL,NULL,'06-OCT-2017','CLM','DONE');

1 row created.

kindly suggest how i can handle the null from sqlldr
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: sqlldr ORA-01722: invalid number
Author: Ales Kavsek, Slovenia
Date: Nov 14, 2017, 10:47, 8 days ago
Score:   Score: 700 PtsScore: 700 PtsScore: 700 PtsScore: 700 PtsScore: 700 Pts
Message: Hi,


load data infile '/home/oracle/mcar/CAMPAIGN_EVENT.csv' insert into table event fields terminated by ","
TRAILING NULLCOLS
(EVENTID,
ID,
INTERFACE char,
EVENTTYPE char,
CMID,
NEWSTATE,
NEWPRIORITY "DECODE(:newpriority,'NULL',null,:newpriority)",
EVENTTIME DATE "YYYY-MM-DD HH24:MI:SS",
USERNAME char,
STATUS char)

Regards,
Ales
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: sqlldr ORA-01722: invalid number
Author: Bruno Vroman, Belgium
Date: Nov 14, 2017, 10:56, 8 days ago
Message: Hello Javed,

try the following:
SELECT TO_NUMBER( '' ) FROM dual;

SELECT TO_NUMBER( NULL ) FROM dual;
SELECT TO_NUMBER( 'NULL' ) FROM dual;
Do you see the difference between second and third line? The second is the NULL meaning "unknown value", but the third is a string with letters N, U, L and L...

In your file you should not have ...,NULL,... but simply ...,,...

Note that in your insert you enclose SMS in parenthesis, as well as SUSPEND_CM, ... but not the strings NULL; why? because you add a "human mistake" by changing the string into what you want to mean...

Best regards,

Bruno
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: sqlldr ORA-01722: invalid number
Author: Bruno Vroman, Belgium
Date: Nov 14, 2017, 11:49, 8 days ago
Message: BTW,
if you look at the "NEWSATE" column: I assume that you expect it to contain nothing (or '' or NULL)... But it contains a string of 4 characters starting with 'N' and ending with 'L'...
;-)
Bruno
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here