No question at this time
DBA Top 10
1 M. Cadot 15900
2 B. Vroman 15500
3 A. Kavsek 10000
4 T. Boles 6800
5 J. Schnackenberg 5800
6 M. Hidayathullah ... 3900
7 P. Wisse 1800
8 T. P 700
9 B. Derous 500
9 R. Wauben 500
9 H. Steijntjes 500
About
DBA-Village
The DBA-Village forum
Forum as RSS
as RSS feed
Site Statistics
Ever registered users48208
Total active users1645
Act. users last 24h7
Act. users last hour0
Registered user hits last week163
Registered user hits last month788
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, 218 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, 218 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, 218 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, 218 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, 218 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, 218 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